mysql 5.5 SHOW ENGINE INNODB STATUS 结果，帮忙解读下，性能方面需要怎么优化？

superlyq006 2017-12-02 11:37:28

Type: InnoDB
Name:
Status:
=====================================
171129 11:44:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 27 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1744145 1_second, 1744144 sleeps, 174413 10_second, 8 background, 8 flush
srv_master_thread log flush and writes: 1753475
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 775269689, signal count 743248737
Mutex spin waits 19657915518, rounds 111644016943, OS waits 211420715
RW-shared spins 4818237421, rounds 96442533131, OS waits 503302688
RW-excl spins 280554717, rounds 1428223922, OS waits 12720141
Spin rounds per wait: 5.68 mutex, 20.02 RW-shared, 5.09 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------

------------
TRANSACTIONS
------------
Trx id counter C7EEFA16
Purge done for trx's n:o < C7ED5825 undo n:o < 0
History list length 9879
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 473332, OS thread handle 0x7f2411aa1700, query id 1633854706 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION C7EEF16B, not started
MySQL thread id 472904, OS thread handle 0x7f2412be5700, query id 1633829832 xxx.xxx.xxx.62 yunxin
---TRANSACTION C7EEF850, not started
MySQL thread id 473310, OS thread handle 0x7f2410859700, query id 1633849089 xxx.xxx.xxx.64 yunxin
---TRANSACTION C7EEF25E, not started
MySQL thread id 472822, OS thread handle 0x7f24120fa700, query id 1633834150 xxx.xxx.xxx.64 yunxin
---TRANSACTION C7EEF9EE, not started
MySQL thread id 472793, OS thread handle 0x7f2416170700, query id 1633854390 xxx.xxx.xxx.61 yunxin
---TRANSACTION C7EEFA06, not started
MySQL thread id 472894, OS thread handle 0x7f2410dae700, query id 1633854536 xxx.xxx.xxx.62 yunxin
---TRANSACTION C7EED348, not started
MySQL thread id 472829, OS thread handle 0x7f231bfbe700, query id 1633772305 xxx.xxx.xxx.62 yunxin
---TRANSACTION C7EEFA04, not started
MySQL thread id 472874, OS thread handle 0x7f2412037700, query id 1633854533 xxx.xxx.xxx.62 yunxin
---TRANSACTION C7EEF0C7, not started
MySQL thread id 472882, OS thread handle 0x7f231bd34700, query id 1633827958 xxx.xxx.xxx.62 yunxin
---TRANSACTION C7EEFA02, not started
MySQL thread id 472831, OS thread handle 0x7f2410108700, query id 1633854524 xxx.xxx.xxx.62 yunxin
---TRANSACTION C7EEF612, not started
MySQL thread id 473220, OS thread handle 0x7f24113c6700, query id 1633841649 xxx.xxx.xxx.62 yunxin
---TRANSACTION C7EEF97C, not started
MySQL thread id 473219, OS thread handle 0x7f2411d6c700, query id 1633852802 xxx.xxx.xxx.62 yunxin
---TRANSACTION C7EEA561, not started
MySQL thread id 473197, OS thread handle 0x7f2411c68700, query id 1633805094 xxx.xxx.xxx.60 yunxin
---TRANSACTION C7EDA54A, not started
MySQL thread id 473191, OS thread handle 0x7f2412f32700, query id 1633805099 xxx.xxx.xxx.60 yunxin
---TRANSACTION C7EEEB5E, not started
MySQL thread id 472944, OS thread handle 0x7f241327f700, query id 1633818051 xxx.xxx.xxx.65 yunxin
---TRANSACTION C7EEF937, not started
MySQL thread id 472910, OS thread handle 0x7f24132c0700, query id 1633851884 xxx.xxx.xxx.61 yunxin
---TRANSACTION C7EEF922, not started
MySQL thread id 472930, OS thread handle 0x7f2410d2c700, query id 1633851600 xxx.xxx.xxx.65 yunxin
---TRANSACTION C7EEF804, not started
MySQL thread id 472854, OS thread handle 0x7f24109df700, query id 1633848418 xxx.xxx.xxx.61 yunxin
---TRANSACTION C7EEF6A2, not started
MySQL thread id 472881, OS thread handle 0x7f231bcb2700, query id 1633853178 xxx.xxx.xxx.65 yunxin
---TRANSACTION C7EEFA13, not started
MySQL thread id 472804, OS thread handle 0x7f2410ef3700, query id 1633854689 xxx.xxx.xxx.61 yunxin
---TRANSACTION C7EEF490, not started
MySQL thread id 473154, OS thread handle 0x7f24281b8700, query id 1633839896 xxx.xxx.xxx.62 yunxin
---TRANSACTION C7EEF95F, not started
MySQL thread id 472973, OS thread handle 0x7f2411240700, query id 1633852438 xxx.xxx.xxx.91 yunxin
---TRANSACTION C7ED3DC0, not started
MySQL thread id 2, OS thread handle 0x7f242cbb6700, query id 1627277838 Slave has read all relay log; waiting for the slave I/O thread to update it
---TRANSACTION C7EEF92B, ACTIVE 2 sec fetching rows
mysql tables in use 3, locked 0
MySQL thread id 472799, OS thread handle 0x7f24128d9700, query id 1633851729 xxx.xxx.xxx.62 yunxin Sorting result
Trx read view will not see trx with id >= C7EEF92C, sees < C7ED5820
---TRANSACTION C7EED6D4, ACTIVE 38 sec fetching rows
mysql tables in use 1, locked 0
MySQL thread id 472218, OS thread handle 0x7f2411713700, query id 1633779441 xxx.xxx.xxx.66 yunxin Sending data
Trx read view will not see trx with id >= C7EED6D5, sees < C7ED5820
---TRANSACTION C7EEAD1F, ACTIVE 101 sec fetching rows
mysql tables in use 1, locked 0
MySQL thread id 472727, OS thread handle 0x7f2412753700, query id 1633678473 xxx.xxx.xxx.66 yunxin Sending data
Trx read view will not see trx with id >= C7EEAD20, sees < C7ED5820
---TRANSACTION C7ED5820, ACTIVE 527 sec
MySQL thread id 472691, OS thread handle 0x7f2410b65700, query id 1633854720 xxx.xxx.xxx.60 yunxin Waiting for query cache lock
Trx read view will not see trx with id >= C7ED5821, sees < C7ED432B
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: doing file i/o (read thread) ev set
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: complete io for buf page (read thread) ev set
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 100 [64, 0, 36, 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
9528752556 OS file reads, 666870688 OS file writes, 159519743 OS fsyncs
2 pending preads, 0 pending pwrites
7363.88 reads/s, 24128 avg bytes/read, 344.14 writes/s, 67.22 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 4102, free list len 2069, seg size 6172, 80854761 merges
merged operations:
insert 255953207, delete mark 13359281, delete 3446282
discarded operations:
insert 428797, delete mark 0, delete 0
Hash table size 276707, node heap has 60 buffer(s)
46299.99 hash searches/s, 20194.51 non-hash searches/s
---
LOG
---
Log sequence number 1301210460345
Log flushed up to 1301210460345
Last checkpoint at 1301210385030
0 pending log writes, 0 pending chkp writes
127382095 log i/o's done, 50.11 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 7236580
Buffer pool size 8192
Free buffers 0
Database pages 8132
Old database pages 2982
Modified db pages 56
Pending reads 78
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 18147458057, not young 0
10777.60 youngs/s, 0.00 non-youngs/s
Pages read 17981945460, created 7046583, written 600569662
10847.01 reads/s, 2.44 creates/s, 310.95 writes/s
Buffer pool hit rate 990 / 1000, young-making rate 10 / 1000 not 0 / 1000
Pages read ahead 3539.68/s, evicted without access 984.59/s, Random read ahead 0.00/s
LRU len: 8132, unzip_LRU len: 0
I/O sum[307520]:cur[3966], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
7 read views open inside InnoDB
Main thread process no. 2698, id 139792960759552, state: sleeping
Number of rows inserted 98217283, updated 75541864, deleted 3963165, read 2434601996409
43.78 inserts/s, 16.07 updates/s, 0.70 deletes/s, 2251813.56 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

...全文