show engine innodb status分析

当前活跃的事务:

1. ------------
2. TRANSACTIONS
3. ------------
4. Trx id counter080157601
5. Purge done for trx's n:o
6. History list length 6
7. Total number of lock structs in row lock hash table 0
8. LIST OF TRANSACTIONS FOR EACH SESSION:
9. ---TRANSACTION 0 0, not started, process no 3396, OS thread id 1152440672
10. MySQL thread id 8080, query id 728900 localhost root
11. show innodb status
12. ---TRANSACTION 0 80157600, ACTIVE 4 sec, process no 3396, OS thread id 1148250464, thread declared inside InnoDB 442
13. mysql tables in use 1, locked 0
14. MySQL thread id 8079, query id 728899 localhost root Sending data
15. select sql_calc_found_rows * from b limit 5
16. Trx read view will not see trx with id>= 0 80157601, sees
17. ---TRANSACTION 0 80157599, ACTIVE 5 sec, process no 3396, OS thread id 1150142816fetching rows, thread declared inside InnoDB 166
18. mysql tables in use 1, locked 0
19. MySQL thread id 8078, query id 728898 localhost root Sending data
20. select sql_calc_found_rows * from b limit 5
21. Trx read view will not see trx with id>= 0 80157600, sees
22. ---TRANSACTION 0 80157598, ACTIVE 7 sec, process no 3396, OS thread id 1147980128fetching rows, thread declared inside InnoDB 114
23. mysql tables in use 1, locked 0
24. MySQL thread id 8077, query id 728897 localhost root Sending data
25. select sql_calc_found_rows * from b limit 5
26. Trx read view will not see trx with id>= 0 80157599, sees
27. ---TRANSACTION 0 80157597, ACTIVE 7 sec, process no 3396, OS thread id 1152305504fetching rows, thread declared inside InnoDB 400
28. mysql tables in use 1, locked 0
29. MySQL thread id 8076, query id 728896 localhost root Sending data
30. select sql_calc_found_rows * from b limit 5
31. Trx read view will not see trx with id>= 0 80157598, sees

如果连接数量很大，InnoDB只能打印出其中的一部分，以维持整个列表的长度。

Transaction id是当前事务的标识符。Purge done for trx's n:o是回收程序启动事务的数量。如果回收程序没有运行事务，InnoDB只能回收旧版本。旧的未提交事务可能会阻塞回收进程而吃掉资源。通过查看当前事务数和最近一次回收事务数的差值就可以发现这个问题。在某些情况下，回收程序效率很低而要维持更新率，在这种情况下差值也会增加，这时innodb_max_purge_lag会有所帮助。"undo n:o"显示回收程序正在处理的undo日志记录数，如果当前没有回收程序运行，则该值为0。

History list length是在回滚段中为回收的事务数。当更新事务提交时该值增加，当回收线程运行时该值减少。

Total number of lock structs in row lock hash table是所有事务分配的行锁结构数。该值并不等于锁住的行数－因为一个锁结构中包含很多行。

MySQL的连接要么是not started状态（该连接没有活动的事务），要么是ACTIVE状态（有活动事务）。注意，尽管连接是"SLEEP"状态，但事务也可以是活跃的－如果事务中有多个语句。事务状态就是事务正在做什么，如"fetching rows","updating"。"Thread declared inside InnoDB 400"意思是线程正在InnoDB内核中运行，当前还可以有400个线程可以进来。InnoDB限制内核中的并发线程数使用变量innodb_thread_concurrency 。如果线程没在InnoDB内核中运行，状态应该是"waiting in InnoDB queue" 或"sleeping before joining InnoDB queue"。为避免太多线程竞争进入InnoDB队列，InnoDB有时会让线程先sleep，而不是wait。这样就会使InnoDB内核中的活跃线程少于innodb_thread_concurrency。这会减少线程等待进入队列的时间，可以调整innodb_thread_sleep_delay的值，该值为微秒。

mysql tables in use 1, locked 0是由线程正使用的表数，和线程锁住的表数。InnoDB一般不会锁表，因此该值一般为0，除非ALTER TABLE或LOCK TABLES相似的操作。

文件I/O:

1. --------
2. FILE I/O
3. --------
4. I/O thread0state: waiting for i/o request(insert buffer thread)
5. I/O thread1state: waiting for i/o request(log thread)
6. I/O thread2state: waiting for i/o request(read thread)
7. I/O thread3state: waiting for i/o request(write thread)
8. Pending normal aio reads:0, aio writes:0,
9. ibuf aio reads:0, log i/o's: 0, sync i/o's:0
10. Pending flushes(fsync)log:0; buffer pool:0
11. 17909940OS file reads,22088963OS file writes,1743764OS fsyncs
12. 0.20reads/s,16384avg bytes/read,5.00writes/s,0.80fsyncs/s

IO的四个进程（括号中的名字）。插入缓存合并，异步刷新，read－ahead和脏缓存刷新。如果线程准备好了则为waiting for i/o request状态，否则它就正在执行某种操作。

Number of pending operation is shown for each of helper threads -these are amount of operations queued for execution or being executed at the same time. Also number of pending fsync operations is displayed.For writes Innodb has to ensure data makes it to the disk - just passing it to OS cache is not enough. This is typically done by calling fsync() for modified files. Constant high values for any of these variables isindication of IO bound workload. Note however - IO requests submited bythreads executing requests are not accounted here so you may have theseat zeroes while workload being IO bound still.

Next, number of file IO operations is shown as well as computedaverages. This is parameters which is great for graphing and monitoring.
"16384 avg bytes/read"shows average size of read requests. For random IO these should be 16K - page size, for full table scan or index scan read-ahead may be performedwhich can increase average read size significantly. So you can thinkabout this value as read-ahead efficiency.

PLAIN TEXT

CODE:

1. -------------------------------------
2. INSERT BUFFER AND ADAPTIVE HASH INDEX
3. -------------------------------------
4. Ibuf for space0: size1, free list len887, seg size889, is not empty
5. Ibuf for space0: size1, free list len887, seg size889,
6. 2431891inserts,2672643merged recs,1059730merges
7. Hash table size8850487, used cells2381348, node heap has4091buffer(s)
8. 2208.17hash searches/s,175.05non-hash searches/s

This section shows insert buffer and adaptive hash status. First line shows status of insert buffer - segment size and free list as well asif
there are any records is insert buffer. Next it shows how manyinserts were done in insert buffer, how many recs were merged and howmany merges did it took. Ratio of number of merges to number of insertsis pretty much insert buffer efficiency.

Adaptive hash index is hash index Innodb builds for some pages tospeed up row lookup replacing btree search with hash search. Thissection shows hash table size, number of used cells and number ofbuffers used by adaptive hash index. You can also see number of hashindex lookups and number of non-hash index lookups which is indicationof hash index efficiency.

There is currently not much you can do to adjust adaptive hash indexor insert buffer behavīor so it is pretty much for informationalpurposes only.

PLAIN TEXT

CODE:

1. ---
2. LOG
3. ---
4. Log sequence number843000620880
5. Log flushed up to843000611265
6. Last checkpoint at842939889199
7. 0pending log writes,0pending chkp writes
8. 14073669log i/o's done, 10.90 log i/o's/second

Log section provides information about log subsystem of Innodb. Youcan see current log sequence number - which is amount of bytes Innodbhas written in log files since system tablespace creation. You can alsosee up to which point logs have been flushed - so how much data isunflushed in log buffer as well as when last checkpoint was performed.Innodb uses fuzzy checkpointing so this line hold log sequence, allchanges up to which has been flushed from buffer pool. Changes havinghigher log sequences may still only be recored in logs and not flushedfrom buffer pool so such log sequences can't be overwritten in logfiles. By monitoring log sequence number and value up to which logs have been flushed you can check if yourinnodb_log_buffer_sizeis optimal - if you see more than 30% of log buffer size being unflushed you may want to increase it.

You also can see number of pending normal log writes and number ofcheckpoint log writes. Number of log/io operations allows to separatetablespace related IO from log related IO so you can see how much IOyour log file requires. Note depending on yourinnodb_flush_log_at_trx_commitvalue your log writes may be more or less expensive. Ifinnodb_flush_logs_at_trx_commit=2 log writes are done to OS cache, andbeing sequential writes these logs writes are pretty fast.

PLAIN TEXT

CODE:

1. ----------------------
2. BUFFER POOL AND MEMORY
3. ----------------------
4. Total memory allocated4648979546; in additional pool allocated16773888
5. Buffer pool size262144
6. Free buffers0
7. Database pages258053
8. Modified db pages37491
9. Pending reads0
10. Pending writes: LRU0, flush list0, single page0
11. Pages read57973114, created251137, written10761167
12. 9.79reads/s,0.31creates/s,6.00writes/s
13. Buffer pool hit rate999/1000

该部分描述了内存的使用情况。包括的信息有：InnoDB分配的总内存，额外分配的内存，缓存池中的页数，空闲页数，页和脏页数。从这些值中 可以看出是否InnoDB缓存分配得恰当－如果总有很多空闲页，表明数据库要比内存小，可以把innoDB缓存调小些。如果空闲页为0，那么数据库页不会 和缓存容量相同，因为还要存锁、索引和得系统结构。

Pending reads and writes are pending requests on buffer pool level.Innodb may merge multiple requests to one on file level so these aredifferent. We can also see different types of IO submited by Innodb -pages to be flushed via LRU pages - dirty pages which were not accessedlong time, flush list - old pages which need to be flushed bycheckpointing process and single page - independent page writes.

We can also see number of pages being read and written. Created pages is empty pages created in buffer pool for new data - when previous page content was not read to the buffer pool.

Finally you can see buffer pool hit ratio which measures buffer poolefficiency. 1000/1000 corresponds to 100% hit rate. It is hard to tellwhat buffer pool hit rate is good enough - it is very workloaddependent. Sometimes 950/1000 will be enough, sometimes you can see IObound workload with hit rate of 995/1000.

PLAIN TEXT

CODE:

1. --------------
2. ROW OPERATIONS
3. --------------
4. 0queries inside InnoDB,0queries in queue
5. 1read views open inside InnoDB
6. Main thread process no.10099, id88021936, state: waiting for server activity
7. Number of rows inserted143, updated3000041, deleted0, read24865563
8. 0.00inserts/s,0.00updates/s,0.00deletes/s,0.00reads/s

Finally last section - row operations which shows activity on the row basics and some system information.

It shows innodb thread queue status - how many threads are waitingand being active. How many read views are open inside Innodb - this iswhen transaction was started but no statement is currently active, state of Innodb main thread which controls scheduling of number of systemoperations - flushing dirty pages, checkpointing, purging, flusing logs, doing insert buffer merge. Values for"state"field are rather self explanatory.

You can also see number of rows operation since system startup aswell as average values. This is also very good values to monitor andgraph - row operations is very good measure of Innodb load. Not all rowoperations are created equal of course and accessing of 10 byte rows ismuch cheaper than accessing 10MB blog, but it is still much more helpful than number of queries, which is even more different.

One more thing to note - SHOW INNODB STATUS is not consistent - itdoes not correspond to some particular point in time. Different lines in SHOW INNODB STATUS populated in different point in times, so sometimesyou may see a bit conflicting information. This is by design asrequiring global lock to provide consitent information would causesignificant overhead.