mysql57slave庫(kù)復(fù)制延遲處理

1、1 , Zabbix報(bào)警Slave延遲2萬多個(gè)操作位去 slave從庫(kù)上查看運(yùn)行狀態(tài)，果然 Seconds_Behind_Master: 28810 ,而 且提示Slave_SQL_Running_State: System lock 而且這個(gè)是變化的，有的時(shí)候提示 Slave_SQL_Running_State: invalidating query cache entries (table) ，而且Seconds Behind Master: 29086 不停的增長(zhǎng)著:mysql> show slave status'G1. row*Slave_IO_State: Waiti

2、 ng for master to send eventMaster_Host: 10.249.3.11Master_User: replMaster_Port: 3317Co nn ect_Retry: 60Master_Log_File: mysql-bi n. 000027Read_Master_Log_Pos: 909456416Relay_Log_File: mysql-relay-bi n.000020Relay_Log_Pos: 19878093Relay_Master_Log_File: mysql-bi n.000014Slave_IO_Ru nning: YesSlave_

3、SQL_Ru nning: YesReplicate_Do_DB:Replicategn ore_DB: Replicate_Do_Table:Replicate_Ig no re_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ig no re_Table:Last_Err no: 0Last_Error:Skip_Cou nter: 0Exec_Master_Log_Pos: 19877880Relay_Log_Space:n til_Co nditi on: No neUn til_Log_File:Un til_Log

4、_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Ce rt:Master_SSL_Cipher:Master_SSL_Key:Seco nds_Behi nd_Master: 29090Master SSL Verify Server Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_lg nore_Server_lds:Master_Server_ld: 12001Master

5、_UUID: cfc81670-fca1-11e5-926d-0017fa0041c0Master_I nfo_File: mysql.slave_master_i nfoSQL_Delay: 0SQL Remai nin g Delay: NULLSlave_SQL_Ru nnin g_State: System lockMaster_Retry_Cou nt: 86400Master_Bi nd:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_

6、Set:Executed_Gtid_Set:Auto_Positio n: 0Replicate_Rewrite_DB:Chann el_Name:Master_TLS_Versio n:1 row in set (0.00 sec)mysql>2，從innodb引擎狀態(tài)分析分析沒有死鎖，也沒有異常信息，看來一切正常:mysql> show engine innodb statusG* 1 rov *Type: Inn oDBName:Status:2016-06-13 21:48:46 0x7fb1a4f65700 INNODB MONITOR OUTPUTPer sec ond

7、 averages calculated from the last 45 sec ondsBACKGROUND THREAD srv_master_thread loops: 389 srv_active, 0 srv_shutdow n, 22 srv_idlesrv_master_thread log flush and writes: 411SEMAPHORESOS WAIT ARRAY INFO: reservation cou nt 3668625OS WAIT ARRAY INFO: sig nal cou nt 2833541RW-shared spi ns 0, rou nd

8、s 89, OS waits 14RW-excl spi ns 0, rounds 53132, OS waits 1579RW-sx spins 38, rou nds 1124, OS waits 35Spin rounds per wait: 89.00 RW-shared, 53132.00 RW-excl, 29.58 RW-sxTRANSACTIONSTrx id cou nter 7963036Purge done for trx's n:o < 7963035 undo n:o < 0 state: running but idleHistory list

9、len gth 1331LIST OF TRANSACTIONS FOR EACH SESSION:-TRANSACTION 421877794228048, not started0 lock struct(s), heap size 1136, 0 row lock(s)-TRANSACTION 7963035, ACTIVE 0 secmysql tables in use 1, locked 11 lock struct(s), heap size 1136, 0 row lock(s), undo log en tries 1MySQL thread id 7, OS thread

10、handle 140400954148608, query id 2760070 System lockFILE I/OI/O thread 0 state: wait ingforcompletedaiorequests(insert buffer thread)I/O thread 1 state: wait ingforcompletedaiorequests(log thread)I/O thread 2 state: wait ingforcompletedaiorequests(read thread)I/O thread 3 state: wait ingforcompleted

11、aiorequests(read thread)I/O thread 4 state: wait ingforcompletedaiorequests(read thread)I/O thread 5 state: wait ingforcompletedaiorequests(read thread)I/O thread 6 state: wait ingforcompletedaiorequests(write thread)I/O thread 7 state: wait ingforcompletedaiorequests(write thread)I/O thread 8 state

12、: wait ingforcompletedaiorequests(write thread)I/O thread 9 state: wait ingforcompletedaiorequests(write thread)Pen di ng normal aio reads: 0, 0, 0, 0 , aio writes: 0, 0, 0, 0,ibuf aio reads:, log i/o's:, sync i/o's:Pending flushes (fsync) log: 0; buffer pool: 014555 OS file reads, 13496 OS

13、file writes, 5454 OS fsyncs0.00 reads/s, 0 avg bytes/read, 35.80 writes/s, 16.82 fsyncs/sINSERT BUFFER AND ADAPTIVE HASH INDEXIbuf: size 1, free list len 0, seg size 2, 0 merges merged operati ons:in sert 0, delete mark 0, delete 0discarded operati ons:in sert 0, delete mark 0, delete 0Hash table si

14、ze 276671, node heap has 2 buffer(s) Hash table size 276671, node heap has 1 buffer(s) Hash table size 276671, node heap has 0 buffer(s) Hash table size 276671, node heap has 0 buffer(s) Hash table size 276671, node heap has 1 buffer(s) Hash table size 276671, node heap has 0 buffer(s) Hash table si

15、ze 276671, node heap has 0 buffer(s) Hash table size 276671, node heap has 2 buffer(s) 6439.79 hash searches/s, 85.26 non-hash searches/sLOGLog seque nee number 6797194382 Log flushed up to 6795126836 Pages flushed up to 6565491797 Last checkpoi nt at 6565491797 0 pending log flushes, 0 pending chkp

16、 writes 1024 log i/o's done, 2.98 log i/o's/seco ndBUFFER POOL AND MEMORYTotal large memory allocated 1099431936Dictio nary memory allocated 342972Buffer pool size 65528Free buffers43909Database pages 21613Old database pages 7814Modified db pages 1427Pending reads 0Pending writes: LRU 0, flu

17、sh list 0, single page 0Pages made young 0, not young 00.00 youn gs/s, 0.00 non-youn gs/sPages read 14334, created 7279, written 97090.00 reads/s, 17.04 creates/s, 24.47 writes/sBuffer pool hit rate 1000 / 1000, youn g-mak ing rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without acces

18、s 0.00/s, Ran dom read ahead 0.00/sLRU len: 21613, un zip_LRU len: 0I/O sum0:cur0, un zip sum0:cur0INDIVIDUAL BUFFER POOL INFO-BUFFER POOL 0Buffer pool size 8191Free buffers5492Database pages 2698Old database pages 975Modified db pages 252Pending reads 0Pending writes: LRU 0, flush list 0, single pa

19、ge 0Pages made young 0, not young 00.00 youn gs/s, 0.00 non-youn gs/sPages read 1802, created 896, writte n 15140.00 reads/s, 1.98 creates/s, 4.20 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Ran dom read ahe

20、ad 0.00/sLRU len: 2698, un zip_LRU len: 0I/O sum0:cur0, un zip sum0:cur0-BUFFER POOL 1Buffer pool size 8191Free buffers5500Database pages 2690Old database pages 972Modified db pages 46Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youn gs/s, 0.00

21、 non-youn gs/sPages read 1794, created 896, writte n 8520.00 reads/s, 2.84 creates/s, 2.09 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Ran dom read ahead 0.00/sLRU len: 2690, un zip_LRU len: 0I/O sum0:cur0,

22、un zip sum0:cur0-BUFFER POOL 2Buffer pool size 8191Free buffers5491Database pages 2699Old database pages 976Modified db pages 58Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youn gs/s, 0.00 non-youn gs/sPages read 1797, created 902, writte n 980

23、0.00 reads/s, 2.84 creates/s, 2.62 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Ran dom read ahead 0.00/sLRU len: 2699, un zip_LRU len: 0I/O sum0:cur0, un zip sum0:cur0-BUFFER POOL 3Buffer pool size 8191Free

24、buffers5461Database pages 2729Old database pages 987Modified db pages 256Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youn gs/s, 0.00 non-youn gs/sPages read 1766, created 963, writte n 14920.00 reads/s, 2.84 creates/s, 3.16 writes/sBuffer pool

25、 hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Ran dom read ahead 0.00/sLRU len: 2729, un zip_LRU len: 0I/O sum0:cur0, un zip sum0:cur0-BUFFER POOL 4Buffer pool size 8191Free buffers5462Database pages 2728Old database pages 987Mo

26、dified db pages 216Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youn gs/s, 0.00 non-youn gs/sPages read 1794, created 934, writte n 13370.00 reads/s, 2.27 creates/s, 3.93 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not

27、0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Ran dom read ahead 0.00/sLRU len: 2728, un zip_LRU len: 0I/O sum0:cur0, un zip sum0:cur0-BUFFER POOL 5Buffer pool size 8191Free buffers5501Database pages 2689Old database pages 972Modified db pages 178Pending reads 0Pending writes: LRU

28、0, flush list 0, single page 0Pages made young 0, not young 00.00 youn gs/s, 0.00 non-youn gs/sPages read 1793, created 896, writte n 11820.00 reads/s, 1.42 creates/s, 2.87 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without acces

29、s 0.00/s, Ran dom read ahead 0.00/sLRU len: 2689, un zip_LRU len: 0I/O sum0:cur0, un zip sum0:cur0-BUFFER POOL 6Buffer pool size 8191Free buffers5499Database pages 2692Old database pages 973Modified db pages 195Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not

30、young 00.00 youn gs/s, 0.00 non-youn gs/sPages read 1796, created 896, writte n 11980.00 reads/s, 1.42 creates/s, 2.76 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Ran dom read ahead 0.00/sLRU len: 2692, un z

31、ip_LRU len: 0I/O sum0:cur0, un zip sum0:cur0-BUFFER POOL 7Buffer pool size 8191Free buffers5503Database pages 2688Old database pages 972Modified db pages 226Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youn gs/s, 0.00 non-youn gs/sPages read 17

32、92, created 896, writte n 11540.00 reads/s, 1.42 creates/s, 2.84 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Ran dom read ahead 0.00/sLRU len: 2688, un zip_LRU len: 0 I/O sum0:cur0, unzip sum0:cur0ROW OPERAT

33、IONS0 queries in side Inn oDB, 0 queries in queue0 read views ope n in side Inn oDBProcess ID=13619, Main thread ID=140400758589184, state: sleepingNumber of rows in serted 1380011, updated 1380018, deleted 0, read48 in serts/s, 3245.46 updates/s, 0.00 deletes/s, 3245.46 reads/sEND OF I

34、NNODB MONITOR OUTPUT1 row in set (0.00 sec)mysql>3，檢查磁盤狀態(tài)看到磁盤還有很大使用空間，沒有多少被占用的：rootazure_dna_dbm2_3_12 data# df -hFilesystemSizeUsed AvailUse%Mounted on/dev/sda129G5.6G22G21% /tmpfs14G014G0% /dev/shm/dev/sdc1007G43G914G5% /data/dev/sdb156G33G21G62% /mnt/resourcerootazure_dna_dbm2_3_12 data#4，查看my

35、sql線程狀況線程正常，slave庫(kù)沒有多余的惡意登錄，也沒有多余的正在使用的慢sql卡住的情況：mysql> show full processlist;+| Id | User| Host| db | Comma nd | Time | State| Info|+|1 | system user | NULL | Conn ect |522 | Waiti ng for master to send event| NULLI|2 | system user | NULL| Connect | 29423| System lock| NULLI|4 | root| localhost | NULL| Query|0 |starti ng| show full proces