MySQL数据库性能检查

线程独享内存

show global variables like ‘%buffer%’;

线程共享内存

show global variables like '%cache%';

QPS(每秒Query量)

QPS = Questions(or Queries) / secondsshow status like ‘Questions’;

TPS

TPS = (Com_commit + Com_rollback) / secondsshow status like ‘Com_commit’;show status like ‘Com_rollback’;

key Buffer命中率

key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%
key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

show status like ‘Key%’;

innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads /innodb_buffer_pool_read_requests) * 100%

show status like ‘innodb_buffer_pool_read%’;

Query Cache命中率

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;

show status like ‘Qcache%’;

Table Cache状态量

show status like ‘open%’;

Thread Cache命中率

Thread_cache_hits = (1 - Threads_created / connections ) * 100%

show status like ‘Connections’;show status like ‘Thread%’;

连接数

show variables like 'max_connections';show global status like 'max_used_connections';

临时表

show global status like 'created_tmp%';

排序使用情况

show global status like 'sort%';

表锁情况

show global status like 'table_locks%';

表扫描情况

show global status like 'handler_read%';

调出服务器完成的查询请求次数

show global status like 'com_select';

Binlog Cache使用状况

show status like ‘Binlog_cache%’;

Innodb_log_waits量

show status like ‘innodb_log_waits’;

查询mysql中所有数据库占用磁盘空间大小

select table_schema,concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,concat(truncate(sum(index_length)/1024/1024,2),' MB') as index_sizefrom information_schema.tablesgroup by table_schemaorder by data_length desc;

查询单个库中所有表的大小

select table_name,concat(truncate(data_length/1024/1024,2),' MB') as data_size,concat(truncate(index_length/1024/1024,2),' MB') as index_sizefrom information_schema.tables where table_schema="mrj"group by table_nameorder by data_length desc;