Mysql效能調優與測試的方法

我們的Mysql服務運作一段時間後，不知什麼原因就變慢了，要怎麼找出原因呢？

一、關鍵性指標

在資料庫效能評測中，有幾項指標很重要，用它來評估資料庫的能力，不是他們能扮演多麼關鍵的作用，而是他們能夠較為明確的代表資料庫在某些方面的能力。

1.IOPS

IOPS：Input/Output operation Per Second, 每秒處理的IO請求次數。
我們知道I/O就是磁碟的讀寫能力，例如每秒讀300M，寫200M，這個即資料的吞吐量（I/O能力的另一個關鍵指標），但IOPS指的可不是讀寫的資料吞吐量，IOPS 指的是每秒能夠處理的I/O 請求次數。

如果想I/O 系統響應夠快，那麼IOPS 越高越好，因為IOPS 和硬件有關，所以，要提高IOPS，就目前來看基本只能拼硬件，傳統方案是使用多區塊磁碟通過RAID 條帶後，使I/O 讀寫能力獲得提升，我們也可以使用固態硬碟SSD來提升IOPS，不過固態硬碟成本可能比較大。

2.QPS

QPS：Query Per Second，每秒請求（查詢）次數。
這個參數非常重要，可以直覺的反映系統的效能，這就像IOPS衡量磁碟每秒鐘能接收多少次請求。

我們可以在MySQL命令列模式下執行 status 指令，傳回的最後一行輸出資訊中就包含 QPS 指標。

3.TPS

TPS：Transaction Per Second，每秒交易數。
TPS參數MySQL原生沒有提供，如果需要我們自己算，可以利用計算的公式：

TPS = (Com_commit + Com_rollback) / Seconds

這個公式有兩個狀態變量，分別代表提交次數和回滾次數，Seconds 就是我們定義的時間間隔。

二、TPCC測試關鍵指標

TPCC-MySQL 由Percona基於TPCC規範開發的一套MySQL基準測試程序，我們使用這套工具來測試前面的三個重要指標。

1.TPCC工具安裝及使用

具體的安裝，可以看這兩篇博文mysql壓力測試工具tpcc-mysql安裝測試使用，mysql性能測試-tpcc，TPCC更能模擬線上業務。

三、資料庫參數配置最佳化

如果資料庫參數配置合理，則可以大幅的提高運作效率，也就是最大化利用系統資源。

1.連接相關參數

1.1 max_connections

max_connections：指定MySQL 服務端最大並發連線數，值得範圍從1~10 萬，預設值為151.
這個參數非常重要，因為它決定了同時最多能有多少個會話連接到MySQL 服務。設定此參數時，根據資料庫伺服器的配置和效能，一般將參數值設定在 500~2000 都沒太大的問題。

1.2 max_connect_errors

max_connect_errors：指定允許連線不成功的最大嘗試次數，值得範圍從1~2^64 之間，在5.6.6 版本預設值是100。

一定不要忽略這個參數，如果嘗試連接的錯誤數量超過該參數指定值，則伺服器就不再允許新的連接，沒錯，就是拒絕連接，儘管MySQL 仍在提供服務，但無法建立新的連線了。可以使用FLUSH HOSTS，使狀態清零或重新啟動資料庫服務，不過這個代價太高了，一般不會這麼幹，所以，這個參數的預設值太小，這裡建議將之設定為10 萬以上的量級。

1.3 interactive_timeout 和wait_timeout

這兩個參數都與連線工作階段的自動逾時斷開有關，前者用於指定關閉互動連線前等待的時間，後者用於指定關閉非交互連接前的等待時間，單位均為秒，預設值均為28800，即8 小時。

1.4 skip-name-resolve

skip-name-resolve：可以將其簡單的理解為停用DNS 解析，注意啊，這個是服務端的行為，連線時不檢查客戶端主機名，而只使用IP。如果制定了該參數，那麼在建立使用者及授予權限時，HOST 欄位必須是IP而不能是主機名稱。建議啟用此參數，對於加快網路連線有一定的幫助，等於是跳過了主機名稱的解析。

1.5 back_log

back_log：指定MySQL 連線請求佇列中存放的最大連線要求數，在5.6.6 版本之前，預設是50 個，最大值不超過65535。在 5.6.6 版本之後，預設值為 -1，表示由MySQL自動調節，所謂自行調節其實也有規則，即 50+（max_connections/5）。

該參數主要應對短時間內有大量的連接請求，MySQL 主線程無法及時為每個連接請求分配（或創建）連接的線程，怎麼辦呢，它也不能直接拒絕，於是就將一部分請求放到等待佇列中待處理，這個等待佇列的長度就是back_log 的參數值，若等待佇列也被放滿了，那麼後續的連線請求才會被拒絕。

2.文件相关参数

2.1 sync_binlog

sync_binlog：指定同步二进制日志文件的平率，默认为0.
如果要性能，则指定该参数为0，为了安全起见则指定该参数值为 1.

2.2 expire_logs_day

expire_logs_day：指定设置二进制日志文件的生命周期，超出则将自动被删除，参数值以天为单位，值得范围从0~99，默认值是0，建议将该参数设置为 7~14 之间，保存一到两周就足够了。

2.2 max_binlog_size

max_binlog_size： 指定二进制日志的大小，值得范围从 4KB~1GB，默认为 1GB。

3.缓存控制参数

3.1 thread_cache_size

thread_cache_size:指定MySQL为快速重用而缓存的线程数量。值得范围从 0~16384，默认值为0.
一般当客户端中断连接后，为了后续再有连接创建时，能够快速创建成功，MySQL 会将客户端中断的连接放入缓存区，而不是马上中断释放资源。这样当有新的客户端请求连接时，就可以快速创建成功。因此，本参数最好保持一定的数量，建议设置在 300~500 之间均可.另外，线程缓存的命中率也是一项比较重要的监控指标，计算规则为（1-Threads_created/Connections）* 100%,我们可以通过该指标来优化和调整thread_cache_size参数。

3.2 query_cache_type

sql_cache意思是说，将查询结果放入查询缓存中。
sql_no_cache意思是查询的时候不缓存查询结果。
sql_buffer_result意思是说，在查询语句中，将查询结果缓存到临时表中。

这三者正好配套使用。sql_buffer_result将尽快释放表锁，这样其他sql就能够尽快执行。

使用 FLUSH QUERY CACHE 命令，你可以整理查询缓存，以更好的利用它的内存。这个命令不会从缓存中移除任何查询。FLUSH TABLES 会转储清除查询缓存。
RESET QUERY CACHE 使命从查询缓存中移除所有的查询结果。

那么mysql到底是怎么决定到底要不要把查询结果放到查询缓存中呢？

是根据query_cache_type这个变量来决定的。

这个变量有三个取值：0,1,2，分别代表了off、on、demand。
mysql默认为开启 on

意思是说，如果是0，那么query cache 是关闭的。
如果是1，那么查询总是先到查询缓存中查找，即使使用了sql_no_cache仍然查询缓存，因为sql_no_cache只是不缓存查询结果，而不是不使用查询结果。

select count(*) from innodb;
1 row in set (1.91 sec)

select sql_no_cache count(*) from innodb;
1 row in set (0.25 sec)

如果是2，DEMAND。
在my.ini中增加一行
query_cache_type=2
重启mysql服务

select count(*) from innodb;
1 row in set (1.56 sec)

select count(*) from innodb;
1 row in set (0.28 sec)

没有使用sql_cache,好像仍然使用了查询缓存

select sql_cache count(*) from innodb;
1 row in set (0.28 sec)

使用sql_cache查询时间也一样，因为sql_cache只是将查询结果放入缓存，没有使用sql_cache查询也会先到查询缓存中查找数据

结论：只要query_cache_type没有关闭，sql查询总是会使用查询缓存，如果缓存没有命中则开始查询的执行计划到表中查询数据。

query cache优缺点
优点很明显，对于一些频繁select query，mysql直接从cache中返回相应的结果集，而不用再从表table中取出，减少了IO开销。
即使query cache的收益很明显，但是也不能忽略它所带来的一些缺点：

1. query语句的hash计算和hash查找带来的资源消耗。mysql会对每条接收到的select类型的query进行hash计算然后查找该query的cache是否存在，虽然hash计算和查找的效率已经足够高了，一条query所带来的消耗可以忽略，但一旦涉及到高并发，有成千上万条query时，hash计算和查找所带来的开销就的重视了；

2. query cache的失效问题。如果表变更比较频繁，则会造成query cache的失效率非常高。表变更不仅仅指表中的数据发生变化，还包括结构或者索引的任何变化；

3. 对于不同sql但同一结果集的query都会被缓存，这样便会造成内存资源的过渡消耗。sql的字符大小写、空格或者注释的不同，缓存都是认为是不同的sql（因为他们的hash值会不同）；

4. 相关参数设置不合理会造成大量内存碎片，相关的参数设置会稍后介绍。

合理利用query cache
query cache有利有弊，合理的使用query cache可以使其发挥优势，并且有效的避开其劣势。

1. 并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更，那么就应该避免在变化频繁的table上使用query cache。mysql中针对query cache有两个专用的sql hint：SQL_NO_CACHE和SQL_CACHE，分别表示强制不使用和强制使用query cache，通过强制不使用query cache，可以让mysql在频繁变化的表上不使用query cache，这样减少了内存开销，也减少了hash计算和查找的开销；

更多有关query cache详情文章，请看这里的原文：mysql query cache优化

3.3 query_cache_size

query_cache_size：指定用于缓存查询结果集的内存区大小，该参数值应为 1024 的整数倍。

这个参数不能太大，也不能太小，查询缓存至少会需要 40KB 的空间分配给其自身结构，太小时缓存结果集就没有意义，热点数据保存不了多少，而且总是很快就被刷新出去；但也不能太大，否则可能过多占用内存资源，影响整机性能，再说太大也没有意义，因为即便数据不被刷新，但只要源数据发生变更，缓存中的数据也就自动失效了，这种情况下分配多大都没有意义。个人建议设置不要超过 256MB。

3.4 query_cache_limit

query_cache_limit：用来控制查询缓存，能够缓存的单条 SQL 语句生成的最大结果集，默认是 1MB，超出的就不要进入查询缓存。这个大小对于很多场景都够了，缩小可以考虑，加大就不用了。

3.5 sort_buffer_size

sort_buffer_size：指定单个会话能够使用的排序区的大小，默认值为 256KB，建议设置为 1~4MB 之间。

3.6 read_buffer_size

read_buffer_size:指定随机读取时的数据缓存区大小，默认是 256KB，最大能够支持4GB，适当加大本参数，对于提升全表扫描的效率会有帮助。

4.InnoDB专用参数

4.1 innodb_buffer_pool_size

innodb_buffer_pool_size：指定InnoDB引擎专用的缓存区大小，用来缓存表对象的数据及索引信息，默认值为 128MB，最大能够支持（2^64 -1）B.

如果你有很多事务的更新，插入或删除很操作，通过修改innodb_buffer_pool 大小这个参数会大量的节省了磁盘I / O。

innodb_buffer_pool_size 是个全局参数，其所分配的缓存区将供所有被访问到的InnoDb表对象使用，若MySQL数据库中的表对象以 InnoDb 为主，那么本参数的值就越大越好，官方文档中建议，可以将该参数设置为服务器物理内存的70%~80%。

4.2 innodb_buffer_instances

innodb_buffer_instances：指定 InnoDB 缓存池分为多少个区域来使用，值得范围从 1~64，默认值为-1，表示由 InnoDB 自行调整。

只有当innodb_buffer_pool_size参数值大于1GB时，本参数才有效，那么本参数怎么设置呢？个人感觉可以参照 InnoDB 缓存池的大小，以 GB 为单位，每GB指定一个instances。例如当innodb_buffer_pool_size设置为16GB时，则指定 innodb_buffer_instances 设置为 16 即可。

5.参数优化案例

测试服务器有 16GB的物理内存，假定其峰值最大的连接数为 500 个，表对象使用InnoDB 存储引擎，我们的内存参数如何配置呢？

具体配置如下：
（1）、首先，为操作系统预留 20% 的内存，约为 3GB。
（2）、与线程相关的几个关键参数设置如下：

  sort_buffer_size=2m
  read_buffer_size=2m
  read_rnd_buffer_size=2m
  join_buffer_size=2m

预计连接数达到峰值时，线程预计最大将有可能占用 500 *（2+2+2+2）= 4GB内存（理论最大值）。

（3）、剩下的空间 16-3-4=9GB，就可以全部都分配给InnoDB 的缓存池，设定相关的参数如下：

innodb_buffer_pool_size=9g
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=16m
innodb_flush_log_at_trx_commit=2

四、MySQL系统状态

想要了解MySQL服务当前在做什么，有个非常重要并且极为常用的命令：

SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST 命令将每一个连接的线程，作为一条独立的记录输出。

还有相似的语句，
SHOW PROFILES 和 SHOW PROFILE可以获取会话执行语句过程中，资源的使用情况。

---

以上是Mysql效能調優與測試的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！