Performance Testing | Database Monitoring | Mysql

Following are some commands that we we can use to monitor mysql database on linux server.

To Monitor Current Queries :

• show processlist;
• show full processlist;

Query Execution Plan

• explain QueryStatement
• explain extended QueryStatement

To show index present in table

           show index from TableName

To Monitor Locks,Waits and Deadlocks

•  show engine innodb status;  (See semaphore section).
• SELECT r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,        b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b  ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r  ON r.trx_id = w.requesting_trx_id\G

Find Largest Tables in Mysql

SELECT CONCAT(table_schema, '.', table_name), CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')    DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,ROUND(index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER  BY data_length + index_length DESC LIMIT  10;

Enabling Slow Query Log*:

1) Enter following lines in the file[my.cnf]

                Default Location:[ Location :/etc/my.cnf]

#ForSlowQuerylog  

datadir                         = /var/lib/mysql

log_slow_queries                = mysql-slow.log

long_query_time                 = 1     

  

2) After making changes restart the mysql .

*Require Root access to make this change

Tool for monitoring: Spotlight (http://www.quest.com/spotlight-on-mysql/)

Please feel free to add anything regarding Mysql Monitoring to this thread.