When using CDC data sources in Jet, one must also take care that the source databases are set up correctly, meaning they have all the features, required for change data capture, enabled.
The MySQL CDC source needs a MySQL database user which will be used for
connecting to the database. For how to create one, see the "CREATE USER
Statement" in the MySQL Reference Manual
8.0 - note
mysql_native_password is no longer the default authentication
plugin in MySQL 8; you will need to specify it using the
IDENTIFIED WITH mysql_native_password BY '<password>' construct).
- SELECT: enables selection of rows from tables (5.7, 8.0)
- RELOAD: enables usage of the FLUSH statement to clear or reload internal caches, flush tables or acquire locks (5.7, 8.0)
- SHOW DATABASES: enables usage of the SHOW DATABASES statement (5.7, 8.0)
- REPLICATION SLAVE: enables reading the server's binlog (source of the CDC events) (5.7, 8.0)
- REPLICATION CLIENT: enables usage of SHOW MASTER STATUS, SHOW SLAVE STATUS and SHOW BINARY LOGS statements (5.7, 8.0)
server-id = 223344 log_bin = mysql-bin binlog_format = ROW binlog_row_image = FULL expire_logs_days = 10
The semantics of these options are as follows:
- server-id: must be unique withing the MySQL cluster (5.7, 8.0)
- log_bin: base name of the sequence of binlog files (5.7, 8.0)
- binlog_format: must be set to "ROW" in order for the CDC source to work properly (5.7, 8.0)
- binlog_row_image: must be set to "FULL" in order for the CDC source to work properly (5.7, 8.0)
- expire_log_days: number of days for automatic binlog file removal (5.7, 8.0)
While the database server is running, the active values of these variables can be checked with the help of the "SHOW VARIABLES Statement" (5.7, 8.0). It's worth pointing out that the names of the options sometimes differ from the names of the MySQL system variables they set. For example:
SHOW VARIABLES LIKE 'server_id';
When an initial consistent snapshot is made for large databases, your established connection could timeout while the tables are being read. You can prevent this behavior by configuring interactive_timeout (5.0, 8.0) and wait_timeout (5.0, 8.0) in your MySQL configuration file.
The PostgreSQL change data capture connector works by exploiting the logical decoding feature of the database, first introduced in version 9.4. This version however is no longer supported. Debezium recommends running change data capture on version 9.6 or later.
PostgreSQL's logical decoding feature is a mechanism which allows the extraction of the changes which were committed to the transaction log and the processing of these changes in a user-friendly manner via the help of an output plug-in.
The output plug-ins currently available are:
decoderbufs, maintained by the Debezium community, based on ProtoBuf
wal2json, maintained by the wal2json community, based on JSON
pgoutput, the standard logical decoding plug-in in PostgreSQL 10 and later, maintained by the Postgres community
pgoutput plug-in is always present and requires no explicit
installation, for the other two follow the instructions provided by
Note: for simplicity Debezium also provides a Docker image based on a vanilla PostgreSQL server image on top of which it compiles and installs all above mentioned plugins.
Running change data capture on a PostgreSQL server requires certain configuration options to be set accordingly. This can be done either by
- editing the
postgresql.conffile, or by
- using the ALTER SYSTEM command
The important properties to set are:
# MODULES shared_preload_libraries = 'decoderbufs,wal2json' # REPLICATION wal_level = logical max_wal_senders = 1 max_replication_slots = 1
shared_preload_libraries contains a comma separated list of installed
wal_levels is used to tell the server to use logical
decoding with the write-ahead log.
Logical decoding uses replication
Replication slots retain WAL data even during connector outages. For
this reason it is important to monitor and limit replication slots to
avoid too much disk consumption and other conditions that can happen,
such as catalog bloat if a slot stays unused for too long. This is why
max_replication_slots parameters are set with
the smallest possible values.
Replication can only be performed by a database user (specifically the
one we set up our CDC connector with) only if the user has appropriate
permissions. The permissions needed are
For setting up database users/roles see the PostgreSQL documentation, but basically the essential command is:
CREATE ROLE name REPLICATION LOGIN;
Note: database super-users already have all the permissions needed by replication too.
Replication can only be performed for a configured number of hosts. The
PostgreSQL server needs to allow access from the host the CDC connector
is running on. To specify such client authentication
options add following lines to the end of the
local replication user trust host replication user 127.0.0.1/32 trust host replication user ::1/128 trust
This example tells the server to allow replication for the specified
user locally or on
localhost, using IPv4 or IPv6.
Streaming CDC data from other databases supported by Debezium is possible in Jet by using the generic Debezium source. This deployment guide however only covers the databases we have first class support for. For the other ones pls. refer to the Debezium documentation:
Enabling the features needed for the MySQL CDC connector (in particular the binlog) has a performance impact on the database. From our measurements we estimated it to around 15%, but this is very dependent on your particular workload. What's certain is that it's not negligible.
Enabling CDC requires pretty much the same settings as replication, so if you already have replication enabled, then there should be no further performance penalty. However the CDC source is an extra replication client, so if you already have replication you might consider connecting the CDC source to your replica, instead of the main database.
Another reason why using a replica for CDC might be useful, is that for large databases the snapshot taken by the CDC source when it first starts can take a significant amount of time and will put heavy load on the database during that period. This might affect the performance of other transactions.
Using Global Transaction IDs
When using a replica for the CDC source and in general when the MySQL server cluster has multiple member it's a good idea to give yourself the option of connecting the CDC source to any one of the members. The source does track the transaction ids in the binlog and can connect to the binlog of another MySQL server on restart, but in order for the ids to match between servers Global Transaction Identifiers (GTIDs) must be enabled in them.
As we've mentioned in the PostgreSQL database setup section the connector uses logical decoding replication slots. All PostgreSQL versions (up to 12) only support logical replication slots on primary servers.
This means that it's not possible to limit the performance impact of running change data capture on replicas. This weakness is somewhat offset by the fact that the logical replication process seems quite limited as far as the amount of resources it can acquire. When measuring its output it behaves quite like a single threaded process, which can't saturate neither CPU nor the network.
In our tests we didn't manage to make it output much more than 20,000 records/second, so on a powerful server running the database it shouldn't affect normal operations too severely.
PostgreSQL failure tolerance associated with replication slots is somewhat lacking in certain aspects. The CDC connector can quite nicely deal with its own restart or connection loss to the primary database, but only as long as replication slots remain intact. Replication slots are not themselves synced to physical replicas, so you can’t continue to use a slot after a master failure results in promotion of a standby.
There are discussions in the PostgreSQL community around a feature called failover slots which would help mitigate this problem, but as of version 12 they have not been implemented yet.