mysqldump --single transaction blocks writers
Posted by: Aleksandar Ivanisevic
Date: July 21, 2014 06:24AM
Date: July 21, 2014 06:24AM
Hi,
according to the docs mysqldump --single transaction should not block writers if all tables are innodb and there is no DDL or OPTIMIZE or ANALYZE and whatnot.
However yesterday i have encountered the following situation (output of show full processlist and show innodb status, somewhat anonymized). Can someone tell me what are the writers waiting for? All inserts are simple insert into x(y,z) values (const, const), no fancy insert selects or anything else.
*************************** 1. row ***************************
now(): 2014-07-21 04:06:01
*************************** 1. row ***************************
Id: 2336222
User: dlh
Host: DLH03:35538
db: dlhbe
Command: Sleep
Time: 4
State:
Info: NULL
*************************** 2. row ***************************
Id: 2337424
User: dlh
Host: dlh-vip:54202
db: dlhbe
Command: Sleep
Time: 9
State:
Info: NULL
*************************** 3. row ***************************
Id: 3749630
User: repl
Host: DLH03:38959
db: NULL
Command: Binlog Dump
Time: 1929524
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 4. row ***************************
Id: 6149757
User: dlh
Host: dlh-vip:50788
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 5. row ***************************
Id: 6152220
User: dlh
Host: DLH03:39252
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 6. row ***************************
Id: 6152552
User: dlh
Host: dlh-vip:59600
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 7. row ***************************
Id: 6153654
User: dlh
Host: DLH03:42901
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 8. row ***************************
Id: 6153956
User: dlh
Host: DLH03:43325
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 9. row ***************************
Id: 6153959
User: dlh
Host: DLH03:43331
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 10. row ***************************
Id: 6154058
User: dlh
Host: dlh-vip:36630
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 11. row ***************************
Id: 6154184
User: dlh
Host: DLH03:43794
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 12. row ***************************
Id: 6154213
User: dlh
Host: DLH03:43803
db: dlhbe
Command: Query
Time: 54
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 13. row ***************************
Id: 6154235
User: dlh
Host: dlh-vip:36998
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 14. row ***************************
Id: 6154239
User: dlh
Host: dlh-vip:37003
db: dlhbe
Command: Query
Time: 54
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 15. row ***************************
Id: 6154242
User: dlh
Host: dlh-vip:37007
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 16. row ***************************
Id: 6154246
User: dlh
Host: dlh-vip:37010
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 17. row ***************************
Id: 6154247
User: dlh
Host: dlh-vip:37012
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 18. row ***************************
Id: 6154250
User: dlh
Host: dlh-vip:37016
db: dlhbe
Command: Query
Time: 54
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 19. row ***************************
Id: 6154251
User: dlh
Host: dlh-vip:37017
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 20. row ***************************
Id: 6154252
User: dlh
Host: dlh-vip:37018
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 21. row ***************************
Id: 6154265
User: dlh
Host: DLH03:43822
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 22. row ***************************
Id: 6154269
User: dlh
Host: DLH03:43824
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 23. row ***************************
Id: 6155315
User: root
Host: localhost
db: dlhbe
Command: Query
Time: 1204
State: Sending data
Info: SELECT /*!40001 SQL_NO_CACHE */ * FROM `db_external_notification`
*************************** 24. row ***************************
Id: 6156544
User: dlh
Host: DLH03:44437
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 25. row ***************************
Id: 6156612
User: dlh
Host: dlh-vip:42326
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 26. row ***************************
Id: 6158068
User: dlh
Host: DLH03:44972
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 27. row ***************************
Id: 6158069
User: dlh
Host: DLH03:44973
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 28. row ***************************
Id: 6158070
User: dlh
Host: DLH03:44974
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 29. row ***************************
Id: 6158073
User: dlh
Host: DLH03:44975
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 30. row ***************************
Id: 6158074
User: dlh
Host: DLH03:44976
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 31. row ***************************
Id: 6158075
User: dlh
Host: DLH03:44977
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 32. row ***************************
Id: 6158076
User: dlh
Host: DLH03:44978
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 33. row ***************************
Id: 6158077
User: dlh
Host: DLH03:44979
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 34. row ***************************
Id: 6158078
User: dlh
Host: dlh-vip:45727
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 35. row ***************************
Id: 6158079
User: dlh
Host: dlh-vip:45728
db: dlhbe
Command: Query
Time: 0
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 36. row ***************************
Id: 6158080
User: dlh
Host: DLH03:44980
db: dlhbe
Command: Query
Time: 1
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 37. row ***************************
Id: 6158081
User: dlh
Host: dlh-vip:45729
db: dlhbe
Command: Sleep
Time: 27
State:
Info: NULL
*************************** 38. row ***************************
Id: 6158082
User: dlh
Host: dlh-vip:45730
db: dlhbe
Command: Query
Time: 0
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 39. row ***************************
Id: 6158083
User: dlh
Host: dlh-vip:45731
db: dlhbe
Command: Query
Time: 0
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 40. row ***************************
Id: 6158087
User: dlh
Host: DLH03:44986
db: dlhbe
Command: Query
Time: 1
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 41. row ***************************
Id: 6158089
User: dlh
Host: dlh-vip:45742
db: dlhbe
Command: Sleep
Time: 27
State:
Info: NULL
*************************** 42. row ***************************
Id: 6158090
User: dlh
Host: dlh-vip:45743
db: dlhbe
Command: Query
Time: 0
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 43. row ***************************
Id: 6158091
User: dlh
Host: dlh-vip:45744
db: dlhbe
Command: Sleep
Time: 0
State:
Info: NULL
*************************** 44. row ***************************
Id: 6158092
User: dlh
Host: dlh-vip:45745
db: dlhbe
Command: Sleep
Time: 27
State:
Info: NULL
*************************** 45. row ***************************
Id: 6158093
User: dlh
Host: dlh-vip:45746
db: dlhbe
Command: Sleep
Time: 27
State:
Info: NULL
*************************** 46. row ***************************
Id: 6158094
User: dlh
Host: dlh-vip:45747
db: dlhbe
Command: Sleep
Time: 27
State:
Info: NULL
*************************** 47. row ***************************
Id: 6158095
User: dlh
Host: dlh-vip:45748
db: dlhbe
Command: Query
Time: 0
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 48. row ***************************
Id: 6158096
User: dlh
Host: dlh-vip:45749
db: dlhbe
Command: Sleep
Time: 28
State:
Info: NULL
*************************** 49. row ***************************
Id: 6158097
User: dlh
Host: dlh-vip:45750
db: dlhbe
Command: Sleep
Time: 27
State:
Info: NULL
*************************** 50. row ***************************
Id: 6158100
User: dlh
Host: DLH03:44990
db: dlhbe
Command: Query
Time: 0
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 51. row ***************************
Id: 6158101
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show full processlist
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2014-07-21 04:06:01 861e4b70 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 60 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3456521 srv_active, 0 srv_shutdown, 1196243 srv_idle
srv_master_thread log flush and writes: 4652764
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 49923667
OS WAIT ARRAY INFO: signal count 63709458
Mutex spin waits 154026294, rounds 667509484, OS waits 11940078
RW-shared spins 49738202, rounds 1094105577, OS waits 29201229
RW-excl spins 17827968, rounds 400775195, OS waits 7537888
Spin rounds per wait: 4.33 mutex, 22.00 RW-shared, 22.48 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-07-20 11:50:09 85fe8b70
.....
------------
TRANSACTIONS
------------
Trx id counter 1252628184
Purge done for trx's n:o < 1252538413 undo n:o < 0 state: running but idle
History list length 25142
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 6158101, OS thread handle 0x861e4b70, query id 368090598 localhost root init
show engine innodb status
---TRANSACTION 1252626917, not started
MySQL thread id 6158097, OS thread handle 0x8509db70, query id 368065640 dlh-vip 80.77.222.224 dlh cleaning up
---TRANSACTION 1252626903, not started
MySQL thread id 6158093, OS thread handle 0x85874b70, query id 368064072 dlh-vip 80.77.222.224 dlh cleaning up
---TRANSACTION 1252628121, not started
MySQL thread id 6158095, OS thread handle 0x84eceb70, query id 368088537 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626916, not started
MySQL thread id 6158094, OS thread handle 0x8607bb70, query id 368065519 dlh-vip 80.77.222.224 dlh cleaning up
---TRANSACTION 1252626922, not started
MySQL thread id 6158092, OS thread handle 0x85fb7b70, query id 368065550 dlh-vip 80.77.222.224 dlh cleaning up
---TRANSACTION 1252626947, not started
MySQL thread id 6158090, OS thread handle 0x8636cb70, query id 368090479 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626918, not started
MySQL thread id 6158089, OS thread handle 0x85c90b70, query id 368065608 dlh-vip 80.77.222.224 dlh cleaning up
---TRANSACTION 1252628080, not started
MySQL thread id 6158087, OS thread handle 0x8c9ffb70, query id 368087871 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252628126, not started
MySQL thread id 6158083, OS thread handle 0x8599ab70, query id 368088717 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252628082, not started
MySQL thread id 6158082, OS thread handle 0x862a8b70, query id 368088333 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626901, not started
MySQL thread id 6158081, OS thread handle 0x85cf2b70, query id 368064364 dlh-vip 80.77.222.224 dlh cleaning up
---TRANSACTION 1252628083, not started
MySQL thread id 6158080, OS thread handle 0x84e3bb70, query id 368087766 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252628144, not started
MySQL thread id 6158079, OS thread handle 0x86277b70, query id 368089342 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626745, not started
MySQL thread id 6158078, OS thread handle 0x863ceb70, query id 368055627 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626597, not started
MySQL thread id 6158070, OS thread handle 0x85969b70, query id 368054983 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626595, not started
MySQL thread id 6158069, OS thread handle 0x85aa2b70, query id 368054560 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626594, not started
MySQL thread id 6158068, OS thread handle 0x8540ab70, query id 368054518 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252622576, not started
MySQL thread id 6156612, OS thread handle 0x8523bb70, query id 368054464 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626580, not started
MySQL thread id 6156544, OS thread handle 0x85f42b70, query id 368054087 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252625043, not started
MySQL thread id 6154251, OS thread handle 0x858d6b70, query id 368054016 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252625299, not started
MySQL thread id 6154252, OS thread handle 0x860ddb70, query id 368053836 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252619213, not started
MySQL thread id 6154269, OS thread handle 0x86019b70, query id 368054387 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626549, not started
MySQL thread id 6154265, OS thread handle 0x8546cb70, query id 368054875 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626539, not started
MySQL thread id 6154250, OS thread handle 0x8656cb70, query id 368053550 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626714, not started
MySQL thread id 6154247, OS thread handle 0x8503bb70, query id 368054813 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626732, not started
MySQL thread id 6154246, OS thread handle 0x85d85b70, query id 368055235 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626724, not started
MySQL thread id 6154242, OS thread handle 0x85a40b70, query id 368055002 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626534, not started
MySQL thread id 6154239, OS thread handle 0x850ffb70, query id 368053665 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626743, not started
MySQL thread id 6154235, OS thread handle 0x85cc1b70, query id 368055556 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626552, not started
MySQL thread id 6154213, OS thread handle 0x8526cb70, query id 368053610 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252619256, not started
MySQL thread id 6154184, OS thread handle 0x850ceb70, query id 368054052 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626722, not started
MySQL thread id 6154058, OS thread handle 0x852ceb70, query id 368054928 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626591, not started
MySQL thread id 6153959, OS thread handle 0x8506cb70, query id 368054418 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252619241, not started
MySQL thread id 6153956, OS thread handle 0x8cd5ab70, query id 368054273 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626553, not started
MySQL thread id 6153654, OS thread handle 0x854ffb70, query id 368053962 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252622916, not started
MySQL thread id 6152552, OS thread handle 0x8543bb70, query id 368054234 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626587, not started
MySQL thread id 6152220, OS thread handle 0x8653bb70, query id 368054304 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626744, not started
MySQL thread id 6149757, OS thread handle 0x86f99b70, query id 368055585 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252628183, ACTIVE 0 sec preparing
11 lock struct(s), heap size 1024, 6 row lock(s), undo log entries 1
MySQL thread id 6158091, OS thread handle 0x85e1cb70, query id 368090599 dlh-vip 80.77.222.224 dlh init
commit
---TRANSACTION 1252538404, ACTIVE 3358 sec fetching rows
mysql tables in use 1, locked 0
MySQL thread id 6155315, OS thread handle 0x85f11b70, query id 367774810 localhost root Sending data
SELECT /*!40001 SQL_NO_CACHE */ * FROM `db_external_notification`
Trx read view will not see trx with id >= 1252538405, sees < 1252538391
--------
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: 1; buffer pool: 0
295282936 OS file reads, 473892052 OS file writes, 253451291 OS fsyncs
1 pending preads, 0 pending pwrites
293.18 reads/s, 8204 avg bytes/read, 62.32 writes/s, 38.78 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 14072, seg size 14074, 11592456 merges
merged operations:
insert 12336707, delete mark 274853607, delete 28754017
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 6225619, node heap has 167 buffer(s)
2606.47 hash searches/s, 1299.91 non-hash searches/s
---
LOG
---
Log sequence number 2303987096755
Log flushed up to 2303987096308
Pages flushed up to 2303985880828
Last checkpoint at 2303985880594
1 pending log writes, 0 pending chkp writes
133410571 log i/o's done, 23.85 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1593704448; in additional pool allocated 0
Dictionary memory allocated 647018
Buffer pool size 96000
Free buffers 8007
Database pages 142275
Old database pages 52493
Modified db pages 557
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 116941040, not young 2313215399
1.42 youngs/s, 5589.27 non-youngs/s
Pages read 295282134, created 5440190, written 312659916
293.18 reads/s, 1.65 creates/s, 33.75 writes/s
Buffer pool hit rate 987 / 1000, young-making rate 0 / 1000 not 266 / 1000
Pages read ahead 0.13/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 142275, unzip_LRU len: 14375
I/O sum[128304]:cur[760], unzip sum[127808]:cur[760]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 12000
Free buffers 958
Database pages 17728
Old database pages 6564
Modified db pages 88
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14883227, not young 1366348819
0.22 youngs/s, 630.92 non-youngs/s
Pages read 37128920, created 688829, written 46032172
33.70 reads/s, 0.22 creates/s, 4.90 writes/s
Buffer pool hit rate 983 / 1000, young-making rate 0 / 1000 not 334 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17728, unzip_LRU len: 1818
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 1
Buffer pool size 12000
Free buffers 964
Database pages 17887
Old database pages 6611
Modified db pages 42
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14832036, not young 1347853892
0.22 youngs/s, 742.89 non-youngs/s
Pages read 37132279, created 671555, written 31068421
40.28 reads/s, 0.12 creates/s, 2.25 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 0 / 1000 not 266 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17887, unzip_LRU len: 1834
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 2
Buffer pool size 12000
Free buffers 1019
Database pages 17735
Old database pages 6530
Modified db pages 34
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14456926, not young 1361677290
0.18 youngs/s, 790.47 non-youngs/s
Pages read 37142751, created 694532, written 32566792
39.77 reads/s, 0.13 creates/s, 2.40 writes/s
Buffer pool hit rate 989 / 1000, young-making rate 0 / 1000 not 235 / 1000
Pages read ahead 0.13/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17735, unzip_LRU len: 1785
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 3
Buffer pool size 12000
Free buffers 1024
Database pages 17805
Old database pages 6552
Modified db pages 51
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14416382, not young 1369851293
0.25 youngs/s, 738.29 non-youngs/s
Pages read 37127164, created 652209, written 43677577
37.80 reads/s, 0.62 creates/s, 6.03 writes/s
Buffer pool hit rate 987 / 1000, young-making rate 0 / 1000 not 259 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17805, unzip_LRU len: 1785
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 4
Buffer pool size 12000
Free buffers 1024
Database pages 17775
Old database pages 6544
Modified db pages 124
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14844262, not young 1383000270
0.10 youngs/s, 644.31 non-youngs/s
Pages read 36638089, created 685866, written 53663045
33.98 reads/s, 0.13 creates/s, 8.35 writes/s
Buffer pool hit rate 988 / 1000, young-making rate 0 / 1000 not 237 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17775, unzip_LRU len: 1782
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 5
Buffer pool size 12000
Free buffers 970
Database pages 17722
Old database pages 6560
Modified db pages 88
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14147801, not young 1327998846
0.08 youngs/s, 692.84 non-youngs/s
Pages read 35559278, created 666045, written 36917203
35.68 reads/s, 0.12 creates/s, 4.52 writes/s
Buffer pool hit rate 990 / 1000, young-making rate 0 / 1000 not 209 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17722, unzip_LRU len: 1805
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 6
Buffer pool size 12000
Free buffers 1024
Database pages 17740
Old database pages 6537
Modified db pages 71
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 15062149, not young 1413480455
0.27 youngs/s, 730.74 non-youngs/s
Pages read 37952905, created 677723, written 33215113
39.43 reads/s, 0.23 creates/s, 2.83 writes/s
Buffer pool hit rate 985 / 1000, young-making rate 0 / 1000 not 291 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17740, unzip_LRU len: 1776
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 7
Buffer pool size 12000
Free buffers 1024
Database pages 17883
Old database pages 6595
Modified db pages 59
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14298257, not young 1332939126
0.10 youngs/s, 618.82 non-youngs/s
Pages read 36600748, created 703431, written 35519593
32.53 reads/s, 0.08 creates/s, 2.47 writes/s
Buffer pool hit rate 980 / 1000, young-making rate 0 / 1000 not 396 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17883, unzip_LRU len: 1790
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 10583, id 2329414512, state: sleeping
Number of rows inserted 463254282, updated 120516095, deleted 228648646, read 733055259
97.83 inserts/s, 13.82 updates/s, 9.83 deletes/s, 6805.20 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
according to the docs mysqldump --single transaction should not block writers if all tables are innodb and there is no DDL or OPTIMIZE or ANALYZE and whatnot.
However yesterday i have encountered the following situation (output of show full processlist and show innodb status, somewhat anonymized). Can someone tell me what are the writers waiting for? All inserts are simple insert into x(y,z) values (const, const), no fancy insert selects or anything else.
*************************** 1. row ***************************
now(): 2014-07-21 04:06:01
*************************** 1. row ***************************
Id: 2336222
User: dlh
Host: DLH03:35538
db: dlhbe
Command: Sleep
Time: 4
State:
Info: NULL
*************************** 2. row ***************************
Id: 2337424
User: dlh
Host: dlh-vip:54202
db: dlhbe
Command: Sleep
Time: 9
State:
Info: NULL
*************************** 3. row ***************************
Id: 3749630
User: repl
Host: DLH03:38959
db: NULL
Command: Binlog Dump
Time: 1929524
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 4. row ***************************
Id: 6149757
User: dlh
Host: dlh-vip:50788
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 5. row ***************************
Id: 6152220
User: dlh
Host: DLH03:39252
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 6. row ***************************
Id: 6152552
User: dlh
Host: dlh-vip:59600
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 7. row ***************************
Id: 6153654
User: dlh
Host: DLH03:42901
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 8. row ***************************
Id: 6153956
User: dlh
Host: DLH03:43325
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 9. row ***************************
Id: 6153959
User: dlh
Host: DLH03:43331
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 10. row ***************************
Id: 6154058
User: dlh
Host: dlh-vip:36630
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 11. row ***************************
Id: 6154184
User: dlh
Host: DLH03:43794
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 12. row ***************************
Id: 6154213
User: dlh
Host: DLH03:43803
db: dlhbe
Command: Query
Time: 54
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 13. row ***************************
Id: 6154235
User: dlh
Host: dlh-vip:36998
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 14. row ***************************
Id: 6154239
User: dlh
Host: dlh-vip:37003
db: dlhbe
Command: Query
Time: 54
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 15. row ***************************
Id: 6154242
User: dlh
Host: dlh-vip:37007
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 16. row ***************************
Id: 6154246
User: dlh
Host: dlh-vip:37010
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 17. row ***************************
Id: 6154247
User: dlh
Host: dlh-vip:37012
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 18. row ***************************
Id: 6154250
User: dlh
Host: dlh-vip:37016
db: dlhbe
Command: Query
Time: 54
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 19. row ***************************
Id: 6154251
User: dlh
Host: dlh-vip:37017
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 20. row ***************************
Id: 6154252
User: dlh
Host: dlh-vip:37018
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 21. row ***************************
Id: 6154265
User: dlh
Host: DLH03:43822
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 22. row ***************************
Id: 6154269
User: dlh
Host: DLH03:43824
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 23. row ***************************
Id: 6155315
User: root
Host: localhost
db: dlhbe
Command: Query
Time: 1204
State: Sending data
Info: SELECT /*!40001 SQL_NO_CACHE */ * FROM `db_external_notification`
*************************** 24. row ***************************
Id: 6156544
User: dlh
Host: DLH03:44437
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 25. row ***************************
Id: 6156612
User: dlh
Host: dlh-vip:42326
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 26. row ***************************
Id: 6158068
User: dlh
Host: DLH03:44972
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 27. row ***************************
Id: 6158069
User: dlh
Host: DLH03:44973
db: dlhbe
Command: Query
Time: 53
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 28. row ***************************
Id: 6158070
User: dlh
Host: DLH03:44974
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 29. row ***************************
Id: 6158073
User: dlh
Host: DLH03:44975
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 30. row ***************************
Id: 6158074
User: dlh
Host: DLH03:44976
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 31. row ***************************
Id: 6158075
User: dlh
Host: DLH03:44977
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 32. row ***************************
Id: 6158076
User: dlh
Host: DLH03:44978
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 33. row ***************************
Id: 6158077
User: dlh
Host: DLH03:44979
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 34. row ***************************
Id: 6158078
User: dlh
Host: dlh-vip:45727
db: dlhbe
Command: Query
Time: 51
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 35. row ***************************
Id: 6158079
User: dlh
Host: dlh-vip:45728
db: dlhbe
Command: Query
Time: 0
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 36. row ***************************
Id: 6158080
User: dlh
Host: DLH03:44980
db: dlhbe
Command: Query
Time: 1
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 37. row ***************************
Id: 6158081
User: dlh
Host: dlh-vip:45729
db: dlhbe
Command: Sleep
Time: 27
State:
Info: NULL
*************************** 38. row ***************************
Id: 6158082
User: dlh
Host: dlh-vip:45730
db: dlhbe
Command: Query
Time: 0
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 39. row ***************************
Id: 6158083
User: dlh
Host: dlh-vip:45731
db: dlhbe
Command: Query
Time: 0
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 40. row ***************************
Id: 6158087
User: dlh
Host: DLH03:44986
db: dlhbe
Command: Query
Time: 1
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 41. row ***************************
Id: 6158089
User: dlh
Host: dlh-vip:45742
db: dlhbe
Command: Sleep
Time: 27
State:
Info: NULL
*************************** 42. row ***************************
Id: 6158090
User: dlh
Host: dlh-vip:45743
db: dlhbe
Command: Query
Time: 0
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 43. row ***************************
Id: 6158091
User: dlh
Host: dlh-vip:45744
db: dlhbe
Command: Sleep
Time: 0
State:
Info: NULL
*************************** 44. row ***************************
Id: 6158092
User: dlh
Host: dlh-vip:45745
db: dlhbe
Command: Sleep
Time: 27
State:
Info: NULL
*************************** 45. row ***************************
Id: 6158093
User: dlh
Host: dlh-vip:45746
db: dlhbe
Command: Sleep
Time: 27
State:
Info: NULL
*************************** 46. row ***************************
Id: 6158094
User: dlh
Host: dlh-vip:45747
db: dlhbe
Command: Sleep
Time: 27
State:
Info: NULL
*************************** 47. row ***************************
Id: 6158095
User: dlh
Host: dlh-vip:45748
db: dlhbe
Command: Query
Time: 0
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 48. row ***************************
Id: 6158096
User: dlh
Host: dlh-vip:45749
db: dlhbe
Command: Sleep
Time: 28
State:
Info: NULL
*************************** 49. row ***************************
Id: 6158097
User: dlh
Host: dlh-vip:45750
db: dlhbe
Command: Sleep
Time: 27
State:
Info: NULL
*************************** 50. row ***************************
Id: 6158100
User: dlh
Host: DLH03:44990
db: dlhbe
Command: Query
Time: 0
State: Waiting for table flush
Info: insert into db_external_notification (...
*************************** 51. row ***************************
Id: 6158101
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show full processlist
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2014-07-21 04:06:01 861e4b70 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 60 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3456521 srv_active, 0 srv_shutdown, 1196243 srv_idle
srv_master_thread log flush and writes: 4652764
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 49923667
OS WAIT ARRAY INFO: signal count 63709458
Mutex spin waits 154026294, rounds 667509484, OS waits 11940078
RW-shared spins 49738202, rounds 1094105577, OS waits 29201229
RW-excl spins 17827968, rounds 400775195, OS waits 7537888
Spin rounds per wait: 4.33 mutex, 22.00 RW-shared, 22.48 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-07-20 11:50:09 85fe8b70
.....
------------
TRANSACTIONS
------------
Trx id counter 1252628184
Purge done for trx's n:o < 1252538413 undo n:o < 0 state: running but idle
History list length 25142
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 6158101, OS thread handle 0x861e4b70, query id 368090598 localhost root init
show engine innodb status
---TRANSACTION 1252626917, not started
MySQL thread id 6158097, OS thread handle 0x8509db70, query id 368065640 dlh-vip 80.77.222.224 dlh cleaning up
---TRANSACTION 1252626903, not started
MySQL thread id 6158093, OS thread handle 0x85874b70, query id 368064072 dlh-vip 80.77.222.224 dlh cleaning up
---TRANSACTION 1252628121, not started
MySQL thread id 6158095, OS thread handle 0x84eceb70, query id 368088537 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626916, not started
MySQL thread id 6158094, OS thread handle 0x8607bb70, query id 368065519 dlh-vip 80.77.222.224 dlh cleaning up
---TRANSACTION 1252626922, not started
MySQL thread id 6158092, OS thread handle 0x85fb7b70, query id 368065550 dlh-vip 80.77.222.224 dlh cleaning up
---TRANSACTION 1252626947, not started
MySQL thread id 6158090, OS thread handle 0x8636cb70, query id 368090479 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626918, not started
MySQL thread id 6158089, OS thread handle 0x85c90b70, query id 368065608 dlh-vip 80.77.222.224 dlh cleaning up
---TRANSACTION 1252628080, not started
MySQL thread id 6158087, OS thread handle 0x8c9ffb70, query id 368087871 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252628126, not started
MySQL thread id 6158083, OS thread handle 0x8599ab70, query id 368088717 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252628082, not started
MySQL thread id 6158082, OS thread handle 0x862a8b70, query id 368088333 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626901, not started
MySQL thread id 6158081, OS thread handle 0x85cf2b70, query id 368064364 dlh-vip 80.77.222.224 dlh cleaning up
---TRANSACTION 1252628083, not started
MySQL thread id 6158080, OS thread handle 0x84e3bb70, query id 368087766 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252628144, not started
MySQL thread id 6158079, OS thread handle 0x86277b70, query id 368089342 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626745, not started
MySQL thread id 6158078, OS thread handle 0x863ceb70, query id 368055627 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626597, not started
MySQL thread id 6158070, OS thread handle 0x85969b70, query id 368054983 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626595, not started
MySQL thread id 6158069, OS thread handle 0x85aa2b70, query id 368054560 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626594, not started
MySQL thread id 6158068, OS thread handle 0x8540ab70, query id 368054518 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252622576, not started
MySQL thread id 6156612, OS thread handle 0x8523bb70, query id 368054464 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626580, not started
MySQL thread id 6156544, OS thread handle 0x85f42b70, query id 368054087 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252625043, not started
MySQL thread id 6154251, OS thread handle 0x858d6b70, query id 368054016 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252625299, not started
MySQL thread id 6154252, OS thread handle 0x860ddb70, query id 368053836 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252619213, not started
MySQL thread id 6154269, OS thread handle 0x86019b70, query id 368054387 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626549, not started
MySQL thread id 6154265, OS thread handle 0x8546cb70, query id 368054875 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626539, not started
MySQL thread id 6154250, OS thread handle 0x8656cb70, query id 368053550 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626714, not started
MySQL thread id 6154247, OS thread handle 0x8503bb70, query id 368054813 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626732, not started
MySQL thread id 6154246, OS thread handle 0x85d85b70, query id 368055235 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626724, not started
MySQL thread id 6154242, OS thread handle 0x85a40b70, query id 368055002 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626534, not started
MySQL thread id 6154239, OS thread handle 0x850ffb70, query id 368053665 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626743, not started
MySQL thread id 6154235, OS thread handle 0x85cc1b70, query id 368055556 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626552, not started
MySQL thread id 6154213, OS thread handle 0x8526cb70, query id 368053610 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252619256, not started
MySQL thread id 6154184, OS thread handle 0x850ceb70, query id 368054052 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626722, not started
MySQL thread id 6154058, OS thread handle 0x852ceb70, query id 368054928 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626591, not started
MySQL thread id 6153959, OS thread handle 0x8506cb70, query id 368054418 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252619241, not started
MySQL thread id 6153956, OS thread handle 0x8cd5ab70, query id 368054273 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626553, not started
MySQL thread id 6153654, OS thread handle 0x854ffb70, query id 368053962 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252622916, not started
MySQL thread id 6152552, OS thread handle 0x8543bb70, query id 368054234 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626587, not started
MySQL thread id 6152220, OS thread handle 0x8653bb70, query id 368054304 DLH03 80.77.222.221 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252626744, not started
MySQL thread id 6149757, OS thread handle 0x86f99b70, query id 368055585 dlh-vip 80.77.222.224 dlh Waiting for table flush
insert into db_external_notification (...
---TRANSACTION 1252628183, ACTIVE 0 sec preparing
11 lock struct(s), heap size 1024, 6 row lock(s), undo log entries 1
MySQL thread id 6158091, OS thread handle 0x85e1cb70, query id 368090599 dlh-vip 80.77.222.224 dlh init
commit
---TRANSACTION 1252538404, ACTIVE 3358 sec fetching rows
mysql tables in use 1, locked 0
MySQL thread id 6155315, OS thread handle 0x85f11b70, query id 367774810 localhost root Sending data
SELECT /*!40001 SQL_NO_CACHE */ * FROM `db_external_notification`
Trx read view will not see trx with id >= 1252538405, sees < 1252538391
--------
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: 1; buffer pool: 0
295282936 OS file reads, 473892052 OS file writes, 253451291 OS fsyncs
1 pending preads, 0 pending pwrites
293.18 reads/s, 8204 avg bytes/read, 62.32 writes/s, 38.78 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 14072, seg size 14074, 11592456 merges
merged operations:
insert 12336707, delete mark 274853607, delete 28754017
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 6225619, node heap has 167 buffer(s)
2606.47 hash searches/s, 1299.91 non-hash searches/s
---
LOG
---
Log sequence number 2303987096755
Log flushed up to 2303987096308
Pages flushed up to 2303985880828
Last checkpoint at 2303985880594
1 pending log writes, 0 pending chkp writes
133410571 log i/o's done, 23.85 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1593704448; in additional pool allocated 0
Dictionary memory allocated 647018
Buffer pool size 96000
Free buffers 8007
Database pages 142275
Old database pages 52493
Modified db pages 557
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 116941040, not young 2313215399
1.42 youngs/s, 5589.27 non-youngs/s
Pages read 295282134, created 5440190, written 312659916
293.18 reads/s, 1.65 creates/s, 33.75 writes/s
Buffer pool hit rate 987 / 1000, young-making rate 0 / 1000 not 266 / 1000
Pages read ahead 0.13/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 142275, unzip_LRU len: 14375
I/O sum[128304]:cur[760], unzip sum[127808]:cur[760]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 12000
Free buffers 958
Database pages 17728
Old database pages 6564
Modified db pages 88
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14883227, not young 1366348819
0.22 youngs/s, 630.92 non-youngs/s
Pages read 37128920, created 688829, written 46032172
33.70 reads/s, 0.22 creates/s, 4.90 writes/s
Buffer pool hit rate 983 / 1000, young-making rate 0 / 1000 not 334 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17728, unzip_LRU len: 1818
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 1
Buffer pool size 12000
Free buffers 964
Database pages 17887
Old database pages 6611
Modified db pages 42
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14832036, not young 1347853892
0.22 youngs/s, 742.89 non-youngs/s
Pages read 37132279, created 671555, written 31068421
40.28 reads/s, 0.12 creates/s, 2.25 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 0 / 1000 not 266 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17887, unzip_LRU len: 1834
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 2
Buffer pool size 12000
Free buffers 1019
Database pages 17735
Old database pages 6530
Modified db pages 34
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14456926, not young 1361677290
0.18 youngs/s, 790.47 non-youngs/s
Pages read 37142751, created 694532, written 32566792
39.77 reads/s, 0.13 creates/s, 2.40 writes/s
Buffer pool hit rate 989 / 1000, young-making rate 0 / 1000 not 235 / 1000
Pages read ahead 0.13/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17735, unzip_LRU len: 1785
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 3
Buffer pool size 12000
Free buffers 1024
Database pages 17805
Old database pages 6552
Modified db pages 51
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14416382, not young 1369851293
0.25 youngs/s, 738.29 non-youngs/s
Pages read 37127164, created 652209, written 43677577
37.80 reads/s, 0.62 creates/s, 6.03 writes/s
Buffer pool hit rate 987 / 1000, young-making rate 0 / 1000 not 259 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17805, unzip_LRU len: 1785
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 4
Buffer pool size 12000
Free buffers 1024
Database pages 17775
Old database pages 6544
Modified db pages 124
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14844262, not young 1383000270
0.10 youngs/s, 644.31 non-youngs/s
Pages read 36638089, created 685866, written 53663045
33.98 reads/s, 0.13 creates/s, 8.35 writes/s
Buffer pool hit rate 988 / 1000, young-making rate 0 / 1000 not 237 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17775, unzip_LRU len: 1782
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 5
Buffer pool size 12000
Free buffers 970
Database pages 17722
Old database pages 6560
Modified db pages 88
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14147801, not young 1327998846
0.08 youngs/s, 692.84 non-youngs/s
Pages read 35559278, created 666045, written 36917203
35.68 reads/s, 0.12 creates/s, 4.52 writes/s
Buffer pool hit rate 990 / 1000, young-making rate 0 / 1000 not 209 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17722, unzip_LRU len: 1805
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 6
Buffer pool size 12000
Free buffers 1024
Database pages 17740
Old database pages 6537
Modified db pages 71
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 15062149, not young 1413480455
0.27 youngs/s, 730.74 non-youngs/s
Pages read 37952905, created 677723, written 33215113
39.43 reads/s, 0.23 creates/s, 2.83 writes/s
Buffer pool hit rate 985 / 1000, young-making rate 0 / 1000 not 291 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17740, unzip_LRU len: 1776
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
---BUFFER POOL 7
Buffer pool size 12000
Free buffers 1024
Database pages 17883
Old database pages 6595
Modified db pages 59
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14298257, not young 1332939126
0.10 youngs/s, 618.82 non-youngs/s
Pages read 36600748, created 703431, written 35519593
32.53 reads/s, 0.08 creates/s, 2.47 writes/s
Buffer pool hit rate 980 / 1000, young-making rate 0 / 1000 not 396 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17883, unzip_LRU len: 1790
I/O sum[16038]:cur[95], unzip sum[15976]:cur[95]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 10583, id 2329414512, state: sleeping
Number of rows inserted 463254282, updated 120516095, deleted 228648646, read 733055259
97.83 inserts/s, 13.82 updates/s, 9.83 deletes/s, 6805.20 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.