《高性能MySQL》（二）基准测试和服务器性能剖析

发表于 更新于 分类于
本文字数： 6.1k 阅读时长 ≈ 6 分钟
《高性能MySQL》读书笔记，内容：基准测试和服务器性能剖析，MySQL查询剖析等。

《高性能MySQL》（二）基准测试和服务器性能剖析

一. 概述

1.1 什么是基准测试？

基准测试是针对系统设计的一种压力测试，通常是为了掌握系统的行为或重现某个系统状态、做新硬件的可靠性测试等。

1.2 为什么需要基准测试？

基准测试是唯一方便有效的、可以学习系统在给定的工作负载下会发生什么的方法。观察系统在不同压力下的行为、评估系统的容量、观察系统如何处理不同的数据。

二. 基准测试的策略

两种主要策略：

  • 集成式：针对整个系统的整体测试；
  • 单组件式：单独测试MySQL。

一般建议做集成式测试，少数如需要比较不同的schema或查询的性能、针对某个具体问题的测试、避免过长的基准测试来检测某些调整的效果，可以考虑只测试MySQL。如果使用的是真实数据，那么只测试MySQL也是有效的。

2.1 测试指标

  • 吞吐量：单位时间内处理的事务数，单位为每秒事务数TPS。
  • 响应时间或者延迟：任务所需的整体时间，通常使用百分比响应时间，如95%的响应时间都是5毫秒。
  • 并发性：同时工作的并发操作 / 线程数 / 连接数，当并发性增加时，要关注吞吐量是否下降，响应时间是否变长。可以通过 sysbench 指定多个线程，记录MySQL数据库的 Threads_running 状态值。
  • 可扩展性：给系统增加一倍的资源，理想情况下可以获得两倍的结果。

三. 基准测试工具

集成式测试工具：

  • ab：Apache HTPP服务器基准测试工具，测试HTTP服务器每秒最多可以处理多少请求，只能针对单个URL进行尽可能快的压力测试。
  • http_load：可以通过输入文件提供多个URL随机选择进行测试。
  • JMeter：Java开发的测试工具，除了Web也可以测试FTP或JDBC等。

单组件式测试工具：

  • mysqlslap：MySQL 5.1发行包提供，模拟服务器的负载，输出计时信息。
  • MySQL Benchmark Suite（sql-bench）：MySQL发行包提供的基准测试套件，主要用于测试服务器执行查询的速度。可以用于比较不同存储引擎或不同配置的性能测试，也可以比较两个服务器的总体性能。单线程且串行执行，只能比较单CPU服务器的性能差别。
  • Super Smack：提供压力测试和负载生成，可以模拟多用户访问，加载测试数据到数据库，支持使用随机数据填充测试表。
  • Database Test Suite：OSDL开源实验室开发。
  • sysbench：多线程系统压测工具，支持Lua脚本语言，全能测试工具，支持MySQL、操作系统和硬件测试。

四. 性能剖析概述

4.1 常见场景

常见性能相关的服务请求：

  • 如何确认服务器是否达到了性能最佳状态？
  • 分析某条语句为什么执行的不够快？
  • 诊断“停顿”，“堆积”或“卡死”的某些间歇性疑难故障？

4.2 性能剖析的标准

定义一个性能的标准：完成某件任务的所需时间度量，也就是响应时间。对于数据库来说就是查询的响应时间，不管是DDL还是DML都用查询来泛指。优化就是在一定的工作负载下尽可能地降低响应时间。

无法测试就无法有效地优化，首先应该测量查询时间花在什么地方，要准确测量需要注意两点：

  • 不要在错误的时间启动和停止测量；
  • 测量的是目标活动本身，而不是聚合后的信息。

如测量慢查询时去排查整个服务器来判断问题出处，测量应该从慢查询开始到结束的时间，而不是查询之前或之后的时间。

查询时间包括：

  • 执行时间：只需测量定位不同的子任务花费的时间，优化去掉一些子任务、降低一些子任务执行频率、提高子任务执行效率。
  • 等待时间：可能因为其他系统间接影响，任务间也可能因为竞争磁盘或CPU资源而相互影响，

通过性能剖析可以分析定位是哪些子任务是优化的目标。

五. 如何进行性能剖析

5.1 性能剖析步骤

性能剖析包括两步：

  • 测量任务所花费的时间；
  • 对结果进行统计和排序，把重要的任务排在前面。

5.2 性能剖析的输出信息

性能剖析工具通常都是计时器，记录任务的开始和结束时间。

性能剖析需要系统可测量化，系统需要有一些测量点可以捕获并收集数据，MySQL直到版本5.5才首次提供Performance Schema 包含一些基于时间的可测量点，在此之前只能收集到记录活动发生次数的 show status 计数器。而 Percona Server 可以提供更多更详细的查询级别测量点。

性能剖析输出的排名、总计、平均值之外缺失的信息：

  • 值得优化的查询：性能剖析不会自动给出需要优化的查询，总响应时间占比小的查询不值得优化，优化成本大于收益的查询不值得优化。
  • 异常情况：有些查询虽然执行频率不高，但每次执行都很慢。
  • 未知的未知：丢失时间-任务的总时间和测量得到的时间之差，如CPU时间为10秒，剖析得到任务时间为9.7秒，存在300毫秒的丢失时间。可能会忽略某些重要信息。
  • 被隐藏的细节：只分析平均值会忽略到一些情况，如医院只看所有病人的平均体温，可以输出更多响应时间的信息，如直方图、百分比、标准差、偏差指数等。

六. 剖析MySQL查询

6.1 慢查询日志

（1）相关参数

  1. slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
  2. log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
  3. slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
  4. long_query_time ：慢查询时间设置，当查询时间多于设定的时间值时，记录日志。

（2）慢查询日志配置

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启，如下所示：

  1. 查询慢日志是否开启

    1
    2
    --OFF 为关闭 ON为开启
    show variables like '%slow_query_log%';

  2. 开启慢查询日志

    1
    set global slow_query_log=1;

    注意：使用set global slowquerylog=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

  3. 永久开启慢查询日志：

    修改my.cnf文件，增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器

    1
    2
    slow_query_log =1
    slow_query_log_file=/usr/local/mysql/data/localhost-slow.log

（3）慢查询时间设置

这个是由参数 long_query_time 控制，默认情况下 long_query_time 的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。

  1. 查询慢日志时间设置

    1
    show variables like 'long_query_time';

  2. 设置慢查询日志时间。

1
set global long_query_time=5;

6.2 剖析服务器负载

最简单的方式就是使用慢查询日志，是MySQL开销最低、精度最高的测量查询工具，使用时只需注意磁盘空间开销，MySQL 5.1 版本后可以通过 long_query_time 为 0 来捕获所有的查询，查询的响应时间可以做到微秒级。

Percona Server 的慢查询日志相比MySQL会记录更多信息，如执行计划、锁、I/O活动等。

可以在业务高峰期开启一个小时的记录查询，首先生成一个剖析报告，需要一个适合的剖析工具，如 pt-query-digest 分析 tcpdump。

6.3 剖析单条查询

定位到需要优化的查询语句后，

  • SHOW PROFILE：

    • 作用：会测量所有服务器上执行的语句，记录耗费时间和执行状态变更相关的数据。

    • 开启：SET profiling = 1;

    • 使用：SHOW PROFILES 和 SHOW PROFILE FOR QUERY 1; 结果按照执行顺序排序，而不是花费时间。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      SET @query_id = 252;
      SELECT STATE, SUM(DURATION) AS Total_R,
      			 ROUND(
      					100 * SUM(DURATION) /
      							(SELECT SUM(DURATION)
      							 FROM information_schema.PROFILING
      							 WHERE QUERY_ID = @query_id), 2
      			 ) AS Pct_R, 
      			 COUNT(*) AS Calls,
      			 SUM(DURATION) / COUNT(*) AS "R/Call"
      FROM information_schema.PROFILING
      WHERE QUERY_ID = @query_id
      GROUP BY STATE
      ORDER BY Total_R DESC

    • 排序后可以明显的找到查询的耗时步骤，找到其中值得优化的部分去优化。

  • SHOW STATUS：

    • 作用：返回一些计数器，有服务器级别和单个连接会话级别的计数器，如 Queries 在会话开始前为0，每提交一条查询增加1。

    • 使用：SHOW GLOBAL STATUS 查看服务器级别的次数统计。SHOW STATUS 包含两种级别的数据。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      --重置计数器
      FLUSH STATUS;
      --执行SQL
      SELECT * FROM tv_tallymaster_bak where book_id in ('100199100','100200000');
      --查看指定计数器结果
      SHOW STATUS WHERE Variable_name LIKE 'Handler%' OR Variable_name LIKE 'Created%'
      -- Created_tmp_tables 表示使用临时表数目
      -- Created_tmp_disk_tables 磁盘临时表
      -- Handler_read_rnd_next 没有用到索引的读操作

    • 只能显示某种活动的频繁程度（如读索引），只有一个参数指全局级别的操作时间（Innodb_row_lock_time）。

    • 比较有用的计数器：

      • 句柄计数器（handler counter）
      • 临时文件
      • 表计数器

    • SHOW STATUS 本身也会创建一个临时表，通过句柄操作访问此表，会影响计数器数字。

    • EXPLAIN 有类似的结果，但基于统计；SHOW STATUS 的结果基于测量。

  • 检查慢查询日志的条目：

    • 使用 Percona Server 得到更详细有用的信息。
    • pt-query-digest 标题一般会打印出字节偏移值（3214），使用tail指令直接跳转到对应部分：tail -c +3214 /path/to/query.log | head -n100

  • 使用 Performance Schema

6.4 诊断间歇性问题

例如一个简单查询，正常情况下使用很快，但有几次不合理的执行了很久。手工执行一遍，非常快；然后用EXPLAIN查询执行计划，也正确的使用了索引。修改WHERE条件的值，排除缓存命中的可能性。

慢查询日志没有执行计划或详细的时间信息，可能是系统有其他东西消耗了资源，比如正在备份，或是某种类型的锁或争用阻塞了查询进度。

尽量避免试错的诊断方式，以下是一些常见的案例，某些是数据库问题，但也有的不是：

  • 应用通过 curl 从一个运行很慢的外部服务获取汇率报价的数据。
  • memcached 缓存中一些重要条目过期，导致大量请求落到MySQL重新生成缓存条目。
  • DNS查询偶尔有超时现象。
  • 可能由于互斥锁争用，或者内部删除查询缓存的算法效率太低的缘故，MySQL的查询缓存有时候会导致服务有短暂的停顿。
  • 当并发度超过某个阈值时，InnoDB的扩展性限制导致查询计划的优化需要很长时间。

只能在问题发生的地方通过观察资源的使用情况，并尽可能的测量出数据，才能避免在没问题的地方耗费精力。

  • 确认单条查询问题还是服务器问题：

    • 服务器整体没问题，只是某条查询慢，可以将注意力放在这条特定的查询上面。

    • 判断方法：

      • 使用 SHOW GLOBAL STATUS ：

        1
        2
        # 每秒捕获一次SHOW GLOBAL STATUS的数据，输出给awk计算并输出每秒的查询数、Threads_connected和Threads_running表示当前正在执行查询的线程数。
        $ mysqladmin ext -i1 | awk '/Queries/{q=$4-qp;qp=$4} /Threads_connected/{tc=$4} /Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'

        每秒查询数一般会下跌，其他两个至少会出现一个尖刺。可能是服务器内部碰到了某种瓶颈，导致新查询在开始执行前因为要获取老查询正在等待的锁而造成堆积（一般也会导致后端服务器出现排队）；也可能是服务区突然收到大量查询请求，比如前端memcached突然失效导致的查询风暴。

      • 使用 SHOW PROCESSLIST ：

        1
        2
        3
        4
        # 通过不停捕获SHOW PROCESSLIST的输出，观察是否有大量现场处于不正常状态或有不正常的特征，如statistics状态一般服务器在查询优化阶段确定表关联顺序—非常快，所以查询不应长时间处与此状态
        # \G垂直输出结果，方便使用 sort|uniq|sort 计算某一列值出现次数，一般State列会很有用
        $ mysql -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -rn
        # 大量线程处于freeing items说明出现了大量有问题查询
        • 也可以直接查询表：SELECT * FROM information_schema.PROCESSLIST
        • 使用 innotop 工具。

      • 使用查询日志：

        • 开启慢查询日志，并在全局级别设置 long_query_time=0 ，确认所有连接都采用了新设置，可能需要重置所有连接，或是使用 Percona Server。
        • 也可以通过 tcpdump 和 pt-query-digest 工具替代。
        • 找到吞吐量突然下降时间段的日志，一般是吞吐量下降后第一个完成的查询。

  • 捕获诊断数据：

    • 辅助工具：

      • 一个可靠且实时的触发器；
      • 一个收集诊断数据的工具。

    • 找到能和正常时的阈值进行比较的指标，如正在运行的线程数量、处于“freeing items”状态的线程数量等：

      1
      $ mysql -e 'SHOW PROCESSLIST/G' | grep -c "State: freeing items"

    • Percona Toolkit 的 pt-stalk 可以在触发条件时收集数据。

    • 需要收集的数据：系统状态、CPU利用率、磁盘使用率和可用空间、ps的输出采样、内存使用率、MySQL的几个SHOW STATUS / SHOW PROCESSLIST / SHOW INNODB STATUS

参考：

🔗 《高性能MySQL》