MySQL Performance Computing bitsCN.com
MySQL Performance Computing
######################################## ######################################## ##
QPS (Queries Per Second ):
Mysql> show /*! 50000 global */status like 'questions ';
Mysql> show /*! 50000 global */status like 'queries ';
QPS = Questions (or Queries)/Seconds
######################################## ######################################## ##
TPS (Transactions Per Second ):
Mysql> show status like 'com _ commit ';
Mysql> show status like 'com _ rollback ';
# Add the following two variable values for distributed transactions
Mysql> show status like 'com _ xa_commit ';
Mysql> show status like 'com _ xa_rollback ';
TPS = (Com_commit + Com_rollback)/Seconds
######################################## ######################################## ##
Key Buffer (MyISAM table index Cache ):
Mysql> show status like 'key _ reads ';
Mysql> show status like 'key _ read_requests ';
Mysql> show status like 'key _ writes ';
Mysql> show status like 'key _ write_requests ';
Key Buffer Read hit rate = (1-Key_reads/Key_read_requests) * 100%
Key Buffer Write hit rate = (1-Key_writes/Key_write_requests) * 100%
######################################## ######################################## ##
Innodb Buffer Pool:
Mysql> show status like 'innodb _ buffer_pool_reads ';
Mysql> show status like 'innodb _ buffer_pool_read_requests ';
Innodb Buffer Pool Read hit rate = (1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%
Mysql> show innodb status/G
######################################## ######################################## ##
Query Cache:
Mysql> show status like 'qcache _ hits ';
Mysql> show status like 'qcache _ inserts ';
Query Cache hit rate = (Qcache_hits/(Qcache_hits + Qcache_inserts) * 100%;
######################################## ######################################## ##
Table Cache:
Mysql> show status like 'open _ tables ';
Mysql> show status like 'opened _ tables ';
Table Cache hit rate = (Open_tables/Opened_tables) * 100%
######################################## ######################################## ##
Thread Cache:
Mysql> show status like 'connections ';
Mysql> show status like 'Threads _ created ';
Thread Cache hit rate = (1-Threads_created/Connections) * 100%
######################################## ######################################## #
BitsCN.com