linux 怎样看mysql的状态
发布网友
共1个回答
热心网友
运行中的mysql状态查看
(1)QPS(每秒Query量)
QPS
=
Questions(or
Queries)
/
seconds
mysql
>
show
global
status
like
'Question%';
(2)TPS(每秒事务量)
TPS
=
(Com_commit
+
Com_rollback)
/
seconds
mysql
>
show
global
status
like
'Com_commit';
mysql
>
show
global
status
like
'Com_rollback';
(3)key
Buffer
命中率
mysql>show
global
status
like
'key%';
key_buffer_read_hits
=
(1-key_reads
/
key_read_requests)
*
100%
key_buffer_write_hits
=
(1-key_writes
/
key_write_requests)
*
100%
(4)InnoDB
Buffer命中率
mysql>
show
status
like
'innodb_buffer_pool_read%';
innodb_buffer_read_hits
=
(1
-
innodb_buffer_pool_reads
/
innodb_buffer_pool_read_requests)
*
100%
(5)Query
Cache命中率
mysql>
show
status
like
'Qcache%';
Query_cache_hits
=
(Qcahce_hits
/
(Qcache_hits
+
Qcache_inserts
))
*
100%;
(6)Table
Cache状态量
mysql>
show
global
status
like
'open%';
比较
open_tables
与
opend_tables
值
(7)Thread
Cache
命中率
mysql>
show
global
status
like
'Thread%';
mysql>
show
global
status
like
'Connections';
Thread_cache_hits
=
(1
-
Threads_created
/
connections
)
*
100%
(8)锁定状态
mysql>
show
global
status
like
'%lock%';
Table_locks_waited/Table_locks_immediate=0.3%
如果这个比值比较大的话，说明表锁造成的阻塞比较严重
Innodb_row_lock_waits
innodb行锁，太大可能是间隙锁造成的
(9)复制延时量
mysql
>
show
slave
status
查看延时时间
(10)
Tmp
Table
状况(临时表状况)
mysql
>
show
status
like
'Create_tmp%';
Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大，
可能是排序句子过多或者是连接句子不够优化。
