【讨论中】发个innodb状态给大家讨论，看这个数据库存在哪些问题

lq2003

发个innodb状态给大家讨论，看这个数据库存在哪些问题，请各位提出优化意见，谢谢！

mysql> show  engine  innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
121011 10:14:22 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 56 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 55559 1_second, 55558 sleeps, 4938 10_second, 7326 background, 7322 flush
srv_master_thread log flush and writes: 55665
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 10720, signal count 6560
Mutex spin waits 33019, rounds 357426, OS waits 6768
RW-shared spins 3975, rounds 118027, OS waits 3892
RW-excl spins 50, rounds 1103, OS waits 34
Spin rounds per wait: 10.82 mutex, 29.69 RW-shared, 22.06 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 1F2E8
Purge done for trx's n < 1F2E6 undo n < 0
History list length 2086
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 4029, OS thread handle 0x4b9ce940, query id 3443543 localhost root
show  engine  innodb status
---TRANSACTION 1F10A, not started
MySQL thread id 3994, OS thread handle 0x4b3b6940, query id 3434200 localhost 127.0.0.1 root
---TRANSACTION 1F10B, not started
MySQL thread id 3996, OS thread handle 0x4b57d940, query id 3434201 localhost 127.0.0.1 root
---TRANSACTION 1F0FE, not started
MySQL thread id 3992, OS thread handle 0x4b785940, query id 3432941 localhost 127.0.0.1 root
---TRANSACTION 1EDEE, not started
MySQL thread id 3910, OS thread handle 0x4ae61940, query id 3414931 localhost 127.0.0.1 root
---TRANSACTION 1F2D0, not started
MySQL thread id 3959, OS thread handle 0x4a5bf940, query id 3442278 121.141.17.212 root
---TRANSACTION 1F2E6, not started
MySQL thread id 3950, OS thread handle 0x4a786940, query id 3443456 121.141.17.212 root
---TRANSACTION 1EDEF, not started
MySQL thread id 3909, OS thread handle 0x4ab55940, query id 3414932 localhost 127.0.0.1 root
---TRANSACTION 1F107, not started
MySQL thread id 3847, OS thread handle 0x4a272940, query id 3434189 localhost 127.0.0.1 root
---TRANSACTION 1F0F7, not started
MySQL thread id 3844, OS thread handle 0x4ab14940, query id 3432690 localhost 127.0.0.1 root
---TRANSACTION 1EDC6, not started
MySQL thread id 3819, OS thread handle 0x49d9f940, query id 3412766 localhost 127.0.0.1 root
---TRANSACTION 0, not started
MySQL thread id 2610, OS thread handle 0x418f8940, query id 3435958 localhost 127.0.0.1 root
---TRANSACTION 1D965, not started
MySQL thread id 3781, OS thread handle 0x4bb13940, query id 3294480 localhost 127.0.0.1 root
---TRANSACTION 1E8E4, not started
MySQL thread id 3783, OS thread handle 0x4a88a940, query id 3386933 121.141.17.212 root
---TRANSACTION 1D484, not started
MySQL thread id 3744, OS thread handle 0x40a0c940, query id 3268721 121.141.17.212 root
---TRANSACTION 1D270, not started
MySQL thread id 3645, OS thread handle 0x415d3940, query id 3255889 localhost 127.0.0.1 root
---TRANSACTION 1DCA8, not started
MySQL thread id 3687, OS thread handle 0x4b375940, query id 3315906 localhost 127.0.0.1 root
---TRANSACTION 1DC97, not started
MySQL thread id 3679, OS thread handle 0x4a12d940, query id 3314407 localhost 127.0.0.1 root
---TRANSACTION 1D596, not started
MySQL thread id 3651, OS thread handle 0x4bb95940, query id 3276050 localhost 127.0.0.1 root
---TRANSACTION 1E3C2, not started
MySQL thread id 3652, OS thread handle 0x4a53d940, query id 3355779 localhost 127.0.0.1 root
---TRANSACTION 1D966, not started
MySQL thread id 3646, OS thread handle 0x4b53c940, query id 3294481 localhost 127.0.0.1 root
---TRANSACTION 1C50F, not started
MySQL thread id 3560, OS thread handle 0x4a0ec940, query id 3177229 localhost 127.0.0.1 root
---TRANSACTION 0, not started
MySQL thread id 100, OS thread handle 0x4aa51940, query id 3355155 localhost 127.0.0.1 root
---TRANSACTION 1C50E, not started
MySQL thread id 3559, OS thread handle 0x4bc99940, query id 3177228 localhost 127.0.0.1 root
---TRANSACTION 1CF72, not started
MySQL thread id 3562, OS thread handle 0x4b848940, query id 3239217 121.141.17.212 root
---TRANSACTION 1C537, not started
MySQL thread id 3477, OS thread handle 0x4bee2940, query id 3178460 localhost 127.0.0.1 root
---TRANSACTION 1BAE8, not started
MySQL thread id 3475, OS thread handle 0x4b438940, query id 3118127 localhost 127.0.0.1 root
---TRANSACTION 0, not started
MySQL thread id 2324, OS thread handle 0x4ad9e940, query id 3355158 localhost 127.0.0.1 root
---TRANSACTION 1BB1E, not started
MySQL thread id 3430, OS thread handle 0x4b230940, query id 3121898 121.141.17.212 root
---TRANSACTION 1C850, not started
MySQL thread id 3335, OS thread handle 0x4b640940, query id 3197688 localhost 127.0.0.1 root
---TRANSACTION 1C83F, not started
MySQL thread id 3332, OS thread handle 0x4a4bb940, query id 3196190 localhost 127.0.0.1 root
---TRANSACTION 0, not started
MySQL thread id 3110, OS thread handle 0x4ba0f940, query id 3441866 localhost 127.0.0.1 root
---TRANSACTION 1F283, not started
MySQL thread id 3186, OS thread handle 0x4b1ef940, query id 3442222 121.141.17.212 root
---TRANSACTION 1F2CD, not started
MySQL thread id 3187, OS thread handle 0x4b5ff940, query id 3442277 121.141.17.212 root
---TRANSACTION 1F26E, not started
MySQL thread id 3185, OS thread handle 0x49fe8940, query id 3442205 121.141.17.212 root
---TRANSACTION 1F2CE, not started
MySQL thread id 2828, OS thread handle 0x40214940, query id 3442276 121.141.17.212 root
---TRANSACTION 0, not started
MySQL thread id 589, OS thread handle 0x4bd9d940, query id 3441848 localhost 127.0.0.1 root
---TRANSACTION 1EF91, not started
MySQL thread id 2827, OS thread handle 0x4addf940, query id 3424538 121.141.17.212 root
---TRANSACTION 1EA5F, not started
MySQL thread id 2829, OS thread handle 0x4a2f4940, query id 3394892 121.141.17.212 root
---TRANSACTION 0, not started
MySQL thread id 2609, OS thread handle 0x4b2b2940, query id 3441815 localhost 127.0.0.1 root
---TRANSACTION 0, not started
MySQL thread id 590, OS thread handle 0x4afe7940, query id 3441859 localhost 127.0.0.1 root
---TRANSACTION 0, not started
MySQL thread id 2326, OS thread handle 0x4b681940, query id 3426170 localhost 127.0.0.1 root
---TRANSACTION 0, not started
MySQL thread id 2201, OS thread handle 0x4a57e940, query id 3148307 localhost 127.0.0.1 root
---TRANSACTION 1EF8B, not started
MySQL thread id 2153, OS thread handle 0x4ba91940, query id 3424533 121.141.17.212 root
---TRANSACTION 1EA64, not started
MySQL thread id 2061, OS thread handle 0x4a745940, query id 3394896 121.141.17.212 root
---TRANSACTION 1EF5F, not started
MySQL thread id 2060, OS thread handle 0x4a9cf940, query id 3424488 121.141.17.212 root
---TRANSACTION 1EF69, not started
MySQL thread id 2059, OS thread handle 0x4a029940, query id 3424505 121.141.17.212 root
---TRANSACTION 0, not started
MySQL thread id 97, OS thread handle 0x4a2b3940, query id 3355136 localhost 127.0.0.1 root
---TRANSACTION 1EA5D, not started
MySQL thread id 1959, OS thread handle 0x4179a940, query id 3394890 121.141.17.212 root
---TRANSACTION 1F2D3, not started
MySQL thread id 1957, OS thread handle 0x4bea1940, query id 3442281 121.141.17.212 root
---TRANSACTION 0, not started
MySQL thread id 101, OS thread handle 0x49d1d940, query id 3443542 localhost 127.0.0.1 root
---TRANSACTION 0, not started
MySQL thread id 166, OS thread handle 0x4aea2940, query id 3355119 localhost 127.0.0.1 root
---TRANSACTION 0, not started
MySQL thread id 148, OS thread handle 0x49ee4940, query id 3435942 localhost 127.0.0.1 root
---TRANSACTION 1EF65, not started
MySQL thread id 643, OS thread handle 0x4bc58940, query id 3424493 121.141.17.212 root
---TRANSACTION 1F279, not started
MySQL thread id 335, OS thread handle 0x4b98d940, query id 3442216 121.141.17.212 root
---TRANSACTION 1F2DB, not started
MySQL thread id 183, OS thread handle 0x4af65940, query id 3442285 121.141.17.212 root
---TRANSACTION 1F272, not started
MySQL thread id 164, OS thread handle 0x4ae20940, query id 3442211 121.141.17.212 root
---TRANSACTION 1F2E7, not started
MySQL thread id 144, OS thread handle 0x4a682940, query id 3443537 121.141.17.212 root
---TRANSACTION 1F2D1, not started
MySQL thread id 140, OS thread handle 0x4a7c7940, query id 3442279 121.141.17.212 root
---TRANSACTION 0, not started
MySQL thread id 98, OS thread handle 0x4a1f0940, query id 3441827 localhost 127.0.0.1 root
--------
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
505 OS file reads, 133404 OS file writes, 42679 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 3.70 writes/s, 1.14 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 796937, node heap has 2 buffer(s)
1.43 hash searches/s, 3.98 non-hash searches/s
---
LOG
---
Log sequence number 228735064
Log flushed up to   228735064
Last checkpoint at  228735064
0 pending log writes, 0 pending chkp writes
33947 log i/o's done, 1.04 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 412090368; in additional pool allocated 0
Dictionary memory allocated 281625
Buffer pool size   24575
Free buffers       23982
Database pages     591
Old database pages 213
Modified db pages  0
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 493, created 98, written 94626
0.00 reads/s, 0.00 creates/s, 2.60 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: 591, unzip_LRU len: 0
I/O sum[0]:cur[0], 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. 22345, id 1238223168, state: sleeping
Number of rows inserted 1006, updated 182614, deleted 0, read 6927714
0.00 inserts/s, 1.18 updates/s, 0.00 deletes/s, 207.73 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.10 sec)

mysql>

lq2003

沙发自己站。

---TRANSACTION 1F10A, not started   这个字段？？？

hunter_search

你的数据库qps比较小哦，不会有啥问题的

lq2003

hunter_search 发表于 2012-10-11 13:31
你的数据库qps比较小哦，不会有啥问题的

多谢！

klathleen

你的库一点压力都没有...

lq2003

klathleen 发表于 2012-10-17 10:33
你的库一点压力都没有...

为啥，是因为这个么？

Dictionary memory allocated 281625
Buffer pool size   24575
Free buffers       23982
Database pages     591
Old database pages 213
Modified db pages  0

klathleen

回复 6# lq2003


    看你的 flush log 刷到磁盘中的数据 和checkpoint 都和源 LSN 一样..
    再看你buffer 中的dirty page     和page 的r/s w/s   LRU   

lq2003

klathleen 发表于 2012-10-17 18:07
回复 6# lq2003

像这种大量的数据，代表什么意思？
srv_master_thread loops: 55559 1_second, 55558 sleeps, 4938 10_second, 7326 background, 7322 flush
srv_master_thread log flush and writes: 55665

如果负载严重的情况  会是什么样子呢？能讲讲么。

龙雪刚

回复 8# lq2003


innodb里面有有个主线程，其中涉及到几个loop。主要的有loop,background loop与flush loop. 每一次做一些事情之后（这里就是 55559 1_second）会休息一秒（这里就是55558 sleeps）,连续10次后，会做一些每10秒做一次的事情(这里就是4938 10_second). 其中，每一秒内如果发现当前 no user activity 就会进入backgroud loop（这里就是7326 background). 在backgroup loop里如果仍然空闲就进入flush loop(即7322 flush)
负载小的情况，1_second : sleeps 基本上是1:1 . 但如果在负载大的时候，并不会总是休息一秒，所以1_second : sleeps > 1: 1 差值越大，可以认为负载越大。另外可以看backgroud ,与flush 次数。负载大的时候，这两个值应该是比较小的。很明显机器那个时候很少时刻是空闲的。

    主要的应该就这些了。其他的下面同学继续补兖。

lq2003

龙雪刚 发表于 2012-10-19 15:29
回复 8# lq2003

感谢感谢！一直找不到这段文字的解释，被你做了！谢谢！