Thursday, November 29, 2012

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.

No comments:

Post a Comment

plz give ur comments !!!!!!