摘要: 原创出处 http://www.iocoder.cn/Performance-Testing/MySQL-benchmark/ 「芋道源码」欢迎转载,保留摘要,谢谢!
MySQL 作为我们日常开发中,使用最多的数据库(基本没有之一),但我们很多开发人员对 MySQL 的性能规格了解得非常少。所以,本文我们想一起来,对 MySQL 本身做一个性能基准测试。
在开始基准测试之前,我们比较快捷的知道,MySQL 大体的性能规格,从各大云厂商提供的 MySQL 云服务。
艿艿:此处,是不是默默的宣传了下国内的云服务。
通过我们看各大厂商提供的指标,我们不难发现,主要是 4 个指标:
如果对基准测试不是很理解的胖友,可以看下 《详解 MySQL 基准测试和 sysbench 工具》 的第一部分基准测试简介。
总结来说,实际就是 2 个维度:
MySQL 的性能测试工具还是比较多的,使用最多的是 sysbench 和 mysqlslap 。本文,我们也会使用这两个工具,进行 MySQL 性能基准测试。
具体的介绍,我们放在下面的章节中。如果对其他测试工具感兴趣,可以看看如下两篇文章:
考虑到有些胖友可能不知道如何安装 MySQL 5.7 版本,可以参考 《在 CentOS7 上使用yum安装 MySQL 5.7》 文章。
FROM 《性能测试工具 sysbench》 sysbench 是一个模块化的、跨平台、多线程基准测试工具,主要用于评估测试各种不同系统参数下的数据库负载情况。它主要包括以下几种方式的测试:
目前 sysbench 主要支持 MySQL、PgSQL、Oracle 这 3 种数据库。
sysbench 也是目前 DBA 最喜欢用来做 MySQL 性能的测试工具。
另外,我们可以发现,云厂商不约而同的使用 sysbench 作为基准测试工具。所以啊,sysbench 可能是比较正确的选型。
下面,我们就开始我们的 sysbench 基准测试之旅。考虑到现在很多公司都是采用阿里云为主,所以我们就参考阿里云的测试方法 。
艿艿:经过几轮的压测,测试出来的 TPS/QPS 巨差,因为没标准去类比,所以我就删除了下面这段,重新去跟老婆大人申请零花钱,买了阿里云服务器去测试。如下是几轮的测试结果: 线程数 / 单表数据量 / 表数 / QPS / TPS
可能和我没做 MySQL 服务器调优配置有关系,走的默认配置。 也可能和服务器的硬盘太差有关系,IOPS 才 100 。如果对 IOPS 的计算逻辑不了解的胖友,感兴趣的,可以看看 《(转)MySQL TPS 和 QPS 的统计和 IOPS》 。 作为一个热爱死磕的艿艿,在 V2EX 的讨论,找到一个通病相连的 《MySQL 的性能指标在什么情况下是正常的呢?》 。
因为抠门,我们拿了手头的刀片机,作为测试服务器。并且,上面实际还跑了蛮多其他服务的,嘿嘿。具体配置如下:
hdparm -i /dev/sda
命令查看,应该是 ST500DM002-1BD142 型号。艿艿:下面,我们拿一台阿里云的 ECS 服务器,进行测试。
我们以 Linux 为例。如果没有 Linux 环境的胖友,可以使用 VirtualBox 安装一个 Linux 虚拟机环境。
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
本文,我们使用的 sysbench 是 1.0.17-2.el7
版本。
-bash-4.2# sysbench --version
sysbench 1.0.17
下面,我们如下三个步骤,进行测试:
需要注意,sysbench 1.0.17 版本,和我们在网上看到的 sysbench 0.5 的版本,命令上有一些差异。
sysbench oltp_common.lua --time=3600 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=buzhidao --mysql-db=sbtest --table-size=10000000 --tables=64 --threads=32 --events=999999999 --report-interval prepare
让我们一起看看每个参数的意思:
如下参数的介绍,我们主要参考了这两篇文章,想要详细了解更多参数的胖友,可以来看看:
oltp_common.lua
:执行的测试脚本。因为我们使用 yum 进行安装,所以胖友需要 cd /usr/share/sysbench/
目录下,看到 sysbench 自带的 lua 测试脚本。--time
:最大的总执行时间,以秒为单位,默认为 10 秒。--events
:最大允许的事件个数,默认为 0 个。
应该和 --time
互相形成最大的执行时间与次数。--mysql-host
:MySQL server host 。--mysql-port
:MySQL server port 。--mysql-user
:MySQL server 账号。--mysql-password
:MySQL server 密码。--mysql-db
:MySQL Server 数据库名。--table-size
:表记录条数。--tables
:表名。--threads
:要使用的线程数,默认 1 个。--report-interval
:以秒为单位定期报告具有指定间隔的中间统计信息,默认为 0 ,表示禁用中间报告。
> 艿艿:这个一定要记得设置下,例如说设置个 10s ,不然一脸懵逼。prepare
:执行准备数据。? 因为阿里云提供的需要生成的数据较多,所以最后艿艿将命令修改成如下:
sysbench oltp_common.lua --time=300 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=MyNewPass4! --mysql-db=sbtest --table-size=1000000 --tables=10 --threads=32 --events=999999999 prepare
--table-size=1000000
和 --tables=10
参数。执行命令后,会自动生成数据库的表、和数据。如下:
[root@iZuf6hci646px19gg3hpuwZ sysbench]# sysbench oltp_common.lua --time=300 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=MyNewPass4! --mysql-db=sbtest --table-size=1000000 --tables=10--threads=32 --events=999999999 prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 1000000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 1000000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 1000000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 1000000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 1000000 records into 'sbtest6'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 1000000 records into 'sbtest7'
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 1000000 records into 'sbtest8'
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 1000000 records into 'sbtest9'
Creating a secondary index on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 1000000 records into 'sbtest10'
Creating a secondary index on 'sbtest10'...
sysbench oltp_read_write.lua --time=300 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=MyNewPass4! --mysql-db=sbtest --table-size=1000000 --tables=10 --threads=16 --events=999999999 --report-interval=10 run
oltp_read_write.lua
:执行的测试脚本。此时,我们在 /usr/share/sysbench/
下,寻找我们想要测试的场景。
oltp_read_write.lua
,表示混合读写,在一个事务中,默认比例是:select:update_key:update_non_key:delete:insert = 14:1:1:1:1
。这也是为什么,我们测试出来的 TPS 和 QPS 的比例,大概在 1:18~20 左右。相当于说,一个事务中,有 18 个读写操作。run
:执行测试。执行后,效果如下:
[root@iZuf6hci646px19gg3hpuwZ sysbench]# sysbench oltp_read_write.lua --time=300 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=MyNewPass4! --mysql-db=sbtest --table-size=1000000 --tables=10 --threads=16 --events=999999999 --rate=0 --histogram=on --report-interval=10 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 16 tps: 784.14 qps: 15705.59 (r/w/o: 10997.12/3138.58/1569.89) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 842.11 qps: 16845.24 (r/w/o: 11791.27/3369.75/1684.22) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 856.99 qps: 17124.49 (r/w/o: 11984.72/3425.78/1713.99) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 835.42 qps: 16714.15 (r/w/o: 11702.34/3340.97/1670.83) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 851.39 qps: 17022.37 (r/w/o: 11913.74/3405.85/1702.78) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
... 省略
如下,是艿艿跑出来的两组测试结果:
sysbench oltp_read_write.lua --time=300 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=MyNewPass4! --mysql-db=sbtest --table-size=1000000 --tables=10 --threads=16 --events=999999999 --report-interval=10 cleanup
cleanup
:执行清理数据。开始打扫战场,嘻嘻。效果如下:
[root@iZuf6hci646px19gg3hpuwZ sysbench]# sysbench oltp_read_write.lua --time=300 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=MyNewPass4! --mysql-db=sbtest --table-size=1000000 --tables=10 --threads=16 --events=999999999 --rate=0 --histogram=on --report-interval=10 runC^C
[root@iZuf6hci646px19gg3hpuwZ sysbench]# sysbench oltp_read_write.lua --time=300 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=MyNewPass4! --mysql-db=sbtest --table-size=1000000 --tables=10 --threads=16 --events=999999999 --report-interval=10 cleanup
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...
--warmup_time
:预热时间,预防冷数据对测试结果的影响。-rate
:指定数量多少事件(事务)平均每秒钟应该执行的所有线程。0(默认)意味着无限的速率,即事件尽快执行。-histogram
:输出测试过程中系统响应时间的分布。percentile
:在延迟统计数据中计算的百分点 (1-100),使用特殊值 0 来禁用百分比计算,默认为 95 。FROM 《MySQL压力测试工具 mysqlslap》 mysqlslap 是一个 MySQL 官方提供的压力测试工具。
比较大的优势,在于 mysqlslap 是 MySQL 官方所提供,并且提供多种引擎的性能测试。
艿艿:更加喜好 sysbench ,所以不会 mysqlslap 会写的相对简单一些。
相比 sysbench 来说,mysqlslap 的测试过程还是比较简洁的,一个命令,即可完成整个过程。如下:
mysqlslap --concurrency=16,32 --iterations=3 --number-int-cols=1 --number-char-cols=2 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=10000 --create-schema=sbtest2 -uroot -pMyNewPass4!
如下参数的介绍,我们主要参考了这两文章,想要详细了解更多参数的胖友,可以来看看:
--concurrency
:并发量,也就是模拟多少个客户端同时执行命令。可指定多个值,以逗号或者 –delimiter
参数指定的值做为分隔符--iterations
:测试执行的迭代次数。--number-int-cols
:自动生成的测试表中包含多少个数字类型的列,默认 1 。此处设置为 1 的原因是,因为我们上面 sysbench 我们生成了一个 int 类型的字段。--number-char-cols
:自动生成的测试表中包含多少个字符类型的列,默认 1 。此处设置为 2 的原因是,因为我们上面 sysbench 我们生成了一个 char 类型的字段。--auto-generate-sql
:自动生成测试表和数据。这个命令,带来的效果,就类似 sysbench 命令的 prepare 指令。--engine
:创建测试表所使用的存储引擎,可指定多个。--number-of-queries
:总的测试查询次数(并发客户数×每客户查询次数)。--create-schema
:测试的 schema ,MySQL中 schema 也就是 database 数据库名。-uroot -pMyNewPass4!
:设置 MySQL 账号和密码。执行命令后,效果如下图:
[root@iZuf6hci646px19gg3hpuwZ sysbench]# mysqlslap --concurrency=16,32 --iterations=3 --number-int-cols=1 --number-char-cols=2 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=10000 --create-schema=sbtest2 -uroot -pMyNewPass4!
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.489 seconds
Minimum number of seconds to run all queries: 0.486 seconds
Maximum number of seconds to run all queries: 0.496 seconds
Number of clients running queries: 16
Average number of queries per client: 625
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.379 seconds
Minimum number of seconds to run all queries: 0.377 seconds
Maximum number of seconds to run all queries: 0.382 seconds
Number of clients running queries: 32
Average number of queries per client: 312
相比来说,mysqlslap 不提供 QPS/TPS 的统计,需要写脚本从 MySQL 统计,或者搭配其它监控工具(例如说,Prometheus MySQL Exporter)。
因为本文确实对 mysqlslap 写的简略,所以可以看看如下几篇文章:
因为本文并未讲 MySQL 性能优化相关的内容,所以需要胖友自己去寻找一些靠谱的资料,继续进行学习。当然,艿艿还是会不断整理一些,写的不错的 MySQL 性能优化相关的内容:
最后,不得不感叹,SSD 硬盘,对 MySQL 等存储服务的巨大收益,特别是使用普通机械磁盘,还是 7500 转的,那个性能渣渣。
另外,在给 MySQL 性能基准测试,可以搭配 Prometheus 等监控系统,通过监控大盘,看看具体的主机性能情况,MySQL 性能情况。
艿艿:上述两个链接,点进去就是监控大盘。