{\rtf1\ansi\ansicpg936\cocoartf1504\cocoasubrtf820 {\fonttbl\f0\fswiss\fcharset0 Helvetica;} {\colortbl;\red255\green255\blue255;} {\*\expandedcolortbl;;} \paperw11900\paperh16840\margl1440\margr1440\vieww10800\viewh8400\viewkind0 \pard\tx566\tx1133\tx1700\tx2267\tx2834\tx3401\tx3968\tx4535\tx5102\tx5669\tx6236\tx6803\pardirnatural\partightenfactor0 \f0\fs24 \cf0 InnoDB: ###### Diagnostic info printed to the standard error stream\ 2019-02-15T13:52:13.504834+08:00 0 [Warning] InnoDB: A long semaphore wait:\ --Thread 139968660428544 has waited at fsp0fsp.cc line 167 for 582.00 seconds the semaphore:\ SX-lock on RW-latch at 0x7f4a109a0278 created in file buf0buf.cc line 1459\ a writer (thread id 139945733732096) has reserved it in mode SX\ number of readers 0, waiters flag 1, lock_word: 10000000\ Last time read locked in file row0sel.cc line 4980\ Last time write locked in file /data1/mutex_futex/mysql-5.7.17/storage/innobase/buf/buf0flu.cc line 1198\ InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:\ InnoDB: Pending preads 0, pwrites 0\ \ =====================================\ 2019-02-15 13:52:20 0x7f479f7fd700 INNODB MONITOR OUTPUT\ =====================================\ Per second averages calculated from the last 20 seconds\ -----------------\ BACKGROUND THREAD\ -----------------\ srv_master_thread loops: 12253 srv_active, 0 srv_shutdown, 590 srv_idle\ srv_master_thread log flush and writes: 12843\ ----------\ SEMAPHORES\ ----------\ OS WAIT ARRAY INFO: reservation count 1545\ --Thread 139968660428544 has waited at fsp0fsp.cc line 167 for 589.00 seconds the semaphore:\ SX-lock on RW-latch at 0x7f4a109a0278 created in file buf0buf.cc line 1459\ a writer (thread id 139945733732096) has reserved it in mode SX\ number of readers 0, waiters flag 1, lock_word: 10000000\ Last time read locked in file row0sel.cc line 4980\ Last time write locked in file /data1/mutex_futex/mysql-5.7.17/storage/innobase/buf/buf0flu.cc line 1198\ OS WAIT ARRAY INFO: signal count 44254\ RW-shared spins 0, rounds 131549, OS waits 650\ RW-excl spins 0, rounds 30355, OS waits 872\ RW-sx spins 20, rounds 396, OS waits 11\ Spin rounds per wait: 131549.00 RW-shared, 30355.00 RW-excl, 19.80 RW-sx\ ------------\ TRANSACTIONS\ ------------\ Trx id counter 3010674013\ Purge done for trx's n:o < 3010674009 undo n:o < 0 state: running but idle\ History list length 31\ LIST OF TRANSACTIONS FOR EACH SESSION:\ ---TRANSACTION 421444328622704, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328621824, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328641184, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328640304, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328639424, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328638544, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328637664, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328636784, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328635024, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328633264, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328632384, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328631504, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328630624, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328624464, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 421444328627984, not started\ 0 lock struct(s), heap size 1136, 0 row lock(s)\ ---TRANSACTION 3010673055, ACTIVE 589 sec updating or deleting\ mysql tables in use 1, locked 1\ 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1\ MySQL thread id 21, OS thread handle 139968660428544, query id 33528726 System lock\ --------\ 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: native aio handle (write thread)\ I/O thread 9 state: waiting for completed aio requests (write thread)\ Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 2, 0] ,\ ibuf aio reads:, log i/o's:, sync i/o's:\ Pending flushes (fsync) log: 0; buffer pool: 0\ 11398920 OS file reads, 16611507 OS file writes, 10821825 OS fsyncs\ 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s\ -------------------------------------\ INSERT BUFFER AND ADAPTIVE HASH INDEX\ -------------------------------------\ Ibuf: size 1, free list len 3396, seg size 3398, 0 merges\ merged operations:\ insert 0, delete mark 0, delete 0\ discarded operations:\ insert 0, delete mark 0, delete 0\ Hash table size 5312557, node heap has 1 buffer(s)\ Hash table size 5312557, node heap has 0 buffer(s)\ Hash table size 5312557, node heap has 1 buffer(s)\ Hash table size 5312557, node heap has 98 buffer(s)\ Hash table size 5312557, node heap has 25 buffer(s)\ Hash table size 5312557, node heap has 25 buffer(s)\ Hash table size 5312557, node heap has 24 buffer(s)\ Hash table size 5312557, node heap has 1 buffer(s)\ 0.00 hash searches/s, 0.00 non-hash searches/s\ ---\ LOG\ ---\ Log sequence number 2601053759160\ Log flushed up to 2601053759160\ Pages flushed up to 2600022941265\ Last checkpoint at 2600022941265\ 0 pending log flushes, 0 pending chkp writes\ 28719 log i/o's done, 0.00 log i/o's/second\ ----------------------\ BUFFER POOL AND MEMORY\ ----------------------\ Total large memory allocated 21967667200\ Dictionary memory allocated 73958534\ Buffer pool size 1310720\ Free buffers 1\ Database pages 2179363\ Old database pages 804300\ Modified db pages 486616\ Pending reads 0\ Pending writes: LRU 0, flush list 8, single page 0\ Pages made young 2556846, not young 16196647\ 0.00 youngs/s, 0.00 non-youngs/s\ Pages read 11383053, created 192250, written 16169471\ 0.00 reads/s, 0.00 creates/s, 0.00 writes/s\ No buffer pool page gets since the last printout\ Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\ LRU len: 2179363, unzip_LRU len: 217938\ I/O sum[0]:cur[0], unzip sum[0]:cur[0]\ ----------------------\ INDIVIDUAL BUFFER POOL INFO\ ----------------------\ ---BUFFER POOL 0\ Buffer pool size 131072\ Free buffers 0\ Database pages 217909\ Old database pages 80419\ Modified db pages 48438\ Pending reads 0\ Pending writes: LRU 0, flush list 0, single page 0\ Pages made young 261461, not young 1575420\ 0.00 youngs/s, 0.00 non-youngs/s\ Pages read 1113306, created 18219, written 1609513\ 0.00 reads/s, 0.00 creates/s, 0.00 writes/s\ No buffer pool page gets since the last printout\ Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\ LRU len: 217909, unzip_LRU len: 21791\ I/O sum[0]:cur[0], unzip sum[0]:cur[0]\ ---BUFFER POOL 1\ Buffer pool size 131072\ Free buffers 0\ Database pages 217929\ Old database pages 80427\ Modified db pages 48639\ Pending reads 0\ Pending writes: LRU 0, flush list 0, single page 0\ Pages made young 250794, not young 1655499\ 0.00 youngs/s, 0.00 non-youngs/s\ Pages read 1156792, created 20003, written 1616024\ 0.00 reads/s, 0.00 creates/s, 0.00 writes/s\ No buffer pool page gets since the last printout\ Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\ LRU len: 217929, unzip_LRU len: 21793\ I/O sum[0]:cur[0], unzip sum[0]:cur[0]\ ---BUFFER POOL 2\ Buffer pool size 131072\ Free buffers 0\ Database pages 217949\ Old database pages 80434\ Modified db pages 48929\ Pending reads 0\ Pending writes: LRU 0, flush list 0, single page 0\ Pages made young 261080, not young 1596348\ 0.00 youngs/s, 0.00 non-youngs/s\ Pages read 1123444, created 18688, written 1620604\ 0.00 reads/s, 0.00 creates/s, 0.00 writes/s\ No buffer pool page gets since the last printout\ Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\ LRU len: 217949, unzip_LRU len: 21795\ I/O sum[0]:cur[0], unzip sum[0]:cur[0]\ ---BUFFER POOL 3\ Buffer pool size 131072\ Free buffers 0\ Database pages 217859\ Old database pages 80400\ Modified db pages 48474\ Pending reads 0\ Pending writes: LRU 0, flush list 1, single page 0\ Pages made young 251326, not young 1656481\ 0.00 youngs/s, 0.00 non-youngs/s\ Pages read 1158845, created 20011, written 1620162\ 0.00 reads/s, 0.00 creates/s, 0.00 writes/s\ No buffer pool page gets since the last printout\ Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\ LRU len: 217859, unzip_LRU len: 21786\ I/O sum[0]:cur[0], unzip sum[0]:cur[0]\ ---BUFFER POOL 4\ Buffer pool size 131072\ Free buffers 0\ Database pages 218019\ Old database pages 80459\ Modified db pages 49084\ Pending reads 0\ Pending writes: LRU 0, flush list 2, single page 0\ Pages made young 261132, not young 1590576\ 0.00 youngs/s, 0.00 non-youngs/s\ Pages read 1120669, created 18353, written 1615520\ 0.00 reads/s, 0.00 creates/s, 0.00 writes/s\ No buffer pool page gets since the last printout\ Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\ LRU len: 218019, unzip_LRU len: 21802\ I/O sum[0]:cur[0], unzip sum[0]:cur[0]\ ---BUFFER POOL 5\ Buffer pool size 131072\ Free buffers 1\ Database pages 217949\ Old database pages 80436\ Modified db pages 48853\ Pending reads 0\ Pending writes: LRU 0, flush list 2, single page 0\ Pages made young 250545, not young 1653720\ 0.00 youngs/s, 0.00 non-youngs/s\ Pages read 1158617, created 20060, written 1620790\ 0.00 reads/s, 0.00 creates/s, 0.00 writes/s\ No buffer pool page gets since the last printout\ Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\ LRU len: 217949, unzip_LRU len: 21794\ I/O sum[0]:cur[0], unzip sum[0]:cur[0]\ ---BUFFER POOL 6\ Buffer pool size 131072\ Free buffers 0\ Database pages 217902\ Old database pages 80421\ Modified db pages 48996\ Pending reads 0\ Pending writes: LRU 0, flush list 3, single page 0\ Pages made young 259886, not young 1584123\ 0.00 youngs/s, 0.00 non-youngs/s\ Pages read 1116836, created 18488, written 1613711\ 0.00 reads/s, 0.00 creates/s, 0.00 writes/s\ No buffer pool page gets since the last printout\ Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\ LRU len: 217902, unzip_LRU len: 21791\ I/O sum[0]:cur[0], unzip sum[0]:cur[0]\ ---BUFFER POOL 7\ Buffer pool size 131072\ Free buffers 0\ Database pages 217879\ Old database pages 80410\ Modified db pages 48097\ Pending reads 0\ Pending writes: LRU 0, flush list 0, single page 0\ Pages made young 250589, not young 1651857\ 0.00 youngs/s, 0.00 non-youngs/s\ Pages read 1159344, created 20013, written 1621705\ 0.00 reads/s, 0.00 creates/s, 0.00 writes/s\ No buffer pool page gets since the last printout\ Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\ LRU len: 217879, unzip_LRU len: 21789\ I/O sum[0]:cur[0], unzip sum[0]:cur[0]\ ---BUFFER POOL 8\ Buffer pool size 131072\ Free buffers 0\ Database pages 217979\ Old database pages 80445\ Modified db pages 48871\ Pending reads 0\ Pending writes: LRU 0, flush list 0, single page 0\ Pages made young 259710, not young 1585662\ 0.00 youngs/s, 0.00 non-youngs/s\ Pages read 1118703, created 18209, written 1613658\ 0.00 reads/s, 0.00 creates/s, 0.00 writes/s\ No buffer pool page gets since the last printout\ Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\ LRU len: 217979, unzip_LRU len: 21798\ I/O sum[0]:cur[0], unzip sum[0]:cur[0]\ ---BUFFER POOL 9\ Buffer pool size 131072\ Free buffers 0\ Database pages 217989\ Old database pages 80449\ Modified db pages 48235\ Pending reads 0\ Pending writes: LRU 0, flush list 0, single page 0\ Pages made young 250323, not young 1646961\ 0.00 youngs/s, 0.00 non-youngs/s\ Pages read 1156497, created 20206, written 1617784\ 0.00 reads/s, 0.00 creates/s, 0.00 writes/s\ No buffer pool page gets since the last printout\ Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\ LRU len: 217989, unzip_LRU len: 21799\ I/O sum[0]:cur[0], unzip sum[0]:cur[0]\ --------------\ ROW OPERATIONS\ --------------\ 0 queries inside InnoDB, 0 queries in queue\ 0 read views open inside InnoDB\ Process ID=9162, Main thread ID=139945586968320, state: sleeping\ Number of rows inserted 12072876, updated 4692654, deleted 0, read 4709332\ 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s\ ----------------------------\ END OF INNODB MONITOR OUTPUT\ ============================\ }