mysql每秒查询结果数qps怎么得到

mysql每秒查询结果数qps怎么得到

(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

查看延时时间

系统吞吐量几个重要参数：QPS（TPS）、并发数、响应时间

理解了上面三个要素的意义之后，就能推算出它们之间的关系：

运行命令

如何计算：

从网上查看如果获取mysql 的qps,tps 的方法有如下两种：

方法一 基于 questions 计算qps,基于 com_commit com_rollback 计算tps

questions = show global status like 'questions'

uptime = show global status like 'uptime'

qps=questions/uptime

com_commit = show global status like 'com_commit'

com_rollback = show global status like 'com_rollback'

uptime = show global status like 'uptime'

tps=(com_commit + com_rollback)/uptime

---