Do two production Linux host of master-slave, have been running very well, yesterday suddenly appear huge delay, run Exec_Master_Log_Pos after a stop motion, and in a minute, so the reciprocating, check each parameter display is normal, no adjustment before, but now the show will appear in the processlist Reading event from the relay log, this state disappear after a meeting, later appeared, disk IO improved after adjusting the sync_binlog=1, the default is 0, the in IO write very low Exec_Master_Log_Pos could not change, so please have a look at the problem where help, thanks a lot,
Innodb status will appear in the following information:
MySQL thread id 10, query the event id 279129 Reading from the relay log
Or:
7 the lock struct (s), heap size 1248, 2 row lock (s), the undo log entries 5
Detailed information is as follows:
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
BACKGROUND THREAD
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Sleeps _second srv_master_thread loops: 1 1, 1, 0, 10 _second 1 background, 0 flush
Srv_master_thread log flush and writes: 23295
-- -- -- -- -- -- -- -- -- --
SEMAPHORES
-- -- -- -- -- -- -- -- -- --
OS WAIT ARRAY INFO: reservation count 1906101, signal count 1785575
,,,,,,
Mutex spin waits, 110381692 rounds of 277492261, OS waits 1637293
RW - Shared spins 64196, 883065 rounds, OS waits 13030
RW - excl spins 377665, 8864472 rounds, OS waits 218680
Spin rounds per wait: 2.51 mutex, 13.76 RW -shared, 23.47 RW - excl
-- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- --
The TRANSACTIONS
-- -- -- -- -- -- -- -- -- -- -- --
Trx id counter 42 b92cd79
Purge the done for TRX 's n & lt; 2 e3d3c544 undo n & lt; 0
The History list length 1753661298
The LIST OF the TRANSACTIONS FOR EACH SESSION:
- the TRANSACTION 0, not started, the process no 206656, OS thread id 139769784071936
MySQL thread id 6, query id 35655 127.0.0.1 root
Show engine INNODB STATUS
- TRANSACTION 42 b92cd78, ACTIVE 1 SEC, process no 206656, OS thread id 139769783015168 inserting
Mysql tables in use 2, locked 2
7 the lock struct (s), heap size 1248, 2 row lock (s), the undo log entries 5
MySQL thread id 10, query id 35655
-- -- -- -- -- -- -- --
The FILE I/O
-- -- -- -- -- -- -- --
The I/O thread 0 state: waiting for I/O request (insert buffer thread)
The I/O thread 1 state: waiting for I/O request (log thread)
The I/O thread 2 state: waiting for I/O request (read thread)
The I/O thread 3 state: waiting for I/O request (read thread)
The I/O thread state: waiting for I/O request (read thread)
The I/O thread state: waiting for I/O request (read thread)
The I/O thread state: waiting for I/O request (write thread)
The I/O thread 7 state: waiting for I/O request (write thread)
The I/O thread state: waiting for I/O request (write thread)
The I/O thread state: waiting for I/O request (write thread)
Pending normal aio reads: 0 (0, 0, 0, 0), aio writes: 0 (0, 0, 0, 0),
Ibuf aio reads: 0, the log I/o 's: 0, sync the I/o' s: 0
Pending flushes (fsync) log: 0; Buffer pool: 0
128661 OS file reads, 22408 OS file writes, 572 OS fsyncs
38.07 reads/s, avg 16414 bytes/read, writes/s, 39.71 0.86 fsyncs/s
Or:
The TRANSACTIONS
-- -- -- -- -- -- -- -- -- -- -- --
Trx id counter 42 b947192
Purge the done for TRX 's n & lt; 2 e3fb5a47 undo n & lt; 259
The History list length 1753294784
The LIST OF the TRANSACTIONS FOR EACH SESSION:
- the TRANSACTION 0, not started, the process no 206656, OS thread id 139769781958400
MySQL thread id 120, query id 279129 127.0.0.1 root
Show engine INNODB STATUS
- 42 b947191 TRANSACTION, and the ACTIVE (PREPARED) 0 SEC, process no 206656, OS thread id 139769783015168
2 lock struct (s), heap size 376, 1 row lock (s), the undo log entries 1
MySQL thread id 10, query the event id 279129 Reading from the relay log
-- -- -- -- -- -- -- --
The FILE I/O
-- -- -- -- -- -- -- --
The I/O thread 0 state: waiting for I/O request (insert buffer thread)
The I/O thread 1 state: waiting for I/O request (log thread)
The I/O thread 2 state: waiting for I/O request (read thread)
The I/O thread 3 state: waiting for I/O request (read thread)
The I/O thread state: waiting for I/O request (read thread)
The I/O thread state: waiting for I/O request (read thread)
The I/O thread state: waiting for I/O request (write thread)
The I/O thread 7 state: waiting for I/O request (write thread)
The I/O thread state: waiting for I/O request (write thread)
The I/O thread state: waiting for I/O request (write thread)
Pending normal aio reads: 0 (0, 0, 0, 0), aio writes: 0 (0, 0, 0, 0),
Ibuf aio reads: 0, the log I/o 's: 0, sync the I/o' s: 0
Pending flushes (fsync) log: 0; Buffer pool: 0
224485 OS file reads, 136603 OS file writes, 3308 OS fsyncs
0.33 reads/s, avg 16384 bytes/read, writes/s, 20.44 0.04 fsyncs/s
Set of parameters is as follows:
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
Value | | Variable_name |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
32 | | max_tmp_tables |
| slave_load_tmpdir |/data - DSK/mysqltmp |
536870912 | | both tmp_table_size |
| tmpdir |/data - DSK/mysqltmp |
20 | | ft_query_expansion_limit |
| have_query_cache | YES |
3.000000 | | long_query_time |
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
