search

huadaonan / mysql-master-ha

Automatically exported from code.google.com/p/mysql-master-ha
0 stars 0 forks source link

mysql innodb locks during checks on slaves #98

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
Switching to Debian 7 and percona-mysql-5.5.16 amd64 from percona repository 
started experiencing unusual slaves slowdowns. After some research, I found 
that there are some strange locks happening during "drop table" (already 
emptied). While not discussing why drop empty table on server with big (50Gb) 
innodb buffer pool slowing down servers drastically, I created a workaround 
adding "engine=myisam" to /usr/local/bin/apply_diff_relay_logs line 370:

"$_mysql --user=$_escaped_slave_user --password=$_escaped_slave_pass 
--host=$opt{slave_ip} --port=$opt{slave_port} -e \"set sql_log_bin=0; create 
table if not exists mysql.apply_diff_relay_logs_test(id int) engine=myisam; 
insert into mysql.apply_diff_relay_logs_test values(1); update 
mysql.apply_diff_relay_logs_test set id=id+1 where id=1; delete from 
mysql.apply_diff_relay_logs_test; drop table 
mysql.apply_diff_relay_logs_test;\""

After that, problem has gone as expected.
Probably this change could be useful in main code.

Debian 7.6, linux 3.2.58, mha-node 0.56, percona-mysql-5.5.16 x64

Original issue reported on code.google.com by mikhail....@ask.fm on 26 Sep 2014 at 2:37