介绍
MySQL 配备了一款名为 mysqlslap的实用小型诊断工具,自 5.1.4 版本以来一直在使用。
mysqlslap 可以模拟大量的客户端连接同时打击数据库服务器. 负载测试参数完全可配置,不同测试运行的结果可用于精简数据库设计或硬件资源。
在本教程中,我们将学习如何使用mysqlslap来加载测试一个MySQL数据库,使用一些基本查询,并看看基准测量如何帮助我们精简这些查询. 经过一些基本演示,我们将通过一个相当现实的测试场景运行,我们将创建一个现有数据库的副本进行测试,从日志中收集查询,并从脚本中运行测试。
本教程中显示的命令、包和文件在 CentOS 7上进行了测试,其他发行版的概念仍然相同。
我应该使用什么样的服务器大小?
如果您对特定数据库服务器感兴趣,则应在具有相同规格的服务器上进行测试,并且安装了数据库的精确副本。
如果你想通过这个教程来学习,并执行其中每个命令,我们建议至少有一个 2 GB Droplet. 由于本教程中的命令旨在对服务器进行征税,你可能会发现他们在较小的服务器上耗费时间。
本教程中的样本输出以各种方式生产,以优化教学的示例。
第一步:在测试系统上安装 MySQL 社区服务器
我们将从测试数据库中安装 MySQL 社区服务器的新副本开始。 **您不应该在生产数据库服务器上运行本教程中的任何命令或查询。
这些测试旨在强调测试服务器,并可能导致生产服务器的延迟或停机时间。
- CentOS 7
- 由 sudo 使用者
- 2 GB Droplet 推荐的命令;请记住,本教程中显示的基准结果是用于教学目的,并且不反映特定的 DigitalOcean基准
首先,我们将创建一个目录,以保留与本教程相关的所有文件. 这将有助于保持事情顺利。
1sudo mkdir /mysqlslap_tutorial
2cd /mysqlslap_tutorial
接下来,我们将下载 MySQL Community Release yum 存储库. 我们正在下载的存储库适用于 Red Hat Enterprise Linux 7 适用于 CentOS 7:
1sudo wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
接下来,我们可以运行rpm -Uvh命令来安装存储库:
1sudo rpm -Uvh mysql-community-release-el7-5.noarch.rpm
通过查看 `/etc/yum.repos.d 文件夹的内容来检查存储库是否已安装:
1sudo ls -l /etc/yum.repos.d
输出应该是这样的:
1-rw-r--r--. 1 root root 1612 Jul 4 21:00 CentOS-Base.repo
2-rw-r--r--. 1 root root 640 Jul 4 21:00 CentOS-Debuginfo.repo
3-rw-r--r--. 1 root root 1331 Jul 4 21:00 CentOS-Sources.repo
4-rw-r--r--. 1 root root 156 Jul 4 21:00 CentOS-Vault.repo
5-rw-r--r--. 1 root root 1209 Jan 29 2014 mysql-community.repo
6-rw-r--r--. 1 root root 1060 Jan 29 2014 mysql-community-source.repo
我们还可以检查正确的MySQL版本是否启用安装:
1sudo yum repolist enabled | grep mysql
在我们的案例中,MySQL 5.6 社区服务器是我们想要的:
1mysql-connectors-community/x86_64 MySQL Connectors Community 10
2mysql-tools-community/x86_64 MySQL Tools Community 6
3mysql56-community/x86_64 MySQL 5.6 Community Server 64
安装 MySQL 社区服务器:
1sudo yum install mysql-community-server
一旦过程完成,让我们检查包含的组件:
1sudo yum list installed | grep mysql
名单应该是这样的:
1mysql-community-client.x86_64 5.6.20-4.el7 @mysql56-community
2mysql-community-common.x86_64 5.6.20-4.el7 @mysql56-community
3mysql-community-libs.x86_64 5.6.20-4.el7 @mysql56-community
4mysql-community-release.noarch el7-5 installed
5mysql-community-server.x86_64 5.6.20-4.el7 @mysql56-community
接下来,我们需要确保MySQL DAEMON运行并在服务器启动时自动启动。
1sudo systemctl status mysqld.service
如果停止,它将显示此输出:
1mysqld.service - MySQL Community Server
2 Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled)
3 Active: inactive (dead)
开始服务:
1sudo systemctl start mysqld.service
确保它已配置为在启动时自动启动:
1sudo systemctl enable mysqld.service
最后,我们必须确保MySQL:
1sudo mysql_secure_installation
这将带来一系列提示,我们将显示下面的提示,你应该在红色中输入的答案. 在开始时,MySQL root用户没有密码,所以只需按 Enter。
在提示中,你需要提供一个新的安全的根密码,你应该自己选择。你应该回答 y删除匿名数据库用户帐户,禁用远程根登录,重新加载特权表等:
1...
2Enter current password for root (enter for none):
3OK, successfully used password, moving on...
4...
5Set root password? [Y/n] y
6New password:
7Re-enter new password:
8Password updated successfully!
9Reloading privilege tables..
10 ... Success!
11...
12Remove anonymous users? [Y/n] y
13 ... Success!
14...
15Disallow root login remotely? [Y/n] y
16 ... Success!
17Remove test database and access to it? [Y/n] y
18 - Dropping test database...
19 ... Success!
20...
21Reload privilege tables now? [Y/n] y
22 ... Success!
23Cleaning up...
现在我们可以连接到数据库并确保一切正常工作:
1sudo mysql -h localhost -u root -p
输入你刚刚设置的 root MySQL 密码,你应该看到输出如下:
1Enter password:
2Welcome to the MySQL monitor....
3
4mysql>
在 mysql> 提示中,输入命令查看所有数据库:
1show databases;
你应该看到的输出如下:
1+--------------------+
2| Database |
3+--------------------+
4| information_schema |
5| mysql |
6| performance_schema |
7+--------------------+
83 rows in set (0.00 sec)
最后,让我们创建一个名为sysadmin的用户帐户。这个帐户将被用来登录MySQL而不是根用户。 请确保为这个用户用自己的密码代替mypassword。
1create user sysadmin identified by 'mypassword';
输出:
1Query OK, 0 rows affected (0.00 sec)
授予特权:
1grant all on *.* to sysadmin;
输出:
1Query OK, 0 rows affected (0.01 sec)
现在让我们回到操作系统提示:
1quit;
输出:
1Bye
步骤二:安装样本数据库
接下来,我们需要安装一个样本数据库来进行测试,这个数据库叫做 employees,它可以从MySQL网站免费访问(http://dev.mysql.com/doc/index-other.html)。该数据库也可以从Launchpad下载(https://launchpad.net/test-db/)。员工数据库由Patrick Crews和Giuseppe Maxia开发,原始数据由Fusheng Wang和Carlo Zaniolo在西门子企业研究公司创建。
数据库的结构足够简单:它只有六个表;但它包含的数据有超过3000万名员工记录(工资表本身有近300万行)。
首先,让我们确保我们在 /mysqlslap_tutorial 目录中:
1cd /mysqlslap_tutorial
下载员工样本数据库的最新版本:
1sudo wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
安装 bzip2 工具,以便我们可以卸载档案:
1sudo yum install bzip2
解开数据库档案. 这将需要一分钟。我们在这里在两个步骤中完成:
1sudo bzip2 -dfv employees_db-full-1.0.6.tar.bz2
2sudo tar -xf employees_db-full-1.0.6.tar
内容将不压缩到一个单独的新目录,名为 employees_db. 我们需要导航到该目录来运行安装数据库的查询. 内容包括 README 文档,更改日志,数据垃圾,以及创建数据库结构的各种 SQL 查询文件:
1cd employees_db
2ls -l
这里是你应该看到的:
1-rw-r--r--. 1 501 games 752 Mar 30 2009 Changelog
2-rw-r--r--. 1 501 games 6460 Oct 9 2008 employees_partitioned2.sql
3-rw-r--r--. 1 501 games 7624 Feb 6 2009 employees_partitioned3.sql
4-rw-r--r--. 1 501 games 5660 Feb 6 2009 employees_partitioned.sql
5-rw-r--r--. 1 501 games 3861 Nov 28 2008 employees.sql
6-rw-r--r--. 1 501 games 241 Jul 30 2008 load_departments.dump
7-rw-r--r--. 1 501 games 13828291 Mar 30 2009 load_dept_emp.dump
8-rw-r--r--. 1 501 games 1043 Jul 30 2008 load_dept_manager.dump
9-rw-r--r--. 1 501 games 17422825 Jul 30 2008 load_employees.dump
10-rw-r--r--. 1 501 games 115848997 Jul 30 2008 load_salaries.dump
11-rw-r--r--. 1 501 games 21265449 Jul 30 2008 load_titles.dump
12-rw-r--r--. 1 501 games 3889 Mar 30 2009 objects.sql
13-rw-r--r--. 1 501 games 2211 Jul 30 2008 README
14-rw-r--r--. 1 501 games 4455 Mar 30 2009 test_employees_md5.sql
15-rw-r--r--. 1 501 games 4450 Mar 30 2009 test_employees_sha.sql
运行此命令连接到 MySQL 并运行 employees.sql 脚本,该脚本将创建数据库并加载数据:
1sudo mysql -h localhost -u sysadmin -p -t < employees.sql
在提示中,在上一节中输入您为 sysadmin MySQL 用户创建的密码。
进程输出将看起来像这样,运行需要一分钟或更长时间:
1+-----------------------------+
2| INFO |
3+-----------------------------+
4| CREATING DATABASE STRUCTURE |
5+-----------------------------+
6+------------------------+
7| INFO |
8+------------------------+
9| storage engine: InnoDB |
10+------------------------+
11+---------------------+
12| INFO |
13+---------------------+
14| LOADING departments |
15+---------------------+
16+-------------------+
17| INFO |
18+-------------------+
19| LOADING employees |
20+-------------------+
21+------------------+
22| INFO |
23+------------------+
24| LOADING dept_emp |
25+------------------+
26+----------------------+
27| INFO |
28+----------------------+
29| LOADING dept_manager |
30+----------------------+
31+----------------+
32| INFO |
33+----------------+
34| LOADING titles |
35+----------------+
36+------------------+
37| INFO |
38+------------------+
39| LOADING salaries |
40+------------------+
现在您可以登录MySQL并运行一些基本查询,以检查数据是否成功导入。
1sudo mysql -h localhost -u sysadmin -p
输入 MySQL 用户的密码 ** sysadmin **。
查看新员工数据库的数据库列表:
1show databases;
输出:
1+--------------------+
2| Database |
3+--------------------+
4| information_schema |
5| employees |
6| mysql |
7| performance_schema |
8+--------------------+
94 rows in set (0.01 sec)
使用员工数据库:
1use employees;
查看其中的桌子:
1show tables;
输出:
1+---------------------+
2| Tables_in_employees |
3+---------------------+
4| departments |
5| dept_emp |
6| dept_manager |
7| employees |
8| salaries |
9| titles |
10+---------------------+
116 rows in set (0.01 sec)
如果您想要,您可以检查每个表的详细信息,我们只会检查标题表的信息:
1describe titles;
输出:
1+-----------+-------------+------+-----+---------+-------+
2| Field | Type | Null | Key | Default | Extra |
3+-----------+-------------+------+-----+---------+-------+
4| emp_no | int(11) | NO | PRI | NULL | |
5| title | varchar(50) | NO | PRI | NULL | |
6| from_date | date | NO | PRI | NULL | |
7| to_date | date | YES | | NULL | |
8+-----------+-------------+------+-----+---------+-------+
94 rows in set (0.01 sec)
查看入场人数:
1mysql> select count(*) from titles;
2+----------+
3| count(*) |
4+----------+
5| 443308 |
6+----------+
71 row in set (0.14 sec)
检查您想要的任何其他数据,我们现在可以回到我们的操作系统提示:
1quit;
步骤三:使用MySQL
我们现在可以开始使用mysqlslap。mysqlslap可以从一个常规的壳提示程序中调用,所以没有必要明确登录MySQL。对于本教程,然而,我们将打开另一个终端连接到我们的Linux服务器,并从那里开始一个新的MySQL会话与我们之前创建的 sysadmin用户,所以我们可以更轻松地检查和更新MySQL中的几件事。
在我们进入测试的特定命令之前,您可能想看看这个最有用的 mysqlslap 选项列表,这可以帮助您稍后设计自己的 mysqlslap 命令。
用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户名: 用户
使用案例:使用自动生成的 SQL 和数据进行基准
当我们使用自动生成的SQL时,mysqlslap将创建一个单独的临时数据库 - 适当地称为mysqlslap。这个数据库将有一个简单的表,其中有一个整数和一个 varchar类型列,其中包含样本数据。
我们开始测试一个单个客户端连接,执行一个自动生成的 SQL 迭代:
1sudo mysqlslap --user=sysadmin --password --host=localhost --auto-generate-sql --verbose
输出应该是这样的:
1Benchmark
2 Average number of seconds to run all queries: 0.009 seconds
3 Minimum number of seconds to run all queries: 0.009 seconds
4 Maximum number of seconds to run all queries: 0.009 seconds
5 Number of clients running queries: 1
6 Average number of queries per client: 0
mysqlslap 报告了输出中所示的一些基准统计数据,报告了运行查询所需的平均、最低和最大秒数。
现在尝试 50 个同时连接,并让自动生成的查询运行 10 次:
1sudo mysqlslap --user=sysadmin --password --host=localhost --concurrency=50 --iterations=10 --auto-generate-sql --verbose
这个命令意味着,50个模拟的客户端连接将同时投放相同的测试查询,该测试将重复10次。
输出显示了与增加的负载有明显的差异:
1Benchmark
2 Average number of seconds to run all queries: 0.197 seconds
3 Minimum number of seconds to run all queries: 0.168 seconds
4 Maximum number of seconds to run all queries: 0.399 seconds
5 Number of clients running queries: 50
6 Average number of queries per client: 0
请注意,运行查询的客户端数: 字段现在显示的值为 50。
自动生成的 SQL 创建了一个简单的表,有两个字段。在大多数生产环境中,表结构要大得多。我们可以指示 mysqlslap 通过将额外的字段添加到测试表中来模拟这一点。 为了做到这一点,我们可以使用两个新的参数: --number-char-cols和 --number-int-cols。 这些参数指定了要添加到测试表的 varchar 和 int 类型的列。
在以下示例中,我们正在使用自动生成的 SQL 查询进行测试,该查询运行在包含 5 个数字列和 20 个字符类型列的表中,我们还模拟了 50 个客户端连接,我们希望测试重复 100 次:
1sudo mysqlslap --user=sysadmin --password --host=localhost --concurrency=50 --iterations=100 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql --verbose
在测试运行时,我们可以切换到另一个终端窗口,在那里我们有一个MySQL会话运行,并查看正在发生的事情。
从 MySQL 提前:
1show databases;
注意 mysqlslap 数据库:
1+--------------------+
2| Database |
3+--------------------+
4| information_schema |
5| employees |
6| mysql |
7| mysqlslap |
8| performance_schema |
9+--------------------+
105 rows in set (0.01 sec)
如果您想要,您可以检查测试数据库中的表;它被称为 t1。
当测试结束时,您会发现性能随着负载增加而进一步放缓:
1Benchmark
2 Average number of seconds to run all queries: 0.695 seconds
3 Minimum number of seconds to run all queries: 0.627 seconds
4 Maximum number of seconds to run all queries: 1.442 seconds
5 Number of clients running queries: 50
6 Average number of queries per client: 0
回到您的 MySQL 终端会话. 我们可以看到 mysqlslap 已经放弃了其丢弃的数据库。 在 MySQL 提示:
1show databases;
1+--------------------+
2| Database |
3+--------------------+
4| information_schema |
5| employees |
6| mysql |
7| performance_schema |
8+--------------------+
94 rows in set (0.00 sec)
使用案例:基于自定义查询的基准
如果您正在评估服务器的物理资源,自动生成的 SQL 非常有用,当您想要查找某个系统所能承担的负载水平时,它很有用。
但是,当您想要解决特定数据库依赖的应用程序的性能时,您将想要在真实数据上测试实际查询,这些查询可能来自您的Web或应用程序服务器。
目前,我们将假设您知道要测试的特定查询,在下一节中,我们将向您展示如何找到正在运行在您的服务器上的查询。
您可以使用 -- Query 选项向 mysqlslap 发送一个 in-line 查询. SQL 陈述不能在它们中有线断,并且需要由半色(;)划界。
在以下代码片段中,我们正在对 dept_emp 表执行一个简单的查询. `dept_emp' 表有超过三十万个记录. 请注意我们如何使用 --create-schema 选项来指定员工数据库:
1sudo mysqlslap --user=sysadmin --password --host=localhost --concurrency=50 --iterations=10 --create-schema=employees --query="SELECT * FROM dept_emp;" --verbose
你应该在一两分钟后收到这样的性能基准:
1Benchmark
2 Average number of seconds to run all queries: 18.486 seconds
3 Minimum number of seconds to run all queries: 15.590 seconds
4 Maximum number of seconds to run all queries: 28.381 seconds
5 Number of clients running queries: 50
6 Average number of queries per client: 1
(注:如果此查询持续时间超过十分钟,或者没有输出,则应尝试用较低的数字进行 -- 竞争和/或 -- 迭代,或者在较大的服务器上尝试。
接下来,我们将使用多个 SQL 语句在 -- Query 参数中。在下面的示例中,我们将每个查询用一个半字符串终止。
1sudo 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 秒):
1Benchmark
2 Average number of seconds to run all queries: 23.800 seconds
3 Minimum number of seconds to run all queries: 22.751 seconds
4 Maximum number of seconds to run all queries: 26.788 seconds
5 Number of clients running queries: 20
6 Average number of queries per client: 5
生产 SQL 语句可能很复杂. 更容易将一个复杂的 SQL 语句添加到脚本中,而不是将其输入到测试中。
为了说明这一点,让我们从 SQL 命令中创建一个脚本文件,我们可以使用下面的代码片段来创建这样的文件:
1sudo echo "SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" > ~/select_query.sql
2
3sudo cp ~/select_query.sql /mysqlslap_tutorial/
select_query.sql 文件现在包含所有五个 SELECT 语句。
由于这个脚本有多个查询,我们可以引入一个新的测试概念. mysqlslap 可以 parallelize 查询. 我们可以通过指定每个测试客户端应该执行的查询数量来做到这一点。
最后,我们还可以使用 --debug-info 交换机,这将为我们提供使用的计算资源的指示。
在下面的代码片段中,我们要求mysqlslap使用我们刚刚创建的脚本文件,我们还指定了查询数参数,该过程将重复两次,我们希望在输出中调试信息:
1sudo 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
完成此命令后,我们可以看到一些有趣的结果:
1Benchmark
2 Average number of seconds to run all queries: 217.151 seconds
3 Minimum number of seconds to run all queries: 213.368 seconds
4 Maximum number of seconds to run all queries: 220.934 seconds
5 Number of clients running queries: 20
6 Average number of queries per client: 50
7
8User time 58.16, System time 18.31
9Maximum resident set size 909008, Integral resident set size 0
10Non-physical pagefaults 2353672, Physical pagefaults 0, Swaps 0
11Blocks in 0 out 0, Messages in 0 out 0, Signals 0
12Voluntary context switches 102785, Involuntary context switches 43
在这里,运行 MySQL 实例中的所有查询的平均秒数为 217 秒,几乎是 4 分钟. 虽然这特别受我们虚拟机可用的 RAM 和 CPU 数量的影响,但这也是由于客户端连接中适度数量的查询数量重复两次。
我们可以看到有大量的非物理页面错误。当数据无法在内存中找到时,页面错误发生,系统必须从磁盘上的交换文件中获取它。
使用案例:实用平衡场景和捕捉实时查询
到目前为止,在我们的示例中,我们一直在对原始员工数据库运行查询,这是 DBA 肯定不希望您做的事情。
我们将向您展示如何备份生产数据库并将其复制到测试环境中. 在本示例中,它位于同一个服务器上,但理想情况下,您会将其复制到具有相同硬件容量的单独服务器。
更重要的是,我们将向您展示如何从生产数据库中实时记录查询并将其添加到测试脚本中,即从生产数据库中获取查询,但对测试数据库运行测试。
一般步骤如下,您可以使用它们进行任何MySQL测试:
1. 将生产数据库复制到测试环境 2. 配置 MySQL 以记录和捕捉生产数据库上的所有连接请求和查询 3. 模拟您正在试图测试的使用案例. 例如,如果您运行购物车,您应该从应用程序中购买一些东西来触发所有相应的数据库查询 4. 关闭查询日志 5. 查看查询日志并列出想要测试的查询 6. 创建一个测试文件,为您想要测试的每个查询 7. 运行测试 8. 使用输
首先,让我们创建员工数据库的备份副本,我们将为其备份创建一个单独的目录:
1sudo mkdir /mysqlslap_tutorial/mysqlbackup
2
3cd /mysqlslap_tutorial/mysqlbackup
创建备份并将其移动到新目录:
1sudo mysqldump --user sysadmin --password --host localhost employees > ~/employees_backup.sql
2
3sudo cp ~/employees_backup.sql /mysqlslap_tutorial/mysqlbackup/
转到 MySQL 测试服务器,创建 employees_backup 数据库:
1CREATE DATABASE employees_backup;
在此时,如果您正在使用单独的服务器进行测试,您应该将 employees_backup.sql 文件复制到它。
1sudo mysql -u sysadmin -p employees_backup < /mysqlslap_tutorial/mysqlbackup/employees_backup.sql
在您的生产 MySQL 数据库服务器上,启用 MySQL 通用查询日志并为其提供文件名。
1SET GLOBAL general_log=1, general_log_file='capture_queries.log';
现在,运行您想要在 MySQL 生产服务器上测试的查询. 在本示例中,我们将从命令行运行查询. 但是,您可能希望从您的应用程序生成查询,而不是直接运行它们。 如果您有一个慢过程或想要测试的网站页面,您应该通过该过程运行或现在访问该网页。
这是我们将在生产MySQL服务器上运行的查询,首先使用正确的数据库:
1USE employees;
现在运行查询:
1SELECT 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;
预期产量:
1489903 rows in set (4.33 sec)
当查询完成时,我们将关闭一般日志:
1SET GLOBAL general_log=0;
请注意,如果您离开登录,请继续将查询添加到日志中,这可能会使测试更加困难。因此,请确保您在完成测试后立即禁用日志。
1sudo ls -l /var/lib/mysql/capt*
2
3-rw-rw----. 1 mysql mysql 861 Sep 24 15:09 /var/lib/mysql/capture_queries.log
让我们将此文件复制到我们的MySQL测试目录. 如果您正在使用单独的服务器进行测试,请将其复制到该服务器。
1sudo cp /var/lib/mysql/capture_queries.log /mysqlslap_tutorial/
在这个例子中,我们想要的查询应该接近尾声。
1sudo tail /mysqlslap_tutorial/capture_queries.log
预期产量:
16294 Query show databases
2 6294 Query show tables
3 6294 Field List departments
4 6294 Field List dept_emp
5 6294 Field List dept_manager
6 6294 Field List employees
7 6294 Field List salaries
8 6294 Field List titles
9140930 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
10140930 15:35:06 6294 Query SET GLOBAL general_log=0
此日志显示 SQL 命令及其时刻标签. 文件末尾附近的 SQL SELECT 语句是我们感兴趣的内容. 它应该与我们在生产数据库上运行的命令完全相同,因为这就是我们捕捉到的位置。
在本示例中,我们已经知道该查询,但在生产环境中,此方法可以非常有用来查找您可能不一定知道在您的服务器上运行的查询。
请注意,如果您在登录时运行或触发不同的查询,此文件将看起来完全不同。在真实情况下,此文件可能会被来自所有不同的连接的数百个条目淹没。您的目标是找到导致瓶颈的查询或查询。您可以通过创建包含文本查询的每个行列表开始。
对于您想要测试的每个查询,将其复制到以 .sql 扩展结束的文件中。
例如:
1sudo vi /mysqlslap_tutorial/capture_queries.sql
内容应该是您要测试的MySQL查询,没有任何线条断裂和没有半色角的结尾:
1SELECT 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
接下来,请确保查询结果没有缓存。 返回您的 test MySQL 会话. 运行以下命令:
1RESET QUERY CACHE;
现在是时候与脚本文件一起运行 mysqlslap 实用程序了. 请确保您在 -- Query 参数中使用正确的脚本文件名称. 我们只会使用十个同时连接,并重复测试两次。
1sudo mysqlslap --user=sysadmin --password --host=localhost --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose
基准输出在我们的系统中是这样的:
1Benchmark
2 Average number of seconds to run all queries: 68.692 seconds
3 Minimum number of seconds to run all queries: 59.301 seconds
4 Maximum number of seconds to run all queries: 78.084 seconds
5 Number of clients running queries: 10
6 Average number of queries per client: 1
那么,我们如何改善这一基准呢?
您需要一定程度的熟悉 MySQL 查询来评估查询正在做什么。
回顾到查询,我们可以看到它正在在多个表中进行多个合并。该查询显示了员工的工作历史,并在这样做时,它正在通过 emp_no 字段连接到不同的表。它还使用 dept_no 字段加入,但由于只有少数部门记录,我们会忽略这一点。
通过一点练习,一旦您找到正在对服务器征税的查询(这就是mysqlslap帮助的部分!),您将能够根据您对MySQL和数据库的知识对查询进行评估。
接下来,您可以尝试改进您的数据库或正在执行的查询。
在我们的例子中,让我们添加我们上面提到的索引,我们将在 emp_no 上创建三个索引,在员工表中的 emp_no 字段中将创建一个索引,在 dept_emp 表中的 emp_no 字段中将创建另一个索引,在标题表中的 emp_no 字段中将创建最后一个索引。
让我们去我们的MySQL测试会话并执行以下命令:
1USE employees_backup;
2
3CREATE INDEX employees_empno ON employees(emp_no);
4
5CREATE INDEX dept_emp_empno ON dept_emp(emp_no);
6
7CREATE INDEX titles_empno ON titles(emp_no);
回到测试服务器上的主终端窗口,如果我们用相同参数执行mysklslap,我们会看到基准值的差异:
1sudo mysqlslap --user=sysadmin --password --host=localhost --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose
1Benchmark
2 Average number of seconds to run all queries: 55.869 seconds
3 Minimum number of seconds to run all queries: 55.706 seconds
4 Maximum number of seconds to run all queries: 56.033 seconds
5 Number of clients running queries: 10
6 Average number of queries per client: 1
我们可以看到,查询执行的平均、最低和最大时间出现了立即的改进,而不是平均68秒,查询现在在55秒内执行,这是同一负载的13秒的改进。
由于此数据库更改在测试环境中产生了良好的结果,您现在可以考虑将其部署到您的生产数据库服务器中,尽管要记住,数据库更改通常在优点和缺点上都有抵消。
您可以重复测试命令和改进的过程,您从日志中收集的所有查询。
故障排除 - mysqlslap 不显示输出
如果您运行测试命令并且没有得到任何输出,这是一个很好的迹象,表明您的服务器资源可能会被最大化。
您可能想再次尝试测试,在 --concurrency 或 --iterations 参数中使用较小的数字。
这可以是找到数据库服务器容量的外部限制的好方法。
结论
mysqlslap 是一个简单、轻量级的工具,易于使用,可以与 MySQL 数据库引擎集成。
在本教程中,我们已经看到如何使用 mysqlslap 及其各种选项,并与样本数据库一起玩。您可以从MySQL网站下载其他样本数据库,并练习这些数据库。
本教程中的最后一个用例仅涉及一个查询,虽然我们通过在所有三个表中添加额外索引来改善了该查询的性能,但在现实生活中,这个过程可能并不那么简单。
实际测试场景更为复杂,但这应该为您提供工具,以便开始测试并提高数据库性能。