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 7Mysql 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 性能更好点。

image-20200425150752896

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

image-20200425143756552

我们从图中的 LatencyGeneral 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:51200total time:7.4958 说明 10 秒内完成了操作。

image-20200425160636638

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 的数据量。

image-20200425160839082

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

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

image-20200425161251890

磁盘 io 测试

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

image-20200425162556938

数据库的瓶颈就是 磁盘 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

image-20200425165105348

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

每个 events 操作 16KBtotal 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

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

image-20200425180509471

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

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 测试结果输出文件

image-20200425192516618

  • 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 结果分析

image-20200425210055724

   -- 第一次结果统计
  (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,库存业务统计
(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交易。

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


文章作者: 张攀钦
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 张攀钦 !
评论
  目录