使用sysbench对MySQL进行基准测试记录-20230311

一、基准测试简介

1、什么是基准测试

数据库的基准测试是对数据库的性能指标进行定量的、可复现的、可对比的测试。

基准测试与压力测试

基准测试可以理解为针对系统的一种压力测试。但基准测试不关心业务逻辑，更加简单、直接、易于测试，数据可以由工具生成，不要求真实；而压力测试一般考虑业务逻辑(如购物车业务)，要求真实的数据。

2、基准测试的作用

对于多数Web应用，整个系统的瓶颈在于数据库；原因很简单：Web应用中的其他因素，例如网络带宽、负载均衡节点、应用服务器（包括CPU、内存、硬盘灯、连接数等）、缓存，都很容易通过水平的扩展（俗称加机器）来实现性能的提高。而对于MySQL，由于数据一致性的要求，无法通过增加机器来分散向数据库写数据带来的压力；虽然可以通过前置缓存（Redis等）、读写分离、分库分表来减轻压力，但是与系统其它组件的水平扩展相比，受到了太多的限制。

而对数据库的基准测试的作用，就是分析在当前的配置下（包括硬件配置、OS、数据库设置等），数据库的性能表现，从而找出MySQL的性能阈值，并根据实际系统的要求调整配置。

3、基准测试的指标

常见的数据库指标包括：

• TPS/QPS：衡量吞吐量。
• 响应时间：包括平均响应时间、最小响应时间、最大响应时间、时间百分比等，其中时间百分比参考意义较大，如前95%的请求的最大响应时间。
• 并发量：同时处理的查询请求的数量。

4、基准测试的分类

对MySQL的基准测试，有如下两种思路：

（1）针对整个系统的基准测试：通过http请求进行测试，如通过浏览器、APP或postman等测试工具。该方案的优点是能够更好的针对整个系统，测试结果更加准确；缺点是设计复杂实现困难。

（2）只针对MySQL的基准测试：优点和缺点与针对整个系统的测试恰好相反。

在针对MySQL进行基准测试时，一般使用专门的工具进行，例如mysqlslap、sysbench等。其中，sysbench比mysqlslap更通用、更强大，且更适合Innodb（因为模拟了许多Innodb的IO特性），下面介绍使用sysbench进行基准测试的方法。

二、MySQL卸载及安装

1、ubuntu完全卸载mysql

可以先用 dpkg --list|grep mysql 查看自己的mysql有哪些依赖

(1)先卸载 mysql-common

sudo apt-get remove mysql-common

(2) 然后再卸载

sudo apt-get autoremove --purge mysql-server-5.7

再用dpkg --list|grep mysql查看，还剩什么就卸载什么

(3) 最后清除残留数据

dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

2、Ubuntu安装mysql

(1) 安装

更新软件库

sudo apt update

安装mysql服务

sudo apt install mysql-server

*这步就已经安装mysql-client。

安装依赖

sudo apt install libmysqlclient-dev

(2) 检查状态

sudo netstat -tap | grep mysql

如果出现“无此命令”，输入命令sudo apt install netstat

(3) 设置root密码

进入root状态

sudo -i

进入mysql

mysql

配置数据库root权限

update mysql.user set authentication_string=PASSWORD(‘123456’), plugin=‘mysql_native_password’ where user=‘root’;

设置生效和退出mysql、root模式

flush privileges;
exit;

重启MySQL服务

sudo /etc/init.d/mysql restart

重新进入MySQL

mysql -u root -p

此部分原文链接：Ubuntu完全卸载与安装Mysql

(4) 为sysbench创建测试数据库

创建测试数据库

mysql -u root -p 123456

mysql> create database sysbench_test;

查看测试数据库

mysql> show database

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+

mysql> \q

创建测试数据过程参考文章：sysbench安装及压测MySQL、Oracle

(5)删除数据库

drop database <数据库名>;

(6)MySQL控制台退出

使用MySQL控制台命令退出MySQL会话时，需要使用以下命令：\q或者\quit。在输入\q或者\quit命令后，MySQL会回复提示信息，声明MySQL会话已经终止，完成MySQL服务器的关闭

(7)错误：FATAL: error 1040: Too many connections

max_connections查看

## 查看最大连接数
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

## 查看已使用最大连接数
SHOW VARIABLES LIKE 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 499   |
+----------------------+-------+

这个问题一般有两种处理方案，解决方案非常容易，我们只需要增加max_connections连接数即可。

增加当前会话的mysql最大连接数

SET GLOBAL max_connections = 1000;

上面mysql连接值临时增加到1000，但仅适用于当前会话。一旦我们重新启动mysql服务或重新启动系统，该值将重置为默认值。

永久增加mysql最大连接数

为了永久增加mysql连接数，我们需要编辑mysql配置文件，即/etc/my.cnf。

sudo vim /etc/my.cnf

## 修改
max_connections = 1000

保存文件重启MySQL即可生效。

扩展多少合适

Max_connextions并不是越大越好的，那么如何配置？

方式一

对于提高MySQL的并发，很大程度取决于内存，官方提供了一个关于innodb的内存计算方式：

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections * (sort_buffer_size + read_buffer_size + binlog_cache_size)
+ max_connections * 2MB

方式二

按照比例扩容：

max_used_connections / max_connections * 100% = [85, 90]%

最大使用连接数/最大连接数达到了80%～90%区间，就建议进行优化或者扩容了。

此部分参考来源：MySQL ERROR 1040: Too many connections

三、sysbench

1、sysbench简介

sysbench是跨平台的基准测试工具，支持多线程，支持多种数据库；主要包括以下几种测试：

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

本文主要介绍对数据库性能的测试。

2、sysbench ubuntu 18.04安装（使用apt命令）

Debian/Ubuntu安装：

sudo apt-get install sysbench

查询sysbench软件版本

sysbench --version

3、 离线安装（源码编译安装）

GitHub下载源码包：https://github.com/akopytov/sysbench/tags

4、sysbench语法

执行sysbench –help，可以看到sysbench的详细使用方法。

sysbench的基本语法如下：

sysbench [options]… [testname] [command]

下面说明实际使用中，常用的参数和命令。

（1）command

command是sysbench要执行的命令，包括prepare、run和cleanup，顾名思义，prepare是为测试提前准备数据，run是执行正式的测试，cleanup是在测试完成后对数据库进行清理。

（2）testname

testname指定了要进行的测试，在老版本的sysbench中，可以通过–test参数指定测试的脚本；而在新版本中，–test参数已经声明为废弃，可以不使用–test，而是直接指定脚本。

例如，如下两种方法效果是一样的：

sysbench --test=./tests/include/oltp_legacy/oltp.lua
sysbench ./tests/include/oltp_legacy/oltp.lua

测试时使用的脚本为lua脚本，可以使用sysbench自带脚本，也可以自己开发。对于大多数应用，使用sysbench自带的脚本就足够了。不同版本的sysbench中，lua脚本的位置可能不同，可以自己在sysbench路径下使用find命令搜索oltp.lua。

P.S.：大多数数据服务都是oltp类型的，如果你不了解什么是oltp，那么大概率你的数据服务就是oltp类型的。

（3）options

sysbench的参数有很多，其中比较常用的包括：

MySQL****连接信息参数

• –mysql-host：MySQL服务器主机名，默认localhost；如果在本机上使用localhost报错，提示无法连接MySQL服务器，改成本机的IP地址应该就可以了。
• –mysql-port：MySQL服务器端口，默认3306
• –mysql-user：用户名
• –mysql-password：密码

MySQL****执行参数

• –oltp-test-mode：执行模式，包括simple、nontrx和complex，默认是complex。simple模式下只测试简单的查询；nontrx不仅测试查询，还测试插入更新等，但是不使用事务；complex模式下测试最全面，会测试增删改查，而且会使用事务。可以根据自己的需要选择测试模式。
• –oltp-tables-count：测试的表数量，根据实际情况选择
• –oltp-table-size：测试的表的大小，根据实际情况选择
• –threads：客户端的并发连接数
• –time：测试执行的时间，单位是秒，该值不要太短，可以选择120
• –report-interval：生成报告的时间间隔，单位是秒，如10

5、sysbench使用举例

在执行sysbench时，应该注意：

（1）尽量不要在MySQL服务器运行的机器上进行测试，一方面可能无法体现网络（哪怕是局域网）的影响，另一方面，sysbench的运行（尤其是设置的并发数较高时）会影响MySQL服务器的表现。

（2）可以逐步增加客户端的并发连接数（–thread参数），观察在连接数不同情况下，MySQL服务器的表现；如分别设置为10,20,50,100等。

（3）一般执行模式选择complex即可，如果需要特别测试服务器只读性能，或不使用事务时的性能，可以选择simple模式或nontrx模式。

（4）如果连续进行多次测试，注意确保之前测试的数据已经被清理干净。

下面是sysbench使用的一个例子：

（1）准备数据

sysbench ./tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.10.10 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=123456 \
--oltp-test-mode=complex \
--oltp-tables-count=10 \
--oltp-table-size=100000 \
--threads=10 \
--time=120 \
--report-interval=10 prepare

其中，执行模式为complex，使用了10个表，每个表有10万条数据，客户端的并发线程数为10，执行时间为120秒，每10秒生成一次报告。

登录MySQL，可以查看“sybench_test”新建表和记录数

mysql -u root -p

use sysbench_test;
show tables;
select count(*) from sbtest1;

（2）执行测试

将测试结果导出到文件中，便于后续分析。

sysbench ./tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.10.10 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=123456 \
--oltp-test-mode=complex \
--oltp-tables-count=10 \
--oltp-table-size=100000 \
--threads=10 \
--time=120 \
--report-interval=10 run >> /home/test/mysysbench.log

（3）清理数据

执行完测试后，清理数据，否则后面的测试会受到影响。

sysbench ./tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.10.10 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=123456 cleanup

5、测试结果

测试结束后，查看输出文件，如下所示：

其中，对于我们比较重要的信息包括：

• queries：查询总数及qps

• transactions：事务总数及tps

• Latency-95th percentile：前95%的请求的最大响应时间，本例中是344毫秒，这个延迟非常大，是因为我用的MySQL服务器性能很差；在正式环境中这个数值是绝对不能接受的。

四、建议

下面是使用sysbench的一些建议。

1、在开始测试之前，应该首先明确：应采用针对整个系统的基准测试，还是针对MySQL的基准测试，还是二者都需要。

2、如果需要针对MySQL的基准测试，那么还需要明确精度方面的要求：是否需要使用生产环境的真实数据，还是使用工具生成也可以；前者实施起来更加繁琐。如果要使用真实数据，尽量使用全部数据，而不是部分数据。

3、基准测试要进行多次才有意义。

4、测试时需要注意主从同步的状态。

5、测试必须模拟多线程的情况，单线程情况不但无法模拟真实的效率，也无法模拟阻塞甚至死锁情况。

五、转载来源

• Ubuntu完全卸载与安装Mysql
• sysbench安装及压测MySQL、Oracle
• sysbench如何安装
• 详解MySQL基准测试和sysbench工具
• 在Ubuntu 20.04上安装MySQL教程
• MySQL ERROR 1040: Too many connections

---

其他参考资料

MySQL安装（转载来源：在Ubuntu 20.04上安装MySQL教程）

在Ubuntu中可以使用以下命令安装MySQL

sudo apt update
sudo apt install mysql-server

安装完成后，MySQL服务将自动启动。要验证MySQL服务器正在运行，请输入：

sudo systemctl status mysql

输出应显示该服务已启用并正在运行：

jianguoliu@jianguoliu-VirtualBox:/home$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: e
   Active: active (running) since Fri 2023-03-10 23:07:21 CST; 38s ago
 Main PID: 10905 (mysqld)
    Tasks: 27 (limit: 4915)
   CGroup: /system.slice/mysql.service
           └─10905 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.p

3月 10 23:07:21 jianguoliu-VirtualBox systemd[1]: Starting MySQL Community Serv
3月 10 23:07:21 jianguoliu-VirtualBox systemd[1]: Started MySQL Community Serv

保护MySQL

MySQL安装随附一个名为的脚本mysql_secure_installation，可让您轻松提高数据库服务器的安全性。

调用不带参数的脚本：

sudo mysql_secure_installation

系统将要求您配置VALIDATE PASSWORD PLUGIN用来测试MySQL用户密码强度并提高安全性的密码：

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

密码验证策略分为三个级别：低，中和强。按下y如果你想设置的验证密码插件或任何其他键移动到下一个步骤：

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0

在下一个提示符下，将要求您设置MySQL root用户的密码：

Please set the password for root here.

New password: 

Re-enter new password: 

如果您设置了验证密码插件，该脚本将向您显示新密码的强度。键入y以确认密码：

Estimated strength of the password: 50 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

接下来，将要求您删除匿名用户，限制root用户对本地计算机的访问，删除测试数据库并重新加载特权表。您应该回答y所有问题。

以root身份登录

要从命令行与MySQL服务器进行交互，请使用MySQL客户端实用程序，该实用程序是作为MySQL服务器软件包的依赖项安装的。

在MySQL 8.0上，auth_socket默认情况下，root用户通过插件进行身份验证。

该auth_socket插件对localhost通过Unix套接字文件从进行连接的用户进行身份验证。这意味着您不能通过提供密码来以root用户身份进行身份验证。

要以root用户身份登录到MySQL服务器，请输入：

sudo mysql

将为您提供MySQL Shell，如下所示：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.41-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

如果要使用外部程序（例如phpMyAdmin）以root用户身份登录到MySQL服务器，则有两个选择。

第一个是将身份验证方法从更改auth_socket为mysql_native_password。您可以通过运行以下命令来做到这一点：

mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'very_strong_password';
mysql > FLUSH PRIVILEGES;

推荐的第二个选项是创建一个新的专用管理用户，该用户可以访问所有数据库：

GRANT ALL PRIVILEGES ON *.* TO 'administrator'@'localhost' IDENTIFIED BY 'very_strong_password';

ion and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

如果要使用外部程序（例如phpMyAdmin）以root用户身份登录到MySQL服务器，则有两个选择。

第一个是将身份验证方法从更改`auth_socket`为`mysql_native_password`。您可以通过运行以下命令来做到这一点：

```markup
mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'very_strong_password';
mysql > FLUSH PRIVILEGES;

推荐的第二个选项是创建一个新的专用管理用户，该用户可以访问所有数据库：

GRANT ALL PRIVILEGES ON *.* TO 'administrator'@'localhost' IDENTIFIED BY 'very_strong_password';