如何使用mysqlslap测量MySQL查询性能

介绍

MySQL 带有一个方便的小诊断工具，称为 mysqlslap，它从 5.1.4 版本开始就已经存在。 它是一个基准测试工具，可以帮助 DBA 和开发人员对他们的数据库服务器进行负载测试。

mysqlslap 可以模拟大量客户端连接同时访问数据库服务器。 负载测试参数是完全可配置的，不同测试运行的结果可用于微调数据库设计或硬件资源。

在本教程中，我们将学习如何使用 mysqlslap 对带有一些基本查询的 MySQL 数据库进行负载测试，并了解基准测试如何帮助我们微调这些查询。 在一些基本的演示之后，我们将运行一个相当真实的测试场景，我们创建一个现有数据库的副本进行测试，从日志中收集查询，并从脚本运行测试。

本教程中显示的命令、包和文件在 CentOS 7 上进行了测试。 其他发行版的概念保持不变。

我应该使用什么大小的服务器？

如果您对特定数据库服务器的基准测试感兴趣，您应该在具有相同规格并安装了数据库的精确副本的服务器上进行测试。

如果您想通过本教程进行学习并执行其中的每个命令，我们建议至少使用 2 GB Droplet。 由于本教程中的命令旨在对服务器征税，您可能会发现它们在较小的服务器上超时。

本教程中的示例输出以多种方式生成，以优化教学示例。

第一步——在测试系统上安装 MySQL Community Server

我们将首先在测试数据库上安装 MySQL Community Server 的新副本。 您不应在生产数据库服务器上运行本教程中的任何命令或查询。

这些测试旨在给测试服务器施加压力，并可能导致生产服务器出现延迟或停机。 本教程使用以下环境进行了测试：

• CentOS 7
• sudo 用户 执行的命令
• 推荐 2 GB 液滴； 请记住，本教程中显示的基准测试结果是出于教学目的而生成的，并不反映特定的 DigitalOcean 基准测试

首先，我们将创建一个目录来保存与本教程相关的所有文件。 这将有助于保持整洁。 导航到此目录：

sudo mkdir /mysqlslap_tutorial
cd /mysqlslap_tutorial

接下来，我们将下载 MySQL Community Release yum 存储库。 我们正在下载的存储库适用于适用于 CentOS 7 的 Red Hat Enterprise Linux 7：

sudo wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

接下来，我们可以运行 rpm -Uvh 命令来安装存储库：

sudo rpm -Uvh mysql-community-release-el7-5.noarch.rpm

通过查看 /etc/yum.repos.d 文件夹的内容来检查存储库是否已安装：

sudo ls -l /etc/yum.repos.d

输出应如下所示：

-rw-r--r--. 1 root root 1612 Jul  4 21:00 CentOS-Base.repo
-rw-r--r--. 1 root root  640 Jul  4 21:00 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root 1331 Jul  4 21:00 CentOS-Sources.repo
-rw-r--r--. 1 root root  156 Jul  4 21:00 CentOS-Vault.repo
-rw-r--r--. 1 root root 1209 Jan 29  2014 mysql-community.repo
-rw-r--r--. 1 root root 1060 Jan 29  2014 mysql-community-source.repo

我们还可以检查是否为安装启用了正确的 MySQL 版本：

sudo yum repolist enabled | grep mysql

在我们的例子中，MySQL 5.6 Community Server 是我们想要的：

mysql-connectors-community/x86_64       MySQL Connectors Community           10
mysql-tools-community/x86_64            MySQL Tools Community                 6
mysql56-community/x86_64                MySQL 5.6 Community Server           64

安装 MySQL 社区服务器：

sudo yum install mysql-community-server

该过程完成后，让我们检查已安装的组件：

sudo yum list installed | grep mysql

该列表应如下所示：

mysql-community-client.x86_64      5.6.20-4.el7      @mysql56-community
mysql-community-common.x86_64      5.6.20-4.el7      @mysql56-community
mysql-community-libs.x86_64        5.6.20-4.el7      @mysql56-community
mysql-community-release.noarch     el7-5             installed
mysql-community-server.x86_64      5.6.20-4.el7      @mysql56-community

接下来，我们需要确保 MySQL 守护进程正在运行并且在服务器启动时自动启动。 检查 mysqld 守护进程的状态。

sudo systemctl status mysqld.service

如果它停止，它将显示以下输出：

mysqld.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled)
   Active: inactive (dead)

启动服务：

sudo systemctl start mysqld.service

确保将其配置为在启动时自动启动：

sudo systemctl enable mysqld.service

最后，我们必须保护 MySQL：

sudo mysql_secure_installation

这将带来一系列提示。 我们将在下面显示提示，您应该在 red 中输入答案。 一开始MySQL root用户没有密码，所以只需按Enter。

在出现提示时，您需要提供一个新的安全 root 密码，您应该自己选择该密码。 您应该回答 y 以删除匿名数据库用户帐户、禁用远程 root 登录、重新加载权限表等：

...
Enter current password for root (enter for none):
OK, successfully used password, moving on...
...
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
...
Remove anonymous users? [Y/n] y
 ... Success!
...
Disallow root login remotely? [Y/n] y
 ... Success!
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
...
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...

我们现在可以连接到数据库并确保一切正常：

sudo mysql -h localhost -u root -p

在提示符处输入您刚刚设置的 root MySQL 密码。 您应该看到如下输出：

Enter password:
Welcome to the MySQL monitor....

mysql>

在 mysql> 提示符下，输入命令查看所有数据库：

show databases;

您应该看到如下输出：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

最后，让我们创建一个名为 sysadmin 的用户帐户。 此帐户将用于登录 MySQL，而不是 root 用户。 请务必将 mypassword 替换为您自己的该用户密码。 我们还将授予此帐户的所有权限。 在 MySQL 提示符下，输入以下命令：

create user sysadmin identified by 'mypassword';

输出：

Query OK, 0 rows affected (0.00 sec)

授予权限：

grant all on *.* to sysadmin;

输出：

Query OK, 0 rows affected (0.01 sec)

现在让我们回到操作系统提示符：

quit;

输出：

Bye

第二步——安装示例数据库

接下来，我们需要安装一个示例数据库进行测试。 这个数据库被称为 employees，它是 可以从 MySQL 网站 自由访问。 该数据库也可以从 Launchpad 下载。 员工数据库由 Patrick Crews 和 Giuseppe Maxia 开发。 原始数据由 Siemens Corporate Research 的 Fusheng Wang 和 Carlo Zaniolo 创建。

我们选择员工数据库是因为它具有大型数据集。 数据库结构很简单：只有六张表； 但它包含的数据有超过 3,000,000 条员工记录（薪水表本身有近 300 万行）。 这将帮助我们模拟更真实的生产工作负载。

首先，让我们确保我们在 /mysqlslap_tutorial 目录中：

cd /mysqlslap_tutorial

下载最新版本的员工样本数据库：

sudo wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2

安装 bzip2 工具，以便我们可以解压缩存档：

sudo yum install bzip2

解压缩数据库存档。 这需要一分钟。 我们在这里分两步进行：

sudo bzip2 -dfv employees_db-full-1.0.6.tar.bz2
sudo tar -xf employees_db-full-1.0.6.tar

内容将被解压缩到一个名为 employees_db 的单独的新目录中。 我们需要导航到该目录以运行安装数据库的查询。 内容包括 README 文档、更改日志、数据转储以及将创建数据库结构的各种 SQL 查询文件：

cd employees_db
ls -l

这是您应该看到的：

-rw-r--r--. 1 501 games       752 Mar 30  2009 Changelog
-rw-r--r--. 1 501 games      6460 Oct  9  2008 employees_partitioned2.sql
-rw-r--r--. 1 501 games      7624 Feb  6  2009 employees_partitioned3.sql
-rw-r--r--. 1 501 games      5660 Feb  6  2009 employees_partitioned.sql
-rw-r--r--. 1 501 games      3861 Nov 28  2008 employees.sql
-rw-r--r--. 1 501 games       241 Jul 30  2008 load_departments.dump
-rw-r--r--. 1 501 games  13828291 Mar 30  2009 load_dept_emp.dump
-rw-r--r--. 1 501 games      1043 Jul 30  2008 load_dept_manager.dump
-rw-r--r--. 1 501 games  17422825 Jul 30  2008 load_employees.dump
-rw-r--r--. 1 501 games 115848997 Jul 30  2008 load_salaries.dump
-rw-r--r--. 1 501 games  21265449 Jul 30  2008 load_titles.dump
-rw-r--r--. 1 501 games      3889 Mar 30  2009 objects.sql
-rw-r--r--. 1 501 games      2211 Jul 30  2008 README
-rw-r--r--. 1 501 games      4455 Mar 30  2009 test_employees_md5.sql
-rw-r--r--. 1 501 games      4450 Mar 30  2009 test_employees_sha.sql

运行以下命令连接 MySQL 并运行 employees.sql 脚本，该脚本将创建数据库并加载数据：

sudo mysql -h localhost -u sysadmin -p -t < employees.sql

在提示符处，输入您在上一节中为 sysadmin MySQL 用户创建的密码。

流程输出将如下所示。 运行大约需要一分钟：

+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+

现在您可以登录 MySQL 并运行一些基本查询来检查数据是否已成功导入。

sudo mysql -h localhost -u sysadmin -p

输入 sysadmin MySQL 用户的密码。

检查新的 employees 数据库的数据库列表：

show databases;

输出：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

使用员工数据库：

use employees;

检查其中的表格：

show tables;

输出：

+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.01 sec)

如果您愿意，您可以查看每个表格的详细信息。 我们将只检查 titles 表的信息：

describe titles;

输出：

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no    | int(11)     | NO   | PRI | NULL    |       |
| title     | varchar(50) | NO   | PRI | NULL    |       |
| from_date | date        | NO   | PRI | NULL    |       |
| to_date   | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

检查条目数：

mysql> select count(*) from titles;
+----------+
| count(*) |
+----------+
|   443308 |
+----------+
1 row in set (0.14 sec)

检查您想要的任何其他数据。 我们现在可以回到我们的操作系统提示符：

quit;

第三步——使用 mysqlslap

我们现在可以开始使用 mysqlslap。 mysqlslap 可以从常规的 shell 提示符中调用，因此无需显式登录 MySQL。 不过，对于本教程，我们将打开另一个与 Linux 服务器的终端连接，并从那里使用我们之前创建的 sysadmin 用户启动一个新的 MySQL 会话，这样我们就可以在 MySQL 中检查和更新一些内容容易地。 因此，我们将使用 sudo 用户打开一个提示，并将一个提示登录到 MySQL。

在我们进入特定的测试命令之前，您可能想看看这个最有用的 mysqlslap 选项列表。 这可以帮助您以后设计自己的 mysqlslap 命令。

选项	这是什么意思
-用户	MySQL 连接数据库服务器的用户名
-密码	用户帐户的密码。 最好在命令行中留空
-主持人	MySQL 数据库服务器名称
-港口	不使用默认连接 MySQL 的端口号
——并发	mysqlslap 将模拟的同时客户端连接数
——迭代	测试查询将运行的次数
--创建模式	运行测试的模式
-询问	要执行的查询。 这可以是 SQL 查询字符串或 SQL 脚本文件的路径
-创造	创建表的查询。 同样，这可以是查询字符串或 SQL 文件的路径
--分隔符	用于分隔多个 SQL 语句的分隔符
-引擎	要使用的 MySQL 数据库引擎（例如，InnoDB）
--自动生成-sql	让 MySQL 使用自己的自动生成的 SQL 命令执行负载测试

用例：使用自动生成的 SQL 和数据进行基准测试

我们将从使用 mysqlslap 的 auto-generate-sql 功能开始。 当我们使用自动生成的 SQL 时，mysqlslap 将创建一个单独的临时数据库——恰当地称为 mysqlslap。 该数据库将有一个简单的表，其中包含一个整数和一个 varchar 类型的列，其中填充了示例数据。 这是检查数据库服务器整体性能的一种快速简便的方法。

我们首先测试单个客户端连接，对自动生成的 SQL 进行一次迭代：

sudo mysqlslap --user=sysadmin --password --host=localhost  --auto-generate-sql --verbose

输出应如下所示：

Benchmark
        Average number of seconds to run all queries: 0.009 seconds
        Minimum number of seconds to run all queries: 0.009 seconds
        Maximum number of seconds to run all queries: 0.009 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

mysqlslap 报告了一些基准测试统计信息，如输出所示。 它报告运行查询所需的平均、最小和最大秒数。 我们还可以看到，用于此负载测试的客户端连接数为 1。

现在尝试 50 个并发连接，并让自动生成的查询运行 10 次：

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=10 --auto-generate-sql --verbose

这个命令的意思是五十个模拟的客户端连接将同时抛出相同的测试查询，并且这个测试将重复十次。

输出向我们展示了负载增加的显着差异：

Benchmark
        Average number of seconds to run all queries: 0.197 seconds
        Minimum number of seconds to run all queries: 0.168 seconds
        Maximum number of seconds to run all queries: 0.399 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

请注意 Number of clients running queries: 字段现在显示的值为 50。 每个客户端的平均查询数为零。

自动生成的 SQL 会创建一个包含两个字段的简单表。 在大多数生产环境中，表结构会比这大得多。 我们可以通过向测试表中添加额外的字段来指示 mysqlslap 模拟这一点。 为此，我们可以使用两个新参数：--number-char-cols 和 --number-int-cols。 这些参数指定要添加到测试表的 varchar 和 int 类型的列数。

在以下示例中，我们使用自动生成的 SQL 查询进行测试，该查询针对具有 5 个数字列和 20 个字符类型列的表运行。 我们还模拟了 50 个客户端连接，我们希望测试重复 100 次：

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=100 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql --verbose

这应该需要更长的时间。 在测试运行时，我们可以切换到另一个正在运行 MySQL 会话的终端窗口，看看发生了什么。 请注意，如果您等待太久，测试将完成，您将无法看到测试数据库。

在 MySQL 提示符下：

show databases;

注意 mysqlslap 数据库：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| mysqlslap          |
| performance_schema |
+--------------------+
5 rows in set (0.01 sec)

如果需要，您可以检查测试数据库中的表； 它被称为 t1。

再次检查您的另一个终端窗口。 测试完成后，您会发现随着负载的增加，性能下降得更多：

Benchmark
        Average number of seconds to run all queries: 0.695 seconds
        Minimum number of seconds to run all queries: 0.627 seconds
        Maximum number of seconds to run all queries: 1.442 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

返回您的 MySQL 终端会话。 我们可以看到 mysqlslap 已经删除了它的一次性数据库。 在 MySQL 提示符下：

show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

用例：使用自定义查询进行基准测试

如果您正在评估服务器的物理资源，那么自动生成的 SQL 是很好的选择。 当您想找到给定系统可以承受的负载级别时，它很有用。

但是，当您想要对特定的依赖于数据库的应用程序的性能进行故障排除时，您需要测试对真实数据的真实查询。 这些查询可能来自您的 Web 或应用程序服务器。

现在，我们假设您知道要测试的特定查询。 在下一节中，我们将向您展示一种查找在您的服务器上运行的查询的方法。

我们将从内联查询开始。 您可以使用 –query 选项向 mysqlslap 提供内联查询。 SQL 语句中不能有换行符，需要用分号(;) 分隔。 查询也需要用双引号引起来。

在下面的代码片段中，我们正在对 dept_emp 表运行一个简单的查询。 dept_emp 表有超过三十万条记录。 请注意我们如何使用 –create-schema 选项指定 employees 数据库：

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=10 --create-schema=employees --query="SELECT * FROM dept_emp;" --verbose

这将需要一段时间才能运行。 一两分钟后，您应该会收到这样的性能基准：

Benchmark
        Average number of seconds to run all queries: 18.486 seconds
        Minimum number of seconds to run all queries: 15.590 seconds
        Maximum number of seconds to run all queries: 28.381 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1

（注意：如果此查询挂起超过十分钟或没有给出任何输出，您应该使用 –concurrency 和/或 –iterations 的较小数字再试一次，或在更大的服务器上尝试。）

接下来，我们将在 –query 参数中使用多个 SQL 语句。 在下面的示例中，我们用分号终止每个查询。 mysqlslap 知道我们正在使用许多单独的 SQL 命令，因为我们指定了 –delimiter 选项：

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=20 --iterations=10 --create-schema=employees --query="SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" --delimiter=";" --verbose

该测试使用相同数量的连接和相同数量的迭代。 但是，多个 SELECT 语句的性能逐渐变慢（平均 23.8 秒 vs. 18.486 秒）：

Benchmark
        Average number of seconds to run all queries: 23.800 seconds
        Minimum number of seconds to run all queries: 22.751 seconds
        Maximum number of seconds to run all queries: 26.788 seconds
        Number of clients running queries: 20
        Average number of queries per client: 5

生产 SQL 语句可能很复杂。 将复杂的 SQL 语句添加到脚本中比将其键入以进行测试更容易。 因此，我们可以指示 mysqlslap 从脚本文件中读取查询。

为了说明这一点，让我们从 SQL 命令创建一个脚本文件。 我们可以使用下面的代码片段来创建这样一个文件：

sudo echo "SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" > ~/select_query.sql

sudo cp ~/select_query.sql /mysqlslap_tutorial/

select_query.sql 文件现在包含所有五个 SELECT 语句。

由于这个脚本有多个查询，我们可以引入一个新的测试概念。 mysqlslap 可以 并行化 查询。 我们可以通过指定每个测试客户端应该执行的查询数来做到这一点。 mysqlslap 使用 –number-of-queries 选项执行此操作。 因此，如果我们有 50 个连接和 1000 个查询要运行，每个客户端将执行大约 20 个查询。

最后，我们还可以使用 –debug-info 开关，它可以指示使用的计算资源。

在下面的代码片段中，我们要求 mysqlslap 使用我们刚刚创建的脚本文件。 我们还指定了 number-of-queries 参数。 该过程将重复两次，我们希望在输出中包含调试信息：

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=20 --number-of-queries=1000 --create-schema=employees --query="/mysqlslap_tutorial/select_query.sql" --delimiter=";" --verbose --iterations=2 --debug-info

这条命令完成后，我们可以看到一些有趣的结果：

Benchmark
        Average number of seconds to run all queries: 217.151 seconds
        Minimum number of seconds to run all queries: 213.368 seconds
        Maximum number of seconds to run all queries: 220.934 seconds
        Number of clients running queries: 20
        Average number of queries per client: 50


User time 58.16, System time 18.31
Maximum resident set size 909008, Integral resident set size 0
Non-physical pagefaults 2353672, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 102785, Involuntary context switches 43

在这里，在我们的 MySQL 实例中运行所有查询的平均秒数是 217 秒，几乎是 4 分钟。 虽然这肯定受到我们虚拟机可用的 RAM 和 CPU 数量的影响，但也是由于来自中等数量的客户端连接的大量查询重复两次。

我们可以看到有大量的非物理页面错误。 当在内存中找不到数据并且系统必须从磁盘上的交换文件中获取数据时，就会发生页面错误。 输出还显示与 CPU 相关的信息。 在这种情况下，我们会看到大量的上下文切换。

用例：实际基准测试场景和捕获实时查询

到目前为止，在我们的示例中，我们一直在对原始员工数据库运行查询。 这是 DBA 肯定不希望您做的事情。 这是有充分理由的。 您不想增加生产数据库的负载，也不想运行可能会删除、更新或将数据插入生产表的测试查询。

我们将向您展示如何备份生产数据库并将其复制到测试环境。 在此示例中，它位于同一台服务器上，但理想情况下，您会将其复制到具有相同硬件容量的单独服务器上。

更重要的是，我们将向您展示如何从生产数据库实时记录查询并将它们添加到测试脚本中。 也就是说，您将从生产数据库获取查询，但针对测试数据库运行测试。

一般步骤如下，你可以将它们用于任何mysqlslap测试：

1. 将生产数据库复制到测试环境。 2。 配置 MySQL 以记录和捕获生产数据库上的所有连接请求和查询。 3. 模拟您尝试测试的用例。 例如，如果您运行购物车，您应该购买一些东西来触发应用程序中所有适当的数据库查询。 4. 关闭查询记录。 5. 查看查询日志并列出要测试的查询。 6. 为每个要测试的查询创建一个测试文件。 7. 运行测试。 8. 使用输出来提高数据库性能。

首先，让我们创建员工数据库的备份。 我们将为它的备份创建一个单独的目录：

sudo mkdir /mysqlslap_tutorial/mysqlbackup

cd /mysqlslap_tutorial/mysqlbackup

创建备份并将其移动到新目录：

sudo mysqldump --user sysadmin --password --host localhost employees > ~/employees_backup.sql

sudo cp ~/employees_backup.sql /mysqlslap_tutorial/mysqlbackup/

转到您的 MySQL 测试服务器。 创建 employees_backup 数据库：

CREATE DATABASE employees_backup;

此时，如果您使用单独的服务器进行测试，则应将 employees_backup.sql 文件复制到其中。 从您的主终端会话中，将备份数据导入 employees_backup 数据库：

sudo mysql -u sysadmin -p employees_backup < /mysqlslap_tutorial/mysqlbackup/employees_backup.sql

在您的 生产 MySQL 数据库服务器 上，启用 MySQL 常规查询日志并为其提供文件名。 通用查询日志捕获 MySQL 数据库实例的连接、断开连接和查询活动。

SET GLOBAL general_log=1, general_log_file='capture_queries.log';

现在运行您要在生产 MySQL 服务器上测试的查询。 在此示例中，我们将从命令行运行查询。 但是，您可能希望从应用程序生成查询，而不是直接运行它们。 如果您想要测试一个缓慢的进程或网站页面，您应该运行该进程或立即访问该网页。 例如，如果您正在运行购物车，您可能希望现在完成结帐过程，这应该会触发数据库服务器上的所有相应查询。

这是我们将在生产 MySQL 服务器上运行的查询。 首先使用正确的数据库：

USE employees;

现在运行查询：

SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date;

预期输出：

489903 rows in set (4.33 sec)

查询完成后，我们将关闭常规日志记录：

SET GLOBAL general_log=0;

请注意，如果您保持登录状态，查询将继续添加到日志中，这可能会使测试更加困难。 因此，请确保在完成测试后立即禁用日志。 让我们检查一下日志文件是在 /var/lib/mysql 目录中创建的：

sudo ls -l /var/lib/mysql/capt*

-rw-rw----. 1 mysql mysql 861 Sep 24 15:09 /var/lib/mysql/capture_queries.log

让我们将此文件复制到我们的 MySQL 测试目录。 如果您使用单独的服务器进行测试，请将其复制到该服务器。

sudo cp /var/lib/mysql/capture_queries.log /mysqlslap_tutorial/

这个日志文件中应该有相当多的数据。 在这个例子中，我们想要的查询应该接近尾声。 检查文件的最后一部分：

sudo tail /mysqlslap_tutorial/capture_queries.log

预期输出：

      6294 Query show databases
         6294 Query show tables
         6294 Field List    departments 
         6294 Field List    dept_emp 
         6294 Field List    dept_manager 
         6294 Field List    employees 
         6294 Field List    salaries 
         6294 Field List    titles 
140930 15:34:52  6294 Query SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date
140930 15:35:06  6294 Query SET GLOBAL general_log=0

此日志显示 SQL 命令及其时间戳。 文件末尾附近的 SQL SELECT 语句是我们感兴趣的。 它应该与我们在生产数据库上运行的命令完全相同，因为那是我们捕获它的地方。

在此示例中，我们已经知道查询。 但是，在生产环境中，此方法对于查找您可能不一定知道在您的服务器上运行的查询非常有用。

请注意，如果您在记录时运行或触发了不同的查询，则此文件看起来会完全不同。 在实际场景中，该文件可能会被来自所有不同连接的数百个条目所淹没。 您的目标是找到导致瓶颈的一个或多个查询。 您可以首先列出包含文本 Query 的每一行。 然后，您将获得一个列表，其中列出了测试期间在您的数据库上运行了哪些查询。

对于您要测试的每个查询，将其复制到以 .sql 扩展名结尾的文件中。

例如：

sudo vi /mysqlslap_tutorial/capture_queries.sql

内容应该是您要测试的 MySQL 查询，没有任何换行符，并且末尾没有分号：

SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date

接下来，确保查询结果没有被缓存。 回到您的 测试 MySQL 会话 。 运行以下命令：

RESET QUERY CACHE;

现在是时候使用脚本文件运行 mysqlslap 实用程序了。 确保在 –query 参数中使用正确的脚本文件名。 我们将只使用十个并发连接并重复测试两次。 从您的 测试服务器 运行它：

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose

在我们的系统中，基准输出如下所示：

Benchmark
        Average number of seconds to run all queries: 68.692 seconds
        Minimum number of seconds to run all queries: 59.301 seconds
        Maximum number of seconds to run all queries: 78.084 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

那么我们如何改进这个基准呢？

您需要对 MySQL 查询有一定程度的熟悉才能评估查询的作用。

回顾查询，我们可以看到它正在跨多个表执行许多连接。 该查询显示了员工的工作历史，在此过程中，它通过 emp_no 字段连接不同的表。 加入也是使用dept_no字段，但由于部门记录很少，我们就忽略了。 由于数据库中有许多 emp_no 条目，因此假设在 emp_no 字段上创建索引可以改进查询是合乎逻辑的。

通过一些练习，一旦您找到了对服务器造成负担的查询（这是 mysqlslap 帮助解决的部分！），您将能够根据您对 MySQL 和数据库的了解对查询进行评估。

接下来，您可以尝试改进您的数据库或正在其上执行的查询。

在我们的例子中，让我们添加上面提到的索引。 我们将在 emp_no 上创建三个索引。 将在 employees 表中的 emp_no 字段上创建一个索引，将在 dept_emp 中的 emp_no 字段上创建另一个索引表，最后一个将在 titles 表的 emp_no 字段上创建。

让我们进入我们的测试 MySQL 会话并执行以下命令：

USE employees_backup;

CREATE INDEX employees_empno ON employees(emp_no);

CREATE INDEX dept_emp_empno ON dept_emp(emp_no);

CREATE INDEX titles_empno ON titles(emp_no);

回到我们在测试服务器上的主终端窗口，如果我们使用相同的参数执行 mysqlslap，我们将看到基准测试中的不同：

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose

Benchmark
        Average number of seconds to run all queries: 55.869 seconds
        Minimum number of seconds to run all queries: 55.706 seconds
        Maximum number of seconds to run all queries: 56.033 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

我们可以看到执行查询的平均、最小和最大时间立即得到改善。 查询现在在 55 秒内执行，而不是平均 68 秒。 对于相同的负载，这是 13 秒的改进。

由于此数据库更改在测试环境中产生了良好的结果，您现在可以考虑将其推广到您的生产数据库服务器，但请记住，数据库更改通常会在优缺点之间进行权衡。

您可以使用从日志中收集的所有查询重复测试命令和改进的过程。

故障排除 - mysqlslap 不显示输出

如果您运行测试命令并且没有得到任何输出，这很好地表明您的服务器资源可能已被用尽。 症状可能包括缺少 Benchmark 输出，或类似 mysqlslap: Error when storing result: 2013 Lost connection to MySQL server during query 的错误。

您可能希望在 –concurrency 或 –iterations 参数中使用较小的数字再次尝试测试。 或者，您可以尝试升级您的测试服务器环境。

这是查找数据库服务器容量外部限制的好方法。

结论

mysqlslap 是一个简单、轻量级的工具，易于使用，并且与 MySQL 数据库引擎原生集成。 它适用于从 5.1.4 版开始的所有 MySQL 版本。

在本教程中，我们了解了如何使用 mysqlslap 及其各种选项并使用示例数据库进行操作。 您可以从 MySQL 站点下载其他示例数据库并使用它们进行练习。 正如我们之前提到的，请不要在生产数据库服务器上运行测试。

本教程中的最后一个用例仅涉及一个查询。 虽然我们通过向所有三个表添加额外的索引来在一定程度上提高了该查询的性能，但在现实生活中这个过程可能并不那么简单。 添加额外索引有时会降低系统性能，DBA 通常需要权衡添加额外索引的好处与可能产生的性能成本。

现实生活中的测试场景更加复杂，但这应该为您提供开始测试和提高数据库性能的工具。