免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 5138 | 回复: 1

[Zabbix] Lock wait timeout exceeded [复制链接]

论坛徽章:
1
数据库技术版块每日发帖之星
日期:2016-04-12 06:20:00
发表于 2015-01-26 11:13 |显示全部楼层
zabbix_server.log日志里报了大量timeout日志, 从zabbix的网页看, 图形没有数据:

24419:20150126:105626.134 [Z3005] query failed: [1205] Lock wait timeout exceeded; try restarting transaction [insert into history (itemid,clock,ns,value) values (24810,1422240930,743553000,0.026667),(26430,1422240930,771607000,0.000000),(26130,1422240930,778740000,1.146667),(24750,1422240930,871980000,0.000000),(27930,1422240930,950482000,71.467139),(24570,1422240930,989375000,35.758143),(27390,1422240930,996335000,99.289617),(26131,1422240931,148204000,0.583333),(27391,1422240931,219118000,99.869752),(24751,1422240931,291109000,0.000000),(24571,1422240931,389929000,97.341611),(26431,1422240931,396703000,0.237159),(24932,1422240932,418593000,0.000000),(26432,1422240932,447387000,14.456603),(26312,1422240932,448680000,0.001111),(25472,1422240932,470549000,68.005063),(26132,1422240932,532003000,0.820000),(26072,1422240932,648236000,0.006027),(24752,1422240932,735213000,35.424804),(24753,1422240933,850445000,182635.830204),(26313,1422240934,73014000,0.000000),(24933,1422240934,75430000,0.000000),(26073,1422240934,211261000,0.000000),(26074,1422240934,398129000,229036.503537),(24934,1422240934,424495000,0.000000),(27394,1422240934,553724000,94.762140),(24574,1422240934,565498000,0.03777,(26254,1422240934,591981000,0.000000),(26314,1422240934,742105000,0.000000);
]

                                                     
mysql版本:
Server version: 5.6.22-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 500      |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+
12 rows in set (0.00 sec)

大家碰到类似的问题没?

论坛徽章:
1
数据库技术版块每日发帖之星
日期:2016-04-12 06:20:00
发表于 2015-01-26 11:14 |显示全部楼层
mysql> show engine innodb status \G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2015-01-26 10:57:14 4d638940 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1190044 srv_active, 0 srv_shutdown, 1709039 srv_idle
srv_master_thread log flush and writes: 2830647
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1334623
OS WAIT ARRAY INFO: signal count 1999836
Mutex spin waits 10580258, rounds 262165644, OS waits 433557
RW-shared spins 1192465, rounds 23954775, OS waits 768501
RW-excl spins 22486, rounds 5062239, OS waits 130166
Spin rounds per wait: 24.78 mutex, 20.09 RW-shared, 225.13 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2014-12-26 15:38:05 4d269940 Transaction:
TRANSACTION 117005, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 8938, OS thread handle 0x4d269940, query id 524185 localhost zabbix updating
update graphs_items set itemid=24038 where gitemid=2065
Foreign key constraint fails for table `zabbixdb`.`graphs_items`:
,
  CONSTRAINT `c_graphs_items_2` FOREIGN KEY (`itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE
Trying to add in child table, in index `graphs_items_1` tuple:
DATA TUPLE: 2 fields;
0: len 8; hex 0000000000005de6; asc       ] ;;
1: len 8; hex 0000000000000811; asc         ;;

But in parent table `zabbixdb`.`items`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 49; compact format; info bits 0
0: len 8; hex 0000000000005de5; asc       ] ;;
1: len 6; hex 00000001b660; asc      `;;
2: len 7; hex d2000001de0110; asc        ;;
3: len 4; hex 80000000; asc     ;;
4: len 0; hex ; asc ;;
5: len 0; hex ; asc ;;
6: len 8; hex 000000000000277f; asc       ' ;;
7: len 21; hex 55736564206469736b207370616365206f6e202431; asc Used disk space on $1;;
8: len 20; hex 7666732e66732e73697a655b433a2c757365645d; asc vfs.fs.size[C:,used];;
9: len 4; hex 8000003c; asc    <;;
10: len 4; hex 80000007; asc     ;;
11: len 4; hex 8000016d; asc    m;;
12: len 4; hex 80000000; asc     ;;
13: len 4; hex 80000003; asc     ;;
14: len 0; hex ; asc ;;
15: len 1; hex 42; asc B;;
16: len 4; hex 80000000; asc     ;;
17: len 4; hex 80000000; asc     ;;
18: len 0; hex ; asc ;;
19: len 4; hex 80000000; asc     ;;
20: len 0; hex ; asc ;;
21: len 0; hex ; asc ;;
22: len 1; hex 31; asc 1;;
23: len 0; hex ; asc ;;
24: len 8; hex 0000000000000000; asc         ;;
25: len 0; hex ; asc ;;
26: SQL NULL;
27: SQL NULL;
28: len 0; hex ; asc ;;
29: len 0; hex ; asc ;;
30: len 0; hex ; asc ;;
31: len 4; hex 80000000; asc     ;;
32: len 4; hex 80000000; asc     ;;
33: len 0; hex ; asc ;;
34: len 0; hex ; asc ;;
35: len 0; hex ; asc ;;
36: len 0; hex ; asc ;;
37: len 4; hex 80000000; asc     ;;
38: len 4; hex 80000004; asc     ;;
39: len 8; hex 0000000000000008; asc         ;;
40: len 0; hex ; asc ;;
41: len 0; hex ; asc ;;
42: len 4; hex 80000000; asc     ;;
43: len 2; hex 3330; asc 30;;
44: len 4; hex 80000000; asc     ;;
45: len 4; hex 80000000; asc     ;;
46: len 4; hex 80000000; asc     ;;
47: len 0; hex ; asc ;;
48: len 4; hex 80000000; asc     ;;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-01-12 20:34:12 4d165940
*** (1) TRANSACTION:
TRANSACTION 1639335, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 8945, OS thread handle 0x4d1e7940, query id 8831236 localhost zabbix update
insert into history_uint (itemid,clock,ns,value) values (23823,1421066043,129454000,0),(23824,1421066044,146037000,0),(23825,1421066045,155611000,0),(23826,1421066046,154278000,0),(23827,1421066047,167052000,396
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 77 page no 8131 n bits 728 index `history_uint_1` of table `zabbixdb`.`history_uint` trx id 1639335 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 8; hex 0000000000005d10; asc       ] ;;
1: len 4; hex d49cf7c0; asc     ;;
2: len 6; hex 00000001e977; asc      w;;

*** (2) TRANSACTION:
TRANSACTION 1639321, ACTIVE 11 sec fetching rows
mysql tables in use 1, locked 1
12408 lock struct(s), heap size 1570344, 3466960 row lock(s), undo log entries 831099
MySQL thread id 229819, OS thread handle 0x4d165940, query id 8831257 localhost zabbix updating
delete from history_uint where itemid=23855 and clock<1420461241
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 77 page no 8131 n bits 728 index `history_uint_1` of table `zabbixdb`.`history_uint` trx id 1639321 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 8; hex 0000000000005d10; asc       ] ;;
1: len 4; hex d49cf7c0; asc     ;;
2: len 6; hex 00000001e977; asc      w;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 77 page no 9070 n bits 328 index `GEN_CLUST_INDEX` of table `zabbixdb`.`history_uint` trx id 1639321 lock_mode X waiting
Record lock, heap no 258 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 6; hex 0000002bdde2; asc    +  ;;
1: len 6; hex 0000001903a7; asc       ;;
2: len 7; hex fb0000018d0110; asc        ;;
3: len 8; hex 0000000000005d0f; asc       ] ;;
4: len 4; hex d4b3bf3b; asc    ;;;
5: len 8; hex 0000000000000000; asc         ;;
6: len 4; hex 87b74fb0; asc   O ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 5390651
Purge done for trx's n < 4165452 undo n < 0 state: running but idle
History list length 330256
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 629798, OS thread handle 0x4d638940, query id 27468477 localhost root init
show engine innodb status
---TRANSACTION 5390633, not started
MySQL thread id 527638, OS thread handle 0x4d165940, query id 27468425 localhost zabbix cleaning up
---TRANSACTION 5390642, not started
MySQL thread id 527634, OS thread handle 0x4d6ba940, query id 27468456 localhost zabbix cleaning up
---TRANSACTION 5390649, not started
MySQL thread id 527643, OS thread handle 0x4d679940, query id 27468473 localhost zabbix cleaning up
---TRANSACTION 5390641, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 527640, OS thread handle 0x4d3ef940, query id 27468453 localhost zabbix update
insert into history (itemid,clock,ns,value) values (26388,1422241009,204743000,0.006243),(27468,1422241009,205599000,0.022095),(24828,1422241009,259421000,0.000000),(26209,1422241009,309380000,0.000000),(27469,1422241009,310302000,0.00831,(24349,1422241009,343612000,91.488821),(24289,1422241009,379202000,0.000520),(26389,1422241009,392097000,0.000000),(24649,1422241009,432130000,35.775932),(24829,1422241009,519978000,0.000000),(27050,1422241010,334774000,81.017341),(24290,1422241010,424377000,0.00363,(24350,1422241010,684174000,0.017646),(25670,1422241010,744075000,90.909309),(26210,142224
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 31352 n bits 552 index `history_1` of table `zabbixdb`.`history` trx id 5390641 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 0000000000006715; asc       g ;;
1: len 4; hex d4b79265; asc    e;;
2: len 6; hex 0000003000ca; asc    0  ;;

------------------
---TRANSACTION 5390623, ACTIVE 25 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 527645, OS thread handle 0x4d4f3940, query id 27468388 localhost zabbix update
insert into history (itemid,clock,ns,value) values (26131,1422240991,947899000,0.733333),(26431,1422240992,22314000,0.23090,(27391,1422240992,37805000,99.869752),(24751,1422240992,172104000,0.000689),(24571,1422240992,177305000,0.000000),(26432,1422240992,223566000,14.474423),(24932,1422240992,274787000,0.000000),(25472,1422240992,330537000,68.005063),(26312,1422240992,332187000,0.001111),(26132,1422240992,367698000,0.750000),(24752,1422240992,645315000,0.000000),(26072,1422240992,645682000,0.021909),(24753,1422240993,820806000,35.446252),(26313,1422240993,947579000,0.000000),(24933,14222409
------- TRX HAS BEEN WAITING 25 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 31160 n bits 632 index `history_1` of table `zabbixdb`.`history` trx id 5390623 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 0000000000006614; asc       f ;;
1: len 4; hex d4b79291; asc     ;;
2: len 6; hex 00000030026d; asc    0 m;;

------------------
---TRANSACTION 5390599, ACTIVE 43 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 527641, OS thread handle 0x4d1e7940, query id 27468322 localhost zabbix update
insert into history (itemid,clock,ns,value) values (26004,1422240985,222232000,99.955734),(26125,1422240985,461071000,0.004457),(26425,1422240985,646135000,84.547856),(24805,1422240985,711989000,35.853370),(26306,1422240986,836772000,0.000000),(26126,1422240986,840586000,10879615.319673),(26966,1422240986,884425000,81.430686),(25346,1422240986,939863000,50.833643),(26426,1422240987,85451000,0.002080),(24626,1422240987,113335000,0.155556),(26127,1422240987,140849000,0.000000),(24627,1422240987,278409000,0.100000),(26307,1422240987,398280000,0.000099),(26967,1422240987,455893000,86.291590),(2642
------- TRX HAS BEEN WAITING 43 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 31136 n bits 656 index `history_1` of table `zabbixdb`.`history` trx id 5390599 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 00000000000065a4; asc       e ;;
1: len 4; hex d4b79299; asc     ;;
2: len 6; hex 0000003002df; asc    0  ;;

------------------
---TRANSACTION 5390595, ACTIVE 49 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 527642, OS thread handle 0x4d124940, query id 27468307 localhost zabbix update
insert into history (itemid,clock,ns,value) values (26054,1422240974,782904000,2.536667),(25874,1422240974,857078000,55.628630),(24854,1422240974,885624000,0.000000),(27434,1422240974,908482000,46.215011),(24494,1422240974,944849000,0.063333),(27374,1422240974,958826000,82.702921),(24674,1422240974,968158000,35.480260),(27674,1422240974,968763000,16.297835),(27435,1422240975,38207000,93.376464),(26235,1422240975,51469000,0.000000),(24855,1422240975,82730000,0.000000),(24675,1422240975,97968000,35.480260),(27855,1422240975,136984000,79.897709),(27375,1422240975,309977000,49.580904),(27675,14222
------- TRX HAS BEEN WAITING 49 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 28493 n bits 704 index `history_1` of table `zabbixdb`.`history` trx id 5390595 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 246 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 00000000000065d8; asc       e ;;
1: len 4; hex d4b79292; asc     ;;
2: len 6; hex 000000300276; asc    0 v;;

------------------
---TRANSACTION 5390496, ACTIVE 112 sec
MySQL thread id 527633, OS thread handle 0x4d228940, query id 27468011 localhost zabbix cleaning up
Trx read view will not see trx with id >= 5390497, sees < 4165449
---TRANSACTION 5390483, ACTIVE 112 sec
MySQL thread id 527627, OS thread handle 0x4d7be940, query id 27467972 localhost zabbix cleaning up
Trx read view will not see trx with id >= 5390484, sees < 4165449
---TRANSACTION 5390424, ACTIVE 137 sec
MySQL thread id 527626, OS thread handle 0x4d6fb940, query id 27467803 localhost zabbix cleaning up
Trx read view will not see trx with id >= 5390425, sees < 4165449
---TRANSACTION 5390370, ACTIVE 163 sec
MySQL thread id 527636, OS thread handle 0x4d0e3940, query id 27467640 localhost zabbix cleaning up
Trx read view will not see trx with id >= 5390371, sees < 4165449
---TRANSACTION 5390354, ACTIVE 165 sec
MySQL thread id 527635, OS thread handle 0x4d4b2940, query id 27467589 localhost zabbix cleaning up
Trx read view will not see trx with id >= 5390355, sees < 4165449
---TRANSACTION 5390277, ACTIVE 205 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 630508, OS thread handle 0x4d430940, query id 27467339 localhost zabbix updating
UPDATE sessions SET lastaccess=1422240829 WHERE userid='1' AND sessionid='6c927599c37650535e5e4baa0117a36d'
Trx read view will not see trx with id >= 5390278, sees < 4165449
------- TRX HAS BEEN WAITING 205 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 237 page no 3 n bits 88 index `PRIMARY` of table `zabbixdb`.`sessions` trx id 5390277 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 30; hex 366339323735393963333736353035333565356534626161303131376133; asc 6c927599c37650535e5e4baa0117a3; (total 32 bytes);
1: len 6; hex 000000523f72; asc    R?r;;
2: len 7; hex 540000e2f92e17; asc T    . ;;
3: len 8; hex 0000000000000001; asc         ;;
4: len 4; hex d4c5ac26; asc    &;;
5: len 4; hex 80000000; asc     ;;

------------------
---TRANSACTION 5390276, ACTIVE 205 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 630507, OS thread handle 0x4d2eb940, query id 27467338 localhost zabbix updating
UPDATE sessions SET lastaccess=1422240829 WHERE userid='1' AND sessionid='6c927599c37650535e5e4baa0117a36d'
Trx read view will not see trx with id >= 5390277, sees < 4165449
------- TRX HAS BEEN WAITING 205 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 237 page no 3 n bits 88 index `PRIMARY` of table `zabbixdb`.`sessions` trx id 5390276 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 30; hex 366339323735393963333736353035333565356534626161303131376133; asc 6c927599c37650535e5e4baa0117a3; (total 32 bytes);
1: len 6; hex 000000523f72; asc    R?r;;
2: len 7; hex 540000e2f92e17; asc T    . ;;
3: len 8; hex 0000000000000001; asc         ;;
4: len 4; hex d4c5ac26; asc    &;;
5: len 4; hex 80000000; asc     ;;

------------------
---TRANSACTION 5390270, ACTIVE 205 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 630506, OS thread handle 0x4d1a6940, query id 27467321 localhost zabbix updating
UPDATE sessions SET lastaccess=1422240829 WHERE userid='1' AND sessionid='6c927599c37650535e5e4baa0117a36d'
Trx read view will not see trx with id >= 5390271, sees < 4165449
------- TRX HAS BEEN WAITING 205 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 237 page no 3 n bits 88 index `PRIMARY` of table `zabbixdb`.`sessions` trx id 5390270 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 30; hex 366339323735393963333736353035333565356534626161303131376133; asc 6c927599c37650535e5e4baa0117a3; (total 32 bytes);
1: len 6; hex 000000523f72; asc    R?r;;
2: len 7; hex 540000e2f92e17; asc T    . ;;
3: len 8; hex 0000000000000001; asc         ;;
4: len 4; hex d4c5ac26; asc    &;;
5: len 4; hex 80000000; asc     ;;

------------------
---TRANSACTION 5390266, ACTIVE 205 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 630505, OS thread handle 0x4d32c940, query id 27467311 localhost zabbix updating
UPDATE sessions SET lastaccess=1422240829 WHERE userid='1' AND sessionid='6c927599c37650535e5e4baa0117a36d'
Trx read view will not see trx with id >= 5390267, sees < 4165449
------- TRX HAS BEEN WAITING 205 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 237 page no 3 n bits 88 index `PRIMARY` of table `zabbixdb`.`sessions` trx id 5390266 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 30; hex 366339323735393963333736353035333565356534626161303131376133; asc 6c927599c37650535e5e4baa0117a3; (total 32 bytes);
1: len 6; hex 000000523f72; asc    R?r;;
2: len 7; hex 540000e2f92e17; asc T    . ;;
3: len 8; hex 0000000000000001; asc         ;;
4: len 4; hex d4c5ac26; asc    &;;
5: len 4; hex 80000000; asc     ;;

------------------
---TRANSACTION 5390258, ACTIVE 207 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 630504, OS thread handle 0x4d5f7940, query id 27467275 localhost zabbix updating
UPDATE sessions SET lastaccess=1422240827 WHERE userid='1' AND sessionid='6c927599c37650535e5e4baa0117a36d'
Trx read view will not see trx with id >= 5390259, sees < 4165449
------- TRX HAS BEEN WAITING 207 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 237 page no 3 n bits 88 index `PRIMARY` of table `zabbixdb`.`sessions` trx id 5390258 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 30; hex 366339323735393963333736353035333565356534626161303131376133; asc 6c927599c37650535e5e4baa0117a3; (total 32 bytes);
1: len 6; hex 000000523f72; asc    R?r;;
2: len 7; hex 540000e2f92e17; asc T    . ;;
3: len 8; hex 0000000000000001; asc         ;;
4: len 4; hex d4c5ac26; asc    &;;
5: len 4; hex 80000000; asc     ;;

------------------
---TRANSACTION 5390229, ACTIVE 218 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 630503, OS thread handle 0x4d881940, query id 27467184 localhost zabbix updating
UPDATE sessions SET lastaccess=1422240816 WHERE userid='1' AND sessionid='6c927599c37650535e5e4baa0117a36d'
Trx read view will not see trx with id >= 5390230, sees < 4165449
------- TRX HAS BEEN WAITING 218 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 237 page no 3 n bits 88 index `PRIMARY` of table `zabbixdb`.`sessions` trx id 5390229 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 30; hex 366339323735393963333736353035333565356534626161303131376133; asc 6c927599c37650535e5e4baa0117a3; (total 32 bytes);
1: len 6; hex 000000523f72; asc    R?r;;
2: len 7; hex 540000e2f92e17; asc T    . ;;
3: len 8; hex 0000000000000001; asc         ;;
4: len 4; hex d4c5ac26; asc    &;;
5: len 4; hex 80000000; asc     ;;

------------------
---TRANSACTION 5388858, ACTIVE 550 sec rollback
ROLLING BACK 77712 lock struct(s), heap size 10516008, 29925596 row lock(s), undo log entries 892259
MySQL thread id 630396, OS thread handle 0x4d2aa940, query id 27467143 localhost zabbix cleaning up
Trx read view will not see trx with id >= 5388859, sees < 4165449
---TRANSACTION 5364847, ACTIVE 7342 sec
101 lock struct(s), heap size 13864, 1487 row lock(s), undo log entries 1480
MySQL thread id 628488, OS thread handle 0x4d471940, query id 27468474 localhost zabbix cleaning up
Trx read view will not see trx with id >= 5364848, sees < 4165449
---TRANSACTION 4169746, ACTIVE 324201 sec
MySQL thread id 527628, OS thread handle 0x4d7ff940, query id 27468056 localhost zabbix cleaning up
Trx read view will not see trx with id >= 4169747, sees < 4165448
---TRANSACTION 4166135, ACTIVE 325067 sec
MySQL thread id 527630, OS thread handle 0x4d269940, query id 27459499 localhost zabbix cleaning up
Trx read view will not see trx with id >= 4166136, sees < 4165448
---TRANSACTION 4165903, ACTIVE 325130 sec
MySQL thread id 527629, OS thread handle 0x4d840940, query id 27468441 localhost zabbix cleaning up
Trx read view will not see trx with id >= 4165904, sees < 4165448
---TRANSACTION 4165758, ACTIVE 325173 sec
MySQL thread id 527631, OS thread handle 0x4d5b6940, query id 27468293 localhost zabbix cleaning up
Trx read view will not see trx with id >= 4165759, sees < 4165448
---TRANSACTION 4165746, ACTIVE 325183 sec
MySQL thread id 527666, OS thread handle 0x4d534940, query id 27468288 localhost zabbix cleaning up
Trx read view will not see trx with id >= 4165747, sees < 4165448
---TRANSACTION 4165687, ACTIVE 325191 sec
MySQL thread id 527632, OS thread handle 0x4d36d940, query id 27468115 localhost zabbix cleaning up
Trx read view will not see trx with id >= 4165688, sees < 4165448
---TRANSACTION 4165501, ACTIVE 325243 sec
MySQL thread id 527646, OS thread handle 0x4d3ae940, query id 27468375 localhost zabbix cleaning up
Trx read view will not see trx with id >= 4165502, sees < 4165448
---TRANSACTION 4165449, ACTIVE 325243 sec
MySQL thread id 527639, OS thread handle 0x4d73c940, query id 27468476 localhost zabbix cleaning up
Trx read view will not see trx with id >= 4165450, sees < 4165448
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1634 OS file reads, 116455344 OS file writes, 23680361 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2633.28 writes/s, 142.40 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 18923039, node heap has 58314 buffer(s)
114831.68 hash searches/s, 546.20 non-hash searches/s
---
LOG
---
Log sequence number 160370889933
Log flushed up to   160369741647
Pages flushed up to 160300916173
Last checkpoint at  160296551086
0 pending log writes, 0 pending chkp writes
2186791 log i/o's done, 4.25 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 8791261184; in additional pool allocated 0
Dictionary memory allocated 812756
Buffer pool size   524280
Free buffers       175482
Database pages     289843
Old database pages 106829
Modified db pages  11342
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 229, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1427, created 288416, written 104424144
0.00 reads/s, 0.00 creates/s, 2548.54 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 289843, unzip_LRU len: 0
I/O sum[0]:cur[12344], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   65535
Free buffers       21584
Database pages     36540
Old database pages 13468
Modified db pages  1585
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 20, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 203, created 36337, written 13014161
0.00 reads/s, 0.00 creates/s, 339.06 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36540, unzip_LRU len: 0
I/O sum[0]:cur[1543], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   65535
Free buffers       22177
Database pages     35986
Old database pages 13263
Modified db pages  1237
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 41, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 142, created 35844, written 12890484
0.00 reads/s, 0.00 creates/s, 292.06 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 35986, unzip_LRU len: 0
I/O sum[0]:cur[1543], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   65535
Free buffers       21811
Database pages     36302
Old database pages 13380
Modified db pages  1569
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 21, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 156, created 36146, written 12374614
0.00 reads/s, 0.00 creates/s, 338.39 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36302, unzip_LRU len: 0
I/O sum[0]:cur[1543], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   65535
Free buffers       21923
Database pages     36269
Old database pages 13368
Modified db pages  1530
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 30, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 224, created 36045, written 12711541
0.00 reads/s, 0.00 creates/s, 320.47 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36269, unzip_LRU len: 0
I/O sum[0]:cur[1543], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   65535
Free buffers       21873
Database pages     36282
Old database pages 13373
Modified db pages  1146
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 29, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 178, created 36104, written 13671259
0.00 reads/s, 0.00 creates/s, 319.81 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36282, unzip_LRU len: 0
I/O sum[0]:cur[1543], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   65535
Free buffers       22427
Database pages     35759
Old database pages 13180
Modified db pages  1476
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 28, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 198, created 35561, written 13819120
0.00 reads/s, 0.00 creates/s, 321.56 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 35759, unzip_LRU len: 0
I/O sum[0]:cur[1543], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   65535
Free buffers       21608
Database pages     36570
Old database pages 13479
Modified db pages  1491
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 18, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 169, created 36401, written 13181555
0.00 reads/s, 0.00 creates/s, 313.97 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36570, unzip_LRU len: 0
I/O sum[0]:cur[1543], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   65535
Free buffers       22079
Database pages     36135
Old database pages 13318
Modified db pages  1308
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 42, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 157, created 35978, written 12761410
0.00 reads/s, 0.00 creates/s, 303.22 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36135, unzip_LRU len: 0
I/O sum[0]:cur[1543], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
21 read views open inside InnoDB
Main thread process no. 8723, id 1250285888, state: sleeping
Number of rows inserted 48100315, updated 1968003, deleted 871876938, read 1879952590
0.00 inserts/s, 0.83 updates/s, 0.00 deletes/s, 18.50 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.13 sec)
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP