InnoDB Monitors provide information about the InnoDB internal state. This information is useful for performance tuning.
Each Monitor can be enabled by creating a table with a special name, which causes InnoDB to write Monitor output periodically.
Also, output for the standard InnoDB Monitor is available on demand through the SHOW ENGINE INNODB STATUS SQL statement.
There are several types of InnoDB Monitors:
The standard InnoDB Monitor displays the following types of information:
- Table and record locks held by each active transaction
- Lock waits of a transactions
- Semaphore waits of threads
- Pending file I/O requests
- Buffer pool statistics
- Purge and insert buffer merge activity of the main InnoDB thread
To enable the standard InnoDB Monitor for periodic output, create a table named innodb_monitor.
To obtain Monitor output on demand, use the SHOW ENGINE INNODB STATUS SQL statement to fetch the output to your client program.
If you are using the mysql interactive client, the output is more readable if you replace the usual semicolon statement terminator with \G:
SHOW ENGINE INNODB STATUS\G
- Setting innodb_lock_wait_timeout
- Set time zone in mysql
- Granting Privileges to users
- Finding all column name of table