A BUG_PHP tutorial related to MySQL rpl_semi_sync_master_timeout

A BUG related to MySQL rpl_semi_sync_master_timeout

1. Background

When deploying an HA system based on MySQL native replication, it was found that in semi-sync mode, semi-sync If the timeout period for downgrading replication to asynchronous replication is set very long, it will seriously affect performance. This is a very strange phenomenon.

2. Phenomenon

Combine different parameters and use sysbench for stress testing.

sysbench --db-driver=mysql --mysql-db=test2 --mysql-host=srdsdevapp69 --mysql-table-engine=innodb --oltp-table-size=5000000 --num-threads=10 --max-time=10 --max-requests=0 --oltp-test-mode=complex --oltp-read-only=off --test=/opt/sysbench-0.5/sysbench/tests/db/insert.lua run

The results are as follows:

rpl_semi_sync_master_enabled	rpl_semi_sync_master_timeout	qps	Remarks
on	21474836480	13.99	About 248 days
	2147483648	196.3	About 24.8 days
	214748364	1251.67	About 2.5 days
	86400000	2146.96	1 day
	43200000	3211.17	12 hours
	21600000	3583.02	6 hours
	10000	3637.16	10 seconds (default)
off	-	8926.76

3. Reason

It is easy to see from the above table that when rpl_semi_sync_master_timeout is large, the execution time of each query is proportional to rpl_semi_sync_master_timeout.

Why does such a weird thing happen? Open the MySQL code and the truth will be revealed immediately!

PluginSemisyncseMisync_master.cc:

#Define time_thousand 1000
1. 
   
#Define time_million 1000000
2. 
   
3. #define TIME_BILLION 1000000000
   
4. ...
   
5. int ReplSemiSyncMaster::commitTrx(const char* trx_wait_binlog_name,
   
6. my_off_t trx_wait_binlog_pos)
   
7. {
   
8. ...
   
9. unsigned long long diff_nsecs =
   
10. start_ts.tv_nsec (unsigned long long)wait_timeout_ * TIME_MILLION;
    
11. abstime.tv_sec = start_ts.tv_sec;
    
12. while (diff_nsecs >= TIME_BILLION)//This while loop is the culprit The culprit! ! !
    
13. {
    
14. abstime.tv_sec ;
    
15. diff_nsecs -= TIME_BILLION;
    
16. }
    
17. abstime.tv_nsec = diff_nsecs;
    
18. ...
    
19. }

There is a while above Loop, the number of loops is equal to the number of seconds corresponding to rpl_semi_sync_master_timeout, that is to say, if it is set to 300 days, it will loop 25,920,000 times, which is not slow!

4. Repair

After replacing the while in that code with equivalent writing, the problem is solved. The measured qps is around 3700, which has nothing to do with rpl_semi_sync_master_timeout.

4.1 Modify code

pluginsemisyncsemisync_master.cc:

1. # diff plugin/semisync/semisync_master.cc plugin/semisync/semisync_master.cc_bak
   
2. 687,688c687,688
   
3. < start_ts.tv_nsec ((unsigned long long)wait_timeout_ % TIME_THOUSAND) * TIME_MILLION;
   
4. < abstime.tv_sec = start_ts.tv_sec ( unsigned long long)wait_timeout_ / TIME_THOUSAND;
   
5. ---
   
6. > start_ts.tv_nsec (unsigned long long)wait_timeout_ * TIME_MILLION;
   
7. > abstime.tv_sec = start_ts.tv_sec;

4.2 Compilation

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_FAST_MUTEXES=1make

Note: The above compilation options I filled it out quite casually. I just copied it from the Internet and then changed it. I just wanted it to be compiled and passed.

4.3 Copy the semi-synchronization plug-in

chown mysql:mysql  ./plugin/semisync/semisync_master.socp -rf ./plugin/semisync/semisync_master.so /usr/local/mysql/lib/mysql/plugin/semisync_master.socp -rf ./plugin/semisync/semisync_master.so /usr/local/mysql/lib/plugin/semisync_master.so

4.4 Restart MySQL

5. Supplement

The bug has been resolved Report to the official MySQL website, http://bugs.mysql.com/80651

http://www.bkjia.com/PHPjc/1108686.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1108686.htmlTechArticleA BUG related to MySQL rpl_semi_sync_master_timeout 1. When deploying the HA system based on MySQL native replication in the background, it was found that semi-synchronization In mode, the timeout of semi-synchronous replication is downgraded to asynchronous replication...