|
😃 😒 😦 😡 😒 😗 😮
一.high performance mysql
😃 😒 😦 😡 😒 😗 😮
二.调试
1.1 基准测试工具
两种测试类型： 集成式测试（full-stack），单组件式测试（single-component）
full-stack
针对整个系统的整体性测试
single-component
单独测试MySQL和 基于MySQL的系统的性能。
-
mysqlslap
http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html
可模拟服务器负载，并输出计时信息。
包含在mysql5.1的发行包中，在4.1及更新的版本中都可以使用。
测试时可以执行并发连接数，并指定SQL语句（可以CLI命令行执行，也可将SQL语句写入参数文件）；若没有指定sql语句，mysqlslap会自动生成查询schema的SELECT语句。 -
MySQL Benchmark Suite（sql-bench）
https://dev.mysql.com/doc/refman/8.0/en/optimize-benchmarking.html
mysql的发行包中自带的基准测试套件，可以用于在不同的数据库服务器上进行比较测试。
单线程，主要用于测试服务器执行查询的速度，结果会显示哪种类型的操作在服务器上执行的更快。
优势为包含大量的预定义的测试，易用性强，可轻松的用于比较不同存储引擎或是不同配置的性能测试；还可用于高层次测试，比价两个服务器的总体性能；还可只执行预定义测试的自己（例如只测试UPDATE的性能）。这些测试大部分为CPU密集型（cpu-bound），但也有些短时间的测试需要大量的磁盘IO操作（IO密集型，IO-bound）。
劣势：单用户模式；测试的数据集很小且用户无法使用指定的数据；同一个测试多次运行的结果差异性可能较大；因为是单线程加串行执行，故无法测试多CPU能力，只能用于比较单CPU服务器性能的差别；使用该测试套件的数据库服务器还需要perl和BDB的支持。 -
Super Smack
http://vegan.net/
http://vegan.net/tony/supersmack/
用于mysql和PostgreSQL的基准测试工具，可以提供压力测试和负载生成。
复杂强大，可以模拟多用户访问；可以加载测试数据到数据库；支持使用随机数据填充测试表。
测试定义在“smack”文件中，smack文件使用简单 的语法定义测试的客户端、表、查询等测试要素。 -
Database Test Suite
http://sourceforge.net/pojects/osdldbt/
http://sourceforge.net/
由开源软件开发实验室（OSDL，Open Source Development Labs）设计，发布在SourceForge网站，是一种类似于某些公业标准的测试工具集，例如有失误处理性能委员会（TPC，Transaction Processing Performance Council）制定的各种标准。其中的dbt2就是一款基于TPC-C OLTP测试工具（未认证）。 -
Percona’s TPCC-MySQL Tool
https://launchpad.net/perconatools
类似TPC-C的基准测试工具集，其中有部分是专门为MySQL测试开发的。
能够模拟真实压力
简单的测试一般使用sysbench；大压力测试一般使用此工具。 -
sysbench
https://launchpad.net/sysbench
https://github.com/akopytov/sysbench
多线程系统测压工具。无法模拟真实的业务压力
全能型测试工具，支持MySQL、操作系统和硬件的硬件测试。
可根据影响数据库服务器的各种因素来评估系统的性能。例如，可以用来测试文件I/O、操作系统调度器、内存分配和传输速度、POSIX线程、数据库服务器等。
sysbench支持Lua脚本语言（http://www.lua.org），Lua对于各种测试场景的设置可以十分灵活。
oltp基准测试可以比较不同系统的性能；I/O和磁盘基准测试可以在系统出现问题是有效诊断、隔离异常的组件。
建立一个脚本库，用于配置基准测试，收集输出结果、系统性能和状态信息，以及分析结果。
使用一种熟练的绘图工具，如gnuplot或是R
gnuplot
http://www.gnuplot.info/
gnuplot is a command-line and GUI program that can generate two- and three-dimensional plots of functions, data, and data fits. The program runs on all major computers and operating systems (GNU/Linux, Unix, Microsoft Windows, macOS, FreeDOS, and many others).[3] It is a program with a fairly long history, dating back to 1986. Despite its name, this software is not part of the GNU Project. is a portable command-line driven graphing utility for Linux, OS/2, MS Windows, OSX, VMS, and many other platforms.
1.sysbench的CPU基准测试 最典型的子系统测试就是cpu基准测试。 下例使用64位整数，测试计算素数知道某个最大值所需时间；比较两台不同的liunx服务器上的测试结果： `cat /proc/cpuinfo` `sysbench --test=cpu --cpu-max-prime=2000 run`
2.sysbench的文件I/O基准测试 文件I/O（fileio）基准测试可以测试系统在不同I/O负载下的性能，用于比较硬盘驱动、不同的RAID卡、不同的RAID模式，可以根据测试结果来调整I/O子系统。 首先是准备阶段（prepare），生成测试用到的数据文件，生成的数据文件至少要比内存大，如果文件中的数据能够完全放入内存中，则操作系统缓存大部分的数据，导致测试结果无法体现I/O密集型（I/O-bound）的工作负载。 `sysbench --test=fileio --file-total-size=150G prepare` 上述命令会在当前目录下创建测试文件，后续运行（run）阶段将通过读写这些文件进行测试，run阶段针对不同的I/O类型有如下测试选项：seqwr（顺序写入）、sedrewr（顺序重写）、seqrd（顺序读入）、rndrd（随机读入）、rndwr（随机写入）、rndrw（混合随机读写）。 如下命令运行文件I/O混合随机读/写基准测试： `sysbench --test=fileio --fiel-total-size=150G --file-test-mode=rndrw --init-rng=on --max-time=300 --max-requests=0 run` 测试完成后，运行清楚（cleanup）操作删除prepare阶段生成的测试文件： `sysbench --test=fileio --file-total-size=150G cleanup`
3.sysbench的在线事务处理OLTP测试 OLTP基准测试模拟一个简单的事务处理系统的工作负载。 `sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root prepare` `sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run`
4.sysbench的其它特性 sysbench还有一些与数据库性能没有直接关系的其他的基准测试。 内存（memory）：测试内存的连续读写性能。 线程（thread）：测试线程调度器的性能。在高负载情况下需测试线程调度器。 互斥锁（mutex）：测试互斥锁的性能，方式是模拟所有线程在同一时刻并发运行，并且都短暂的请求互斥锁。 顺序写（seqwr）：测试顺序写的性能，对于测试系统的实际性能瓶颈十分重要，可以用于测试RAID控制器的高速缓存的性能状况。
| MySQL基础测试套件（MySQL Benchmark Suite）由一组基于Perl开发的基准测试工具组成，在MySQL安装目录下的sql-banch子目录下，在linux系统上，默认的安装路径是 /usr/share/mysql/sql-bench |
| I/O性能测试工具：iozone（https://www.iozone.org/）、bonnie++（https://www.coker.com.au/bonnie++/） |
iozone
https://www.iozone.org/
bonnie++
说明： https://www.jianshu.com/p/2e048e23e9f7
安装： https://www.coker.com.au/bonnie++/
1.2 服务器性能剖析
经常遇到的三个性能相关的服务请求：
- 如何确认服务器是否达到了性能最佳的状态
- 找出某条语句为什么执行不够快
- 诊断被用户描述成“停顿”、“堆积”或是“卡死的某些疑难故障”
解决上述问题的方法就是专注于测量 服务器的时间花费在哪里，
使用的技术就是 性能剖析（profiling）
1.2.1 性能优化
| 性能优化第一原则：性能即响应时间 | | 性能优化的第二原则：无法测量就无法有效优化 | | 精确测量：可花费90%时间测量响应时间花费在哪里 | | 合适的测量范围：只测量需要优化的活动 |
性能优化第一原则：性能即响应时间
性能度量=任务+完成该任务的时间
每个人在不同场景下对性能有不同的定义，如：“每秒查询次数”、“CPU利用率”、“可扩展性”等。
但将性能定义为完成某件任务所需要的时间度量，即：性能即响应时间，该原则将作为性能度量的第一原则，通过任务和时间而不是资源来度量性能。
数据库服务器的目的是执行SQL语句，所以他关注的任务时查询或是语句，如select、update、delete等；数据库服务器的性能用查询的响应时间来度量，单位是每个查询所花费的时间。
常见的思维误区：
1.【认为性能优化是降低CPU的使用率，可以减少对资源的使用】
此为一个常见陷阱，资源是用来消耗并用来工作的，所以有时候消耗更多的资源能够加快查询速度。
很多时候将使用老版本InnoDB引擎的MySQL升级到新版本后，CPU会上升的很厉害，这并不代表性能出现了问题，反而说明新版本的InnoDB对资源的利用率上升了。
查询的响应时间则更能体现升级后的性能是不是更好。版本升级有时的确会带来一些bug，比如不能利用某些索引而导致CPU的利用率上升，但是切记CPU的利用率只是一种现象，而不是很好的可度量目标。
2.【仅仅将性能优化看成是提升每秒查询数】
提升每秒查询量，只是吞吐量优化，吞吐量的优化可以看做性能优化的副产品。
所以如果目标是降低响应时间，那么就需要弄清服务器执行查询所花费的时间消耗在了哪里，然后去减少或消除那些对获得查询结果来说不必要的工作。所以第一步应该测量服务器执行查询的时间花费在了哪里。故性能优化的第二原则为：无法测量就无法有效优化。
如果测量了系统中完整切正确的数据，性能问题即可暴露出来；如果通过测量没有找到正确答案，要么是测量方式错了，要么是测量的不够完整。故需要找到合适的测量范围，合适的测量范围即为只测量需要优化的活动。
以下两种情况经常导致不合适的测量范围：
- 在错误的时间启动和停止测量
- 测量的是聚合后的信息，而不是目标活动本身
例如 一个常见错误：先看慢查询，然后又去排查整个服务器的情况来判断问题在哪里。 如果确认有慢查询，那么就应该测量慢查询，而不是测量整个服务器。测量的应该是从慢查询从开始到结束的时间，而不是查询之前或之后的时间。
完成一项任务所需的时间可以分成两部分：执行时间+等待时间
-
执行时间优化：
最好的办法是通过测量定位不同的子任务花费的时间，然后优化去掉一些子任务、降低子任务的执行频率或是提升子任务的执行效率。
定位和优化子任务：一些运行不频繁或是很短的子任务对整体响应时间的影响很小，通常可以忽略不计，那么如何确认那些子任务是优化的目标？—使用性能剖析。 -
等待时间优化：
相对较为复杂，因为等待时间有可能是由其他系统间接影响导致，任务之间也可能由于争用磁盘I/O、CPU资源而相互影响。
根据时间是花在执行上还是等待上的不同，诊断所需的工具和技术也不同。
1.2.2 性能剖析
MySQL的性能剖析（profile）将最重要的任务放在最前面，单还有很多没有显示出来的信息也很重要：
-
值得优化的查询（worthwhile query）
性能剖析不会自动给出那些查询值得花时间优化。
强调优化两点原则：
第一，一些只占总响应时间比重很小的查询是不值得优化的。根据阿姆达尔定律（Amdahl’s Law），对一个占总响应时间不超过5%的查询进行优化，无论如何努力，收益也不会超过5%。
第二，如果优化成本大于收益，就应该停止优化。 -
异常情况
某些任务即使没有出现在性能剖析输出的前面也需要优化。比如某些任务的执行次数很少，但是每次执行的都十分缓慢，严重影响用户体验。因其执行频率低，所以总得响应时间占比并不突出。 -
丢失时间
一款好的性能剖析工具会显示可能的“丢失的时间”，丢失的时间指的是任务的总时间和实际的测量时间之间的差。
例如，如果处理器的CPU时间是10s，而剖析到的任务总时间是9.7是，那么就有300ms的丢失时间，这可能是有些任务每有测量到，也可能是由于测量的误差和精度之间的问题。
如果工具发现了这类问题则需引起重视，即使性能剖析没有发现丢失时间也需要注意考虑这类问题存在的可能性。 -
响应时间分布
性能剖析无法显示所有响应时间的分布，不能只相信平均值，平均值无法表达全部情况。
好的性能剖析工具可以自动获得这些信息。例如Percona Server（https://www.percona.com/software/mysql-database/percona-server）的percona-toolkit包下的pt-query-digest工具就在结果中包含了很多这类信息，并且将其输出在剖析报告中。
对任何需要消耗时间的任务都可以做性能剖析，也包括应用程序，而且剖析应用程序一般比剖析数据库简单，且回报更高。
对系统进行性能剖析建议自上而下进行，这样可以追踪自用户发起请求到服务器响应的整个流程。
性能瓶颈的可能影响因素：
- 外部资源，比如调用了外部的web服务器或者搜索引擎
- 应用需要处理大量的数据，比如分析一个超大的XML文件
- 在循环中执行昂贵的操作，比如滥用正则表达式
- 使用量低效算法，比如使用暴力搜索算法来查找列表中的项
但是确定数据库相关的问题只需要一款应用程序的剖析工具即可。
Note：性能剖析本身会导致服务器变慢吗？
性能剖析和定期检测都会带来额外开销，问题在于这部分的开销有多少，并且由此获得的收益是否能够抵消这些开销。
一个高性能的应用程序应该尽可能的测量一切可以测量的地方，并且接受这些测量带来的开销，这些开销应该被当做应用程序的一部分。Oracle的性能优化大师Tom Kyte回答Oracle中测量点的开销时说，测量点至少Wie性能优化贡献10%。一旦发现问题时，每天变化的性能统计数据将会起到很大作用，而且性能统计数据还可以帮助规划硬件采购、资源分配以及预测周期性的性能顶峰。
New Relic
New Relic is a San Francisco, California-based technology company which develops cloud-based software to help website and application owners track the performances of their services.
New Relic的软件即服务SaaS（software-as-a-service）会插入到应用程序中进行性能剖析，将收集到的数据发送到一个基于web的仪表盘中，使用仪表盘可以更容易利用面向响应时间的方法分析应用的性能。New Relic的可测量点涵盖从web浏览器到应用代码，在到数据库及其他外部调用。
像New Relic这类工具的好处是可以全天候的测量生产环境的代码—既不限于测试环境也不限于某个时间段。
1.2.3 通过性能剖析进行性能优化
一旦实践面向响应时间的性能优化方法后就会发现需要不断的对系统进行性能剖析（profiling）。
性能剖析是测量和分析响应时间花费在哪里的主要方法。
性能剖析一般分为两个步骤：
- 测量任务所花费的时间
- 对结果进行统计和排序，将重要的任务放在前面。
性能剖析工具的基本工作方式：在任务开始时启动计时器，在任务结束是停止计时器，然后用结束时间减去启动时间得到相应时间。。
在对系统进行性能剖析前，必须先要能够进行测量，这需要系统可测量化的支持。
可测量的系统一般会有多个可测量点，用以捕获收集数据，但实际很少有系统可以做到可测量化。大部分系统都没有多少可测量点，即使有也只提供一些活动的技术，而且没有活动花费的时间统计。例如：MySQL直到5.5版才第一次提供了Performance Schema，其中有一些基于时间的测量点，而5.1之前的版本没有任何基于时间的测量点。能够从MySQL收集到的服务器操作数据大多是show status计数器的形式，这些计数器统计的是某种活动发生的次数。
这也是使用Percona Server for MySQL的主要原因，Percona Server从5.0开始提供更详细的查询级别的测量点。
理想的性能优化技术依赖更多的可测量点，但是即使系统没有提供可测量点，还可以从外部去测量系统进而展开优化工作。例如Percona Server的慢查询日志揭露的一些性能低下的原因，如磁盘I/O等待或者行级锁等待。
1.2.4 剖析MySQL查询
1.2.4.1 剖析服务器负载
服务器端的剖析可以有效审计效率低下的查询，定位和优化“坏查询”能够显著提升应用程序性能，降低服务器的整体性压力。
MySQL的每一个新版本中都增加了更多的可测量点，但如果只是需要剖析并找出代价高的查询，则直接使用慢查询日志即可。
捕获MySQL的查询到日志文件
在MySQL中，慢查询日志最初只是捕获比较慢的查询，而性能剖析却要针对所有的查询。在5.0及之前的版本中慢查询日志的响应时间单位是秒，粒度太粗；在5.1及之后的版本中慢查询日志的功能得到加强，可以通过设置long_query_time为0来捕获所有的查询，且查询的响应时间可以设置为微秒级。如果使用的是Percona Server for MySQL，则在5.0版本就具备了这些特性，且Percona Server提供了对日志内容和查询捕获的更多控制能力。 在MySQL的当前版本中，慢查询日志是开销最低、精度最高的测量查询时间的工具，且慢查询日志带来的额外磁盘I/O操作基本可以忽略，在I/O密集型场景的基准测试显示慢查询日志带来的开销基本可以忽略不计。
更需要注意的则是日志可能消耗大量的磁盘空间。
如果长期开启慢查询日志，注意需要部署日志轮转（long rotation）工具；或者不要长期开启慢查询日志，只在需要收集负载样本的期间开启即可。 MySQL还有另外一种查询日志，称为“通用日志”，但很少用于分析和剖析服务器性能。通用日志在查询请求到服务器时进行记录，所以不包含响应时间和执行计划等重要信息。 MySQL5.1之后支持将日志记录到数据库的表中，但多数情况下没有必要，因为其对性能的影响较大，而且MySQL5.1在将慢查询记录到文件中是已经支持微秒级别的信息，然而将慢查询记录到表中会导致时间粒度退化为只能到秒级，而秒级的慢查询日志没有意义。 Percona Server的慢查询日志比MySQL官方版本记录了更多细节且有价值的信息，如查询执行计划、锁、I/O活动等；在可管理性上也进行了增强，如全局修改针对每个连接的long_query_time的阈值，这样当应用使用连接池或是持久连接时，可以不用重置会话级别的变量而启动或者停止连接的查询日志。 总体来说慢查询日志是一种轻量且功能全面的性能剖析工具，是优化服务器查询的利器 当因为某种原因如权限不足等，无法在服务器上记录查询时，可以使用如下两种替代技术，且这两种技术都集成到了Percona Toolkit中的query-digest中。
-
第一种是通过--processlist选项不断查看SHOW FULL PROCESSLIST的输出，记录查询第一次出现的时间和消失的时间。问题：无法捕获所有的查询，一些较快的查询可能在两次执行间隙就执行完了，从而无法捕获到。 -
第二种是通过抓取TCP网络包，然后根据MySQL的客户端、服务端通信协议进行分析。可以先通过tcpdump工具将网络数据包保存到磁盘中，然后使用pt-query-digest的--type=tcpdump选项来解析并分析查询。此方法精度较高，且可以捕获所有的查询；还可以解析更高级的协议特性，如可以解析二进制协议，从而创建并执行服务端预解析的语句以及压缩协议。
分析查询日志
最好利用慢查询日志捕获服务器上所有的查询，并进行分析。可以在一些典型的时间窗口如业务高峰期的一个小时内记录查询；如果业务趋势比较稳定，则捕获时间可以在1min内。 注意不要直接打开慢查询日志分析（浪费时间且低效），首先应该生成一个剖析报告，使用自顶向下的分析方式。
可以使用pt-query-digest工具，从慢查询日志中生成剖析报告。该工具可以将查询报告保存到数据库中，以及追踪工作负载随时间的变化。
1.2.4.2 剖析单条查询
1.2.5 间歇性问题诊断
单条查询问题还是服务器问题
|