Home > php教程 > php手册 > MySQL rpl_semi_sync_master_timeout相关的一件BUG

MySQL rpl_semi_sync_master_timeout相关的一件BUG

WBOY
Release: 2016-06-13 08:44:45
Original
1495 people have browsed it

MySQL rpl_semi_sync_master_timeout相关的一件BUG

1.背景

部署基于MySQL原生复制的HA系统时,发现在半同步模式下,半同步复制降级为异步复制的超时时间如果设得很长,会严重影响性能高,这是个很奇怪的现象。


2.现象

组合不同参数,用sysbench做压力测试。

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


结果如下:

rpl_semi_sync_master_enabled rpl_semi_sync_master_timeout qps 备注
on 21474836480 13.99 约248天
2147483648 196.3 约24.8天
214748364 1251.67 约2.5天
86400000 2146.96 1天
43200000 3211.17 12小时
21600000 3583.02 6小时
10000 3637.16 10秒(默认值)
off - 8926.76


3. 原因

从上面的表不难看出,当rpl_semi_sync_master_timeout很大时,每个查询的执行时间和rpl_semi_sync_master_timeout成正比。

为什么会出现这么奇葩的事?翻开MySQL的代码,立刻真相大白!


plugin\semisync\semisync_master.cc:

  1. #define TIME_THOUSAND 1000
  2. #define TIME_MILLION 1000000
  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)//这个while循环是罪魁祸首!!!
  13. {
  14. abstime.tv_sec++;
  15. diff_nsecs -= TIME_BILLION;
  16. }
  17. abstime.tv_nsec = diff_nsecs;
  18. ...
  19. }

上面有个while循环,循环次数等于rpl_semi_sync_master_timeout对应的秒数,也就是说,如果设置成300天的话,要循环25920000次,不慢才怪!



4. 修复

把那段代码中的while替换等价的写法后,问题解决。测出的qps在3700左右,和rpl_semi_sync_master_timeout无关。

4.1 修改代码

plugin\semisync\semisync_master.cc:

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


4.2 编译

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
Copy after login

注:上面的编译选项填的比较随意,从网上随便抄了后再改的,只求编译通过。

4.3 拷贝半同步插件

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
Copy after login


4.4重启MySQL

5. 补充

该Bug已报告到MySQL官方网站,http://bugs.mysql.com/80651

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Recommendations
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template