SHOW INNODB STATUS walk through
Many people asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance.
To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information about transaction processing and all kinds of other things. In MySQL 5 number of Innodb performance counters were exported and now available in SHOW STATUS output. Most of them are same as you previously could find in SHOW INNODB STATUS, there are however few which were not available before.
In SHOW INNODB STATUS many values are per second. If you’re planning to use these values make sure they are sampled over decent period of time. In the very start of printout Innodb will print:
Make sure data is sampled for at least 20-30 seconds. If averages are calculated for last 0 or 1 second they are pretty much unusable.
To be honest I do not really like averages Innodb provides as it is hard to get average for interval you want to have, if you’re writing scripts to look at SHOW INNODB STATUS it is much better to use global counters and get averages manually. They are still however quite helpful if you’re just looking at output.
Next sections in Semaphores information:
There are two portions in this section. One is list of current waits. This section will only contain any entries if you’re running in high concurrency envinronment, so Innodb has to fall back to OS waits frequently. If wait was resolved via Spinlock it will not be seen in this section.
Looking at this section you can get an idea what might be hot spot in your workload. It however requires some knowledge of source code – you only get file names and lines (which are different in different versions), you get no information what this object is responsible for. You however can well guess from file names – in this case file is “buf0buf.ic” what means there is some buffer pool contention. However if you want to know more – you need to browse source.
You also see some details printed about wait. “lock var” is current value for the mutex object (locked=1/free=0) , “waiters flag” is current number of waiters, plus you can see wait status information “wait is ending” in this case which means mutex is already free for grabs but os has not yet scheduled thread so it could proceed with execution.
The second piece of information is event counters – “reservation count” and “signal count” show how actively innodb uses internal sync array – how frequently slots are allocated in it and how frequently threads are signaled using sync array. These counters can be used to represent frequency with which Innodb needs to fall back to OS Wait. There is direct information about OS waits as well – you can see “OS Waits” for
mutexes, as well as for read-write locks. For these information both for exclusive locks and for shared locks is displayed. OS Wait is not exactly the same as “reservation” – before falling back to complex wait using sync_array Innodb tries to “yield” to OS hoping when name thread is scheduled next time object will be free already. OS Waits are relatively slow, and if you get tens of thousands of OS waits per second it may be the problem. The other way to look at it is context switch rate in your OS stats.
The other important peice of information is number of “spin waits” and “spin rounds”. Spin locks are low cost wait, compared to OS wait, it is however active wait which wastes your CPU cycles, so if you see very large amount of spin waits and spin rounds significant CPU resources may be wasted. It should come to hundreds of thousands spin rounds per second to start really worry for most CPUs. innodb_sync_spin_loops can be used to ballance between wasting CPU time running spin locks and doing unneeded context switches.
The next section is about deadlock errors:
For last deadlock Innodb shows transactions which caused deadlocks, their state during deadlock, what locks they were holding and what they were waiting for, which of transactions Innodb decided to roll back to resolve deadlock. Note – Innodb only prints information about few of the locks which transaction is holding. Also only last statement from each transactions is displayed, while locks rows could be locked by one of previous statements. For complex deadlock investigations you might need to look at the log files to find truly conflicting statements. For most simple cases information from SHOW INNODB STATUS is good enough.
As for deadlock information we have similar information about last failed foreign key constraint:
Innodb will print statement which caused error. Definition of foreign key which failed as well as the closest match which was located in parent table. There is a lot of scary information in hex but it is not important for most diagnostic cases – It is left by Innodb developers so SHOW INNODB STATUS can also be used as debugging tool.
Next section you will find in SHOW INNODB STATUS is information about currently active transactions:
If you have small number of connections all connections will be printed in transaction list, if you have large number of connections Innodb will only print number of them, cutting the list so SHOW INNODB STATUS output will not grow too large.
Transaction id is current transaction identifier – it is incremented for each transaction. Purge done for trx’s n:o is number of transaction to which purge is done. Innodb can only purge old versions if they there are no running transactions potentially needing them. Old stale uncommitted transactions may block purge process eating up resources. By looking at transaction counter difference between current and last purged transaction you will be able to spot it. In some rare cases purge also could have hard time to keep up with update rate, in this case difference between these values will also grow and innodb_max_purge_lag will become your friend. “undo n:o” will show the undo log record number which purge is currently processing, if it is active otherwise it will be zero.
History list length 6 is number of unpurged transactions in undo space. It is increased as transactions which have done updates are commited and decreased as purge runs.
Total number of lock structs in row lock hash table is number of row lock structures allocated by all transactions. Note not same as number of locked rows – there are normally many rows for each lock structure.
For each of connections for MySQL there will be ether not started state if there is no active Innodb transaction for this connection, or ACTIVE if transaction is active. Note transaction can be active even if connection is in “Sleep” stage – if it is multiple statement transaction. Innodb also will print OS thread_id and process id which may be helpful if you would like to use gdb to connect to running mysqld for troubleshooting purposes and similar things. Also transaction status is reported which is basically what transaction is doing it can be “fetching rows”, “updating” and couple of other values. “Thread declared inside InnoDB 400” means thread is running inside Innodb kernel and still has 400 tickets to use. Innodb tries to limit thread concurrency allowing only innodb_thread_concurrency threads to run inside Innodb kernel at the same time. If thread is not runniing inside innodb kernel status could be “waiting in InnoDB queue” or “sleeping before joining InnoDB queue”. Latest one is quite interesting – to avoid too many threads competing to enter innodb queue at the same time Innodb makes thread to sleep for some time before trying to wait (if no free slot was available). This may cause number of threads active inside kernel being less than number of threads allowed by innodb_thread_concurrency. For certain workloads it may help to decrease the time thread waits before it enters the queue. This is done by adjusting innodb_thread_sleep_delay variable. Value is specified in microseconds.
mysql tables in use 1, locked 0 is number of tables used by transaction in question (meaning it was accessed) and number of tables locked by transactions. Innodb does not lock tables for normal operation so number of tables locked normally stays 0, unless it is ALTER TABLE or similar statement, or if LOCK TABLES was used.
In addition to Innodb specific information, there is generic statement information which is visible in SHOW PROCESSLIST showed in SHOW INNODB STATUS, such as statement which is being executed, query id, query status etc.
Next section you will see is section showing details of file IO:
This section shows state of file IO helper threads – insert buffer thread, log thread, read thread and write thread. These are responsible appropriately for insert buffer merges, asynchronous log flushes, read-ahead and flushing of dirty buffers. Normal reads originated from query executions are executed by threads running queries. On Unix/Linux you will always see 4 helper threads, on Windows it however can be adjusted by innodb_file_io_threads variable. For each helper thread you can see thread state – if thread is ready – waiting for i/o request or if it is executing certain operation.
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 is indication of IO bound workload. Note however – IO requests submited by threads executing requests are not accounted here so you may have these at zeroes while workload being IO bound still.
Next, number of file IO operations is shown as well as computed averages. 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 performed which can increase average read size significantly. So you can think about this value as read-ahead efficiency.
This section shows insert buffer and adaptive hash status. First line shows status of insert buffer – segment size and free list as well as if
there are any records is insert buffer. Next it shows how many inserts were done in insert buffer, how many recs were merged and how many merges did it took. Ratio of number of merges to number of inserts is pretty much insert buffer efficiency.
Adaptive hash index is hash index Innodb builds for some pages to speed up row lookup replacing btree search with hash search. This section shows hash table size, number of used cells and number of buffers used by adaptive hash index. You can also see number of hash index lookups and number of non-hash index lookups which is indication of hash index efficiency.
There is currently not much you can do to adjust adaptive hash index or insert buffer behavior so it is pretty much for informational purposes only.
Log section provides information about log subsystem of Innodb. You can see current log sequence number – which is amount of bytes Innodb has written in log files since system tablespace creation. You can also see up to which point logs have been flushed – so how much data is unflushed in log buffer as well as when last checkpoint was performed. Innodb uses fuzzy checkpointing so this line hold log sequence, all changes up to which has been flushed from buffer pool. Changes having higher log sequences may still only be recored in logs and not flushed from buffer pool so such log sequences can’t be overwritten in log files. By monitoring log sequence number and value up to which logs have been flushed you can check if your innodb_log_buffer_size is 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 of checkpoint log writes. Number of log/io operations allows to separate tablespace related IO from log related IO so you can see how much IO your log file requires. Note depending on your innodb_flush_log_at_trx_commit value your log writes may be more or less expensive. If innodb_flush_logs_at_trx_commit=2 log writes are done to OS cache, and being sequential writes these logs writes are pretty fast.
This section shows Buffer pool activity and memory usage. You can see total memory allocated by Innodb (sometimes it is higher than you anticipated), amount of memory allocated in additional memory pool (so you can check if it is sized right), total number of pages in buffer pool, number of pages free, pages allocated by database pages and dirty pages. From these values you can learn if your buffer pool is sized well – if you have constantly a lot of pages free, it probably means your active database size is smaller than allocated buffer pool size so you can tune it down. Even if free pages is zero as in this case database pages will not be equal to total size of buffer pool, because buffer pool also stores lock information, adaptive hash indexes and some other system structures.
Pending reads and writes are pending requests on buffer pool level. Innodb may merge multiple requests to one on file level so these are different. We can also see different types of IO submited by Innodb – pages to be flushed via LRU pages – dirty pages which were not accessed long time, flush list – old pages which need to be flushed by checkpointing 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 pool efficiency. 1000/1000 corresponds to 100% hit rate. It is hard to tell what buffer pool hit rate is good enough – it is very workload dependent. Sometimes 950/1000 will be enough, sometimes you can see IO bound workload with hit rate of 995/1000.
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 waiting and being active. How many read views are open inside Innodb – this is when transaction was started but no statement is currently active, state of Innodb main thread which controls scheduling of number of system operations – 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 as well as average values. This is also very good values to monitor and graph – row operations is very good measure of Innodb load. Not all row operations are created equal of course and accessing of 10 byte rows is much 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 – it does not correspond to some particular point in time. Different lines in SHOW INNODB STATUS populated in different point in times, so sometimes you may see a bit conflicting information. This is by design as requiring global lock to provide consitent information would cause significant overhead.
Note: our book has an updated, more complete, more in-depth explanation of SHOW INNODB OUTPUT.
1183
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
