Mysql master-slave synchronization, why is the main sequence to unite binlog is random read from the library?


Have bosses to tell about it, it is mysql particular good is like that, or you can set?
Thank you very much

CodePudding user response:

For library parallel copying ability
Sql_thread coordinator is the coordinator before, now only responsible for reading log in the relay and distributed transactions, real update log into a worker thread, and the number of worker threads is determined by the parameters slave_parallel_workers, when distributed transaction meet the following two basic principles:
1, do not make updates to cover, which requires to update the same lines of two transactions must be distributed to the same worker
2, the same can't be opened, must be in the same worker

Compared with the parallel distribution policy according to table, press line parallel strategy in determining thread distribution, need to consume more computing resources, constraints:
1, to be able to resolve the oil from the binlog table name, primary key, and the only index value, which is the main library of the binlog format is row
2, the table must have primary key
3, there can be no foreign keys, on the table if there is a foreign key, cascade update do not record in binlog, such conflict detection inaccurate
The three constraints, is the DBA business developers must abide by the use of online,

Mysql 5.6 supports the parallel copying, there are two advantages
1, construct the hash value soon, just need the library; And an instance on the db number also not many, there will be no 1 million
2, does not require the binlog format, because it can be very easy to get the library statement format binlog

MariaDB parallel replication strategy
1, can be in the same group of committed transaction, must not modify the same line;
2, can be executed in parallel on the main library affairs, for the library, but there must be something can be executed in parallel on

Logic is as follows:
1, in a group of committed transaction together, there is a same commit_id, the next group is commit_id + 1
2, commit_id wrote binlog directly inside the
3, to prepare the library application, the same commit_id thing distributed to multiple workers follow
4, which, after the completion of a set of all executive coordinator to take the next batch


Mysql5.7 parallel replication strategy, the parameters of the slave -- the parallel type parallel replication strategy to control the
1, configured for the database, said using mysql5.6 version of the parallel strategy according to the library
2, the configuration of logical_clock means is similar to the strategy of mariaDB but mysql 5.7 this strategy for parallelism has been optimized, at the same time in the execution status of all transactions may be executed in parallel,
In the two-phase commit process, can only achieve redolog prepare stage, it means the transaction has passed the lock conflict inspection,
Mysql 5.7 parallel replication strategy thought is:
1, at the same time prepare state of affairs in the case can be parallel
2, in the prepare state of affairs, and between the commit state of affairs, in the case of library also can parallel execution time,

Binlog group submitted has two parameters:
Binlog_group_commit_sync_delay said how many microsecond delay after call fsync
Binlog_group_commit_sync_no_delay_count said accumulated many times after call fsync
These two parameters are deliberately stretched binlog from the write to fsync time, to reduce the plate number, here can be used to make more transaction in the prepare phase, thus increasing the case library replication parallelism, these two parameters can be either the main library submit slowly, also can let for libraries to perform faster,

Mysql 5.7.22 increase parallel replication strategy, based on the parallel copying wirteset, corresponding binlog_transaction_dependency_tracking add a parameter is used to control whether to enable this new strategy
1, commit_order said is described above, according to at the same time to prepare and commit to determine whether can be the strategy of parallel
2, writeset represents each row for transactions involving the update compute hash value, this line of collection writeset, if two transactions without the same operation line, meaning that they are writeset no intersection, can be parallel,
3, writeset_session, is on the basis of wrieset one more constraints, namely in the main library on the same thread has executed two transactions in case library, to ensure that the same order first,
Hash value is through the library name + table name + index name + value calculated,
Compared with mysql 5.5 line according to the distribution strategy has the following advantages
1, after writeset is generated in the main library, write directly to the inside of the binlog, thus in the library of execution time don't need to parse binlog content, rows of data (event), save a lot of computation
2, do not need to put the whole transaction binlog sweep over which the worker can decide distribution, more save memory
3, due to the case library of distribution policy is not dependent on the binlog content, so the binlog is also can be the statement formats,

CodePudding user response:

Just copy type