如何衡量与mysqlslap MySQL查询性能-系统运维-优客志

介绍

MySQL带有一个方便的小诊断工具称为mysqlslap这一直以来各地5.1.4版本。它是一个基准测试工具，可以帮助DBA和开发人员加载测试他们的数据库服务器。

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

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

该命令，包和本教程中所示的文件在CentOS 7进行了测试。该概念是相同的其他发行。

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

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

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

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

第一步 - 在测试系统上安装MySQL社区服务器

我们将通过一个测试数据库上安装的MySQL社区服务器的新副本开始， 你不应该从本教程生产数据库服务器上运行的任何命令或查询。

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

CentOS 7
命令被执行sudo的用户
2 GBDroplet推荐; 请记住，本教程中显示的基准测试结果是为了教学目的而生成的，并不反映特定的DigitalOcean基准测试

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

sudo mkdir /mysqlslap_tutorial
cd /mysqlslap_tutorial

接下来，我们将下载MySQL社区版本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社区服务器是我们想要的东西：

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

这将产生一系列提示。我们会告诉提示下面，你应该在红色输入答案。在开始的时候没有为MySQL的root用户没有密码，所以只需按Enter键 。

在提示下，您需要提供一个新的安全根密码，您应该自己选择。你应该回答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

输入您刚才在提示符下设置的根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)

最后，让我们创建一个名为系统管理员的用户帐户。此帐户将用于登录到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

第二步 - 安装示例数据库

接下来，我们需要安装一个示例数据库进行测试。这个数据库被称为员工，它是从MySQL网站可以自由进出。该数据库也可以从下载启动板。员工数据库由Patrick Crews和Giuseppe Maxia开发。原始数据由Fusheng Wang和Carlo Zaniolo在西门子公司研究所创建。

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

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

cd /mysqlslap_tutorial

下载最新版本的employees示例数据库：

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

在提示符下，输入您的系统管理员 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

输入系统管理员 MySQL用户的密码。

检查为新员工数据库的数据库列表：

show databases;

输出：

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

使用employees数据库：

use employees;

检查其中的表：

show tables;

输出：

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

如果需要，您可以检查每个表的详细信息。我们只检查标题表的信息：

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服务器的另一端连接，并从那里开始与我们之前创建的管理员用户一个新的MySQL会话，因此我们可以在MySQL中更容易检查和更新了一些东西。所以，我们将有一个提示与我们的sudo用户打开，一个提示登录MySQL。

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

选项	这是什么意思
- 用户	连接到数据库服务器的MySQL用户名
- 密码	用户帐户的密码。最好在命令行中留空
- 主办	MySQL数据库服务器名称
- 港口	如果未使用默认值，则用于连接到MySQL的端口号
- 并发	同时客户端连接数mysqlslap将会模拟
--iterations	将运行测试查询的次数
--create-schema	将运行查询的数据库
--query	要执行的查询。这可以是SQL查询字符串或SQL脚本文件的路径
- 创建	查询创建表。同样，这可以是查询字符串或SQL文件的路径
--delimiter	用于分隔多个SQL语句的定界符
- 发动机	要使用的MySQL数据库引擎（例如，InnoDB）
--auto-generate-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

注意运行查询的客户端数量如何：场现在显示的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语句中不能有换行符，它们需要用分号（;）分隔。查询也需要用双引号括起来。

在下面的代码片段，我们正在运行针对部门emp表的简单查询。 `deptemp`表有超过三十万条记录。请注意我们是如何规定的--create-schema选项员工数据库：

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知道我们使用的是因为我们指定的对齐和美化--delimiter选项许多单独的SQL命令：

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秒对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-的查询的选项。因此，如果我们有50个连接和1000个查询要运行，每个客户端将执行大约20个查询。

最后，我们还可以使用--debug-信息交换，这会给我们所使用的计算资源的指示。

在下面的代码片段中，我们要求mysqlslap使用我们刚刚创建的脚本文件。我们还号指定的查询参数。该过程将重复两次，我们希望在输出中提供调试信息：

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;

在这一点上，如果你使用的是单独的服务器进行测试，您应该复制的员工backup.sql文件交给它。从你的主终端会话，导入备份数据到数据库employeesbackup：

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语句是我们感兴趣的，应该是完全一样的，我们跑了生产数据库命令，因为这是我们捕获它。

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

请注意，如果您在记录时运行或触发了不同的查询，此文件将看起来完全不同。在实际情况下，该文件可能被来自所有不同连接的数百个条目淹没。您的目标是找到导致瓶颈的查询或查询。您可以通过每一个包括文本查询行的名单开始。然后，您将有一个列表，在测试期间在数据库上运行什么查询。

对于要测试的每个查询，将其复制成与结尾的文件.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 没有现场参加不同的表。它也使用DEPTNO字段的加盟，但因为只有少数部门的记录，我们会忽略这一点。由于有许多EMP数据库中没有条目，这是合乎逻辑的假设，关于EMPNO字段创建索引可以提高查询。

通过一些小练习，一旦你找到了对服务器计费的查询（这是mysqlslap帮助的部分），你就可以根据你对MySQL和数据库的了解对查询进行评估。

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

在我们的例子中，让我们添加上面提到的索引。我们将在EMP创建三个指标无。其中一个指数将在雇员表中的EMPNO字段创建，另一个索引将在EMP中的deptemp表中创建无域，最后一个将在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

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

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

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

疑难解答 - mysqlslap不显示输出

如果运行测试命令并且没有获得任何输出，这是一个很好的指示，您的服务器资源可能超出。症状可能包括缺少基准输出，或类似的错误mysqlslap: Error when storing result: 2013 Lost connection to MySQL server during query 。

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

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

结论

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

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

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

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

如何衡量与mysqlslap MySQL查询性能

介绍

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

第一步 - 在测试系统上安装MySQL社区服务器

第二步 - 安装示例数据库

第三步 - 使用mysqlslap

用例：使用自动生成的SQL和数据进行标准化

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

使用案例：实际基准场景和捕获实时查询

疑难解答 - mysqlslap不显示输出

结论

相关推荐

推荐云主机，建站更安全

热度排行

热门标签

回顶部

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏