【MySQL附录】A3：MySQL存储引擎性能对比及深入分析（使用sysbench测试）

最新推荐文章于 2023-02-07 10:43:29 发布

zyplanke

最新推荐文章于 2023-02-07 10:43:29 发布

阅读量671

点赞数

分类专栏： MySQL Linux 文章标签： MySQL

本文链接：https://blog.csdn.net/zyplanke/article/details/102985808

版权

MySQL 同时被 2 个专栏收录

26 篇文章 3 订阅

订阅专栏

Linux

7 篇文章 0 订阅

订阅专栏

在前文基础教程中介绍了MySQL的各个存储引擎（storage engine）。例如MyISAM、InnoDB、Memory存储引擎。各个存储引擎的特点、用途、性能不同。

为了了解存储引擎之间的性能差异，便于在实际工作中选择引擎提供帮助。我们使用sysbench工具对MySQL各个存储引擎的表进行性能测试，分别观察其性能表现。

特别说明：本文仅是简单测试，仅对引擎性能差异有个初步直观的了解，不代表最终的性能差异。由于性能表现与硬件配置、场景选择、参数调整、数据量大小及形态分布都有直接影响。一般应结合具体业务系统，在真实的硬件/网络/参数/数据等基础上的测试，得到的数据才具有实际意义。

工具介绍

sysbench是基于LuaJIT的可编写脚本的多线程基准测试工具。它最常用于数据库基准测试，但也可以用于创建不涉及数据库服务器的任意复杂的工作负载。

sysbench自带以下基准测试：

oltp_*.lua：OLTP的数据库基准测试的集合（老版本的脚本放在了子目录tests/include/oltp_legacy下面）
fileio：文件系统级基准
cpu：简单的CPU基准测试
memory：内存访问基准
threads：基于线程的调度程序基准
mutex：POSIX互斥基准测试

sysbench的通用命令行语法为：

	  sysbench [options]... [testname] [command]

testname可以选择：已经内置自带的（例如，fileio， memory，cpu等），或者以捆绑的Lua脚本（例如的名称oltp_read_only,），或者自定义Lua脚本的路径。如果未指定testname或者 testname称是破折号（“ -”），sysbench接收stdin作为输入（视为ua脚本)。
command要执行的命令，包括prepare、run和cleanup，顾名思义，prepare是为测试提前准备数据，run是执行正式的测试，cleanup是在测试完成后对数据库进行清理。
options 有很多

可以sysbench --help用来显示常规命令行语法和选项。

与MySQL数据库有关的选项:

通用选项：以下中括号中的值表示默认值
--threads=N 指定线程数[1]
--events=N 限制最大请求数，0表示不限制[0]
--time=N 限制最长执行时间，0表示不限制[10]
--events和--time二者选一即可
--forced-shutdown=STRING 达到最长执行时间后还需等待多久关闭sysbench
off表示禁用该功能[off]
--thread-stack-size=SIZE 每个线程使用的栈空间大小[64K]
--rate=N 平均事务处理速率，0表示不限制[0]
--report-interval=N 每隔几秒报告一次结果，0表示禁用间隔报告[0]
--config-file=FILENAME 从文件中读取命令行选项
--tx-rate=N 已废弃，是--rate的别名[0]
--max-requests=N 已废弃，是--events的别名[0]
--max-time=N 已废弃，是--time的别名[0]
--num-threads=N 已废弃，是--threads的别名[1]

mysql相关选项：
--mysql-host=[LIST,...] MySQL server host [localhost]
--mysql-port=[LIST,...] MySQL server port [3306]
--mysql-socket=[LIST,...] MySQL socket
--mysql-user=STRING MySQL user [sbtest]
--mysql-password=STRING MySQL password []
--mysql-db=STRING MySQL database name [sbtest]
--mysql-ignore-errors=[LIST,...] 要忽略的错误代码，值可为"all" [1213,1020,1205]

环境准备

环境包括两类：被测环境（运行MySQL服务）、测试机（运行sysbench）。两者处于同一个局域网。

被测环境。一台虚拟机：CPU仅i5-6200U一个逻辑核core（一个超线程），内存2GB，磁盘为固态硬盘。该机的IP为192.168.43.201，上面已经安装CentOS7.7及MySQL5.7.28（均为64bit）。
测试机。硬件配置高于被测MySQL机器。该操作系统CentOS Linux，机运行sysbench，作为性能测试压力发起和性能数据统计。

被测环境准备：在被测的MySQL中，创建专门用于测试的database（schema），创建测试user并授权：

mysql> create database sbtest;
Query OK, 1 row affected (0.01 sec)

mysql> create user 'sbtest'@'%' identified by 'SB4*test';
Query OK, 0 rows affected (0.00 sec)

mysql>  grant all privileges on sbtest.* to 'sbtest'@'%'; 
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

测试机准备：们专门使用一台机器（该机器hostname为tester，硬件性能配置高于MySQL服务器），该机操作系统CentOS7 Linux，该机能连上互联网，以root用户运行以下命令，安装sysbench：

# curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | bash
# sudo yum -y install sysbench

安装后，查看版本：

[root@host /tmp]# sysbench --version
sysbench 1.0.18

测试步骤（MySQL OLAP测试）

sysbench安装完后，自带的测试脚本放在目录/usr/share/sysbench下（也有可能放在/usr/local/share/sysbench/目录下），该目录下有很多文件及目录。本文介绍的是新版本sysbench oltp lua脚本的用法(/usr/share/sysbench/*.lua)，所以不涉及传统的(在目录/usr/share/sysbench/tests/include/oltp_legacy/*.lua)。

准备测试表

建立测试所用的表，这些测试表放在database(schena)=sbtest中。使用sysbench自带的lua脚本/usr/share/sysbench/oltp_common.lua。该脚本有多个选项（可以直接打开该脚本查看源代码），这里使用的选项参见如下命令

$ sysbench --mysql-host=192.168.43.201 \
        --mysql-port=3306 \
        --mysql-user=sbtest \
        --mysql-password=SB4*test \
        --mysql-db=sbtest \
        /usr/share/sysbench/oltp_common.lua \
        --mysql_storage_engine=MEMORY \
        --tables=10 \
        --table_size=100000 \
        prepare

提示：如果报FATAL: mysql_drv_query() returned error 1114 (The table 'sbtest1' is full) 的错误。是因为MEMORY的单表size限制取决于max_heap_table_size参数。需要修改/etc/my.cnf文件，在[mysqld]下增加两行参数大小。然后重启MySQL服务。

max_heap_table_size=96M
tmp_table_size=64

可以通过mysql客户端，查看准备测试表的结果：

[zyplanke@tester ~]$ mysql -h 192.168.43.201 -u sbtest -p -D sbtest

mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest10         |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
| sbtest6          |
| sbtest7          |
| sbtest8          |
| sbtest9          |
+------------------+
10 rows in set (0.00 sec)

mysql> show create table sbtest1;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                          |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=MEMORY AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.00 sec)

执行测试

准备完成后，用于测试的lua脚本为/usr/share/sysbench/oltp_read_write.lua

$ sysbench --threads=10 \
        --time=30 \
        --report-interval=3 \
        --mysql-host=192.168.43.201 \
        --mysql-port=3306 \
        --mysql-user=sbtest \
        --mysql-password=SB4*test \
        --mysql-db=sbtest \
        /usr/share/sysbench/oltp_read_write.lua \
        --tables=10 \
        --table_size=100000 \
        run

测试执行的结果为：

Initializing worker threads...

Threads started!

[ 3s ] thds: 10 tps: 12.99 qps: 301.69 (r/w/o: 220.44/51.95/29.30) lat (ms,95%): 926.33 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 10 tps: 16.33 qps: 327.35 (r/w/o: 228.68/66.00/32.67) lat (ms,95%): 816.63 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 10 tps: 16.34 qps: 317.72 (r/w/o: 220.37/64.68/32.67) lat (ms,95%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 10 tps: 16.00 qps: 327.35 (r/w/o: 230.34/65.34/31.67) lat (ms,95%): 719.92 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 10 tps: 18.67 qps: 375.65 (r/w/o: 264.66/73.33/37.67) lat (ms,95%): 646.19 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 10 tps: 18.00 qps: 358.97 (r/w/o: 249.98/72.99/36.00) lat (ms,95%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 10 tps: 15.34 qps: 305.39 (r/w/o: 214.37/60.34/30.67) lat (ms,95%): 877.61 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 10 tps: 15.00 qps: 301.00 (r/w/o: 211.00/60.00/30.00) lat (ms,95%): 909.80 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 10 tps: 15.66 qps: 315.29 (r/w/o: 221.30/62.66/31.33) lat (ms,95%): 802.05 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 13.67 qps: 275.02 (r/w/o: 191.35/56.34/27.34) lat (ms,95%): 977.74 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            6776
        write:                           1936
        other:                           968
        total:                           9680
    transactions:                        484    (16.00 per sec.)
    queries:                             9680   (319.99 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.2489s
    total number of events:              484

Latency (ms):
         min:                                  311.54
         avg:                                  623.25
         max:                                 1267.19
         95th percentile:                      861.95
         sum:                               301654.99

Threads fairness:
    events (avg/stddev):           48.4000/0.92
    execution time (avg/stddev):   30.1655/0.08

清除测试表

测试完毕后，通过以下命令删除由sysbench创建的表及数据。而我们自己手工创建的database(schema)及user，需要我们自行删除。

$ sysbench --mysql-host=192.168.43.201 \
        --mysql-port=3306 \
        --mysql-user=sbtest \
        --mysql-password=SB4*test \
        --mysql-db=sbtest \
        /usr/share/sysbench/oltp_common.lua \
        --tables=10 \
        cleanup

各存储引擎(storage engine)的性能对比

上文仅以其中一种存储引擎为例说展示测试步骤。在同一个环境中，分别以存储引擎为InnoDB、Memory，重复上面的：准备、执行、清除三个步骤。并记录其性能表现，数据汇总如下（建议多测试几次）：

sysbench run选项： --threads=10，--tables=10， --table_size=100000
性能测试结果数据	MyISAM	Memory	InnoDB
queries performed
read:	55286	6776	54642
write:	15796	1936	15612
other:	7898	968	78060
total:	78980	9680	80780
transactions:	3949 (131.33 per sec.)	484 (16.00 per sec.)	3903 (129.74 per sec.)
queries:	78980 (2626.69 per sec.)	9680 (319.99 per sec.)	78060 (2594.78 per sec.)
ignored errors:	0	0	0
reconnects:	0	0	0

General statistics:
total time:	30.0515s	30.2489s	30.0817s
total number of events:	3949	484	3903

从上表中发现了什么现象？

InnoDB比MyISAM性能仅略低一点点。为什么？
MEMORY性能最差。它本应该是最高的，为什么？

第1个现象

我们可以解释为InnoDB与MyISAM差不多，所以MySQL在8.0默认全部是InnoDB这样在保证基本性能不损失的情况下，还能获得事务一致性。但仔细一想，如果真有这样的好事，那MySQL早就废掉MyISAM使用InnoDB。

所以我们还要再深入分析，InnoDB比MyISAM主要是事务一致性的区别。我们要看看性能测试的方法是否体现了事务一致性（事务在Table中的行锁争用）。查看sysbench的oltp_common.lua脚本发现，还有其他选型。比如index_updates和non_index_updates选型。在上面的测试中，这两个选型默认是1，既每次事务请求中仅包含一次update操作。我们测试线程是10，总共10个表，每个线程的事物请求仅一次update，这种测试很难体现出事务在行级锁争用的情况。所以上面的数据两个性能差异极小就不奇怪了（为何还有极少量差异？猜测可能是InnoDB的涉及事务的检查和判断代码略多点）

为了体现这种事务争用，增加其事务争用的概率。我们修改执行测试run中的选项：①threads选项保持10不变，将table选项的数量从10减为5。②将index_updates和non_index_updates选项设置为30。修改后的执行测试的命令类似如下（准备测试命令和清理命令不变，与上面相同）：

$ sysbench --threads=10 \
        --time=30 \
        --report-interval=3 \
        --mysql-host=192.168.43.201 \
        --mysql-port=3306 \
        --mysql-user=sbtest \
        --mysql-password=SB4*test \
        --mysql-db=sbtest \
        /usr/share/sysbench/oltp_read_write.lua \
        --tables=5 \
        --table_size=100000 \
        --index_updates=30 \
        --non_index_updates=30 \
        run

然后，在同环境中，分别对MyISAM和InnoDB重新测试（包括准备、执行、清除三个步骤），数据汇总如下：

sysbench run选项： --threads=10，--tables=5， --table_size=100000，--index_updates=30，--non_index_updates=30
性能测试结果数据	MyISAM	Memory	InnoDB
queries performed
read:	18998		19334
write:	84130		68215
other:	2718		2254
total:	105846		89803
transactions:	1357 (44.93 per sec.)		873 (28.50 per sec.)
queries:	105846 (3504.42 per sec.)		89803 (2931.38 per sec.)
ignored errors:	0		508 (16.58 per sec.)
reconnects:	0		0

General statistics:
total time:	30.2016s		30.6332s
total number of events:	1357		873

注：通过sysbench的verbosity选项，InnoDB中的errors信息都是：Ignoring error 1213 Deadlock found when trying to get lock; try restarting transaction。这也间接说明此时，InnoDB下事务之间已经存在争用及死锁。

【结论】从上表中可以看出，相对于MyISAM，InnoDB在transactions事务方面性能降低较多，在queries检索方面有所降低。

第2个现象

不是说好的MEMORY存储引擎读写性能都极高嘛，怎么测试出来的结果不符合预期。研究sysbench的oltp_read_write.lua的脚本我们可以看到，里面执行了ranges一个范围区段的查询。而MEMORY存储引擎的索引是HASH结构，这种HASH结构特别适合精确查询，既Where条件等于具体的某个值（在sysbeach中叫point_select），而不适合以一段范围作为Where条件（在sysbeach中叫ranges）。

对此，我们修改执行测试run中的选项，设置选项--range_selects=false，既不进行range测试。为了获得MyISAM和InnoDB对比数据，命令中其他选项与上文相同。修改后的命令类似如下（准备测试命令和清理命令不变）：

$ sysbench --threads=10 \
        --time=30 \
        --report-interval=3 \
        --mysql-host=192.168.43.201 \
        --mysql-port=3306 \
        --mysql-user=sbtest \
        --mysql-password=SB4*test \
        --mysql-db=sbtest \
        /usr/share/sysbench/oltp_read_write.lua \
        --tables=5 \
        --table_size=100000 \
        --index_updates=30 \
        --non_index_updates=30 \
        --range_selects=false \
        run

然后，在同环境中，再一次分别对MyISAM、Memory、InnoDB重新测试（准备、执行、清除三个步骤命令参数相同），数据汇总如下：

sysbench run选项： --threads=10，--tables=5， --table_size=100000，--index_updates=30，--non_index_updates=30， --range_selects=false
性能测试结果数据	MyISAM	Memory	InnoDB
queries performed
read:	16090	23000	17200
write:	99757	142598	83184
other:	3219	4602	2764
total:	119066	170200	103148
transactions:	1609 (52.97 per sec.)	2300 (76.26 per sec.)	1044 (34.25 per sec.)
queries:	119066 (3919.43 per sec.)	170200 (5642.87 per sec.)	103148 (3383.56 per sec.)
ignored errors:	0	0	676 (22.17 per sec.)
reconnects:	0	0	0

General statistics:
total time:	30.3744s	30.1602s	30.4833s
total number of events:	1609	2300	1044

注：①因为运行MySQL机器的内存仅2GB，在测试Memory引擎过程中观察到Linux操作系统swap空间有增加。②通过sysbench的verbosity选项，查看测试InnoDB过程中的errors信息都是：Ignoring error 1213 Deadlock found when trying to get lock; try restarting transaction。

【结论】从上表中可以看出，Memory无论在事务方面和检索查询方面性能都是最优的（优势仅限于精确查询）；InnoDB比MyISAM在检索查询的性能有少量下降，在transactions事务的性能降低较多。

zyplanke

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【MySQL附录】A3：MySQL存储引擎性能对比及深入分析（使用sysbench测试）

在前文基础教程中介绍了MySQL的各个存储引擎（storage engine）。例如MyISAM、InnoDB、Memory存储引擎。各个存储引擎的特点、用途、性能不同。为了了解存储引擎之间的性能差异，便于在实际工作中选择引擎提供帮助。我们使用sysbench工具对MySQL各个存储引擎的表进行性能测试，分别观察其性能表现。特别说明：本文仅是简单测试，仅对引擎性能差异有个...
复制链接

扫一扫

专栏目录