Basic MySQL Performance Monitoring

regular Ubuntu cli tools

• mysqladmin status – mysqladmin status -uroot -p

MySQL command line interface tools

[bash]mysql -uroot -p[/bash] to open the command line.

[sql]SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;
SHOW PROCESSLIST;
SHOW GLOBAL STATUS LIKE ‘Qcache_%’;[/sql]

cli tools

• mytop – top for MySQL. Install using: sudo apt-get install mytop (assuming Ubuntu operating system). There is a very useful setting file that can be used to set parameters instead of having to include them in each command. Save the file as ~/.mytop.
• MySQLTuner – provides suggestions on performance improvements and my.cnf settings by analyzing data on your mysql database server.

Setting considerations

• If Open_tables (SHOW GLOBAL STATUS will show this) is equal to your [bash]table_cache size[/bash] (set in /etc/mysql/my.cnf) that means it is being capped by your setting. The more MySQL has to read the table from disk the more IO and slower response, so if you have available RAM increasing the table_cache size may well make a big difference.
• Key_reads/Key_read_request ratio should normally be < 0.01 (per MySQL manual, this means that nearly all key requests are taken from RAM). You can get both values using SHOW GLOBAL STATUS and then calculate the ratio. If the ratio is too high, consider increasing the key_buffer (in /etc/mysql/my.sql).
• key_writes/key_writes_request should normally be near 1 (per MySQL manual)