常见Mysql系统参数调整

Love The Way You Lie 2022-08-13 18:44 648阅读 0赞

MySQL参数对于MySQL数据库的功能和形成有非常重要的影响，下面是一些从网络上收集的一些资料，对于mysql数据库的常见参数有一些借鉴和参考的意义。

常见Mysql系统参数调整

列出MySQL服务器运行各种状态值：

mysql> **show global status;**

查询MySQL服务器配置信息：

mysql> **show variables;**

# 1.  慢查询 #

mysql> **show variables like '%slow%';**

\+---------------------+-------------------------------+

| Variable\_name       | Value                         |

\+---------------------+-------------------------------+

| log\_slow\_queries  （已废弃）   | OFF                           |

| slow\_launch\_time    | 2                             |

| slow\_query\_log      | OFF                           |

| slow\_query\_log\_file |/var/lib/mysql/twf13-slow.log |

\+---------------------+-------------------------------+

4 rows in set (0.00 sec)

mysql> **show global status like '%slow%';**

\+---------------------+-------+

| Variable\_name       | Value |

\+---------------------+-------+

| Slow\_launch\_threads | 0     |

| Slow\_queries        | 0    |

\+---------------------+-------+

2 rows in set (0.00 sec)

配置中关闭了记录慢查询，执行时间超过2秒的即为慢查询，系统显示有0个慢查询，你可以分析慢查询日志，找出有问题的SQL语句，慢查询时间 不宜设置过长，否则意义不大，最好在5秒以内，如果你需要微秒级别的慢查询，可以考虑给MySQL打补丁：http://www.percona.com/docs/wiki/release:start，记得找对应的版本。

打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主-从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。（消耗的主要是CPU）

mysqldumpslow查询慢查询日志

# 2.  连接数 #

经常会遇见”MySQL: ERROR 1040: Too many connections”的情况，一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max\_connections值过小：

**max\_connections**

**Max\_used\_connections**

**Connections**

**max\_user\_connections**

**back\_log**

**max\_connect\_errors**

** **

**max\_connections**：整个MySQL允许的最大连接数；

这个参数主要影响的是整个MySQL应用的并发处理能力，当系统中实际需要的连接量大于

max\_ connections的情况下，由于MySQL的设置限制，那么应用中必然会产生连接请求的等待，

从而限制了相应的并发量。所以一般来说，只要MySQL主机性能允许，都是将该参数设置的尽可能大一点。一般来说500到800左右是一个比较合适的参考值

当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小

mysql> **show variables like 'max\_connections';**

\+-----------------+-------+

| Variable\_name   | Value |

\+-----------------+-------+

| max\_connections | 151   |

\+-----------------+-------+

1 row in set (0.00 sec)

这台MySQL服务器最大连接数是151，然后查询一下服务器响应的最大连接数：

mysql> **show global status like 'Max\_used\_connections';**

\+----------------------+-------+

| Variable\_name        | Value |

\+----------------------+-------+

| Max\_used\_connections | 1     |

\+----------------------+-------+

查看当前连接数

mysql> **show global status like 'Connections';**

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| Connections   | 2    |

MySQL服务器过去的最大连接数是1，没有达到服务器连接数上限151，应该没有出现1040错误，比较理想的设置是：

Max\_used\_connections /max\_connections \* 100% ≈ 85%（好像和上面有冲突）

最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

max\_user\_connections：每个用户允许的最大连接数；是针对于单个用户的连接限制。在一般情况下我们可能都较少使用这个限制，只有在一些专门提供MySQL数据存储服务，或者是提供虚拟主机服务的应用中可能需要用到。

**back\_log** 是要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back\_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。如果期望在一个短时间内有很多连接，你需要增加它。也就是说，如果MySql的连接数据达到max\_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back\_log，如果等待连接的数量超过back\_log，将不被授予连接资源。另外，这值（back\_log）限于您的操作系统对到来的 TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制（可以检查你的OS文档找出这个变量的最大值），试图设定 back\_log高于你的操作系统的限制将是无效的。

mysql> **show variables like 'back\_log';**

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| back\_log      | 50   |

\+---------------+-------+

l  **max\_connect\_errors**

max\_connect\_errors默认值为10，如果受信帐号错误连接次数达到10则自动堵塞，需要flush hosts来解除。如果你得到象这样的一个错误：

Host ’hostname’ is blocked because of many connection errors.

Unblock with ’mysqladmin flush-hosts’

这意味着，mysqld已经得到了大量(max\_connect\_errors)的主机’hostname’的在中途被中断了的连接请求。在 max\_connect\_errors次失败请求后，mysqld认定出错了(象来字一个黑客的攻击)，并且阻止该站点进一步的连接，直到某人执行命令mysqladmin flush-hosts。

内网连接的话，建议设置在10000以上，已避免堵塞，并定期flushhosts。

l  **connect\_timeout**

指定MySQL服务等待应答一个连接报文的最大秒数，超出该时间，MySQL向客户端返回 bad handshake。默认值是5秒，在内网高并发环境中建议设置到10-15秒，以便避免bad hand shake。建议同时关注[thread\_cache\_size][thread_cache_size]并设置[thread\_cache\_size][thread_cache_size]为非0值，大小具体调整。

** **

l  **skip-name-resolve**

skip-name-resolve能大大加快用户获得连接的速度，特别是在网络情况较差的情况下。MySQL在收到连接请求的时候，会根据请求包 中获得的ip来反向追查请求者的主机名。然后再根据返回的主机名又一次去获取ip。如果两次获得的ip相同，那么连接就成功建立了。在DNS不稳定或者局 域网内主机过多的情况下，一次成功的连接将会耗费很多不必要的时间。假如MySQL服务器的ip地址是广域网的，最好不要设置skip-name- resolve。

# 3.  key\_buffer\_size #

key\_buffer\_size，索引缓存大小；

这个参数用来设置整个MySQL中的常规KeyCache大小。一般来说，如果我们的MySQL是运行在32位平台纸上，此值建议不要超过2GB大小。如果是运行在64位平台纸上则不用考虑此限制，但也最好不要超过4GB。

对于key\_buffer\_size的设置我们一般需要通过三个指标来计算，第一个是系统索引的总大小，第二个是系统可用物理内存，第三个是根据系统当前的KeyCache命中率。对于一个完全从零开始的全新系统的话，可能出了第二点可以拿到很清楚的数据之外，其他的两个数据都比较难获取，第三点是完全没有。

当然，我们可以通过MySQL官方手册中给出的一个计算公式粗略的估算一下我们系统将来的索引大小，不过前提是要知道我们会创建哪些索引，然后通过各索引估算出索引键的长度，以及表中存放数据的条数，公式如下：

Key\_Size =key\_number\*(key\_length+4)/0.67

Max\_key\_buffer\_size<Max\_RAM-QCache\_Usage-Threads\_Usage-System\_Usage

Threads\_Usage = max\_connections \*(sort\_buffer\_size + join\_buffer\_size +

read\_buffer\_size+read\_rnd\_buffer\_size+thread\_stack)

系统中记录的与Key Cache相关的性能状态参数变量：

◆Key\_blocks\_not\_flushed，已经更改但还未刷新到磁盘的DirtyCacheBlock；

◆Key\_blocks\_unused，目前未被使用的CacheBlock数目；

◆Key\_blocks\_used，已经使用了的CacheBlock数目；

◆Key\_read\_requests，CacheBlock被请求读取的总次数；

◆Key\_reads，在CacheBlock中找不到需要读取的Key信息后到“.MYI”文件中读取的次数；

◆Key\_write\_requests，CacheBlock被请求修改的总次数；

◆Key\_writes，在CacheBlock中找不到需要修改的Key信息后到“.MYI”文件中读入再修改的次数；

key\_buffer\_size是对MyISAM表性能影响最大的一个参数，下面一台以MyISAM为主要存储引擎服务器的配置：

mysql> **show variables like 'key\_buffer\_size';**

\+-----------------+-----------+

| Variable\_name   | Value    |

\+-----------------+-----------+

| key\_buffer\_size | 268435456 |

\+-----------------+-----------+

分配了256MB内存给key\_buffer\_size，我们再看一下key\_buffer\_size的使用情况：

mysql> **show global status like 'key\_read%';**

\+-------------------+-------+

| Variable\_name     | Value |

\+-------------------+-------+

| Key\_read\_requests | 470   |

| Key\_reads         | 17   |

\+-------------------+-------+

一共有470个索引读取请求，有17个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：

key\_cache\_miss\_rate = Key\_reads / Key\_read\_requests \* 100%

比如上面的数据，key\_cache\_miss\_rate为0.036%，27个索引读取请求有一个直接读硬盘，key\_cache\_miss\_rate在0.1%以下都很好(每1000个请求有一个直接读硬盘)，如果key\_cache\_miss\_rate在 0.01%以下的话，key\_buffer\_size分配的过多，可以适当减少。

MySQL服务器还提供了**key\_blocks**\_\*参数：

mysql> show global status like ‘key\_blocks\_u%’;

\+————————+————-+

| Variable\_name | Value |

\+————————+————-+

| Key\_blocks\_unused | 0 |

| Key\_blocks\_used | 413543 |

\+————————+————-+

Key\_blocks\_unused表示未使用的缓存簇(blocks)数，Key\_blocks\_used表示曾经用到的最大的blocks数， 比如这台服务器，所有的缓存都用到了，要么增加key\_buffer\_size，要么就是过渡索引了，把缓存占满了。比较理想的设置：

Key\_blocks\_used / (Key\_blocks\_unused + Key\_blocks\_used) \*100% ≈ 80%

KeyBuffer命中率：KeyBuffer命中率代表了MyISAM类型表的索引的Cache

命中率。该命中率的大小将直接影响MyISAM类型表的读写性能。KeyBuffer命

中率实际上包括读命中率和写命中率两种，MySQL中并没有直接给出这两个命中率

的值，但是可以通过如下方式计算出来：

key\_buffer\_read\_hits=(1-Key\_reads/Key\_read\_requests)\*100%

key\_buffer\_write\_hits=(1-Key\_writes/Key\_write\_requests)\*100%

获取所需状态变量值：

mysql> **show global status like 'key\_%';**

\+------------------------+--------+

| Variable\_name          | Value  |

\+------------------------+--------+

| Key\_blocks\_not\_flushed | 0      |

| Key\_blocks\_unused      | 231943 |

| Key\_blocks\_used        | 17    |

| Key\_read\_requests      | 498   |

| Key\_reads              | 17     |

| Key\_write\_requests     | 52    |

| Key\_writes             | 12     |

\+------------------------+--------+

# 4.  临时表 #

TmpTable的状况主要是用于监控MySQL使用临时表的量是否过多，是否有临时表过大而不得不从内存中换出到磁盘文件上。

mysql> **show global status like ‘created\_tmp%’;**

\+————————-+———+

| Variable\_name | Value |

\+————————-+———+

| Created\_tmp\_disk\_tables | 21197 |

| Created\_tmp\_files | 58 |

| Created\_tmp\_tables | 1771587 |

\+————————-+———+

从上面的状态信息可以了解到系统使用了1771587次临时表，其中有21197次临时表比较大，无法在内存中完成，而不得不使用到磁盘文件。如果Created\_tmp\_tables非常大 ，则可能是系统中排序操作过多，或者是表连接方式不是很优化。而如果是Created\_tmp\_disk\_tables与Created\_tmp\_tables的比率过高，如超过10%，则我们需要考虑是否tmp\_table\_size这个系统参数所设置的足够大。当然，如果系统内存有限，也就没有太多好的解决办法了。

每次创建临时表，Created\_tmp\_tables增加，如果是在磁盘上创建临时表，Created\_tmp\_disk\_tables也增加,Created\_tmp\_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：

Created\_tmp\_disk\_tables / Created\_tmp\_tables \* 100% <=25%

比如上面的服务器Created\_tmp\_disk\_tables / Created\_tmp\_tables \* 100%= 1.20%，应该相当好了。我们再看一下MySQL服务器对临时表的配置：

mysql> show variables where Variable\_name in(‘tmp\_table\_size’, ‘max\_heap\_table\_size’);

\+———————+———–+

| Variable\_name | Value |

\+———————+———–+

| max\_heap\_table\_size | 268435456 |

| tmp\_table\_size | 536870912 |

\+———————+———–+

只有256MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表。

# 5.  Table Cache相关优化 #

为了解决打开表文件描述符太过频繁的问题，MySQL在系统中实现了一个TableCache的机制

在MySQL中我们通过table\_cache（从MySQL5.1.3开始改为table\_open\_cache），来设置系统中为我们Cache的打开表文件描述符的数量。

通过MySQL官方手册中的介绍，我们设置table\_cache大小的时候应该通过max\_connections参数计算得来，公式如下：

table\_cache=max\_connections\*N；（可能不太合理）

其中N代表单个Query语句中所包含的最多Table的数量。

这个计算的公式只能计算出我们同一时刻需要打开的描述符的最大数量，而table\_cache的设置也不一定非得根据这个极限值来设定，因为table\_open\_cache所设定的只是Cache打开的描述符的数量的大小，而不是最多能够打开的量的大小。

table\_open\_cache的设置：

mysql> **show variables like '%table\_open\_cache%';**

\+------------------+-------+

| Variable\_name    | Value |

\+------------------+-------+

| table\_open\_cache | 256   |

\+------------------+-------+

当前系统使用情况：

mysql> **show global status like 'open%tables%';**

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| Open\_tables   | 54   |

| Opened\_tables | 64    |

\+---------------+-------+

上面的结果显示系统设置的table\_open\_cache为256个，也就是说在该MySQL中，TableCache中可以Cache 256个打开文件的描述符；当前系统中打开的描述符仅仅则只有54个。打开过的表的数量是64个。

Open\_tables表示打开表的数量，Opened\_tables表示打开过的表数量，如果Opened\_tables数量过大，说明配置中table\_cache(5.1.3之后这个值叫做table\_open\_cache)值可能太小

比较合适的值为：

Open\_tables / Opened\_tables \* 100% >= 85%

Open\_tables / table\_open\_cache \* 100% <= 95%

# 6.  进程使用情况 #

在MySQL中，为了尽可提高客户端请求创建连接这个过程的性能，实现了一个ThreadCache池，将空闲的连接线程存放在其中，而不是完成请求后就销毁。这样，当有新的连接请求的时候，MySQL首先会检查ThreadCache池中是否存在空闲连接线程，如果存在则取出来直接使用，如果没有空闲连接线程，才创建新的连接线程。

l  **thread\_cache\_size**：ThreadCache池中应该存放的连接线程数。

当系统最初启动的时候，并不会马上就创建thread\_cache\_size所设置数目的连接线程存放在ThreadCache池中，而是随着连接线程的创建及使用，慢慢的将用完的连接线程存入其中。当存放的连接线程达到thread\_cache\_size值之后，MySQL就不会再续保存用完的连接线程了。

如果我们的应用程序使用的短连接，ThreadCache池的功效是最明显的。因为在短连接的数据库应用中，数据库连接的创建和销毁是非常频繁的，如果每次都需要让MySQL新建和销毁相应的连接线程，那么这个资源消耗实际上是非常大的，而当我们使用了ThreadCache之后，由于连接线程大部分都是在创建好了等待取用的状态，既不需要每次都重新创建，又不需要在使用完之 后 销 毁 ， 所 以 可 以 节 省 下 大 量 的 系 统 资 源 。 所 以 在 短 连 接 的 应 用 系 统 中 ，thread\_cache\_size的值应该设置的相对大一些，不应该小于应用系统对数据库的实际并发请求数。

l  **thread\_stack**：每个连接线程被创建的时候，MySQL给他分配的内存大小。（默认值即可）

l  连接线程相关的系统变量的设置值：

mysql> **show variables like 'thread%';**

\+--------------------+---------------------------+

| Variable\_name      |Value                     |

\+--------------------+---------------------------+

| thread\_cache\_size  | 32                         |

| thread\_concurrency | 8                         |

| thread\_handling    |one-thread-per-connection |

| thread\_stack       |196608                    |

\+--------------------+---------------------------+

l  系统被连接的次数以及当前系统中连接线程的状态值：

mysql> **show status like 'Connections';**

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| Connections   | 127    |

\+---------------+-------+

mysql>**show status like '%thread%';**

\+------------------------+-------+

|Variable\_name |Value|

\+------------------------+-------+

|Delayed\_insert\_threads|0 |

|Slow\_launch\_threads |0 |

|Threads\_cached |4 |

|Threads\_connected |7 |

|Threads\_created |11 |

|Threads\_running |1 |

\+------------------------+-------+

Threads\_created表示创建过的线程数，如果发现Threads\_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread\_cache\_size值。

通过上面的命令，我们可以看出，系统设置了ThreadCache池最多将缓存32个连接线程，每个连接线程创建之初，系统分配**192KB**的内存堆栈空给他。系统启动到现在共接收到客户端的连接**127**次，共创建了**11**个连接线程，但前有**7**个连接线程处于和客户端连接的状态，而7个连接状态的线程中只有一个是active状态，也就是说只有一个正在处理客户端提交的请求。而在ThreadCache池中当共Cache了4个连接线程。

通过系统设置和当前状态的分析，我们可以发现，thread\_cache\_size的设置已经足够了，甚至还远大于系统的需要。所以我们可以适当减少thread\_cache\_size的设置，比如设置为8或者16。根据Connections和Threads\_created这两个系统状态值，我们还可以计算出系统新建连接连接的Thread Cache命中率，也就是通过ThreadCache池中取得连接线程的次数与系统接收的总连接次数的比率，如下：

Threads\_Cache\_Hit=(Connections-Threads\_created)/Connections\*100%

我们可以通过上面的这个运算公式计算一下上面环境中的ThreadCache命中率：Thread\_Cache\_Hit=(127-12)/127\*100%=90.55%

一般来说，当系统稳定运行一段时间之后，我们的ThreadCache命中率应该保持在90%左右甚至更高的比率才算正常。可以看出上面环境中的ThreadCache命中比率基本还算是正常的。

# 7.  查询缓存 #

MySQL的QueryCache实现原理实际上并不是特别的复杂，简单的来说就是将客户端请求的Query语句（当然仅限于SELECT类型的Query）通过一定的hash算法进行一个计算而得到一个hash值，存放在一个hash桶中。同时将该Query的结果集（ResultSet）也存放在一个内存Cache中的。存放Queryhash值的链表中的每一个hash值所在的节点中同时还存放了该Query所对应的ResultSet的Cache所在的内存地址，以及该Query所涉及到的所有Table的标识等其他一些相关信息。系统接受到任何一个SELECT类型的Query的时候，首先计算出其hash值，然后通过该hash值到QueryCache中去匹配，如果找到了完全相同的Query，则直接将之前所Cache的ResultSet返回给客户端而完全不需要进行后面的任何步骤即可完成这次请求。而后端的任何一个表的任何一条数据发生变化之后，也会通知QueryCache，需要将所有与该Table有关的Query的Cache全部失效，并释放出之前占用的内存地址，以便后面其他的Query能够使用。

从上面的实现原理来看，QueryCache确实是以比较简单的实现带来巨大性能收益的功能。但是很多人可能都忽略了使用QueryCache之后所带来的负面影响：

a)      Query语句的hash运算以及hash查找资源消耗。当我们使用QueryCache之后，每条SELECT类型的Query在到达MySQL之后，都需要进行一个hash运算然后查找是否存在该Query的Cache，虽然这个hash运算的算法可能已经非常高效了，hash查找的过程也已经足够的优化了，对于一条Query来说消耗的资源确实是非常非常的少，但是当我们每秒都有上千甚至几千条Query的时候，我们就不能对产生的CPU的消耗完全忽视了。

b)      QueryCache的失效问题。如果我们的表变更比较频繁，则会造成QueryCache的失效率非常高。这里的表变更不仅仅指表中数据的变更，还包括结构或者索引等的任何变更。也就是说我们每次缓存到QueryCache中的Cache数据可能在刚存入后很快就会因为表中的数据被改变而被清除，然后新的相同Query进来之后无法使用到之前的Cache。

c)      QueryCache中缓存的是ResultSet，而不是数据页，也就是说，存在同一条记录被Cache多次的可能性存在。从而造成内存资源的过渡消耗。当然，可能有人会说我们可以限定QueryCache的大小啊。是的，我们确实可以限定QueryCache的大小，但是这样，QueryCache就很容易造成因为内存不足而被换出，造成命中率的下降。

l  适度使用QueryCache

虽然QueryCache的使用会存在一些负面影响，但是我们也应该相信其存在是必定有一定价值。我们完全不用因为QueryCache的上面三个负面影响就完全失去对QueryCache的信心。只要我们理解了QueryCache的实现原理，那么我们就完全可以通过一定的手段在使用QueryCache的时候扬长避短，重发发挥其优势，并有效的避开其劣势。

1.      首先，我们需要根据QueryCache失效机制来判断哪些表适合使用Query哪些表不适合。由于QueryCache的失效主要是因为Query所依赖的Table的数据发生了变化，造成Query的ResultSet可能已经有所改变而造成相关的QueryCache全部失效，那么我们就应该避免在查询变化频繁的Table的Query上使用，而应该在那些查询变化频率较小的Table的Query上面使用。MySQL中针对QueryCache有两个专用的SQL Hint（提示）：SQL\_NO\_CACHE和SQL\_CACHE，分别代表强制不使用Query Cache和强制使用QueryCache。我们完全可以利用这两个SQLHint，让MySQL知道我们希望哪些SQL使用QueryCache而哪些SQL就不要使用了。这样不仅可以让变化频繁Table的Query浪费QueryCache的内存，同时还可以减少QueryCache的检测量。

2.      其次，对于那些变化非常小，大部分时候都是静态的数据，我们可以添加SQL\_CACHE的SQLHint，强制MySQL使用QueryCache，从而提高该表的查询性能。

3.      最后，有些SQL的ResultSet很大，如果使用QueryCache很容易造成Cache内存的不足，或者将之前一些老的Cache冲刷出去。对于这一类Query我们有两种方法可以解决，一是使用SQL\_NO\_CACHE参数来强制他不使用Query Cache而每次都直接从实际数据中去查找，另一种方法是通过设定“query\_cache\_limit”参数值来控制Query Cache中所Cache的最大Result Set ，系统默认为1M（1048576）。当某个Query的Result Set大于“query\_cache\_limit”所设定的值的时候，Query Cache是不会Cache这个Query的。

l  QueryCache的相关系统参数变量和状态变量

mysql>**show variableslike '%Query\_cache%';**

\+------------------------------+----------+

|Variable\_name                | Value    |

\+------------------------------+----------+

|have\_query\_cache             | YES      |

|query\_cache\_limit            |1048576  |

|query\_cache\_min\_res\_unit     | 4096     |

|query\_cache\_size             | 16777216 |必须是1024的整数倍

| query\_cache\_type             | ON       |选项:OFF、ON、DEMAND(只有包含了SQL\_CACHE选项的查询才能被缓存)

|query\_cache\_wlock\_invalidate | OFF      |

\+------------------------------+----------+

●“have\_query\_cache”：该MySQL是否支持QueryCache；

●“query\_cache\_limit”：QueryCache存放的单条Query最大ResultSet，默认1M；超过不缓存

●“query\_cache\_min\_res\_unit”：QueryCache每个ResultSet存放的最小内存大小，默认4k；

●“query\_cache\_size”：系统中用于QueryCache内存的大小；

●“query\_cache\_type”：系统是否打开了QueryCache功能；

●“query\_cache\_wlock\_invalidate”：针对于MyISAM存储引擎，设置当有WRITELOCK在某个Table上面的时候，读请求是要等待WRITELOCK释放资源之后再查询还是允许直接从QueryCache中读取结果，默认为FALSE（可以直接从QueryCache中取得结果）。

mysql> **show status like'Qcache%';**

\+-------------------------+----------+

| Variable\_name           |Value    |

\+-------------------------+----------+

| Qcache\_free\_blocks      |12       |

| Qcache\_free\_memory      |16596104 |

| Qcache\_hits             |482      |

| Qcache\_inserts          |181      |

| Qcache\_lowmem\_prunes    |0        |

| Qcache\_not\_cached       | 1       |

| Qcache\_queries\_in\_cache | 71      |

| Qcache\_total\_blocks     |177      |

\+-------------------------+----------+

●“Qcache\_free\_blocks”：QueryCache中目前还有多少剩余的blocks。如果该值显示较大，则说明QueryCache中的内存碎片较多了，可能需要寻找合适的机会进行整理（）。**如果Qcache\_free\_blocks大致等于Qcache\_total\_blocks/2，则说明碎片非常严重。如果Qcache\_lowmem\_prunes的值正在增加，并且有大量的自由块，这意味着碎片导致查询正在被从缓存中永久删除。**

可以使用**FLUSH QUERY CACHE**命令移除碎片，这个命令会把所有的存储块向上移动，并把自由块移到底部。

**查询缓存碎片率 =Qcache\_free\_blocks / Qcache\_total\_blocks \* 100%**

如果查询缓存碎片率超过20%，可以用FLUSHQUERY CACHE整理缓存碎片，或者试试减小query\_cache\_min\_res\_unit，如果你的查询都是小数据量的话。

** **

●“Qcache\_free\_memory”：QueryCache中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的QueryCache内存大小是否足够，是需要增加还是过多了；

**查询缓存利用率 =(query\_cache\_size – Qcache\_free\_memory) / query\_cache\_size \* 100%**

查询缓存利用率在25%以下的话说明query\_cache\_size设置的过大，可适当减小;查询缓存利用率在80%以上而且Qcache\_lowmem\_prunes> 50的话说明query\_cache\_size可能有点小，要不就是碎片太多。

** **

●“Qcache\_hits”：多少次命中。通过这个参数我们可以查看到QueryCache的基本效果；

●“Qcache\_inserts”：多少次未命中然后插入。通过“Qcache\_hits”和“Qcache\_inserts”两个参数我们就可以算出QueryCache的命中率了：

**QueryCache命中率=Qcache\_hits/(Qcache\_hits+Qcache\_inserts)；**

● “Qcache\_lowmem\_prunes”：多少条Query因为内存不足而被清除出QueryCache。通过“Qcache\_lowmem\_prunes”和“Qcache\_free\_memory”相互结合，能够更清楚的了解到我们系统中QueryCache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换出

●“Qcache\_not\_cached”：因为query\_cache\_type的设置或者不能被cache的Query的数量；

●“Qcache\_queries\_in\_cache”：当前QueryCache中cache的Query数量；

●“Qcache\_total\_blocks”：当前QueryCache中的block数量；

l  QueryCache的限制

QueryCache由于存放的都是逻辑结构的ResultSet，而不是物理的数据页，所以在性能提升的同时，也会受到一些特定的限制。

a) 5.1.17之前的版本不能Cache帮定变量的Query，但是从5.1.17版本开始，QueryCache已经开始支持帮定变量的Query了；

b) 所有子查询中的外部查询SQL不能被Cache；

c) 在Procedure，Function以及Trigger中的Query不能被Cache；

d) 包含其他很多每次执行可能得到不一样结果的函数的Query不能被Cache。

鉴于上面的这些限制，在使用QueryCache的过程中，建议通过精确设置的方式来使用，仅仅让合适的表的数据可以进入QueryCache，仅仅让某些Query的查询结果被Cache。

# 8.  排序使用情况 #

sort\_buffer\_size：系统中对数据进行排序的时候使用的Buffer；

SortBuffer同样是针对单个Thread的，所以当多个Thread同时进行排序的时候，系统中就会出现多个Sort Buffer。一般我们可以通过增大SortBuffer的大小来提高ORDERBY或者是GROUPBY的处理性能。系统默认大小为2MB，最大限制和JoinBuffer一样，在MySQL5.1.23版本之前最大为4GB，从5.1.23版本开始，在除了Windows之外的64位的平台上可以超出4GB的限制。

mysql> **show variables like 'sort\_buffer\_size';**

\+------------------+---------+

| Variable\_name    | Value  |

\+------------------+---------+

| sort\_buffer\_size | 1048576 |

\+------------------+---------+

mysql> **show global status like ‘sort%’;**

\+——————-+————+

| Variable\_name | Value |

\+——————-+————+

| Sort\_merge\_passes | 29 |

| Sort\_range | 37432840 |

| Sort\_rows | 9178691532 |

| Sort\_scan | 1860569 |

\+——————-+————+

Sort\_merge\_passes 包括两步。MySQL 首先会尝试在内存中做排序，使用的内存大小由系统变量 Sort\_buffer\_size 决定，如果它的大小不够把所有的记录都读到内存中，MySQL就会把每次在内存中排序的结果存到临时文件中，等 MySQL 找到所有记录之后，再把临时文件中的记录做一次排序。这再次排序就会增加 Sort\_merge\_passes。实际上，MySQL 会用另一个临时文件来存再次排序的结果，所以通常会看到 Sort\_merge\_passes 增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增加 Sort\_buffer\_size 会减少 Sort\_merge\_passes 和创建临时文件的次数。但盲目的增加 Sort\_buffer\_size 并不一定能提高速度，见 How fast can you sort data with MySQL?(引自http://qroom.blogspot.com/2007/09/mysql-select-sort.html，貌似被墙)

另外，增加read\_rnd\_buffer\_size(3.2.3是record\_rnd\_buffer\_size)的值对排序的操作也有一点的好处，参见：http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read\_rnd\_buffer\_size/

# 9.  文件打开数（Open\_files） #

mysql> show global statuslike ‘open\_files’;

\+—————+——-+

| Variable\_name | Value |

\+—————+——-+

| Open\_files | 1410 |

\+—————+——-+

mysql> show variables like‘open\_files\_limit’;

\+——————+——-+

| Variable\_name | Value |

\+——————+——-+

| open\_files\_limit | 4590 |

\+——————+——-+

比较合适的设置：Open\_files /open\_files\_limit \* 100% <= 75%

# 10.        表锁情况 #

mysql> show global status like ‘table\_locks%’;

\+———————–+———–+

| Variable\_name | Value |

\+———————–+———–+

| Table\_locks\_immediate | 490206328 |

| Table\_locks\_waited | 2084912 |

\+———————–+———–+

Table\_locks\_immediate表示立即释放表锁数，Table\_locks\_waited表示需要等待的表锁数，如果 Table\_locks\_immediate / Table\_locks\_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。示例中的服务器 Table\_locks\_immediate / Table\_locks\_waited = 235，MyISAM就足够了。

# 11.        表扫描情况 #

mysql> show global status like ‘handler\_read%’;

\+———————–+————-+

| Variable\_name | Value |

\+———————–+————-+

| Handler\_read\_first | 5803750 |

| Handler\_read\_key | 6049319850 |

| Handler\_read\_next | 94440908210 |

| Handler\_read\_prev | 34822001724 |

| Handler\_read\_rnd | 405482605 |

| Handler\_read\_rnd\_next | 18912877839 |

mysql> show global status like ‘com\_select’;

\+—————+———–+

| Variable\_name | Value |

\+—————+———–+

| Com\_select | 222693559 |

\+—————+———–+

计算表扫描率：

表扫描率 = Handler\_read\_rnd\_next / Com\_select

如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read\_buffer\_size值会有一些好处，但最好不要超过8MB。

**Handler\_read\_first**  
  
The number of times the first entry was read from an index. If this value ishigh, it suggests that the server is doing a lot of full index scans; forexample, SELECT col1 FROM foo, assuming that col1 is indexed.  
  
此选项表明SQL是在做一个全索引扫描，注意是全部，而不是部分，所以说如果存在WHERE语句，这个选项是不会变的。如果这个选项的数值很大，既是好事也是坏事。说它好是因为毕竟查询是在索引里完成的，而不是数据文件里，说它坏是因为大数据量时，简便是索引文件，做一次完整的扫描也是很费时的。

**Handler\_read\_key**  
  
The number of requests to read a row based on a key. If this value is high, itis a good indication that your tables are properly indexed for your queries.  
  
此选项数值如果很高，那么恭喜你，你的系统高效的使用了索引，一切运转良好。

**Handler\_read\_next**  
  
The number of requests to read the next row in key order. This value isincremented if you are querying an index column with a range constraint or ifyou are doing an index scan.  
  
此选项表明在进行索引扫描时，按照索引从数据文件里取数据的次数。

**Handler\_read\_prev**  
  
The number of requests to read the previous row in key order. This read methodis mainly used to optimize ORDER BY ... DESC.  
  
此选项表明在进行索引扫描时，按照索引倒序从数据文件里取数据的次数，一般就是ORDER BY ... DESC。

**Handler\_read\_rnd**  
  
The number of requests to read a row based on a fixed position. This value ishigh if you are doing a lot of queries that require sorting of the result. Youprobably have a lot of queries that require MySQL to scan entire tables or youhave joins that don't use keys properly.  
  
简单的说，就是查询直接操作了数据文件，很多时候表现为没有使用索引或者文件排序

**Handler\_read\_rnd\_next**  
  
The number of requests to read the next row in the data file. This value is highif you are doing a lot of table scans. Generally this suggests that your tablesare not properly indexed or that your queries are not written to take advantageof the indexes you have.  
  
此选项表明在进行数据文件扫描时，从数据文件里取数据的次数。

说到判断查询方式优劣这个问题，就再顺便提提show profile语法，在新版MySQL里提供了这个功能：  
  
mysql> set profiling=on;  
  
mysql> use mysql;  
mysql> select \* from user;  
  
mysql> show profile;  
\+--------------------+----------+  
|Status            | Duration |  
\+--------------------+----------+  
| starting           |0.000078 |  
| Opening tables     | 0.000022 |  
| System lock        | 0.000010 |  
| Table lock         | 0.000014 |  
|init              | 0.000054 |  
| optimizing         | 0.000008 |  
| statistics         | 0.000015 |  
| preparing          | 0.000014 |  
| executing          | 0.000007 |  
| Sending data       | 0.000139 |  
|end               | 0.000007 |  
| query end          | 0.000007 |  
| freeing items      | 0.000044 |  
| logging slow query | 0.000004 |  
| cleaning up        | 0.000005 |  
\+--------------------+----------+

# 12.        常见mysql监控指标 #

●QPS（每秒Query量）：这里的QPS实际上是指MySQLServer每秒执行的Query

总量，在MySQL5.1.30及以下版本可以通过Questions状态值每秒内的变化量

来近似表示，而从MySQL5.1.31开始，则可以通过Queries来表示。Queries是

在MySQL5.1.31才新增的状态变量。主要解决的问题就是Questions状态变量

并没有记录存储过程中所执行的Query（当然，在无存储过程的老版本MySQL中

则不存在这个区别），而Queries状态变量则会记录。二者获取方式：

**QPS=Questions(orQueries)/Seconds**

获取所需状态变量值：

mysql> show status like 'Qu%';

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| Queries       | 1373 |

| Questions     | 40   |

\+---------------+-------+

这里的Seconds是指累计出上述两个状态变量值的时间长度，后面用到的地方也

代表同样的意思。

●TPS（每秒事务量）：在MySQLServer中并没有直接事务计数器，我们只能通过

回滚和提交计数器来计算出系统的事务量。所以，我们需要通过以下方式来得到客

户端应用程序所请求的TPS值：

**TPS=(Com\_commit+Com\_rollback)/Seconds**

如果我们还使用了分布式事务，那么还需要将Com\_xa\_commit和

Com\_xa\_rollback两个状态变量的值加上。

●KeyBuffer命中率：KeyBuffer命中率代表了MyISAM类型表的索引的Cache

命中率。该命中率的大小将直接影响MyISAM类型表的读写性能。KeyBuffer命

中率实际上包括读命中率和写命中率两种，MySQL中并没有直接给出这两个命中率

的值，但是可以通过如下方式计算出来：

**key\_buffer\_read\_hits=(1-Key\_reads/Key\_read\_requests)\*100%**

**key\_buffer\_write\_hits=(1-Key\_writes/Key\_write\_requests)\*100%**

获取所需状态变量值：

mysql> show status like 'Key%';

\+------------------------+--------+

| Variable\_name          | Value  |

\+------------------------+--------+

| Key\_blocks\_not\_flushed | 0      |

| Key\_blocks\_unused      | 231934 |

| Key\_blocks\_used        | 26    |

| Key\_read\_requests      | 1003  |

| Key\_reads              | 26     |

| Key\_write\_requests     | 89    |

| Key\_writes             | 26     |

\+------------------------+--------+

通过这两个计算公式，我们很容易就可以得出系统当前KeyBuffer的使用情况

●InnodbBuffer命中率：这里InnodbBuffer所指的是innodb\_buffer\_pool，也就是用来缓存Innodb类型表的数据和索引的内存空间。类似Keybuffer，我们同样可以根据MySQLServer提供的相应状态值计算出其命中率：

**innodb\_buffer\_read\_hits=(1-Innodb\_buffer\_pool\_reads/Innodb\_buffer\_pool\_read\_requests)\*100%**

获取所需状态变量值：

mysql> show status like'Innodb\_buffer\_pool\_read%';

\+---------------------------------------+-------+

| Variable\_name                         | Value |

\+---------------------------------------+-------+

| Innodb\_buffer\_pool\_read\_ahead\_rnd     | 0    |

| Innodb\_buffer\_pool\_read\_ahead         | 0    |

| Innodb\_buffer\_pool\_read\_ahead\_evicted |0     |

**| Innodb\_buffer\_pool\_read\_requests      | 462  |**

**| Innodb\_buffer\_pool\_reads              | 145   |**

\+---------------------------------------+-------+

●QueryCache命中率：如果我们使用了QueryCache，那么对QueryCache命中

率进行监控也是有必要的，因为他可能告诉我们是否在正确的使用QueryCache。

QueryCache命中率的计算方式如下：

**Query\_cache\_hits=(Qcache\_hits/(Qcache\_hits+Qcache\_inserts))\*100%**

获取所需状态变量值：

mysql> show status like 'Qcache%';

\+-------------------------+----------+

| Variable\_name           | Value    |

\+-------------------------+----------+

| Qcache\_free\_blocks      | 1       |

| Qcache\_free\_memory      | 16596104 |

| Qcache\_hits             | 482      |

| Qcache\_inserts          | 181      |

| Qcache\_lowmem\_prunes    | 0       |

| Qcache\_not\_cached       |1        |

| Qcache\_queries\_in\_cache | 71       |

| Qcache\_total\_blocks     | 166     |

\+-------------------------+----------+

●TableCache状态量：TableCache的当前状态量可以帮助我们判断系统参数

table\_open\_cache的设置是否合理。如果状态变量Open\_tables与

Opened\_tables之间的比率过低，则代表TableCache设置过小，个人认为该值

处于80%左右比较合适。注意，这个值并不是准确的TableCache命中率。

获取所需状态变量值：

mysql> show status like 'Open%';

\+--------------------------+-------+

| Variable\_name            | Value |

\+--------------------------+-------+

| Open\_files               | 88    |

| Open\_streams             | 0     |

| Open\_table\_definitions   | 69   |

| Open\_tables              | 62    |

| Opened\_files             | 200   |

| Opened\_table\_definitions | 0     |

| Opened\_tables            | 0     |

\+--------------------------+-------+

●ThreadCache命中率：ThreadCache命中率能够直接反应出我们的系统参数

thread\_cache\_size设置的是否合理。一个合理的thread\_cache\_size参数能够

节约大量创建新连接时所需要消耗的资源。ThreadCache命中率计算方式如下：

**Thread\_cache\_hits=(1-Threads\_created/Connections)\*100%**

获取所需状态变量值：

mysql> show status like 'Thread%';

\+-------------------+-------+

| Variable\_name     | Value |

\+-------------------+-------+

| Threads\_cached    | 1    |

| Threads\_connected | 1     |

| Threads\_created   | 2    |

| Threads\_running   | 1    |

\+-------------------+-------+

mysql> show status like 'Connections';

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| Connections   | 142  |

\+---------------+-------+

正常来说，ThreadCache命中率要在90%以上才算比较合理。

●锁定状态：锁定状态包括表锁和行锁两种，我们可以通过系统状态变量获得锁定总

次数，锁定造成其他线程等待的次数，以及锁定等待时间信息。

mysql>SHOW STATUS LIKE '%lock%';

\+-------------------------------+-------+

|Variable\_name |Value|

\+-------------------------------+-------+

......

|Innodb\_row\_lock\_current\_waits|0 |

|Innodb\_row\_lock\_time |0 |

|Innodb\_row\_lock\_time\_avg |0 |

|Innodb\_row\_lock\_time\_max |0 |

|Innodb\_row\_lock\_waits |0 |

......

|Table\_locks\_immediate |44 |

|Table\_locks\_waited |0 |

\+-------------------------------+-------+

通过上述系统变量，我们可以得出表锁总次数，其中造成其他现线程等待的次数。

同时还可以得到非常详细的行锁信息，如行锁总次数，行锁总时间，每次行锁等待

时间，行锁造成最大等待时间以及当前等待行锁的线程数。通过对这些量的监控，

我们可以清晰的了解到系统整体的锁定是否严重。如当Table\_locks\_waited与

Table\_locks\_immediate的比值较大，则说明我们的表锁造成的阻塞比较严重，可

能需要调整Query语句，或者更改存储引擎，亦或者需要调整业务逻辑。当然，

具体改善方式必须根据实际场景来判断。而Innodb\_row\_lock\_waits较大，则说

明Innodb的行锁也比较严重，且影响了其他线程的正常处理。同样需要查找出原

因并解决。造成Innodb行锁严重的原因可能是Query语句所利用的索引不够合

理（Innodb行锁是基于索引来锁定的），造成间隙锁过大。也可能是系统本身处理

能力有限，则需要从其他方面（如硬件设备）来考虑解决。

●复制延时量：复制延时量将直接影响了Slave数据库处于不一致状态的时间长短。

如果我们是通过Slave来提供读服务，就不得不重视这个延时量。我们可以通过在Slave节点上执行“SHOWSLAVESTATUS”命令，取Seconds\_Behind\_Master项的值来了解Slave当前的延时量（单位：秒）。当然，该值的准确性依赖于复制是否处于正常状态。每个环境下的Slave所允许的延时长短与具体环境有关，所以复制延时多长时间是合理的，只能由读者朋友根据各自实际的应用环境来判断。

●Tmp table状况：Tmp Table的状况主要是用于监控MySQL使用临时表的量是否过多，是否有临时表过大而不得不从内存中换出到磁盘文件上。临时表使用状态信息可以通过如下方式获得：

mysql> **show status like 'Created\_tmp%';**

\+-------------------------+-------+

| Variable\_name           | Value |

\+-------------------------+-------+

| Created\_tmp\_disk\_tables | 1     |

| Created\_tmp\_files       | 5    |

| Created\_tmp\_tables      | 18   |

\+-------------------------+-------+

从上面的状态信息可以了解到系统使用了18次临时表，其中有1次临时表比较大，无法在内存中完成，而不得不使用到磁盘文件。如果Created\_tmp\_tables非常大，则可能是系统中排序操作过多，或者是表连接方式不是很优化。而如果是Created\_tmp\_disk\_tables与Created\_tmp\_tables的比率过高，如超过10%，则我们需要考虑是否tmp\_table\_size这个系统参数所设置的足够大。当然，如果系统内存有限，也就没有太多好的解决办法了。

●BinlogCache使用状况：BinlogCache用于存放还未写入磁盘的Binlog信息 。相关状态变量如下：

mysql>**SHOW STATUS LIKE'Binlog\_cache%';**

\+-----------------------+-------+

|Variable\_name |Value|

\+-----------------------+-------+

|Binlog\_cache\_disk\_use|0 |

|Binlog\_cache\_use |0 |

\+-----------------------+-------+

如果Binlog\_cache\_disk\_use值不为0，则说明BinlogCache大小可能不够，建议增加binlog\_cache\_size系统参数大小。

●Innodb\_log\_waits量：Innodb\_log\_waits状态变量直接反应出InnodbLogBuffer空间不足造成等待的次数。

mysql>**SHOW STATUS LIKE'Innodb\_log\_waits';**

\+------------------+-------+