MySQL基准测试及性能优化

mysql基准测试

什么是基准测试

数据库的基准测试是对数据库的性能指标进行定量的、可复现的、可对比的测试。

基准测试与压力测试

• 基准测试可以理解为针对系统的一种压力测试。但基准测试不关心业务逻辑，更加简单、直接、易于测试，数据可以由工具生成，不要求真实；
• 压力测试一般考虑业务逻辑(如购物车业务)，要求真实的数据。

基准测试的作用

对于多数Web应用，整个系统的瓶颈在于数据库；原因很简单：Web应用中的其他因素，例如网络带宽、负载均衡节点、应用服务器（包括CPU、内存、硬盘灯、连接数等）、缓存，都很容易通过水平的扩展（俗称加机器）来实现性能的提高。而对于MySQL，由于数据一致性的要求，无法通过增加机器来分散向数据库写数据带来的压力；虽然可以通过前置缓存（Redis等）、读写分离、分库分表来减轻压力，但是与系统其它组件的水平扩展相比，受到了太多的限制。

而对数据库的基准测试的作用，就是分析在当前的配置下（包括硬件配置、OS、数据库设置等），数据库的性能表现，从而找出MySQL的性能阈值，并根据实际系统的要求调整配置。

基准测试的指标

常见的数据库指标包括：

• TPS/QPS：衡量吞吐量。
• 响应时间：包括平均响应时间、最小响应时间、最大响应时间、时间百分比等，其中时间百分比参考意义较大，如前95%的请求的最大响应时间。。
• 并发量：同时处理的查询请求的数量。

基准测试的分类

对MySQL的基准测试，有如下两种思路：

• 针对整个系统的基准测试：通过http请求进行测试，如通过浏览器、APP或postman等测试工具。该方案的优点是能够更好的针对整个系统，测试结果更加准确；缺点是设计复杂实现困难。

• 只针对MySQL的基准测试：优点和缺点与针对整个系统的测试恰好相反。在针对MySQL进行基准测试时，一般使用专门的工具进行，例如mysqlslap、sysbench等。其中，sysbench比mysqlslap更通用、更强大，且更适合Innodb（因为模拟了许多Innodb的IO特性）

常见的MySQL压力测试工具

• mysqlslap

• Sysbench

  功能强大，官网：https://github.com/akopytov/sysbench

• tpcc-mysql

• MySQL Benchmark Suite

• MySQL super-smack

• MyBench

mysqlslap(压测)

官方介绍：https://dev.mysql.com/doc/refman/5.7/en/mysqlslap.html

mysqlslap，mysqlslap是MySQL5.1.4之后自带的benchmark基准测试工具，该工具可以模拟多个客户端同时并发的向服务器发出查询更新，给出了性能测试数据而且提供了多种引擎的性能比较。测试的过程默认生成一个mysqlslap的schema，生成测试表T1，查询和插入测试数据，mysqlslap库自动生成，如果已经存在则先删除。用–only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。

使用格式：

mysqlslap [options]

常用参数[options]说明

# 自动生成测试表和测试数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力
--auto-generate-sql, -a 

# 测试语句的类型。代表要测试的环境是读操作还是写操作 还是两者混合。取值包括：read，key，write，update和mixed（默认）
--auto-generate-sql-load-type=type

# 代表对生成的表自动添加auto_increment列，从5.1.18版开始支持
--auto-generate-sql-add-autoincrement

# 自动生成的测试表中包含多少个字符类型的列，默认1
--number-char-cols=N, -x N

# 自动生成的测试表中包含多少个数据类型的列，默认1
--number-int-cols=N, -y N

# 总的测试查询次数（并发客户数*每客户查询次数）
--number-of-queries=N

# 使用自定义脚本执行测试，例如可以调用自定义的存储过程或SQL语句来执行测试
--query=name， -q

# 代表自定义的测试库名，测试的schema
--create-schema

# 多少条DML提交一次
--commit=N

# 如服务器和客户端都支持压缩，则压缩信息
--compress, -C

# 表示并发量，即模拟多少个客户端同时执行select，可指定多个值，以逗号或 --delimiter参数指定值作为分隔符，如：--concurrency=100,200,500
--concurrency=N, -c N

# 代表要测试的引擎，可以有多个，用分隔符隔开。如：--engines=myisam，Innodb
--engine=engine_name, -e engine_name

# 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
--iterations=N, -i N

# 只打印测试语句而不实际执行
--only-print

# 执行N条语句后断开重连
--detach=N

# 打印内存和CPU的相关信息
--debug-info, -T

运行结果各项含义

# 运行所有语句的平均秒数
Average number of …

# 运行所有语句的最小秒数
Minimum number of …

# 运行所有语句的最大秒数
Maximum number of …

# 客户端数量
Number of clients …

# 每个客户端运行查询的平均数
Average number of queries per client

mysqslap示例

# 单线程测试
mysqlslap -a -uroot -p123456

# 多线程测试 使用 --concurrency来模拟并发连接
mysqlslap -a -c 100 -uroot -p123456

# 迭代测试 用于需要多次执行测试得到平均值
mysqlslap -a -i 10 -uroot -p123456
mysqlslap --auto-generate-sql-add-autoincrement -a -uroot -p123456
mysqlslap -a --auto-generate-sql-load-type=read -uroot -p123456
mysqlslap -a --auto-generate-sql-secondary-indexes=3 -uroot -p123456
mysqlslap -a --auto-generate-sql-write-number=1000 -uroot -p123456
mysqlslap --create-schema world -q "select count(*) from city" -uroot -p123456
mysqlslap -a -e innodb -uroot -p123456
mysqlslap -a --number-of-queries=10 -uroot -p123456

# 测试同时不同的存储引擎的性能进行对比
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb -uroot -p123456

# 执行一次测试，分别50，100个并发，执行1000次总查询
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 -uroot -p123456

# 50和100个并发分别得到一次测试结果（Benchmark），并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 -uroot -p123456

Sysbench(压测)

sysbench是跨平台的基准测试工具，支持多线程，支持多种数据库；主要包括以下几种测试

• cpu性能
• 磁盘io性能
• 调度程序性能
• 内存分配及传输速度
• POSIX线程性能
• 数据库性能(OLTP基准测试)

本文主要介绍对数据库性能的测试

安装Sysbench

# 下载并解压
wget https://github.com/akopytov/sysbench/archive/refs/tags/1.0.20.tar.gz -O "sysbench-1.0.20.tar.gz"
tar -zxvf sysbench-1.0.20.tar.gz
cd sysbench-1.0.20

# 安装依赖
yum install -y automake libtool

# 开始编译安装（安装前确认在sysbench-1.0.20目录下）
./autogen.sh
./configure

# 这里换成服务器中mysql路径下的include
export LD_LIBRARY_PATH=/mitr/baseProgram/mysql/include
make && make install

# 查看版本确认是否安装成功
sysbench --version

安装时遇到的问题

查看版本时报错

sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

解决方法

# 查看动态链接库
ldconfig -p | grep mysql

# /mitr/baseProgram/mysql/lib/libmysqlclient.so.20在自定义mysql安装位置下
ln -s /mitr/baseProgram/mysql/lib/libmysqlclient.so.20 /usr/lib
ldconfig

# 再次查看动态链接库
ldconfig -p | grep mysql

# 然后再次查看版本号即可
sysbench --version

ldconfig

ldconfig命令的用途主要是在默认搜寻目录/lib和/usr/lib以及动态库配置文件/etc/ld.so.conf内所列的目录下，搜索出可共享的动态链接库（格式如lib*.so*）,进而创建出动态装入程序(ld.so)所需的连接和缓存文件。缓存文件默认为/etc/ld.so.cache，此文件保存已排好序的动态链接库名字列表，为了让动态链接库为系统所共享，需运行动态链接库的管理命令ldconfig，此执行程序存放在/sbin目录下

ldconfig几个需要注意的地方：

• 往/lib和/usr/lib里面加东西，是不用修改/etc/ld.so.conf的，但是完了之后要调一下ldconfig，不然这个library会找不到。
• 在上面两个目录以外加东西的时候，一定要修改/etc/ld.so.conf，然后再调用ldconfig，不然也会找不到。比如安装了一个mysql到/usr/local/mysql，mysql有一大堆library在/usr/local/mysql/lib下面
• 如果想在这两个目录以外放lib，但是又不想在/etc/ld.so.conf中加东西（或者是没有权限加东西）。那也可以，就是export一个全局变量LD_LIBRARY_PATH，然后运行程序的时候就会去这个目录中找library。一般来讲这只是一种临时的解决方案，在没有权限或临时需要的时候使用。
• ldconfig做的这些东西都与运行程序时有关，跟编译时一点关系都没有。编译的时候还是该加-L就得加，不要混淆了。
• 总之，就是不管做了什么关于library的变动后，最好都ldconfig一下，不然会出现一些意想不到的结果。不会花太多的时间，但是会省很多的事。
• 再有，诸如libdb-4.3.so文件头中是会含有库名相关的信息的（即含“libdb-4.3.so”，可用strings命令查看），因此仅通过修改文件名以冒充某已被识别的库（如libdb-4.8.so）是行不通的。为此可在编译库的Makefile中直接修改配置信息，指定特别的库名。

执行./configure报错：cannot find -lmysqlclient_r

解决方法

# 安装mysql-devel
yum install mysql-devel
# 重新执行./configure 继续安装即可

sysbench语法

执行sysbench –help，可以看到sysbench的详细使用方法。sysbench的基本语法如下：

sysbench [options]… [testname] [command]

command是sysbench要执行的命令，包括prepare、run和cleanup，顾名思义，prepare是为测试提前准备数据，run是执行正式的测试，cleanup是在测试完成后对数据库进行清理。

testname指定了要进行的测试，在老版本的sysbench中，可以通过--test参数指定测试的脚本；而在新版本中，--test参数已经声明为废弃，可以不使用–test，而是直接指定脚本。

# 如以下两种写法效果是一样的
sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua
sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua

测试时使用的脚本为lua脚本，可以使用sysbench自带脚本，也可以自己开发。对于大多数应用，使用sysbench自带的脚本就足够了。不同版本的sysbench中，lua脚本的位置可能不同可以使用locate进行搜索

# 安装locate工具
yum install -y mlocate

# 搜索
updatedb
locate oltp.lua

option 是sysbench的参数，一下是比较常用的参数

# mysql连接参数
--mysql-host # mysql服务器主机名/IP
--mysql-port # mysql服务端口,默认3306
--mysql-user # mysql用户名
--mysql-password # mysql密码

# mysql执行参数
# 执行模式：simple(简单查询语句)、nontrx(查询,插入,更新 不使用事务)、complex(默认,增删改查,使用事务)
--oltp-test-mode 
--oltp-tables-count # 测试表的数量
--oltp-table-size # 测试表的大小(数据量)
--threads # 客户端并发连接数
--time # 测试执行的时间,单位是秒,该值不要太短,可以使120
--report-interval # 生成报告的时间间隔,单位是秒,如10

sysbench使用注意

• 尽量不要在MySQL服务器运行的机器上进行测试，一方面可能无法体现网络（哪怕是局域网）的影响，另一方面，sysbench的运行（尤其是设置的并发数较高时）会影响MySQL服务器的表现。
• 可以逐步增加客户端的并发连接数（–thread参数），观察在连接数不同情况下，MySQL服务器的表现；如分别设置为10,20,50,100等。
• 一般执行模式选择complex即可，如果需要特别测试服务器只读性能，或不使用事务时的性能，可以选择simple模式或nontrx模式。
• 如果连续进行多次测试，注意确保之前测试的数据已经被清理干净。

sysbench测试mysql TPS/QPS

# 创建用于测试的数据库
mysql> create database sbtest;

# (执行模式为complex,使用10张表,每张表有100万条数据,客户端并发的线程为100,执行时间为300秒,每10秒生成一次报告)
# 准备数据
sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=10.0.0.68 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=1000000 --threads=100 --time=300 --report-interval=10 prepare

# 执行测试
sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=10.0.0.68 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=1000000 --threads=100 --time=300 --report-interval=10 run >> /root/mysysbench.log

# 清理数据(清除数据一定要指定--oltp-tables-count --oltp-table-size 并与准备数据和执行时的一致)
sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=10.0.0.68 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-tables-count=10 --oltp-table-size=1000000 cleanup

sysbench测试mysql脚本

# mysqlpressure.sh

#!/bin/bash
oltp_path=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua
host=10.0.0.68
port=3306
user=root
password=123456
oltp_tables_count=10
oltp_table_size=100000
threads=50
test_time=120
report_path=/var/log/sysbench.report
echo ">>>正在准备测试数据>>>"
sysbench $oltp_path --mysql-host=$host --mysql-port=$port --mysql-user=$user --mysql-password=$password --oltp-test-mode=complex --oltp-tables-count=$oltp_tables_count --oltp-table-size=$oltp_table_size --threads=$threads --report-interval=10 prepare

echo ">>>正在进行压力测试>>>"
sysbench $oltp_path --mysql-host=$host --mysql-port=$port --mysql-user=$user --mysql-password=$password --oltp-test-mode=complex --oltp-tables-count=$oltp_tables_count --oltp-table-size=$oltp_table_size --threads=$threads --time=$test_time --report-interval=10 run >$report_path

echo ">>>正在进行测试数据清理>>>"
sysbench $oltp_path --mysql-host=$host --mysql-port=$port --mysql-user=$user --mysql-password=$password --oltp-test-mode=complex --oltp-tables-count=$oltp_tables_count --oltp-table-size=$oltp_table_size cleanup

echo ">>>压力测试完毕测试报告位于$report_path"

sysbench解读测试报告

SQL statistics:
    queries performed:
        read: # 读总数
        write: # 写总数
        other: # 其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作，例如COMMIT等)
        total: # 全部总数
    transactions: # TPS 总事务数(每秒事务数)
    queries: # QPS 查询总数（每秒查询数)
    ignored errors: # 总忽略错误总数(每秒忽略错误次数)
    reconnects: # 重连总数(每秒重连次数)
    
General statistics:
    total time: # 总耗时
    total number of events: # 共发生多少事务数

Latency (ms):
         min: # 最小耗时
         avg: # 平均耗时
         max: # 最长耗时
         95th percentile: # 超过95%平均耗时
         sum: 

Threads fairness: # 并发统计
    events (avg/stddev): # 总处理事件数/标准偏差
    execution time (avg/stddev): # 总执行时间/标准偏差

其中mysql测试主要关注 ：

• queries：查询总数及qps
• transactions：事务总数及tps
• Latency-95th percentile：前95%的请求的最大响应时间

FIO(磁盘压测工具)

详见 Centos7磁盘IOPS压力测试

服务器资源监控工具nmon

nmon 是 Nigel’s performance Monitor for Linux(nigel 的 Linux 性能监控器)的英文缩写；它有三种运行模式，分别是：实时屏幕模式、数据收集模式、定时计划执行模式；这款工具运行时，本身资源消耗很小，而且监控的资源种类比较多，使用非常简单，所以，在没有搭建专业的监控平台时，很多人都会想到用它来监控服务器

nmon安装与使用

# 查看centos的发行版本
cat /etc/redhat-release

# 根据版本在nmon的下载页面下载对应版本，然后解压即可
# nmon：http://nmon.sourceforge.net/pmwiki.php?n=Site.Download&utm_source=testingpai.com
yum install -y wget 

# centos7 对应版本
wget https://jaist.dl.sourceforge.net/project/nmon/nmon16m_helpsystems.tar.gz

# 解压
tar -zxvf nmon16m_helpsystems.tar.gz

cp nmon_x86_64_centos7 /usr/local/bin/nmon
chmod +x /usr/local/bin/nmon

实时屏幕模式

直接执行nmon，开启nmon的实时屏幕展示模式

参数说明

c # 带条形图的 CPU 利用率统计信息（CPU 核心线程）
m # 内存和交换统计
d # 磁盘 I/O 繁忙百分比 & 每秒读\写数据量 KB/s 图
r # 资源：机器类型，名称，缓存详细信息和操作系统版本以及 Distro + LPAR
t # top 进程，1 基础、3 性能、4 大小、5 I/O 仅 root 用户可用
n # 网络统计信息和错误（如果没有错误，则消失）
j # 文件系统，包括日记文件系统
k # 内核统计信息运行队列，上下文切换，派生，平均负载和正常运行时间
U # CPU 使用率统计信息 user, user_nice, system, idle, iowait, irq, softirq, steal, guest, guest_nice
u # 进程详细信息

可以连续输入参数 例如：cmdU

数据收集模式

执行nmon -f，开启nmon的数据收集模式，该命令不在后面跟参数，则默认为 -s300 -c288

注：直接执行这个命令，后面不带参数，该命令将在后台持续执行24小时，手机24小时资源使用情况

参数说明

-f # 标准输出到表格文件，默认 -s300 -c288 ，为 24 小时，输出文件格式为：hostname_YMD_HHMM.nmon
-F # 类似-f，但是支持指定输出文件的名称
-a # GPU 加速，统计信息
-b # 切换黑白 和彩色模式
-c # 总统计次数
-s # 数据统计间隔时间， 单位为：秒
-d # 最大的磁盘数，默认 256
-D # 与-g 一起使用以添加磁盘等待/服务时间和运行中状态
-g # 用户定义的磁盘组获取数据：生成 BBBG 和 DG 行
-J # 关闭日志文件系统统计信息收集（可能导致自动挂载 NFS 出现问题）
-m # 把输出文件保存到指定文件夹。通过 cron 启动 nmon 时有用
-M # 为每个 CPU 线程添加 MHz 统计信息。 某些 POWER8 型号 CPU 内核的频率可能不同
-N # 包括适用于 V2，V3 和 V4 的 NFS 网络文件系统
-p # nmon 启动时将输出 PID。 在脚本中很有用，可捕获 PID 以便以后安全停止
-r # 在基准测试中用于记录运行详细信息，以供以后分析[默认主机名]
-t # 在输出中包括 top 流程
-T # -t 增强，它将命令行参数保存在 UARG 部分中
-U # 包括 Linux 10 CPU 使用率统计信息（文件中的 CPUUTIL 行）

常用参数 -f -s -c -m -p

常用案例

# 监控服务器，将结果标准输出，命令将后台持续运行24小时
nmon -f

# 每隔3秒收集一次，收集10次，将结果标准输出
nmon -f -s3 -c10

# 每隔3秒收集一次，收集10次，将结果标准输出到指定路径，注意：文件夹要存在
nmon -f -s3 -c10 -m /tmp/nmon

定时计划执行模式

nmon -x 开启定时计划执行模式，若后面不添加任何参数，默认为-s900 -c96 即每隔900秒获取一次数据，总共执行96次，也就是24小时，收集的数据结果将采用标准输出

使用-X参数，默认为-s30 -c120 即每隔30秒获取一次数据共执行120次，也就是1小时，收集的数据结果采用标准输出

-z 用root账户运行，收集1天数据，输出到/var/perf/tmp文件夹

查看结果

标准输出结果查看

nmon的标准输出结果文件为：主机名_年月日_时分。nmon是一种文本数据文件，可以用文本编辑器打开，但不便于阅读，可以使用nmon的analysis工具打开

• 本地电脑下载 nmon_analysis:

http://nmon.sourceforge.net/pmwiki.php?n=Site.Nmon-Analyser&utm_source=testingpai.com

• 解压刚刚下载的tar包

• 从Linux服务器上下载nmon标准输出文件到本地电脑

• 使用Excel打开刚刚解压的nmon analyser v**.xlsm文件，启用宏

• 点击 【Analyze nmon data】按钮，选择从服务器上下载的 nmon 文件，将会自动分析，生成一个 xlsx 的文件，保存，自动会用 Excel 打开，打开我们就能看到多个图表

如果生成的nmon标准输出文件过大，可以使用其他工具：如 nmonchar.tar

http://nmon.sourceforge.net/pmwiki.php?n=Site.Nmonchart&utm_source=testingpai.com

# 需要安装ksh
yum install -y ksh

# 下载之后解压并进入到nmonchart40
./nmonchart 被分析的nmon文件.nmon 生成结果文件.html

# 将生成的html文件下载到本地机器即可查看

生产环境 my.cnf 配置案例(未完待续)

配置文件生成工具参考链接：https://imysql.com/my_cnf_generator

计算mysql占用内存大小链接：http://www.mysqlcalculator.com

# 设置服务端使用的字符集
character_set_server=utf8mb4

# 禁止域名解析
skip_name_resolve=1

# 客户端连接失败次数，阻止客户端暴力破解密码 默认100
# 每个客户端连接最大的错误允许数量，当超过该次数，MySql服务器将禁止此主机的连接请求，直到MySQL服务器重启或通过flush hosts命令清空此主机的相关信息
# 需要配合 skip_name_resolve=1 一起用才会生效
max_connect_errors=100

# 最大客户端连接数 默认值 151 最小值1 最大值100000
# max_connections 还取决于操作系统对单进程允许打开最大文件数的限制
# 也就是说如果操作系统限制单个进程最大可以打开100个文件
# 那么 max_connections 设置为200也没什么用
# ulimit -n 查看操作系统对单进程打开最大文件数限制 默认为1024
max_connections=5000

# 推荐设置：1； 默认为0
# 应用场景：主从复制时使用，具体看业务需求
# 0：代表mysql不控制写binlog的时间，由file system自由去控制，此时的mysql的并发性达到最好，但是一旦系统崩溃会丢失很多还未写入binlog的数据（比如说正在删数据和更新数据）
# 1：最安全，最多丢掉一个事务或者是一条语句，但是此时它的性能很差，此参数设为0或者是1之间的性能能差4～5倍
# sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘
sync_binlog=1

# 隔离级别
# 1.read-uncommitted：读未提交，允许脏读：读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到
# 2.read-committed：读提交，不允许脏读，但允许不可重复读：读提交是指，一个事务提交之后，它做的变更才会被其他事务看到
# 3.epeatable-read：可重复读，不允许脏读、不可重复读，但允许幻读：可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的
# 4.serializable：串行化，以上都不允许：“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行
transaction_isolation=READ-COMMITTED

# 每个连接传输数据大小，最大1G，必须是1024的倍数,一般设为最大的BLOB的值
# 针对一个事务中的一行记录的大小，当一行记录超过限制的大小将会报错
# sql文件中每次insert完进同一张表的所有数据被称为一个数据包（packet），max_allowed_packet就是来限制这个的大小的阈值，大于这个值，mysql的I/O连接会关闭，会报错
max_allowed_packet=128M

# InnoDB相关参数

# 确定日志文件何时write、flush
# ！！！ 核心交易系统设置为1，默认为1，其他设置2或0 ！！！
# 0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作；就是每秒调用 flush + fsync ，定时器自己维护。

# 1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认（因此会保留每一份redo日志）；就是实时调用 flush + fsync 没法批处理，性能很低。

# 2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失；就是实时flush ,定时 fsync 交给OS维护定时器。

# 除非你用的是小型机或者是超大规模mysql集群一类如：游戏行业，那么需要保留每z一秒的事务，否则请设成2，要不然会严重影响系统性能。这个参数是5.6所没有的。

# 如果不配的后果：默认为1，影响系统写性能。

# innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数，当两个参数都设置为1的时候写入性能最差

# 推荐做法是
# innodb_flush_log_at_trx_commit=2
# sync_binlog=500或1000
innodb_flush_log_at_trx_commit=2

# 控制着innodb数据文件及redo log的打开、刷写模式
# 确定日志及数据文件如何write、flush
innodb_flush_method=O_DIRECT

# innodb使用一个缓冲池来保存索引和原始数据，可设置这个变量到物理内存大小的80%
# 建议设置为内存的20%-65%（独立的mysql服务器）
# 20%：因为对于4G内存的mysql服务器按照20%系统内存设置
innodb_buffer_pool_size=48128M

# 与innodb_buffer_pool_size相辅相成,在32位机器下innodb_buffer_pool_instances一般为1
# 在64位机器上,这个值为8-64
# innodb_buffer_pool_instances其实为CPU核数
# 作用：
# 1.对于缓冲池在数千兆字节范围内的系统，通过减少争用不同线程对缓存页面进行读写的争用，将缓冲池划分为多个单独的实例可以提高并发性
# 2.使用散列函数将存储在缓冲池中或从缓冲池读取的每个页面随机分配给其中一个缓冲池实例。每个缓冲池管理自己的空闲列表,刷新列表,LRU和连接到缓冲池的所有其他数据结构，并受其自己的缓冲池互斥量保护。
# innodb_buffer_pool_size的设置需要为pool_instance的整数倍
innodb_buffer_pool_instances=24

# 定义了InnoDB后台任务每秒可用的I/O操作数（IOPS）默认200 系统吞吐量上不去
# 影响 innodb 刷脏页的能力 脏页：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”
# 该值对读无效,对写很有意义,它会直接决定mysql的tps(吞吐性能)
# 需要根据paas或者是ias的vm的硬盘性号来定
# 最好的方法是测量你的存储设置的随机写吞吐量，然后给innodb_io_capacity_max设置为你的设备能达到的最大IOPS
# SATA/SAS：innodb_io_capacity_max=200
# SAS raid10：innodb_io_capacity_max=2000
# SSD：innodb_io_capacity_max=8000
# fusion-io(闪存卡)：innodb_io_capacity_max=25000-50000
innodb_io_capacity_max=8000

# innodb_io_capacity就设置为它的50-75%，特别是系统主要是写操作时
innodb_io_capacity=4000

# 控制buffer pool刷脏页时是否把脏页邻近的其他脏页一起刷到磁盘，在传统的机械硬盘时代，打开这个参数能够减少磁盘寻道的开销，显著提升性能，设置成1(默认状态)Innodb会刷新一个extent中的所有页面，因SSD在随机IO上没有额外负载，所以不需要启用该特性。即 机械盘 innodb_flush_neighbors=1 固态 innodb_flush_neighbors=0，mysql8.0已经把这个默认值设为0了。
# 当 srv_flush_neighbors 为 0时， [low, high] 只包含一个页面
# 当 srv_flush_neighbors 为 1时， [low, high] 包含邻近的页面，页面数小于等于变量值buf_flush_area
# 当 srv_flush_neighbors 为 2时， [low, high] 包含邻近的页面，页面数等于变量值buf_flush_area
innodb_flush_neighbors=1

# 在日志组中每个日志文件的大小
innodb_log_file_size=1G
# 在日志组中的文件总数
innodb_log_files_in_group=3

# 表的字段索引长度限制
# 如果客户端和服务端的字符集设置为utf8mb4则innodb_large_prefix=1
# 因为mysql5.6之前一直是单列索引限制767（256*3-1）3是字符最大占用空间(utf8)
# mysql5.6以后，开始支持4个字节的utf8mb4 255*4>767 于是增加了这个参数
# 默认值为OFF，当改为ON时，允许列索引最大达到3072
# mysql5.6中开关叫ON，OFF，5.7中是1或0，由于前面这是了utf8mb4,那么这个值必须开启
# 未开启：会导致索引微笑，或者查询时不走最有计划
innodb_large_prefix=1

# 影响page cleaner线程每次刷脏页的数量 默认为1024
innodb_lru_scan_depth=2000

# 开启慢查询日志
# 默认情况下，慢查询日志禁用，因为开启慢查询日志或多或少的会对mysql的性能产生一些影响
# 在慢查询日志功能开启时，只有SQL执行时间超过long_query_time参数值的的语句才会在慢查询日志中记录
slow_query_log=ON

# 慢查询时长
# 最小值和默认值分别为0  10，单位为秒
long_query_time=2

# 以FILE类型存储慢查询日志时的存储位置
# 如果没有为慢查询日志指定名称，默认为host_name-slow.log
slow_query_log_file=/mitr/baseProgram/mysql/data/slow.log

# 所有线程打开表的数量
open_files_limit=65535

# 限制Innodb能打开的表的数据
# 这个值默认是300,如果库里的表特别多的情况,可以适当增大为1000. innodb_open_files的大小对InnoDB效率的影响比较小。
# 但是在InnoDBcrash的情况下,innodb_open_files设置过小会影响recovery的效率.
# 所以用InnoDB的时候还是把innodb_open_files放大一些比较合适
innodb_open_files=3000