mysql 多个sum优化 mysql多核优化

mysql性能调优

mysql性能调优设计到方方面面，我在这里就抛砖引玉

一、CPU选择

1. 选择标准

OLTP需要IO密集型操作 OLAP是CPU密集型操作。所以为了支持更大内存,最好选择支持64位的多核CPU。

2. 调优方式

可以利用CPU多核特性,增大读写线程的个数.

mysql> show variables like 'innodb_%io_threads' ;
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_read_io_threads  | 4     |
| innodb_write_io_threads | 4     |
+-------------------------+-------+

二、内存选择

内存的大小是最能直接反映数据库的性能。通过之前各个章节的介绍，已经了解到InnoDB存储引擎既缓存数据，又缓存索引，并且将它们缓存于一个很大的缓冲池中，即InnoDB Buffer Pool。因此，内存的大小直接影响了数据库的性能。

1. 选择标准

应该在开发应用前预估“活跃”数据库的大小是多少，并以此确定数据库服务器内存的大小。当然，要使用更多的内存还必须使用64位的操作系统。

当缓冲池的大小已经大于数据文件本身的大小，所有对数据文件的操作都可以在内存中进行。因此这时的性能应该是最优的，再调大缓冲池并不能再提高数据库的性能。

2. 调优方式

(1) 提前预估“活跃”数据库的大小

(2) 对预估值进行压测

这里需要判断当前数据库的内存是否已经达到瓶颈。

可以通过查看当前服务器的状态，比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率，通常InnoDB存储引擎的缓冲池的命中率不应该小于99%。如果小于99%，考虑调大buffer pool size。

mysql> SHOW GLOBAL STATUS LIKE 'innodb%read%';
+---------------------------------------+---------+
| Variable_name                         | Value   |
+---------------------------------------+---------+
| Innodb_buffer_pool_read_ahead_rnd     | 0       |
| Innodb_buffer_pool_read_ahead         | 0       |
| Innodb_buffer_pool_read_ahead_evicted | 0       |
| Innodb_buffer_pool_read_requests      | 2412    |
| Innodb_buffer_pool_reads              | 357     |
| Innodb_data_pending_reads             | 0       |
| Innodb_data_read                      | 6902272 |
| Innodb_data_reads                     | 496     |
| Innodb_pages_read                     | 356     |
| Innodb_rows_read                      | 15      |
+---------------------------------------+---------+

三、硬盘选择

1. 传统机械硬盘 VS 固态硬盘

(1). 特点

• 传统机械硬盘:由于磁头需要旋转和定位,因此顺序访问的速度要远高于随机访问。
• 固态硬盘:读写速度是非对称的。读取速度要远快于写入的速度。

2. 调优方式

(1) 考虑innodb_io_capacity参数

innodb_io_capacity:用来当刷新脏数据时，控制MySQL每秒执行的写IO量。
如果是固态硬盘，考虑适当增大innodb_io_capacity,充分利用固态硬盘带来的高IOPS特性。

(2) 考虑关闭关闭邻接页的刷新

• 工作原理:当刷新一个脏页时，InnoDB存储引擎会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。
• 优点:通过AIO可以将多个IO写入操作合并为一个IO操作,故该工作机制在传统机械磁盘下有着显著的优势。

传统机械键盘要开启该特性，固态硬盘可以选择关闭邻接页的刷新，同样可以为数据库的性能带来一定效果的提升。

四、合理地设置RAID

1. RAID定义

RAID（Redundant Array of Independent Disks，独立磁盘冗余数组）的基本思想就是把多个相对便宜的硬盘组合起来，成为一个磁盘数组，使性能达到甚至超过一个价格昂贵、容量巨大的硬盘。

2. RAID的作用

• 增强数据集成度
• 增强容错功能
• 增加处理量或容量

3. RAID的组合方式

(1) 常见的组合方式

常见的RAID组合方式可分为RAID 0、RAID 1、RAID 5、RAID 10和RAID 50等。

(2) 调优方式

对于数据库应用来说，RAID 10是最好的选择，它同时兼顾了RAID 1和RAID 0的特性。
优点:读取与写入速度快,有镜像备份,可靠性高
缺点:当一个磁盘失效时，性能可能会受到很大的影响，因为条带（strip）会成为瓶颈。

4. RAID Write Back功能

(1) 定义

RAID Write Back功能是指RAID控制器能够将写入的数据放入自身的缓存中，并把它们安排到后面再执行。

(2) 优点

不用等待物理磁盘实际写入的完成，因此写入变得更快了。对于数据库来说，这显得十分重要。

(3) 调优方式

视情况开启RAID Write Back功能。

当操作系统或数据库关机时，Write Back功能可能会破坏数据库的数据。这是由于已经写入的数据库可能还在RAID卡的缓存中，数据可能并没有完全写入磁盘，而这时故障发生了。为了解决这个问题，目前大部分的硬件RAID卡都提供了电池备份单元（BBU，Battery Backup Unit），因此可以放心地开启Write Back的功能。不过我发现每台服务器的出厂设置都不相同，应该将RAID设置要求告知服务器提供商，开启一些认为需要的参数。

如果没有启用Write Back功能，那么在RAID卡设置中显示的就是Write Through。Write Through没有缓冲写入，因此写入性能可能不是很好，但它却是最安全的写入。

五、操作系统的选择

使用64位的操作系统，并且使用64位mysql。

六、文件系统的选择

每个操作系统都默认支持一种文件系统并推荐用户使用，如Windows默认支持NTFS，Solaris默认支持ZFS。而对于Linux这样的操作系统，不同发行版本默认支持的文件系统各不相同，有的默认支持EXT3，有的是ReiserFS，有的是EXT4，有的是XFS。
虽然不同特性的文件系统有很多，但是在实际使用过程中从未感觉到文件系统的性能差异有多大。
文件系统可提供的功能也许是DBA需要关注的，例如ZFS文件系统本身就可以支持快照，因此就不需要LVM这样的逻辑卷管理工具。此外，可能还需要知道mount的参数，这些参数在每个文件系统中可能有所不同。

七、基准测试工具的选择

1. 基准测试工具作用

基准测试工具可以用来对数据库或操作系统调优后的性能进行对比。

2. sysbench

(1) 介绍

sysbench是一个模块化的、跨平台的多线程基准测试工具，主要用于测试各种不同系统参数下的数据库负载情况。
官网地址

(2) 功能

它主要包括以下几种测试方式：

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

(3) 安装

//这里以ubuntu 18.04 为例
apt search sysbench
apt install sysbench
sysbench --version

(4) 测试磁盘

//查看帮助
shell > sysbench fileio help
//sysbench的fileio测试需要经过prepare、run和cleanup三个阶段。prepare是准备阶段，生产需要的测试文件，run是实际测试阶段，cleanup是清理测试产生的文件。
//准备:准备16个文件、总大小2GB的fileio测试：
shell > sysbench fileio --file-num=16 --file-total-size=2G prepare
//运行:测试的最大随机读取请求是100000000次，如果在180秒内不能完成，测试即结束。
shell > sysbench fileio --file-total-size=2G --file-test-mode=rndrd --max-time=180 --max-requests=100000000 --num-threads=16 --file-num=16 --file-extra-flags=direct --file-fsync-freq=0 --file-block-size=16384  run
//删除测试文件
shell > sysbench fileio --file-num=16 --file-total-size=2G  cleanup

(4) 测试MySQL数据库的OLTP性能

需要经历prepare、run和cleanup阶段。prepare阶段会根据选项产生一张指定行数的表，默认表在sbtest架构下，表名为sbtest（sysbench默认生成表的存储引擎为InnoDB）.

//查找相关脚本
shell > find / -name "*" |grep sysbench|grep -E *.lua
/usr/share/sysbench/oltp_read_only.lua
/usr/share/sysbench/oltp_update_non_index.lua
/usr/share/sysbench/oltp_update_index.lua
/usr/share/sysbench/oltp_write_only.lua
/usr/share/sysbench/select_random_points.lua
/usr/share/sysbench/oltp_read_write.lua
/usr/share/sysbench/bulk_insert.lua
/usr/share/sysbench/oltp_common.lua
/usr/share/sysbench/oltp_delete.lua
/usr/share/sysbench/oltp_insert.lua
/usr/share/sysbench/select_random_ranges.lua
/usr/share/sysbench/oltp_point_select.lua

//oltp_read_write.lua 脚本使用
//1. 查看help
shell > sysbench --help
shell > sysbench oltp_read_write help

//2. 首先创建sysbench所需数据库sbtest(这是sysbench默认使用的库名，必须创建测试库)
shell> mysql -uroot -p
mysql> create database sbtest;

//3. 准备数据：--tables=10表示创建10个测试表，--table_size=100000表示每个表中插入10W行数据
shell > sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=密码 --db-driver=mysql --tables=10 --table_size=100000  prepare

//4. 测试 
shell > sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=密码 --db-driver=mysql --tables=10 --table_size=100000  run

//5. 删除测试数据(没有删除测试数据库)
shell > sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=密码 --db-driver=mysql --tables=10 --table_size=100000 cleanup
mysql> drop database sbtest;

//6. 测试结果
SQL statistics:
    queries performed:
        read:          35098   # 执行的读操作数量
        write:         10028   # 执行的写操作数量
        other:         5014    # 执行的其它操作数量
        total:         50140
    transactions:      2507   (124.29 per sec.)    # 执行事务的平均速率
    queries:           50140  (2485.82 per sec.)   # 平均每秒能执行多少次查询
    ignored errors:    0      (0.00 per sec.)
    reconnects:        0      (0.00 per sec.)

General statistics:
    total time:                  20.1694s     # 总消耗时间
    total number of events:      2507         # 总请求数量(读、写、其它)

Latency (ms):
         min:                            2.32
         avg:                           32.13
         max:                          575.78
         95th percentile:              118.92    # 采样计算的平均延迟
         sum:                        80554.96

Threads fairness:
    events (avg/stddev):           626.7500/2.49
    execution time (avg/stddev):   20.1387/0.04

3. tpcc-mysql

(1) 介绍

官网地址

TPC（Transaction Processing Performance Council，事务处理性能协会）是一个用来评价大型数据库系统软硬件性能的非盈利组织。TPC-C是TPC协会制定的，用来测试典型的复杂OLTP（在线事务处理）系统的性能。目前在学术界和工业界普遍采用TPC-C来评价OLTP应用的性能。

TPC-C的性能度量单位是tpmC，tpm是transaction per minute的缩写，C代表TPC的C基准测试。该值越大，代表事务处理的性能越高。

tpcc-mysql是开源的TPC-C测试工具，该测试工具完全遵守TPC-C的标准。

(2) 安装

//安装mysql-dev 开发环境(ubuntu 18.04)
shell > apt install libmysqlclient-dev
shell > git clone https://github.com/Percona-Lab/tpcc-mysql
shell > cd src
shell > make

(3) 组成

tpcc-mysql由以下两个工具组成。

• tpcc_load：根据仓库数量，生成9张表中的数据。
• tpcc_start：根据不同选项进行TPC-C测试。

(4) 使用

//1. 创建库表
shell> mysql -uroot -p
mysql> create database tpcc1000;
mysql> use tpcc1000
mysql> source ./create_table.sql
mysql> source add_fkey_idx.sql

//2. load 数据
shell> ./tpcc_load --help
选项[warehouse]意为指定测试库下的仓库数量
选项[part]为只创建数据到[part]对应的表中
选项[min_wh]、[max_wh]为min_wid max_wid

//-w 10 指建立的仓库数量
//单进程加载(可选)
shell> ./tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p密码 -w 10
//并发加载(可选,注意可能要修改脚本中的一些内容)
shell> ./load.sh tpcc1000 10


//3. 压测
shell> ./tpcc_start --help
-w warehouse 仓库；
-c 连接数；
-r warmup_time:指定预热时间，以秒为单位，默认是10秒，主要目的是为了将数据加载到内存；
-l running_time:指定测试执行时间，以秒为单位，默认是20秒；
-i report_interval:指定生产报告的时间间隔，默认是10秒，我这里设置了20秒；
-f report_file:将测试中各项操作的记录输出到指定文件内保存；
-t trx_file:输出更详细的操作信息到指定文件内保存；

shell> ./tpcc_start -h 127.0.0.1 -p 3306 -d tpcc1000 -u root -p 密码  -w 10 -c 10 -r 100 -l 300 -i 20
shell> ./tpcc_start -h 127.0.0.1 -p 3306 -d tpcc1000 -u root -p 密码 -w 10 -c 10 -r 100 -l 300 -i 20 -f ./tpcc_mysql.log -t ./tpcc_mysql.rtx

以下为本次测试结果数据截图(我的云服务器比较垃圾)