MySQL几点重要的性能指标计算和优化的方法代码总结

下面小编就为大家带来一篇MySQL几点重要的性能指标计算和优化方法总结。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧

1 QPS计算(每秒查询数)

针对MyISAM引擎为主的DB

MySQL> show GLOBAL status like 'questions';+---------------+------------+| Variable_name | Value   |+---------------+------------+| Questions   | 2009191409 |+---------------+------------+1 row in set (0.00 sec)mysql> show global status like 'uptime';+---------------+--------+| Variable_name | Value |+---------------+--------+| Uptime    | 388402 |+---------------+--------+1 row in set (0.00 sec)

QPS=questions/uptime=5172，mysql自启动以来的平均QPS，如果要计算某一时间段内的QPS，可在高峰期间获取间隔时间t2-t1，然后分别计算出t2和t1时刻的q值，QPS=(q2-q1)/(t2-t1)

针对InnnoDB引擎为主的DB

mysql> show global status like 'com_update';+---------------+----------+| Variable_name | Value  |+---------------+----------+| Com_update  | 87094306 |+---------------+----------+1 row in set (0.00 sec)mysql> show global status like 'com_select';+---------------+------------+| Variable_name | Value   |+---------------+------------+| Com_select  | 1108143397 |+---------------+------------+1 row in set (0.00 sec)mysql> show global status like 'com_delete';+---------------+--------+| Variable_name | Value |+---------------+--------+| Com_delete  | 379058 |+---------------+--------+1 row in set (0.00 sec)mysql> show global status like 'uptime';+---------------+--------+| Variable_name | Value |+---------------+--------+| Uptime    | 388816 |+---------------+--------+1 row in set (0.00 sec)

QPS=(com_update+com_insert+com_delete+com_select)/uptime=3076,某一时间段内的QPS查询方法同上。

2 TPS计算(每秒事务数)

mysql> show global status like 'com_commit';+---------------+---------+| Variable_name | Value  |+---------------+---------+| Com_commit  | 7424815 |+---------------+---------+1 row in set (0.00 sec)mysql> show global status like 'com_rollback';+---------------+---------+| Variable_name | Value  |+---------------+---------+| Com_rollback | 1073179 |+---------------+---------+1 row in set (0.00 sec)mysql> show global status like 'uptime';+---------------+--------+| Variable_name | Value |+---------------+--------+| Uptime    | 389467 |+---------------+--------+1 row in set (0.00 sec)TPS=(com_commit+com_rollback)/uptime=22

3 线程连接数和命中率

mysql> show global status like 'threads_%';+-------------------+-------+| Variable_name   | Value |+-------------------+-------+| Threads_cached  | 480  |   //代表当前此时此刻线程缓存中有多少空闲线程| Threads_connected | 153  |  //代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数| Threads_created  | 20344 |  //代表从最近一次服务启动，已创建线程的数量| Threads_running  | 2   |   //代表当前激活的（非睡眠状态）线程数+-------------------+-------+4 rows in set (0.00 sec)mysql> show global status like 'Connections';+---------------+-----------+| Variable_name | Value   |+---------------+-----------+| Connections  | 381487397 |+---------------+-----------+1 row in set (0.00 sec)线程缓存命中率=1-Threads_created/Connections  = 99.994%我们设置的线程缓存个数mysql> show variables like '%thread_cache_size%';+-------------------+-------+| Variable_name   | Value |+-------------------+-------+| thread_cache_size | 500  |+-------------------+-------+1 row in set (0.00 s<strong>本文来源gaodai#ma#com搞@代~码^网+</strong>ec)

喜欢 (0)赏

[搞代码]

分享 (0)