Как измерить производительность запросов MySQL с помощью mysqlslap

Введение

MySQL поставляется с удобным небольшим диагностическим инструментом под названием mysqlslap, который существует с версии 5.1.4. Это инструмент сравнительного анализа, который может помочь администраторам баз данных и разработчикам провести нагрузочное тестирование своих серверов баз данных.

mysqlslap может эмулировать большое количество клиентских подключений к серверу базы данных одновременно. Параметры нагрузочного тестирования полностью настраиваются, а результаты различных прогонов тестов можно использовать для тонкой настройки структуры базы данных или аппаратных ресурсов.

В этом руководстве мы узнаем, как использовать mysqlslap для загрузки и тестирования базы данных MySQL некоторыми базовыми запросами, и посмотрим, как бенчмаркинг может помочь нам в тонкой настройке этих запросов. После некоторых основных демонстраций мы рассмотрим довольно реалистичный тестовый сценарий, в котором мы создаем копию существующей базы данных для тестирования, собираем запросы из журнала и запускаем тест из сценария.

Команды, пакеты и файлы, показанные в этом руководстве, были протестированы на CentOS 7. Принципы остаются теми же для других дистрибутивов.

Какой размер сервера мне следует использовать?

Если вы заинтересованы в тестировании конкретного сервера базы данных, вам следует провести тестирование на сервере с такими же характеристиками и с установленной точной копией вашей базы данных.

Если вы хотите пройти этот учебник в учебных целях и выполнить каждую команду в нем, мы рекомендуем дроплет размером не менее 2 ГБ. Поскольку команды в этом руководстве предназначены для нагрузки на сервер, вы можете обнаружить, что они истекают на меньшем сервере.

Образец выходных данных в этом учебнике был создан различными способами, чтобы оптимизировать примеры для обучения.

Шаг первый — установка MySQL Community Server в тестовой системе

Мы начнем с установки новой копии MySQL Community Server в тестовой базе данных. Вы не должны запускать какие-либо команды или запросы из этого руководства на рабочем сервере базы данных.

Эти тесты предназначены для нагрузки на тестовый сервер и могут вызвать задержки или простои на рабочем сервере. Это руководство было протестировано в следующей среде:

ЦентрОС 7
Команды, выполняемые пользователем sudo
Рекомендуется капля объемом 2 ГБ; имейте в виду, что результаты тестов, показанные в этом руководстве, были получены в учебных целях и не отражают конкретные тесты DigitalOcean

Во-первых, мы создадим каталог для хранения всех файлов, связанных с этим руководством. Это поможет содержать вещи в порядке. Перейдите в этот каталог:

sudo mkdir /mysqlslap_tutorial
cd /mysqlslap_tutorial

Далее мы загрузим репозиторий yum MySQL Community Release. Репозиторий, который мы загружаем, предназначен для Red Hat Enterprise Linux 7, который работает с CentOS 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

Это вызовет ряд подсказок. Мы покажем подсказки ниже, а ответы, которые вы должны ввести, будут выделены красным. В начале нет пароля для root-пользователя MySQL, поэтому просто нажмите 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

Введите корневой пароль 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

Шаг второй — установка образца базы данных

Далее нам нужно установить образец базы данных для тестирования. Эта база данных называется сотрудниками и называется Launchpad. База данных сотрудников была разработана Патриком Крюсом и Джузеппе Максиа. Исходные данные были созданы Фушэном Ваном и Карло Дзаньоло из Siemens Corporate Research.

Мы выбираем базу данных сотрудников, потому что она содержит большой набор данных. Структура базы данных достаточно проста: в ней всего шесть таблиц; но содержащиеся в нем данные содержат более 3 000 000 записей о сотрудниках (сама таблица зарплат содержит почти три миллиона строк). Это поможет нам эмулировать более реалистичную производственную рабочую нагрузку.

Во-первых, давайте удостоверимся, что мы находимся в каталоге /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 можно вызывать из обычной командной строки, поэтому нет необходимости явно входить в MySQL. Однако для этого руководства мы откроем еще одно терминальное соединение с нашим сервером Linux и начнем оттуда новый сеанс MySQL с пользователем sysadmin, которого мы создали ранее, чтобы нам было легче проверять и обновлять некоторые вещи в MySQL. Итак, у нас будет одно приглашение, открытое нашим пользователем sudo, и одно приглашение, вошедшее в MySQL.

Прежде чем мы перейдем к конкретным командам для тестирования, вы можете взглянуть на этот список наиболее полезных параметров mysqlslap. Это может помочь вам в разработке собственных команд mysqlslap позже.

Option	What it means
–user	MySQL username to connect to the database server
–password	Password for the user account. It’s best to leave it blank in command line
–host	MySQL database server name
–port	Port number for connecting to MySQL if the default is not used
–concurrency	The number of simultaneous client connections mysqlslap will emulate
–iterations	The number of times the test query will be run
–create-schema	The schema in which to run the tests
–query	The query to execute. This can either be a SQL query string or a path to a SQL script file
–create	The query to create a table. Again, this can be a query string or a path to a SQL file
–delimiter	The delimiter used to separate multiple SQL statements
–engine	The MySQL database engine to use (e.g., InnoDB)
–auto-generate-sql	Lets MySQL perform load testing with its own auto-generated SQL command

Вариант использования: сравнительный анализ с автоматически сгенерированным SQL и данными

Мы начнем с использования функции автогенерации sql mysqlslap. Когда мы используем автоматически сгенерированный 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 сообщает несколько тестовых статистических данных, как показано в выводе. Он сообщает среднее, минимальное и максимальное количество секунд, затраченных на выполнение запроса. Мы также можем видеть, что количество клиентских подключений, использованных для этого нагрузочного теста, равно одному.

Теперь попробуйте 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)

Вы можете проверить таблицу в тестовой базе данных, если хотите; это называется т1.

Снова проверьте другое окно терминала. Когда тест завершится, вы обнаружите, что производительность еще больше снизилась с увеличением нагрузки:

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 хорош, если вы оцениваете физические ресурсы сервера. Это полезно, когда вы хотите определить уровень нагрузки, которую может выдержать данная система.

Однако, если вы хотите устранить неполадки производительности для конкретного приложения, зависящего от базы данных, вы захотите протестировать реальные запросы на реальных данных. Эти запросы могут исходить от вашего веб-сервера или сервера приложений.

На данный момент мы предполагаем, что вы знаете конкретный запрос, который хотите протестировать. В следующем разделе мы покажем вам, как найти запросы, которые выполняются на вашем сервере.

Мы начнем со встроенных запросов. Вы можете отправить встроенный запрос в mysqlslap с опцией -query. Операторы SQL не могут иметь разрывов строк и должны быть разделены точкой с запятой (;). Запросы также должны быть заключены в двойные кавычки.

В следующем фрагменте кода мы выполняем простой запрос к таблице dept_emp. Таблица dept_emp содержит более трехсот тысяч записей. Обратите внимание, как мы указали базу данных employees с опцией –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. , или попробуйте на более крупном сервере.)

Далее мы будем использовать несколько операторов SQL в параметре –query. В следующем примере мы завершаем каждый запрос точкой с запятой. 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 секунды против 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 использовать только что созданный файл сценария. Мы также указываем параметр количество запросов. Процесс будет повторяться дважды, и нам нужна отладочная информация на выходе:

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 минуты. Хотя на это, безусловно, повлиял объем ОЗУ и ЦП, доступных для нашей виртуальной машины, это также было связано с большим количеством запросов от умеренного количества клиентских подключений, повторяющихся дважды.

Мы видим, что было большое количество нефизических сбоев страниц. Ошибки страниц возникают, когда данные не могут быть найдены в памяти, и системе приходится искать их из файла подкачки на диске. Выходные данные также показывают информацию, связанную с процессором. В данном случае мы видим большое количество переключений контекста.

Пример использования: практический сценарий сравнительного анализа и сбор оперативных запросов

До сих пор в наших примерах мы выполняли запросы к исходной базе данных сотрудников. Администраторы баз данных определенно не хотели бы, чтобы вы это делали. И на это есть веская причина. Вы не хотите добавлять нагрузку в свою рабочую базу данных и не хотите запускать тестовые запросы, которые могут удалять, обновлять или вставлять данные в ваши рабочие таблицы.

Мы покажем вам, как сделать резервную копию рабочей базы данных и скопировать ее в тестовую среду. В этом примере он находится на том же сервере, но в идеале вы должны скопировать его на отдельный сервер с такой же аппаратной мощностью.

Что еще более важно, мы покажем вам, как записывать запросы в режиме реального времени из производственной базы данных и добавлять их в сценарий тестирования. То есть вы будете получать запросы из производственной базы данных, но запускать тесты для тестовой базы данных.

Общие шаги следующие, и вы можете использовать их для любого теста mysqlslap:

1. Скопируйте производственную базу данных в тестовую среду.

Для начала создадим резервную копию базы данных сотрудников. Мы создадим отдельный каталог для его резервной копии:

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. Один индекс будет создан для поля emp_no в таблице employees, другой индекс будет создан для поля emp_no в таблице dept_emp. , а последний будет создан в поле emp_no в таблице titles.

Перейдем к нашему тестовому сеансу 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 не показывает вывод

Если вы запускаете тестовую команду и не получаете никаких результатов, это хороший признак того, что ресурсы вашего сервера могут быть исчерпаны. Симптомы могут включать отсутствие выходных данных Benchmark или ошибку типа mysqlslap: Ошибка при сохранении результата: 2013 Потеряно соединение с сервером MySQL во время запроса.

Вы можете повторить тест с меньшим числом в параметре –concurrency или –iterations. В качестве альтернативы вы можете попробовать обновить среду тестового сервера.

Это может быть хорошим способом определить внешние пределы возможностей вашего сервера базы данных.

Заключение

mysqlslap — это простой, легкий инструмент, который прост в использовании и изначально интегрируется с ядром базы данных MySQL. Он доступен для всех выпусков MySQL, начиная с версии 5.1.4.

В этом уроке мы увидели, как использовать mysqlslap с его различными опциями, и поэкспериментировали с образцом базы данных. Вы можете загрузить другие образцы баз данных с сайта MySQL и попрактиковаться с ними. Как мы упоминали ранее, не запускайте тесты на рабочем сервере базы данных.

Последний вариант использования в этом руководстве включал только один запрос. Хотя мы несколько улучшили производительность этого запроса, добавив дополнительные индексы ко всем трем таблицам, в реальной жизни этот процесс может оказаться не таким простым. Добавление дополнительных индексов может иногда снижать производительность системы, и администраторам баз данных часто приходится взвешивать преимущества добавления дополнительного индекса и возможные потери производительности.

Сценарии тестирования в реальной жизни более сложны, но это должно дать вам инструменты для начала тестирования и повышения производительности вашей базы данных.