Код:
mysql> SHOW ENGINE INNODB STATUS\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
121206 14:25:43 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2904394 1_second, 2904393 sleeps, 290438 10_second, 15 background, 15 flush
srv_master_thread log flush and writes: 2906178
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1597508, signal count 4760552
Mutex spin waits 117552833, rounds 72794554, OS waits 775306
RW-shared spins 1843468, rounds 17015164, OS waits 430068
RW-excl spins 190276, rounds 16063532, OS waits 349131
Spin rounds per wait: 0.62 mutex, 9.23 RW-shared, 84.42 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
121120 10:56:37 Transaction:
TRANSACTION 305362A0, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 594525, OS thread handle 0x7faea743f700, query id 1615771258 10.20.0.166 fttx updating
DELETE FROM documents
Foreign key constraint fails for table `fttx`.`document_parameters`:
,
CONSTRAINT `FK_document_parameters_DOCUMENT_ID` FOREIGN KEY (`DOCUMENT_ID`) REFERENCES `documents` (`ID`)
Trying to delete or update in parent table, in index `PRIMARY` tuple:
DATA TUPLE: 10 fields;
0: len 4; hex 8000000b; asc ;;
1: len 6; hex 0000305362a0; asc 0Sb ;;
2: len 7; hex 3f000003ac29da; asc ? ) ;;
3: len 8; hex 8000124cd03763cb; asc L 7c ;;
4: SQL NULL;
5: len 14; hex cdeee2fbe920e4eeeaf3ece5edf2; asc ;;
6: len 4; hex 80000005; asc ;;
7: SQL NULL;
8: len 4; hex 80000001; asc ;;
9: len 4; hex 80000001; asc ;;
But in child table `fttx`.`document_parameters`, in index `FK_document_parameters_DOCUMENT_ID`, there is a record:
PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 4; hex 8000009a; asc ;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
121205 22:28:55
*** (1) TRANSACTION:
TRANSACTION 3636FFCB, ACTIVE 0 sec inserting
mysql tables in use 6, locked 6
LOCK WAIT 27 lock struct(s), heap size 3112, 1873 row lock(s), undo log entries 2
MySQL thread id 1222349, OS thread handle 0x7faea4e97700, query id 3158776005 192.168.254.65 bill update
INSERT INTO inet_auth_error_24_201212 (deviceId, deviceTitle, contractId, contractTitle, servId, hash, servTitle, code, logCoordinateRecordId, lastTime, count) VALUES (2,'CN-ASR1002-1',6153,'AK-220/10',12968,0,'AK-220/10',100,96826,NOW(),1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 52200 page no 42 n bits 872 index `deviceId` of table `bgbilling`.`inet_auth_error_24_201212` /* Partition `p20121201` */ trx id 3636FFCB lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000004912ba2; asc + ;;
*** (2) TRANSACTION:
TRANSACTION 3636FFCC, ACTIVE 0 sec starting index read, thread declared inside InnoDB 1
mysql tables in use 6, locked 6
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 1222348, OS thread handle 0x7faea7c1e700, query id 3158776004 192.168.254.65 bill Searching rows for update
UPDATE inet_auth_error_24_201212 SET lastTime=NOW(), count=count+1, logCoordinateRecordId=96827 WHERE lastTime>'2012-12-05' AND deviceId=3 AND contractId=17178 AND servId=19057 AND code=100
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 52200 page no 42 n bits 872 index `deviceId` of table `bgbilling`.`inet_auth_error_24_201212` /* Partition `p20121201` */ trx id 3636FFCC lock_mode X
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000004912ba2; asc + ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 52200 page no 8 n bits 224 index `GEN_CLUST_INDEX` of table `bgbilling`.`inet_auth_error_24_201212` /* Partition `p20121201` */ trx id 3636FFCC lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 6; hex 000004912ba2; asc + ;;
1: len 6; hex 000035505526; asc 5PU&;;
2: len 7; hex c20000021c0120; asc ;;
3: len 4; hex 80000002; asc ;;
4: len 4; hex 80000003; asc ;;
5: len 9; hex 415352313030322d32; asc ASR1002-2;;
6: len 4; hex 80005308; asc S ;;
7: len 9; hex 4953475f504152414d; asc ISG_PARAM;;
8: len 4; hex 80000046; asc F;;
9: len 4; hex 80000000; asc ;;
10: len 12; hex 4c4f43414c5f53494d504c45; asc LOCAL_SIMPLE;;
11: len 4; hex 80000002; asc ;;
12: len 4; hex 80000001; asc ;;
13: len 8; hex 8000124cd52a97c4; asc L * ;;
14: len 4; hex 80000e71; asc q;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 366DBDB2
Purge done for trx's n:o < 366DBD79 undo n:o < 0
History list length 2908
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 366DBAFC, not started
MySQL thread id 1247158, OS thread handle 0x7faea51e4700, query id 3205853158 192.168.254.80 fttx
---TRANSACTION 366D56AB, not started
MySQL thread id 1246963, OS thread handle 0x7fae97b6d700, query id 3205837503 192.168.254.65 bill
---TRANSACTION 366D50F5, not started
MySQL thread id 1246961, OS thread handle 0x7faea46b8700, query id 3205808049 192.168.254.65 bill
---TRANSACTION 366DB982, not started
MySQL thread id 1246910, OS thread handle 0x7faea7c1e700, query id 3205849945 192.168.254.65 bill
---TRANSACTION 366D5102, not started
MySQL thread id 1246902, OS thread handle 0x7faea501d700, query id 3205808051 192.168.254.65 bill
---TRANSACTION 366D7F19, not started
MySQL thread id 1246899, OS thread handle 0x7faea843e700, query id 3205808052 192.168.254.65 bill
---TRANSACTION 366D5CF4, not started
MySQL thread id 1246897, OS thread handle 0x7fae97c30700, query id 3205804839 192.168.254.65 bill
---TRANSACTION 366D9F43, not started
MySQL thread id 1246896, OS thread handle 0x7fae97f3c700, query id 3205852982 192.168.254.65 bill
---TRANSACTION 366D524C, not started
MySQL thread id 1246886, OS thread handle 0x7faea7b9c700, query id 3205822222 192.168.254.65 bill
---TRANSACTION 366D50A9, not started
MySQL thread id 1246885, OS thread handle 0x7faea4e56700, query id 3205793104 192.168.254.65 bill
---TRANSACTION 366D56F6, not started
MySQL thread id 1246884, OS thread handle 0x7fae97820700, query id 3205852978 192.168.254.65 bill
---TRANSACTION 366DBAE4, not started
MySQL thread id 1246882, OS thread handle 0x7faea7fac700, query id 3205852967 192.168.254.65 bill
---TRANSACTION 366D50A7, not started
MySQL thread id 1246881, OS thread handle 0x7faea4c4e700, query id 3205793103 192.168.254.65 bill
---TRANSACTION 366D4AF6, not started
MySQL thread id 1246880, OS thread handle 0x7faea4a46700, query id 3205778782 192.168.254.65 bill
---TRANSACTION 366D4C4D, not started
MySQL thread id 1246875, OS thread handle 0x7fae97924700, query id 3205821166 192.168.254.65 bill
---TRANSACTION 366D4B6E, not started
MySQL thread id 1246872, OS thread handle 0x7faea5d10700, query id 3205852980 192.168.254.65 bill
---TRANSACTION 366D88A2, not started
MySQL thread id 1246871, OS thread handle 0x7faea4b8b700, query id 3205822221 192.168.254.65 bill
---TRANSACTION 366D51EB, not started
MySQL thread id 1246870, OS thread handle 0x7faea81b4700, query id 3205822220 192.168.254.65 bill
---TRANSACTION 366D5040, not started
MySQL thread id 1246679, OS thread handle 0x7faea4d52700, query id 3205793102 192.168.254.65 bill
---TRANSACTION 366DBD9F, not started
MySQL thread id 1246497, OS thread handle 0x7fae97e38700, query id 3205856240 192.168.254.65 bill
---TRANSACTION 0, not started
MySQL thread id 1246451, OS thread handle 0x7fafc01de700, query id 3205856407 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 366DBDA9, not started
MySQL thread id 1246407, OS thread handle 0x7faea74c1700, query id 3205856324 192.168.254.65 bill
---TRANSACTION 366DBB77, not started
MySQL thread id 1245974, OS thread handle 0x7faea49c4700, query id 3205854190 192.168.254.65 bill
---TRANSACTION 366DBD6C, not started
MySQL thread id 1245973, OS thread handle 0x7faea442e700, query id 3205855776 192.168.254.65 bill
---TRANSACTION 366DB978, not started
MySQL thread id 1245972, OS thread handle 0x7faea82b8700, query id 3205849927 192.168.254.65 bill
---TRANSACTION 366DBAEA, not started
MySQL thread id 1245970, OS thread handle 0x7faea41a4700, query id 3205853019 192.168.254.65 bill
---TRANSACTION 366D4B05, not started
MySQL thread id 1245888, OS thread handle 0x7fae978e3700, query id 3205837501 192.168.254.65 bill
---TRANSACTION 366D889E, not started
MySQL thread id 1245887, OS thread handle 0x7faea4636700, query id 3205837500 192.168.254.65 bill
---TRANSACTION 366D4CF8, not started
MySQL thread id 1245886, OS thread handle 0x7faea7fed700, query id 3205852956 192.168.254.65 bill
---TRANSACTION 366DBCF6, not started
MySQL thread id 1245871, OS thread handle 0x7fafc0364700, query id 3205856241 192.168.254.65 bill
---TRANSACTION 366DBD95, not started
MySQL thread id 1245870, OS thread handle 0x7faea7912700, query id 3205856149 192.168.254.65 bill
---TRANSACTION 366DBDAD, not started
MySQL thread id 1245811, OS thread handle 0x7faea73bd700, query id 3205856371 192.168.254.65 bill
---TRANSACTION 366DBD9E, not started
MySQL thread id 1245805, OS thread handle 0x7faea780e700, query id 3205856238 192.168.254.65 bill
---TRANSACTION 366DBDAF, not started
MySQL thread id 1245800, OS thread handle 0x7faea40a0700, query id 3205856392 192.168.254.65 bill
---TRANSACTION 366DBD9B, not started
MySQL thread id 1245803, OS thread handle 0x7faea42e9700, query id 3205856231 192.168.254.65 bill
---TRANSACTION 366DBDAB, not started
MySQL thread id 1245810, OS thread handle 0x7faea4983700, query id 3205856337 192.168.254.65 bill
---TRANSACTION 366DBDA8, not started
MySQL thread id 1245801, OS thread handle 0x7fae97a69700, query id 3205856317 192.168.254.65 bill
---TRANSACTION 366DBDA0, not started
MySQL thread id 1245808, OS thread handle 0x7faea483e700, query id 3205856262 192.168.254.65 bill
---TRANSACTION 366DBDA7, not started
MySQL thread id 1245788, OS thread handle 0x7faea7d22700, query id 3205856316 192.168.254.65 bill
---TRANSACTION 366DBDAA, not started
MySQL thread id 1245797, OS thread handle 0x7faea80b0700, query id 3205856351 192.168.254.65 bill
---TRANSACTION 366DBDA2, not started
MySQL thread id 1245796, OS thread handle 0x7faea8605700, query id 3205856260 192.168.254.65 bill
---TRANSACTION 366DBDB0, not started
MySQL thread id 1245787, OS thread handle 0x7faea583d700, query id 3205856403 192.168.254.65 bill
---TRANSACTION 366DBDA3, not started
MySQL thread id 1245778, OS thread handle 0x7faea81f5700, query id 3205856274 192.168.254.65 bill
---TRANSACTION 366DBDA6, not started
MySQL thread id 1245785, OS thread handle 0x7faea79d5700, query id 3205856303 192.168.254.65 bill
---TRANSACTION 366DBDA5, not started
MySQL thread id 1245780, OS thread handle 0x7faea5e96700, query id 3205856294 192.168.254.65 bill
---TRANSACTION 366DBDA1, not started
MySQL thread id 1245770, OS thread handle 0x7faea7647700, query id 3205856261 192.168.254.65 bill
---TRANSACTION 366DBDAE, not started
MySQL thread id 1245769, OS thread handle 0x7faea83bc700, query id 3205856385 192.168.254.65 bill
---TRANSACTION 366DBDAC, not started
MySQL thread id 1245768, OS thread handle 0x7faea5739700, query id 3205856370 192.168.254.65 bill
---TRANSACTION 366DBDB1, not started
MySQL thread id 1245765, OS thread handle 0x7faea57fc700, query id 3205856406 192.168.254.65 bill
---TRANSACTION 366DB7AF, not started
MySQL thread id 1245763, OS thread handle 0x7faea7890700, query id 3205845788 192.168.254.65 bill
---TRANSACTION 366DBDA4, not started
MySQL thread id 1245761, OS thread handle 0x7faea4f9b700, query id 3205856296 192.168.254.65 bill
---TRANSACTION 366DBABD, not started
MySQL thread id 1245721, OS thread handle 0x7faea77cd700, query id 3205852939 192.168.254.65 bill
---TRANSACTION 366DBD93, not started
MySQL thread id 1245720, OS thread handle 0x7faea4a87700, query id 3205856139 192.168.254.65 bill
---TRANSACTION 366DB74D, not started
MySQL thread id 1245714, OS thread handle 0x7faea5e55700, query id 3205852648 192.168.254.65 bill
---TRANSACTION 366DB74F, not started
MySQL thread id 1245703, OS thread handle 0x7faea7480700, query id 3205852660 192.168.254.65 bill
---TRANSACTION 365CDB49, not started
MySQL thread id 1238062, OS thread handle 0x7fae97b2c700, query id 3205817641 10.20.0.166 fttx
--------
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
4849787 OS file reads, 12951606 OS file writes, 6896188 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 46.73 writes/s, 19.67 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 66, seg size 68, 2476 merges
merged operations:
insert 1440, delete mark 11137, delete 366
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 8850487, node heap has 4540 buffer(s)
14235.92 hash searches/s, 433.10 non-hash searches/s
---
LOG
---
Log sequence number 69625072608
Log flushed up to 69625063019
Last checkpoint at 69624790838
0 pending log writes, 0 pending chkp writes
5108983 log i/o's done, 16.60 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4395630592; in additional pool allocated 0
Dictionary memory allocated 7844752
Buffer pool size 262144
Free buffers 1
Database pages 257603
Old database pages 95071
Modified db pages 552
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5089414, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4848039, created 72724, written 7447791
0.00 reads/s, 0.07 creates/s, 29.46 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: 257603, unzip_LRU len: 0
I/O sum[1105]:cur[221], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 1775, id 140388136679168, state: sleeping
Number of rows inserted 4590002, updated 5087549, deleted 1379168, read 4841479145580
9.60 inserts/s, 47.73 updates/s, 2.13 deletes/s, 27695.69 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
ERROR:
No query specified
Вход
Регистрация
FAQ
Поиск
