Mysql 8.0 安装与基准测试

前言

2019 年的时候就打算写 Mysql 系列的博客，想对自己学的东西的做一个总结，一拖再拖啊，2020-5 至 202-6 两个月专心写 Mysql 相关的东西。

Mysql 我不想只限于增删改查，于是买了两本书《Mysql DBA 修炼之道》《高性能 Mysql》和掘金上一个关于 Mysql 小册子。《高性能 Mysql》还没有看完，一遍写博客，一遍看吧。

了解 Mysql 原理相关的东西之后，我们再也不用记忆哪些军规了。

Mysql 系列的内容会包含

• Mysql 安装与基准测试
• Mysql 中 mysql 数据库中的表说明和权限相关
• Mysql 表设计
• Mysql 日志介绍
• Mysql 索引
• Mysql 锁、mvcc、事务
• Mysql 查看执行计划与 sql 调优
• Mysql 重要的配置参数相关说明
• Mysql 主从复制
• Mysql 备份与恢复
• 分布式事务与阿里 seata
• 数据库中间件 Apache ShardingSphere

相关内容会在 Centos 7 和 Mysql 8.0 下验证

Mysql 8.0 安装

因为不想牵扯到 docker 相关的东西，使用 docker 安装 mysql 就不介绍了。

官网 yum 安装

# 下载 mysql yum 源
rpm -Uvh https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm


# 安装好 rpm 包之后，清空以前缓存
yum clean all
yum makecache

# 运行完上述命令，查询 mysql 的源
yum list | grep "mysql.*-community.*server"

# 安装 mysql 
sudo yum install mysql-community-server

官网 yum 源在国外，如果你嫌慢，可以从网易 mysql 镜像下载所需的所有 rpm 包，自建本地 yum 源

自建 yum 源安装

网易 Mysql 8.0 镜像

# 创建目录，下载 mysql 8.0.18 所需的镜像
mkdir /opt/mysql

# 进入 /opt/mysql 下载 rpm 依赖
cd /opt/mysql

# 下载 mysql rpm 依赖
wget http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar

# 解压文件到 /opt/mysql 下
tar -xf mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar

# 在 /opt/mysql 创建本地 yum 源，有 /opt/mysql/repodata，说明创建成功
createrepo .

# 新建本地 yum 源
cd /etc/yum.repos.d
touch mysql.repo

mysql.repo 内容如下 baseurl 为 /opt/mysql/repodata 的父目录。

[mysql-custom]
name=mysql-custom
baseurl=file:///opt/mysql/
gpgcheck=0
enabled=1

# 重新生成 yum 源数据
yum clean all
yum makecache

# 运行完上述命令，查询 mysql 的源
yum list | grep "mysql.*-community.*server"

# 安装 mysql 
sudo yum install mysql-community-server

启动 Mysql

# 创建开机启动
sudo systemctl enable mysqld

# 去除开机启动
sudo systemctl disable mysqld

# 启动 mysql 
sudo systemctl start mysqld

# 查看 mysql 运行的状态
sudo systemctl status mysqld

# 关闭 mysql 
sudo systemctl stop mysqld

# 重启 mysql 
sudo systemctl restart mysqld

启动 Mysql 之后会生成一个临时密码。使用这个临时密码登录，然后把密码改掉。

# 查询初始化密码
sudo grep 'temporary password' /var/log/mysqld.log

# 登录 mysql ,用上述得到密码
mysql -uroot -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Mysql@12345678';

配置外网链接 Mysql

# 因为是研究使用，所以我用 root 连接 mysql。生产环境不要用 root 连接 mysql
# 修改用户信息，让 mysql 可以远程链接,host=% 代表任意 ip
use mysql;
update user set host='%' where user='root';

# 退出 mysql 客户端
exit 

修改 /etc/my.cnf

# 生产环境中，设置特定的 ip 
bind-address          = 0.0.0.0

允许外网链接 mysql 的话，不要忘了设置开放 3306 端口。

Root 密码忘记修改

# 编辑配置文件，
vim /etc/my.cnf

# [mysqld] 下添加 skip-grant-tables

# 重新启动 mysql
systemctl restart mysqld

# 免密码登录 mysql
mysql

# 以下操作是在 msyql 客户端操作的，
# 选择数据库
use mysql;
# 查看用户信息
select host, user, authentication_string, plugin from user;

# 修改密码为空
update user set authentication_string='' where user='root';

# 退出 mysql
# 去掉 /etc/my.cnf [mysqld] 中的 skip-grant-tables
# 重新启动 mysql
systemctl restart mysqld

# 修改密码的时候，'root'@'%' 要和查询到的信息一致
ALTER USER 'root'@'%' IDENTIFIED BY 'Mysql@123456';

测试部分感兴趣想研究可以看下，一般云上数据库都会给出性能指标的。

sysbench 测试 Mysql 性能

Mysql 配置新的参数之后，怎么确定修改的参数能提升性能呢，这就需要测试了。可以使用工具 sysbench ，TPCC-Mysql 测试。

Mysql 的基准测试可以使用 sysbench，可以用于 磁盘 io，cpu 测试，内存测试等。

[root@centos-7 parallels]# sysbench -v
sysbench 1.0.17 (using system LuaJIT 2.0.4)

CPU 测试

sysbench cpu help

#[root@centos-7 parallels]#  sysbench cpu help
#sysbench 1.0.17 (using system LuaJIT 2.0.4)

#cpu options:
#  --cpu-max-prime=N upper limit for primes generator [10000]

sysbench 通过素数运算来测试 cpu，--cpu-max-prime 参数指定计算到最大的素数范围。默认是在十秒内计算，因此这种情况下，events 越大说明 cpu 的性能越好。一个 event 为从最小素数计算到最大素数（--cpu-max-prime）。

# 为左图数据
sysbench cpu --cpu-max-prime=500000 run

# 为右图数据
sysbench cpu --cpu-max-prime=50000 run

查看图中 total number of events 可知计算到 5000 能执行更多的 events。

我们可以在不同的主机上执行相同的命令进行测试，看哪个 cpu 性能更好点。

同一台主机上，我们可以指定不同的线程来看最终的 events 。

我们从图中的 Latency 和 General statistics 可以知道开启 15 线程时，延迟 (查看 avg) 提高了，但是吞吐量(total events)增加了。通过开启不同线程的测试，列出数据，可以找到最佳线程数量，低延迟，高吞吐。

内存测试

--memory-block-size 定义一次 event 操作的大小，--memory-total-size 指定总的大小。

# 查看帮助信息
sysbench memory help

sysbench 1.0.17 (using system LuaJIT 2.0.4)

memory options:
  --memory-block-size=SIZE    size of memory block for test [1K]
  --memory-total-size=SIZE    total size of data to transfer [100G]
  --memory-scope=STRING       memory access scope {global,local} [global]
  --memory-hugetlb[=on|off]   allocate memory from HugeTLB pool [off]
  --memory-oper=STRING        type of memory operations {read, write, none} [write]
  # seq 顺序操作，rnd 随机操作。随机操作比顺序操作要慢不少
  --memory-access-mode=STRING memory access mode {seq,rnd} [seq]

sysbench memory run --memory-block-size=1M --memory-total-size=50G --memory-access-mode=rnd --memory-oper=write --threads=20

每次操作 1M ，total number of events:51200，total time:7.4958 说明 10 秒内完成了操作。

sysbench memory run --memory-block-size=1M --memory-total-size=100G --memory-access-mode=rnd --memory-oper=write --threads=20

10 秒传输的总的数据量为 69G 左右。每次操作 1M ，total number of events:69217，total time:10.019` 说明 10 秒内只传输了 69G 的数据量。

当我们将内存改成顺序读写（--memory-access-mode=seq），很快就完成了操作。

sysbench memory run --memory-block-size=1M --memory-total-size=100G --memory-access-mode=seq --memory-oper=write --threads=20

磁盘 io 测试

# 查看测试磁盘 io 帮助信息
sysbench fileio help

数据库的瓶颈就是 磁盘 io，这块需要我们好好测试。异步读写，读写模式（随机/顺序） 等。

—file-test-mode 指定文件的读写模式。参考《Mysql DBA 修炼之道》。

• seqwr ：顺序读写。
• seqrewr： 顺序重写。
• seqrd：顺序读。
• rndrd：随机读。
• rndwr：随机写。
• rndrw：随机读写。

进入某个已知目录下测试，这样你可以知道生成的文件在哪里。

# 生成测试文件
sysbench fileio prepare --threads=15 --file-num=50 --file-total-size=15G --file-test-mode=rndrw

# 测试数据
sysbench fileio run --threads=15 --file-num=50 --file-total-size=15G --file-test-mode=rndrw --time=60

# 删除生成的测试文件
sysbench fileio cleanup --threads=15 --file-num=50 --file-total-size=15G --file-test-mode=rndrw --time=60

因为 Mysql 的数据页默认是 16KB 这里也指定 file-block-size 为 16KB。

每个 events 操作 16KB ，total number of events 550299，操作了十秒，读写的数据量没有达到 15G。我们还可以看到磁盘的吞吐量。这些都是磁盘 io 的参考指标。

Mysql 测试

提前创建用户

create database sysbench_test;
CREATE USER 'test'@'%' IDENTIFIED BY 'Mysql@123456';
grant all privileges on sysbench_test.* to 'test'@'%';
FLUSH PRIVILEGES;

创建三个表，并在每个表中插入数据 10000000

sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=test --mysql-password='Mysql@123456' --mysql-db=sysbench_test --db-driver=mysql --tables=3 --table-size=10000000  prepare

执行测试，每 10 秒打印测试结果到窗口。

sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=test --mysql-password='Mysql@123456' --mysql-db=sysbench_test --db-driver=mysql --tables=3 --table-size=10000000 --report-interval=10 --threads=20 --time=600 run

测试的时候可以使用 top 和 free -m -s 3 查看系统 cpu 和 内存使用情况。

测试之后别忘了删除测试数据。

sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=test --mysql-password='Mysql@123456' --mysql-db=sysbench_test --db-driver=mysql --tables=3 --table-size=10000000 --report-interval=10 --threads=20 --time=600 cleanup

TPCC-Mysql 测试 Mysql

由于 sysbench 只是测试单个表，业务比较单一，tpcc-mysql 会生成多个表，模拟的环境更贴近生产。

TPCC 百度百科

tpcc-mysql的业务逻辑相关的几个表作用如下：

customer 表对应客户

district 表对应地区

history 表对应历史订单，

item 表对应商品

new_orders 表对应新订单

order_line 表对应订单状态

orders 表对应下单

stock 表对应库存状态

warehouse 表对应电商仓库数量10个

下载源码进行编译

git clone https://github.com/Percona-Lab/tpcc-mysql

cd tpcc-mysql/src

# 当前构建需要 mysql_config 这个依赖，centos 中安装 mysql-devel
yum install mysql-devel

#  构建，make如果成功后，会在tpcc-mysql目录下生成tpcc_load与tpcc_start两个工具。
make

以下操作在 README.md 中可以看到。

创建数据库、加载 sql 文件创建表

mysqladmin -uroot -pMysql@12345678 create sysbench_test;
mysql -uroot -pMysql@12345678 sysbench_test < create_table.sql
mysql -uroot -pMysql@12345678 sysbench_test < add_fkey_idx.sql

数据库和表结构创建好之后，初始化测试数据

# 查看参数帮助信息
./tpcc_load --help 

tpcc_load -h server_host -P port -d database_name -u mysql_user -p mysql_password -w 
warehouses -l part -m min_wh -n max_wh

# 对应生成那个表数据
* [part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS

一般设置 -w 为 100，如果是 ssd 建议设置 1000。

tpcc_load -h localhost -P 3306 -d sysbench_test -u test -p Mysql@123456 -w 100

git 项目下有个 load.sh 脚本可用于并行初始化数据

开始测试

./tpcc_start -h 127.0.0.1 -P 3306 -d sysbench_test -u test -p Mysql@123456 -w 100 -c 20 -r 300 -l 1800 -i 10 -f ./test.log -t ./tpcc_mysql.rtx >> tpcc_noaid_2_20140921_64.log 2>&1

-h 服务器名
-P 端口号，默认为3306
-d 数据库名
-u 用户名
-p 密码
-w 仓库的数量
-c 线程数，默认为1
-r 数据预热时间，单位:s，默认为10s，预热时间不少于五分钟
-l 测试时间，单位:s，默认为20s，测试时间不少于半小时
-i 指定生成报告间隔时长
-f 测试结果输出文件

• 10，20，30 第一列指的是 生成报告的时间间隔
• trx: 12920 - 在指定间隔内，new order 的事务执行次数。值越大说明性能越好。
• 95%: 9.483: 在指定间隔内 95% 的 new order 的 事务平均响应时间
• 99%: 18.738: - 在指定间隔内 99% 的 new order 的 事务平均响应时间
• max_rt: 213.169: 指定间隔内 new order 的事务的最大响应时间
• the rest: 12919|98.778, 1292|101.096, 1293|443.955, 1293|670.842 is throughput and max response time for the other kind of transactions and can be ignored

Ram Results 结果分析

   -- 第一次结果统计
  (success,简写sc)次数，延迟(late,简写lt)次数，重试(retry,简写rt)次数，失败(failure,简写fl)次数
  [0] sc:100589  lt:0  rt:0  fl:0   -- 新订单业务统计
  [1] sc:100552  lt:0  rt:0  fl:0   -- Payment，支付业务统计
  [2] sc:10059  lt:0  rt:0  fl:0    -- Order-Status，订单状态业务统计
  [3] sc:10057  lt:0  rt:0  fl:0    -- Delivery，发货业务统计
  [4] sc:10058  lt:0  rt:0  fl:0    -- Stock-Level，库存业务统计

<Constraint Check>(all must be [OK])
[transaction percentage]  --事物比例
        Payment: 43.48% (>=43.0%) [OK]  --支付
   Order-Status: 4.35% (>= 4.0%) [OK]   --订单状态
       Delivery: 4.35% (>= 4.0%) [OK]   --发货
    Stock-Level: 4.35% (>= 4.0%) [OK]    --查库存
 [response time (at least 90% passed)]    --响应时间，必须超过90%才算通过
      New-Order: 99.92%  [OK]    --创建订单
        Payment: 99.98%  [OK]    --支付
   Order-Status: 99.90%  [OK]    --查询状态
       Delivery: 100.00%  [OK]    --发货
    Stock-Level: 100.00%  [OK]    --查库存

Tpmc 描述了系统在执行Payment、Order-status、Delivery、Stock-Level这四种交易的同时，每分钟可以处理多少个New-Order交易。

还有一些测试结果是和脚本不知道怎么使用，后续再补充吧，这部分内容已经研究了两天了，网上也没有资料参考，进行不下去了

---

本文由 张攀钦的博客 http://www.mflyyou.cn/ 创作。 可自由转载、引用，但需署名作者且注明文章出处。

如转载至微信公众号，请在文末添加作者公众号二维码。微信公众号名称：Mflyyou