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

秒速五厘米 2023-06-20 06:26 24阅读 0赞

**1 QPS计算(每秒查询数)**

针对MyISAM引擎为主的DB

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

针对InnnoDB引擎为主的DB

QPS=(com\_update+com\_insert+com\_delete+com\_select)/uptime=3076,某一时间段内的QPS查询方法同上。

**2 TPS计算(每秒事务数)**

**3 线程连接数和命中率**

线程缓存命中率=1-Threads\_created/Connections = 99.994%

我们设置的线程缓存个数

根据Threads\_connected可预估thread\_cache\_size值应该设置多大，一般来说250是一个不错的上限值，如果内存足够大，也可以设置成thread\_cache\_size值和threaads\_connected值相同；

或者通过观察threads\_created值，如果该值很大或一直在增长，可以适当增加thread\_cache\_size的值；在休眠状态下每个线程大概占用256KB左右的内存，所以当内存足够时，设置太小也不会节约太多内存，除非该值已经超过几千。

**4 表缓存**

我们设置的打开表的缓存和表定义缓存

**针对MyISAM：**

mysql每打开一个表，都会读入一些数据到table\_open\_cache 缓存 中，当mysql在这个缓存中找不到相应的信息时，才会去磁盘上直接读取，所以该值要设置得足够大以避免需要重新打开和重新解析表的定义，一般设置为max\_connections的10倍，但最好保持在10000以内。

还有种依据就是根据状态open\_tables的值进行设置，如果发现open\_tables的值每秒变化很大，那么可能需要增大table\_open\_cache的值。

table\_definition\_cache 通常简单设置为服务器中存在的表的数量，除非有上万张表。

**针对InnoDB：**

与MyISAM不同，InnoDB的open table和open file并无直接联系，即打开frm表时其相应的ibd文件可能处于关闭状态；故InnoDB只会用到table\_definiton\_cache，不会使用table\_open\_cache；其frm文件保存于table\_definition\_cache中，而idb则由innodb\_open\_files决定(前提是开启了innodb\_file\_per\_table)，最好将innodb\_open\_files设置得足够大，使得服务器可以保持所有的.ibd文件同时打开。

**5 最大连接数**

我们设置的max\_connections大小

通常max\_connections的大小应该设置为比Max\_used\_connections状态值大，Max\_used\_connections状态值反映服务器连接在某个时间段是否有尖峰，如果该值大于max\_connections值，代表客户端至少被拒绝了一次，可以简单地设置为符合以下条件：Max\_used\_connections/max\_connections=0.8

**6 Innodb 缓存命中率**

缓冲池命中率 = (Innodb\_buffer\_pool\_read\_requests)/(Innodb\_buffer\_pool\_read\_requests + Innodb\_buffer\_pool\_read\_ahead + Innodb\_buffer\_pool\_reads)=99.994%

如果该值小于99.9%，建议就应该增大innodb\_buffer\_pool\_size的值了，该值一般设置为内存总大小的75%-85%，或者计算出操作系统所需缓存+mysql每个连接所需的内存（例如排序缓冲和临时表）+MyISAM键缓存，剩下的内存都给innodb\_buffer\_pool\_size，不过也不宜设置太大，会造成内存的频繁交换，预热和关闭时间长等问题。

**7 MyISAM Key Buffer命中率和缓冲区使用率**

缓冲区的使用率=1-(Key\_blocks\_unused\*key\_cache\_block\_size/ key\_buffer\_size)=18.6%

读命中率=1-Key\_reads /Key\_read\_requests=99.98%

写命中率=1-Key\_writes / Key\_write\_requests =99.05%

可看到缓冲区的使用率并不高，如果很长一段时间后还没有使用完所有的键缓冲，可以考虑把缓冲区调小一点。

键缓存命中率可能意义不大，因为它和应用相关，有些应用在95%的命中率下就工作良好，有些则需要99.99%，所以从经验上看，每秒的缓存未命中次数更重要，假设一个独立磁盘每秒能做100个随机读，那么每秒有5个缓冲未命中可能不会导致I/O繁忙，但每秒80个就可能出现问题。

每秒缓存未命中=Key\_reads/uptime=0.33

**8 临时表使用情况**

可看到总共创建了56265812 张临时表，其中有19226325 张涉及到了磁盘IO，大概比例占到了0.34，证明数据库应用中排序，join语句涉及的数据量太大，需要优化SQL或者增大tmp\_table\_size的值，我设的是64M。该比值应该控制在0.2以内。

**9 binlog cache使用情况**

Binlog\_cache\_disk\_use表示因为我们binlog\_cache\_size设计的内存不足导致缓存二进制日志用到了临时文件的次数。

Binlog\_cache\_use 表示 用binlog\_cache\_size缓存的次数。

当对应的Binlog\_cache\_disk\_use 值比较大的时候 我们可以考虑适当的调高 binlog\_cache\_size 对应的值。

**10 Innodb log buffer size的大小设置**

innodb\_log\_buffer\_size我设置了8M，应该足够大了；Innodb\_log\_waits表示因log buffer不足导致等待的次数，如果该值不为0，可以适当增大innodb\_log\_buffer\_size的值。

**11 表扫描情况判断**

mysql> show global status like 'Handler_read%';
    +-----------------------+--------------+
    | Variable_name | Value |
    +-----------------------+--------------+
    | Handler_read_first | 19180695 |
    | Handler_read_key | 30303690598 |
    | Handler_read_last | 290721 |
    | Handler_read_next | 51169834260 |
    | Handler_read_prev | 1267528402 |
    | Handler_read_rnd | 219230406  |
    | Handler_read_rnd_next | 344713226172 |
    +-----------------------+--------------+
    7 rows in set (0.00 sec)

Handler\_read\_first：使用索引扫描的次数，该值大小说不清系统性能是好是坏。

Handler\_read\_key：通过key进行查询的次数，该值越大证明系统性能越好。

Handler\_read\_next：使用索引进行排序的次数。

Handler\_read\_prev：此选项表明在进行索引扫描时，按照索引倒序从数据文件里取数据的次数，一般就是ORDER BY ... DESC。

Handler\_read\_rnd：该值越大证明系统中有大量的没有使用索引进行排序的操作，或者join时没有使用到index。

Handler\_read\_rnd\_next：使用数据文件进行扫描的次数，该值越大证明有大量的全表扫描，或者合理地创建索引，没有很好地利用已经建立好的索引。

**12 Innodb\_buffer\_pool\_wait\_free**

该值不为0表示buffer pool没有空闲的空间了，可能原因是innodb\_buffer\_pool\_size设置太大，可以适当减少该值。

**13 join操作信息**

该值表示在join操作中没有使用到索引的次数，值很大说明join语句写得很有问题

该值表示第一个表使用ranges的join数量，该值很大说明join写得没有问题，通常可查看select\_full\_join和select\_range的比值来判断系统中join语句的性能情况

如果该值不为0需要检查表的索引是否合理，表示在表n+1中重新评估表n中的每一行的索引是否开销最小所做的联接数，意味着表n+1对该联接而言并没有有用的索引。