Оптимизация запросов MySQL с помощью кеширования ProxySQL в Ubuntu 16.04

Автор выбрал фонд Free Software Foundation для получения пожертвования в рамках программы Write for DOnations.

Введение

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

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

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

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

Предварительные требования

Для прохождения этого обучающего руководства вам потребуется следующее:

• Один сервер Ubuntu 16.04 с минимум 2 ГБ оперативной памяти, пользователем без прав root с привилегиями sudo, а также брандмауэр, как указано в руководстве по первоначальной настройке сервера Ubuntu 16.04.

Шаг 1 — Установка и настройка сервера MySQL

Сначала мы установим сервер MySQL и настроим его для использования с ProxySQL в качестве сервера бэкэнда для обслуживания запросов клиентов.

В Ubuntu 16.04 mysql-server можно установить с помощью этой команды:

sudo apt-get install mysql-server

Нажмите Y для подтверждения установки.

Затем вам будет предложено ввести пароль root пользователя MySQL. Введите надежный пароль и сохраните его для последующего использования.

Теперь, когда у вас есть готовый сервер MySQL, необходимо настроить его для корректной работы с ProxySQL. Вам нужно добавить пользователя monitor для ProxySQL для мониторинга сервера MySQL, поскольку ProxySQL прослушивает сервер бэкэнда с помощью протокола SQL, не используя соединение TCP или запросы HTTP GET, чтобы убедиться, что сервер работает. monitor будет использовать пустое соединение SQL для определения того, запущен сервер или нет.

Выполните вход в командную строку MySQL:

mysql -uroot -p

-uroot позволяет выполнить вход, используя root пользователя MySQL, а -p запрашивает пароль пользователя root. Этот пользователь root отличается от пользователя root сервера, а пароль — это пароль, который вы вводили при установке пакета mysql-server.

Введите пароль root и нажмите ENTER.

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

Создайте пользователя monitor:

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';

Запрос CREATE USER используется для создания нового пользователя, который может подключаться с конкретных IP-адресов. Использование % означает, что пользователь может подключаться с любого IP-адреса. IDENTIFIED BY устанавливает пароль для нового пользователя, введите любой пароль, но обязательно запомните его для последующего использования.

После создания пользователя monitor создайте пользователя sammy:

CREATE USER 'sammy'@'%' IDENTIFIED BY 'sammy_password';

Затем предоставьте привилегии вашим новым пользователям. Запустите следующую команду для настройки monitor:

GRANT SELECT ON sys.* TO 'monitor'@'%';

Запрос GRANT используется для предоставления привилегий пользователям. Здесь мы предоставили только SELECT для всех таблиц в базе данных sys пользователю monitor, ему нужна только эта привилегия для прослушивания сервера бэкэнда.

Теперь мы предоставим все права доступа для всех баз данных пользователю sammy:

GRANT ALL PRIVILEGES on *.* TO 'sammy'@'%';

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

Примените изменения привилегий, выполнив следующую команду:

FLUSH PRIVILEGES;

После этого выйдите из командной строки mysql:

exit;

Вы установили mysql-server и создали пользователя для ProxySQL для мониторинга сервера MySQL и еще одного пользователя для выполнения клиентских запросов. Далее мы установим и настроим ProxySQL.

Шаг 2 — Установка и настройка сервера ProxySQL

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

На странице релизов ProxySQL на GitHub вы найдете файлы установки для стандартных дистрибутивов Linux. Для этого обучающего руководства мы будем использовать wget для загрузки файла установки ProxySQL версии 2.0.4 Debian:

wget https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb

Затем установите пакет с помощью dpkg:

sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb

После установки запустите ProxySQL с помощью этой команды:

sudo systemctl start proxysql

Вы можете проверить, выполнен ли запуск ProxySQL корректно, с помощью этой команды:

sudo systemctl status proxysql

Результат должен выглядеть примерно так:

Output
root@ubuntu-s-1vcpu-2gb-sgp1-01:~# systemctl status proxysql
● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)
   Active: active (exited) since Wed 2019-06-12 21:32:50 UTC; 6 months 7 days ago
     Docs: man:systemd-sysv-generator(8)
    Tasks: 0
   Memory: 0B
      CPU: 0

Теперь пришло время подключить ваш сервер ProxySQL к серверу MySQL. Для этой цели воспользуйтесь интерфейсом ProxySQL для администратора SQL, который по умолчанию слушает порт 6032 на localhost и использует admin в качестве имени пользователя и пароля.

Установите подключение к интерфейсу с помощью следующей команды:

mysql -uadmin -p -h 127.0.0.1 -P6032

Введите admin при запросе пароля.

-uadmin задает значение admin для имени пользователя, а флаг -h указывает в качестве хоста localhost. Используется порт 6032, заданный с помощью флага -P.

Здесь вам нужно явно задать хост и порт, поскольку по умолчанию клиент MySQL подключается с помощью файла локальных сокетов и порта 3306.

Теперь, когда вы выполнили вход в командную строку mysql как admin, настройте пользователя monitor, чтобы ProxySQL мог его использовать. Для начала применим стандартные запросы SQL для установки значений двух глобальных переменных:

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';

Переменная mysql-monitor_username указывает имя пользователя MySQL, который будет использоваться для проверки того, запущен ли сервер бэкэнда. Переменная mysql-monitor_password указывает на пароль, который будет использоваться при подключении к серверу бэкэнда. Используйте пароль, созданный для пользователя monitor.

Каждый раз, когда вы вносите изменение в интерфейсе администратора ProxySQL, вам нужно использовать команду LOAD для применения изменений для запущенного экземпляра ProxySQL. Вы изменили глобальные переменные MySQL, а для применения изменений нужно загрузить их в RUNTIME:

LOAD MYSQL VARIABLES TO RUNTIME;

Затем с помощью SAVE сохраните изменения в базе данных на диске для сохранения изменений в случае перезапуска. ProxySQL использует собственную локальную базу данных SQLite для хранения собственных таблиц и переменных:

SAVE MYSQL VARIABLES TO DISK;

Теперь мы сообщим ProxySQL о сервере бэкэнда. Таблица mysql_servers содержит информацию о каждом сервере бэкэнда, к которому ProxySQL может подключаться и выполнять запросы, поэтому добавьте новую запись с помощью стандартного SQL-оператора INSERT со следующими значениями для hostgroup_id, hostname и port:

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);

Чтобы применить изменения, запустите LOAD и SAVE еще раз:

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

После этого мы укажем ProxySQL, какой пользователь будет подключаться к серверу бэкэнда; задайте sammy в качестве пользователя и замените sammy_password на созданный вами пароль:

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sammy', 'sammy_password', 1);

Таблица mysql_users содержит информацию о пользователях, используемых для подключения к серверам бэкэнда; вы задали username, password и default_hostgroup.

Воспользуйтесь командами LOAD и SAVE:

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Затем выйдите из командной строки mysql:

exit;

Чтобы убедиться, что вы можете подключиться к серверу бэкэнда с помощью ProxySQL, выполните следующий тестовый запрос:

mysql -usammy -h127.0.0.1 -p -P6033 -e "SELECT @@HOSTNAME as hostname"

В этой команде мы используем флаг -e для выполнения запроса и закрытия подключения. Запрос выводит имя хоста сервера бэкэнда.

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

Output
+----------------------------+
| hostname                   |
+----------------------------+
| your_hostname        |
+----------------------------+

Дополнительную информацию о конфигурации ProxySQL можно найти в шаге 3 руководства по использованию ProxySQL в качестве инструмента распределения нагрузки для MySQL в Ubuntu 16.04.

Ранее мы настроили ProxySQL для использования сервера MySQL в качестве сервера бэкэнда и подключения к нему с помощью ProxySQL. Теперь мы готовы к использованию mysqlslap для оценки производительности запроса без кеширования.

Шаг 3 — Тестирование с помощью mysqlslap без кеширования

На этом шаге вы загрузите тестовую базу данных для выполнения запросов с помощью mysqlslap для проверки задержки без кеширования и определения скорости выполнения ваших запросов. Также вы узнаете, как ProxySQL хранит записи запросов в таблице stats_mysql_query_digest.

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

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

Чтобы загрузить базу данных, выполните клонирование репозитория Github с помощью этой команды:

git clone https://github.com/datacharmer/test_db.git

Затем откройте директорию test_db и загрузите базу данных на сервер MySQL с помощью этих команд:

cd test_db
mysql -uroot -p < employees.sql

Эта команда использует перенаправление командной строки для считывания запросов SQL в файле employees.sql и выполнения их на сервере MySQL для создания структуры базы данных.

Результат должен выглядеть следующим образом:

Output
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
data_load_time_diff
00:00:32

После загрузки базы данных на ваш сервер MySQL убедитесь, что mysqlslap работает, выполнив следующий запрос:

mysqlslap -usammy -p -P6033 -h127.0.0.1  --auto-generate-sql --verbose

mysqlslap имеет аналогичные флаги для клиента mysql; в этой команде используются следующие флаги:

• -u указывает пользователя, который будет подключаться к серверу.
• -p запрашивает пароль пользователя.
• -P выполняет подключение с помощью заданного порта.
• -h подключается к заданному хосту.
• --auto-generate-sql позволяет MySQL выполнять тестирование загрузки с помощью генерируемых самостоятельно запросов.
• --verbose делает вывод более информативным.

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

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

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

Затем необходимо выяснить, какие запросы mysqlslap выполнил в последней команде, изучив stats_mysql_query_digest в ProxySQL. Это позволит нам получить digest для запросов, представляющий собой нормализованную форму SQL-оператора, на которую можно будет ссылаться позднее для кеширования.

Выполните вход в интерфейс администратора ProxySQL с помощью этой команды:

mysql -uadmin -p -h 127.0.0.1 -P6032

Затем выполните этот запрос для получения информации в таблице stats_mysql_query_digest:

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

Вы увидите примерно следующий результат:

+------------+----------+-----------+--------------------+----------------------------------+
| count_star | sum_time | hostgroup | digest             | digest_text                      |
+------------+----------+-----------+--------------------+----------------------------------+
| 1          | 598      | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname    |
| 1          | 0        | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ? |
+------------+----------+-----------+--------------------+----------------------------------+
2 rows in set (0.01 sec)

Предыдущий запрос производит выборку данных из таблицы stats_mysql_query_digest, которая содержит информацию обо всех выполненных запросах в ProxySQL. Здесь у вас выбрано пять столбцов:

• count_star: количество времени, затраченное на выполнение запроса.
• sum_time: общее время в миллисекундах, которое требуется для выполнения этого запроса.
• hostgroup: группа хостов, которая использовалась для выполнения запроса.
• digest: дайджест выполненного запроса.
• digest_text: фактический запрос. В нашем примере второй запрос параметризирован с помощью символов ?, используемых на месте переменных. select @@version_comment limit 1 и select @@version_comment limit 2, следовательно, группируются вместе как один запрос с одинаковым дайджестом.

Теперь, когда мы знаем, как проверить данные запроса в таблице stats_mysql_query_digest, закройте командную строку mysql:

exit;

Загружаемая база данных содержит несколько таблиц с демонстрационными данными. Теперь мы проверим запросы в таблице dept_emp, используя любые записи, для которых значение from_date больше 2000-04-20, и регистрируя среднее время исполнения.

Используйте эту команду для запуска теста:

mysqlslap -usammy -P6033 -p -h127.0.0.1  --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

Здесь мы используем следующие новые флаги:

• --concurrency=100: задает число пользователей для симуляции, в данном случае 100.
• --iterations=20: выполняет запуск теста 20 раз и вычисляет результаты для всех итераций.
• --create-schema=employees: указывает в качестве базы данных employees.
• --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'": указывает запрос, выполняемый во время теста.

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

Output
Benchmark
        Average number of seconds to run all queries: 18.117 seconds
        Minimum number of seconds to run all queries: 8.726 seconds
        Maximum number of seconds to run all queries: 22.697 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

Полученные вами цифры могут отличаться. Сохраните эти цифры, чтобы сравнить их с результатами после активации кеширования.

После теста ProxySQL без кеширования нужно снова запустить тот же тест, но в этот раз уже активировать кеширование.

Шаг 4 — Тестирование с помощью mysqlslap с кешированием

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

Чтобы активировать кеширование, вам нужно знать дайджесты запросов, которые будут кешироваться. Выполните вход в интерфейс администратора ProxySQL с помощью этой команды:

mysql -uadmin -p -h127.0.0.1 -P6032

Затем выполните этот запрос снова для получения списка выполненных запросов и их дайджестов:

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

Результат должен выглядеть примерно так:

Output
+------------+-------------+-----------+--------------------+------------------------------------------+
| count_star | sum_time    | hostgroup | digest             | digest_text                              |
+------------+-------------+-----------+--------------------+------------------------------------------+
| 2000       | 33727110501 | 1         | 0xC5DDECD7E966A6C4 | SELECT * from dept_emp WHERE from_date>? |
| 1          | 601         | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname            |
| 1          | 0           | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ?         |
+------------+-------------+-----------+--------------------+------------------------------------------+
3 rows in set (0.00 sec)

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

Следующие несколько запросов будут добавлять новое правило запроса в ProxySQL, которое будет сопоставлять дайджест предыдущего запроса и добавлять для него значение cache_ttl. cache_ttl — это количество миллисекунд, в течение которых результат будет кешироваться в памяти:

INSERT INTO mysql_query_rules(active, digest, cache_ttl, apply) VALUES(1,'0xC5DDECD7E966A6C4',2000,1);

В этой команде мы добавляем новую запись в таблицу mysql_query_rules; эта таблица хранит все правила, применяемые перед выполнением запроса. В этом примере мы добавляем значение столбца cache_ttl​​​, которое приводит к тому, что соответствующий запрос по указанному дайджесту будет кешироваться в течение определенного числа миллисекунд, указанного в этом столбце. Вы добавили 1 в столбце применения, чтобы гарантировать, что правило будет применяться к запросам.

Воспользуйтесь командами LOAD и SAVE для этих изменений, после чего закройте командную строку mysql:

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
exit;

Теперь, когда кеширование активировано, перезапустите тест еще раз для проверки результата:

mysqlslap -usammy -P6033 -p -h127.0.0.1  --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

Результат будет выглядеть примерно следующим образом:

Output
Benchmark
        Average number of seconds to run all queries: 7.020 seconds
        Minimum number of seconds to run all queries: 0.274 seconds
        Maximum number of seconds to run all queries: 23.014 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

Здесь вы можете увидеть большую разницу в среднем времени исполнения: оно падает с 18.117 секунд до 7.020.

Заключение

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

В этом обучающем руководстве мы использовали один уровень кеширования. Вы также можете попробовать веб-кеширование, которое размещается перед веб-сервером и кеширует ответы для аналогичных запросов, отправляя ответ клиенту без обращения к серверам бэкэнда. Это очень похоже на кеширование с ProxySQL, но на другом уровне. Чтобы узнать больше о веб-кешировании, ознакомьтесь со статьей Основы веб-кеширования: терминология, HTTP-заголовки и стратегии кеширования.

Сервер MySQL также имеет собственный кеш запросов; подробнее о нем вы можете узнать в нашем руководстве по оптимизации MySQL с помощью кеширования запросов в Ubuntu 18.04.