高性能 MySQL 笔记（一）

高性能 MySQL 笔记（一）

1. 基准测试

OLTP (Online Transactional Processing，联机事务处理) 是专注于面向事务的任务的一类数据处理，通常涉及在数据库中插入，更新或删除少量数据，主要是处理大量用户下的大量事务。

1.1 为什么基准测试很重要

因为基准测试是唯一方便有效的、可以学习系统在给定的工作负载下会发生什么的方法。

基准测试可以观察系统在不同压力下的行为，评估系统的容量，掌握哪些是重要的变化，或者观察系统如何处理不同的数据。

1.2 策略

• 集成式：整个应用
• 单组件式：仅mysql

1.3 指标

• 吞吐量

  单位时间内的事务处理数 （TPS,TPM）

• 响应时间或延迟

  用于测试任务所需的整体时间，使用图表有助于理解测试结果

• 并发性

  在任意时间有多少同时发生的并发请求

• 可扩展性

  给系统增加一倍的工作，在理想情况下就能获得两倍的结果（即吞吐量增加一倍），

  或者说，给系统增加一倍的资源（比如两倍的cpu数），就可以获得两倍的吞吐量。

1.4 获取系统性能和状态

show global variables;

show gloabl status;

show engine innodb status;

show full processlist;

1.5 基准测试工具

1.5.1 集成式测试工具

• ab
• http_load
• JMeter

1.5.2 单组件式测试工具

• mysqlslap

• MySQL Benchmark Suit(sql-bench)

• Super Smack

• Database Test Suite

• Percona‘s TPCC-MySQL Tool

• sysbeach： 多线程传统压测工具，可以用来测试文件I/O、操作系统调度器、内存分配和传输速度、POSIX线程，以及数据库服务器等。

2. 服务器性能剖析

2.1 性能优化简介

第一原则，我们将性能定义为完成某件任务所需要的时间度量，换句话说，性能即响应时间

先要搞清楚时间花在哪里

第二原则，无法测量就无法有效优化

完成一项任务所需要的时间可以分成两部分：

• 执行时间：测量定位不同的子任务花费的时间，然后优化掉一些子任务，降低子任务的执行频率或者提升子任务的效率
• 等待时间：相对复杂，可能是由其他系统间接导致，任务之间也可能由于争用磁盘或者CPU资源而相互影响

2.1.1 通过性能剖析进行优化

性能剖析 profiling ：是测量和分析时间花费在哪里的主要方法

两个步骤：

1. 测量任务所花费时间
2. 对结果进行统计和排序，将重要的任务排到前面

2.1.2 理解性能优化

• 值得优化的查询
• 异常情况
• 未知的为止
• 被掩藏的细节

pt-query-digest 就在剖析的结果里包含了很多这类细节信息，并且输出在剖析报告中。

2.2 剖析MySQL查询

2.2.1 剖析服务器负载

捕获mysql的查询到日志文件中

分析查询日志

强烈建议大家从现在开始起就利用慢查询日志捕获服务器上的所有查询，并且进行分析。

通过慢查询日志记录查询或者使用pt-query-digest分析tcpdump 的结果，是可以找到的最好的两种方式。

2.2.2 剖析单条查询

使用 show profile

mysql> set profiling = 1;
mysql> show profiles;

使用 show status

使用慢查询日志

使用 Performance Schema

2.2.3 使用性能剖析

---

2.3 诊断间歇性问题

尽量不要使用试错的方式来解决问题，低效且沮丧

2.3.1 单条查询问题还是服务器问题

使用show global status

使show processlist

使用查询日志 ：需要开启慢查询日志并在全局级别设置long_query_time=0，并且要确认所有的连接都采用了新的设置。

理解发现的问题

2.3.2 捕获诊断数据

1. 一个可靠且实时的“触发器”，也就是能区分什么时候问题出现的方法。
2. 一个收集诊断数据的工具。

诊断触发器 pt-stalk

需要收集什么样的数据

解释结果数据

2.4 其他剖析工具

使用 user_statistics 表

show tables from information_schema like "%_statistics";

使用 strace

2.5 总结

• 我们认为定义性能最有效的方法是响应时间
• 如果无法测量就无法有效的优化
• 测量的最佳开始点是应用程序，而不是数据库
• 大多数系统无法完整测量，测量有时候也会由错误的结果。但也可以想办法绕过一些限制，并得到好的结果。
• 完整的测量会产生大量需要分析的数据，所以需要用到剖析器。这是最佳的工具，可以帮助将重要的问题冒泡到前面，这样就可以决定从哪里开始分析会比较好。
• 剖析报告也是一种汇总信息，掩盖和丢弃了太多细节。不能完全依赖它。
• 有两种消耗时间的操作：工作或者等待。大多数剖析器只能测量因为工作而消耗的时间，所以等待分析有时候是很有用的补充，尤其是当CPU利用率很低但工作却一直无法完成的时候
• 优化和提升是两回事，当继续提升的成本超过收益的时候，应当停止优化。
• 注意你的直觉，但应该只根据直觉来指导解决问题的思路，而不是用于确定是同的问题。决策应当尽量基于数据而不是感觉。