Description:
We are in a master/master solution with all reads/writes going to one master (primary) and second server used for backups and failover (secondary).
On 2 of our 31 master/master pairs, when we issue stop slave, both slave threads stop immediately, but IOwait on the CPU doubles - triples, and continues until "slave start" is issued. No other threads are connected (except the replication connection from the primary master) or performing work. The same issue occurs if the DB is brought up with slave skip start. Once the slave thread is restarted, IO goes back to normal.
Centos 5, running mysql 5.0.68 ent
Box 1: 1 dual core CPU, 8 GB RAM,
I have reduced global innodb_max_dirty_pages_pct and gotten dirty buffers down to 0 and the io still runs hot.
Example iostat -x during this period:
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 8.50 16.00 101.50 832.00 6644.00 63.63 2.04 17.52 8.44 99.15
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-5 0.00 0.00 16.00 110.50 832.00 6868.00 60.87 2.04 16.28 7.84 99.15
Using show innodb status, I see that the log_buffer still seems to be flushing and sequence # incrementing. Here are two samples, with ~60 seconds between while slave is stopped.
---
LOG
---
Log sequence number 97 2015658440
Log flushed up to 97 2015646953
Last checkpoint at 97 1926146586
0 pending log writes, 0 pending chkp writes
205885465 log i/o's done, 2.33 log i/o's/second
---
LOG
---
Log sequence number 97 2017042788
Log flushed up to 97 2017017580
Last checkpoint at 97 1927895762
0 pending log writes, 0 pending chkp writes
205885643 log i/o's done, 2.21 log i/o's/second
Pertinent my.cnf parameters (consistent across other boxes on other clusters that do not have these problems)
+---------------------------------+------------------------+
| innodb_additional_mem_pool_size | 33554432 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 5368709120 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | /var/lib/mysql |
| innodb_adaptive_hash_index | ON |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 120 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 1363148800 |
| innodb_log_files_in_group | 3 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 25 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
So, why does IO go through the roof on these two servers when the slave is stopped and no other connections occur?
Laine
How to repeat:
It only happens on two of our 31 secondary servers, but happens everytime we stop the slave, and continues until slave is started.
