Help Center/ Data Replication Service/ FAQs/ Product Consulting/ How Does MySQL Data Synchronization Affect the Source and Destination Databases?
Updated on 2024-09-25 GMT+08:00
View PDF
Share

How Does MySQL Data Synchronization Affect the Source and Destination Databases?

The following uses a MySQL-to-MySQL large-scale synchronization task as an example. The number of database connections varies depending on the specifications.

Impact on the Source

  • During the initialization of a full migration or synchronization task, DRS needs to query all inventory data in the source database. DRS uses simple SQL statements to query data, and the query speed is limited by the I/O performance and network bandwidth of the source database. Generally, if the bandwidth is not limited, the query workload of the source database will be increased by 50 MB/s and 2 to 4 vCPUs will be occupied. If the source database is read concurrently, about 6 to 10 sessions are occupied.
    • Fewer than eight sessions are used to query some system tables, such as tables, views, and columns in the information_schema database, in the source database.
    • Fewer than four connections are used to query table shards in the source database. For example, in the following statement, the conditions following where in the SELECT statement contain only the primary key or unique key. 
      select id from *** where id>12345544 and limit 10000,1;
    • Fewer than four connections are used to query SQL statements. For example, in the following statement, the information after select is all column names in the table, and the conditions following where contain only the primary key or unique key if no data is filtered. 
      select id,name,msg from *** where id>12345544 and id<=12445544;
    • The SQL statement for locking a table without a primary key is similar to the following statement. The table is locked to obtain the consistency point of the table without a primary key. After the table is locked, a connection is obtained to unlock the table. 
      flush table *** with read lock
      lock table *** read
  • In the incremental phase, there is no stress on the CPU of the source database. Only one dump connection is used to obtain incremental logs in real time, and the I/O and bandwidth are affected at the same rate as the binlog generation rate.

Impact on the Destination Database

  • During the initialization of a full migration or synchronization task, DRS needs to write structures, inventory data, and indexes of the source database to the destination database in sequence. Different processes are not performed concurrently. Generally, the total number of active connections is less than 8.
    • Fewer than eight sessions are used to create structures.
    • Fewer than eight sessions are writing data. Example: 
      insert into *** (id,name,msg) values (***);
    • Fewer than eight sessions are used to create indexes. Example: 
      alter table *** add index ***;
  • In the incremental phase, DRS parses the incremental data in the logs of the source database into SQL statements and executes the SQL statements in the destination database. Generally, the total number of connections is less than 64.
    • DDL statements of a single connection are executed in serial mode. When a DDL statement is executed, no other DML statement is executed.
    • There are a maximum of 64 DML connections (short connections, with a default timeout interval of 30 seconds). The DML statements include insert, update, delete, and replace.

To evaluate the impact on the source database, you can create a test task and adjust the migration policy by using rate limiting or run the test during off-peak hours.

Parent topic: Product Consulting