Оптимизация запросов MySQL с помощью ProxySQL в Ubuntu 16.04
mySQL, Ubuntu | Комментировать запись
ProxySQL – это прокси-сервер с поддержкой SQL, который может служить связующим звеном между приложением и базой данных. Он предлагает множество функций, таких как распределение нагрузки между несколькими серверами MySQL и кэширование запросов. В этом мануале основное внимание будет уделено кэшированию ProxySQL и тому, как с его помощью можно оптимизировать запросы базы данных MySQL.
Читайте также: ProxySQL как балансировщик нагрузки MySQL в Ubuntu 16.04
Кэширование MySQL происходит следующим образом: при отправке запроса результат сохраняется в кэше, и если этот запрос повторяется позже, результат можно просто извлечь из кэша, не обращаясь к базе данных. Это может значительно увеличить скорость обработки общих запросов. Но многие средства кэширования требуют внесения изменений в код приложения, что может привести к ошибке. К счастью, ProxySQL позволяет избежать редактирования кода благодаря поддержке прозрачного кэширования.
При прозрачном кэшировании изменять конфигурацию ProxySQL нужно только администраторам баз данных: они включают кэширование наиболее распространенных запросов. Эти изменения можно внести через интерфейс администратора ProxySQL. Все, что нужно сделать разработчику, – это подключиться к прокси-серверу, поддерживающему протокол, и прокси-сервер сам решит, можно ли обработать запрос из кэша, не обращаясь к внутреннему серверу.
В этом мануале мы покажем, как использовать ProxySQL для настройки прозрачного кэширования MySQL в Ubuntu 16.04. Затем мы протестируем производительность сервера с кэшированием и без, используя инструмент mysqlslap, чтобы вы увидели, сколько времени можно сэкономить.
Требования
Для работы вам понадобится сервер Ubuntu 16.04, настроенный по этому мануалу.
1: Установка и настройка сервера MySQL
Сначала мы установим сервер MySQL и настроим его для поддержки ProxySQL в качестве бэкенда для обслуживания клиентских запросов.
В Ubuntu 16.04 пакет mysql-server можно установить с помощью этой команды:
sudo apt-get install mysql-server
Нажмите Y, чтобы подтвердить установку.
Затем вам будет предложено выбрать пароль для root-пользователя MySQL. Введите сложный пароль.
Теперь, когда у вас есть готовый сервер MySQL, вы настроите его для поддержки ProxySQL. Вам необходимо добавить пользователя ProxySQL по имени monitor для мониторинга сервера MySQL, поскольку ProxySQL прослушивает внутренний сервер через протокол SQL, а не через соединение TCP или HTTP GET-запросы. Пользователь monitor будет использовать фиктивное соединение SQL, чтобы определить состояние сервера.
Сначала войдите в оболочку MySQL:
mysql -uroot -p
Флаг -uroot авторизует вас как пользователя root MySQL, а -p запрашивает пароль пользователя root. Введите тот пароль, который вы выбрали при установке пакета mysql-server, и нажмите Enter.
Теперь нужно создать двух пользователей: одного для ProxySQL (по имени monitor), а второго – для выполнения клиентских запросов и предоставления им необходимых привилегий (здесь мы условно назовем его 8host, но вы можете выбрать другое имя).
Создайте пользователя monitor:
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';
Запрос CREATE USER используется для создания нового пользователя, который может подключаться с определенных IP-адресов. Символ % означает, что пользователь может подключиться с любого IP-адреса. IDENTIFIED BY устанавливает пароль нового пользователя; выберите сложный пароль и обязательно запомните его для дальнейшей работы.
После создания пользователя monitor создайте пользователя 8host:
CREATE USER '8host'@'%' IDENTIFIED BY '8host_password';
Затем предоставьте своим новым пользователям требуемые привилегии. Выполните следующую команду для настройки monitor:
GRANT SELECT ON sys.* TO 'monitor'@'%';
Запрос GRANT используется для предоставления привилегий пользователям. Здесь вы предоставили пользователю только право запускать SELECT для всех таблиц в базе данных sys; этого достаточно для прослушивания внутреннего сервера.
Теперь предоставьте пользователю 8host все привилегии на все БД:
GRANT ALL PRIVILEGES on *.* TO '8host'@'%';
Это позволит 8host позже сделать все необходимые запросы для проверки вашей базы данных.
Обновите привилегии:
FLUSH PRIVILEGES;
Закройте оболочку mysql:
exit;
Вы установили mysql-сервер и создали пользователей для ProxySQL и для выполнения клиентских запросов. Давайте установим ProxySQL.
2: Установка и настройка сервера ProxySQL
Теперь вы можете установить сервер ProxySQL, который будет использоваться в качестве уровня кэширования ваших запросов. Уровень кэширования работает как промежуточный уровень между вашими серверами приложений и внутренними серверами баз данных; он используется для подключения к базе данных и сохранения результатов наиболее частых запросов в ее памяти для быстрого доступа к ним.
Страница релизов ProxySQL на Github предлагает установочные файлы для дистрибутивов Linux. В этом мануале мы исполльзуем wget для загрузки установочного файла Debian версии 2.0.4 ProxySQL:
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
Вы получите примерно такой результат:
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. Для этого используйте SQL-интерфейс администратора ProxySQL, который по умолчанию прослушивает порт 6032 на локальном хосте (имя пользователя – admin, пароль – admin).
Подключитесь к интерфейсу, выполнив команду:
mysql -uadmin -p -h 127.0.0.1 -P6032
Введите учетные данные.
Флаг -uadmin задает имя пользователя admin, а флаг -h указывает localhost в качестве хоста. Порт 6032 определяется флагом -P.
Мы явно указали хост и порт, потому что по умолчанию клиент MySQL подключается с помощью локального файла сокетов и порта 3306.
Теперь, когда вы вошли в оболочку mysql как администратор, настройте пользователя 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;
Затем сохраните изменения в базе данных на диске, чтобы сделать эти настройки постоянными. ProxySQL использует собственную локальную базу данных SQLite для хранения таблиц и переменных:
SAVE MYSQL VARIABLES TO DISK;
Теперь нужно рассказать ProxySQL о внутреннем сервере. Таблица mysql_servers содержит информацию о каждом бэкенд сервере, к которому ProxySQL может подключаться и выполнять запросы, поэтому добавьте в нее новую запись, используя стандартный оператор SQL INSERT:
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, какой пользователь будет подключаться к бэкенд серверу; это будет пользователь 8host. Укажите в команде его пароль.
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('8host', '8host_password', 1);
Таблица mysql_users содержит информацию о пользователях для подключения к бэкенд серверам. Мы указали параметры username, password и default_hostgroup.
Чтобы изменения вступили в силу, снова запустите LOAD и SAVE:
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
Выйдите из оболочки СУБД:
exit;
Чтобы убедиться, что вы можете подключиться к бэкенд серверу с помощью ProxySQL, выполните следующий тестовый запрос:
mysql -u8host -h127.0.0.1 -p -P6033 -e "SELECT @@HOSTNAME as hostname"
В этой команде вы использовали флаг -e, чтобы выполнить запрос и закрыть соединение. Запрос выведет имя хоста бэкенд сервера.
Примечание: Для прослушивания входящих соединений ProxySQL по умолчанию использует порт 6033.
Вывод будет выглядеть следующим образом (вместо your_hostname будет имя вашего хоста):
+----------------------------+
| hostname |
+----------------------------+
| your_hostname |
+----------------------------+
Итак, вы настроили ProxySQL, и теперь он использует MySQL в качестве бэкэнда. Чтобы проверить, как все работает, вы подключились к бэкенду с помощью ProxySQL. Теперь давайте используем mysqlslap для оценки производительности запросов без кэширования.
3: Тестирование сервера без кэширования с помощью mysqlslap
На данном этапе нужно загрузить тестовую базу данных, чтобы вы могли выполнять запросы к ней с помощью mysqlslap и измерить задержку без кэширования, установив контрольный показатель для скорости ваших запросов. Вы также узнаете, как ProxySQL хранит записи запросов в таблице stats_mysql_query_digest.
mysqlslap – это клиентский эмулятор нагрузки, который используется для нагрузочного тестирования MySQL. Он может проверить работу сервера MySQL с помощью автоматически сгенерированных запросов или нескольких пользовательских запросов, выполняемых в базе данных. Он устанавливается вместе с клиентским пакетом MySQL, поэтому вам не нужно его устанавливать отдельно.
Давайте загрузим БД с тестовыми данными. В этом мануале мы предлагаем использовать этот пример. База данных employees содержит большой набор данных, который может наглядно показать различия в оптимизации запросов. Эта БД содержит шесть таблиц и более 300 000 записей о сотрудниках. Это поможет вам эмулировать крупномасштабную производственную нагрузку.
Чтобы загрузить эту базу данных, сначала клонируйте репозиторий Github с помощью этой команды:
git clone https://github.com/datacharmer/test_db.git
Затем войдите в каталог test_db и загрузите базу данных на сервер MySQL:
cd test_db
mysql -uroot -p < employees.sql
Эта команда использует перенаправление оболочки для чтения запросов SQL в файле employee.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
data_load_time_diff
00:00:32
Как только база данных будет загружена на ваш сервер MySQL, убедитесь, что mysqlslap работает:
mysqlslap -u8host -p -P6033 -h127.0.0.1 --auto-generate-sql --verbose
mysqlslap предоставляет флаги, аналогичные флагам клиента mysql; Вот такие флаги используются в этой команде:
- -u задает пользователя для подключения к серверу.
- -p запрашивает пароль пользователя.
- -P подключается через указанный порт.
- -h подключается к указанному хосту.
- –auto-generate-sql позволяет MySQL выполнять нагрузочное тестирование, используя собственные сгенерированные запросы.
- –verbose выводит полученную информацию в расширенном формате.
Вы получите подобный вывод:
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. Это даст дайджест запросов, который является нормализованной формой оператора 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 -u8host -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′”: вы указывает запрос, выполненный в тесте.
Тест займет несколько минут. После того, как это будет сделано, вы получите результаты, подобные следующим:
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
На этом этапе вы увидите, как кэширование помогаем уменьшить задержку при выполнении одинаковых запросов. Здесь мы можем определить выполненные запросы, извлечь их дайджесты из таблицы stats_mysql_query_digest в ProxySQL и использовать их для включения кэширования. Затем мы еще раз запустим тесты, чтобы проверить разницу.
Чтобы включить кэширование, нужно знать дайджесты запросов, которые будут кэшироваться. Войдите в интерфейс администратора 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;
Вы получите такой результат:
+------------+-------------+-----------+--------------------+------------------------------------------+
| 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, и в результате соответствующий запрос по данному дайджесту будет кэшироваться в течение количества миллисекунд, указанных в этом столбце. В столбце apply мы указываем 1, чтобы правило применялось к запросам.
Загрузите и сохраните изменения, а затем выйдите из оболочки mysql:
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
exit;
Теперь, когда кэширование включено, повторите тест еще раз, чтобы сравнить результаты:
mysqlslap -u8host -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
Вы получите такой вывод:
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 очень хорошо влияет на обработку запросов.
Читайте также: Оптимизация работы MySQL с помощью кэша запросов в Ubuntu 18.04
Tags: MySQL, mysqlslap, ProxySQL, Ubuntu, Ubuntu 16.04