Web lists-archives.com

Re: MySQL 5.7 Innodb performans issue




On Wednesday, May 20, 2015, wagnerbianchi.com wrote:
> Can you share the SHOW ENGINE INNODB STATUS\G ?

Sure, here it is. 

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2015-05-20 20:29:56 0x7f9a4c189700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 204 srv_active, 0 srv_shutdown, 2320 srv_idle
srv_master_thread log flush and writes: 2524
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5507
OS WAIT ARRAY INFO: signal count 5827
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 1901, rounds 3750, OS waits 1849
RW-excl spins 108, rounds 1784098, OS waits 8
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 1.97 RW-shared, 16519.43 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1826118
Purge done for trx's n:o < 1826115 undo n:o < 0 state: running but idle
History list length 1793
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421804350357096, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421804350355272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1826117, ACTIVE (PREPARED) 0 sec
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 12, OS thread handle 140329332676352, query id 123121 localhost 127.0.0.1 dahls 
query end
UPDATE washperiod SET washperiodstatus_id=1 WHERE washperiod_id=127687474
--------
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
104317 OS file reads, 6881 OS file writes, 6297 OS fsyncs
164.08 reads/s, 16384 avg bytes/read, 21.90 writes/s, 21.90 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 38 merges
merged operations:
 insert 130, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 53118839, node heap has 400 buffer(s)
783.22 hash searches/s, 854.11 non-hash searches/s
---
LOG
---
Log sequence number 137891623643
Log flushed up to   137891623643
Pages flushed up to 137884471017
Last checkpoint at  137884471017
0 pending log flushes, 0 pending chkp writes
4586 log i/o's done, 21.90 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 27485798400
Dictionary memory allocated 1342452
Buffer pool size   1638225
Free buffers       1533395
Database pages     104430
Old database pages 38704
Modified db pages  2007
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 104305, created 125, written 1448
164.08 reads/s, 0.30 creates/s, 0.00 writes/s
Buffer pool hit rate 992 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 50.39/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 104430, unzip_LRU len: 0
I/O sum[0]:cur[8], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   204775
Free buffers       188541
Database pages     16183
Old database pages 5993
Modified db pages  587
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 16136, created 47, written 257
18.00 reads/s, 0.20 creates/s, 0.00 writes/s
Buffer pool hit rate 996 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 7.50/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 16183, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   204775
Free buffers       192460
Database pages     12269
Old database pages 4548
Modified db pages  179
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12269, created 0, written 153
14.80 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 995 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 3.80/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 12269, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   204775
Free buffers       192081
Database pages     12645
Old database pages 4687
Modified db pages  93
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12644, created 1, written 155
16.50 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 990 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 3.90/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 12645, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   204775
Free buffers       192108
Database pages     12617
Old database pages 4677
Modified db pages  366
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12614, created 3, written 194
22.20 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 993 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 11.20/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 12617, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   204775
Free buffers       191980
Database pages     12744
Old database pages 4724
Modified db pages  214
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12736, created 8, written 172
20.10 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 990 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 7.50/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 12744, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   204775
Free buffers       192376
Database pages     12349
Old database pages 4578
Modified db pages  198
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12328, created 21, written 158
26.70 reads/s, 0.10 creates/s, 0.00 writes/s
Buffer pool hit rate 989 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 11.60/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 12349, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   204775
Free buffers       191857
Database pages     12865
Old database pages 4768
Modified db pages  236
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 8, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12855, created 10, written 165
25.30 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 991 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 4.90/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 12865, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   204800
Free buffers       191992
Database pages     12758
Old database pages 4729
Modified db pages  134
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12723, created 35, written 194
20.50 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 986 / 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: 12758, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=23924, Main thread ID=140300711941888, state: sleeping
Number of rows inserted 16718, updated 6422, deleted 12070, read 18051815
34.80 inserts/s, 21.00 updates/s, 31.40 deletes/s, 113447.76 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)



-- 
Jørn Dahl-Stamnes
homepage: http://photo.dahl-stamnes.net/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql