Оптимизация работы MySQL с помощью кэша запросов в Ubuntu 18.04

14 декабря, 2019 12:08 пп 6 027 views | Комментариев нет

, | | Комментировать запись

Кэш запросов – это функция MySQL, которая позволяет ускорить извлечение данных из БД. Это делается путем сохранения в памяти операторов SELECT с извлеченными записями; если позже клиент отправляет идентичный запрос, данные выводятся быстрее, поскольку не нужно повторно выполнять команды в БД.

По сравнению с данными, хранящимися на диске, кэшированные данные из RAM (Random Access Memory) можно получить быстрее, что улучшает операции ввода/вывода (I/O). Например, благодаря кэшу запросов производительность сайтов WordPress или e-commerce порталов с большим количеством операций чтения и почти не меняющимися данными может значительно вырасти.

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

Примечание: Кэш запросов устарел в версии MySQL 5.7.20 и был удален в MySQL 8.0, но он по-прежнему является мощным инструментом и его можно использовать в тех версиях MySQL, которые поддерживают его. В более новых версиях MySQL можно использовать альтернативные сторонние инструменты  для оптимизации производительности базы данных MySQL (такие как ProxySQL).

Требования

1: Проверка кэша запросов

Перед настройкой кэша запросов нужно проверить, поддерживает ли ваша версия MySQL эту функцию. Сначала подключитесь по ssh к серверу Ubuntu 18.04:

ssh user_name@your_server_ip

Затем выполните следующую команду, чтобы войти на сервер MySQL как пользователь root:

sudo mysql -u root -p

При появлении запроса введите root пароль сервера MySQL, а затем нажмите Enter, чтобы продолжить.

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

show variables like 'have_query_cache';

Вы должны получить такой вывод:

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.01 sec)

Если в have_query_cache установлено значение YES, это означает, что кэш запросов поддерживается вашей версией СУБД. Если ваша версия не поддерживает кэш, можно использовать альтернативные сторонние инструменты  для оптимизации производительности MySQL (такие как ProxySQL).

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

2: Проверка стандартных переменных кэша запросов

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

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

show variables like 'query_cache_%' ;

В выводе вы увидите переменные:

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

Значение query_cache_limit определяет максимальный размер отдельных результатов запроса, которые могут быть кэшированы. Значение по умолчанию составляет 1 048 576 байт, что эквивалентно 1 МБ.

MySQL не обрабатывает кэшированные данные целиком; он обрабатывает их блоками. Минимальный объем памяти, выделяемый каждому блоку, определяется переменной query_cache_min_res_unit. Значение по умолчанию составляет 4096 байт, или 4 КБ.

Переменная query_cache_size контролирует общий объем памяти, выделенной для кэша запросов. Если в ней установлено значение 0, это означает, что кэш запросов отключен. В большинстве случаев по умолчанию может быть установлено значение 16 777 216 (около 16 МБ). Кроме того, следует иметь в виду, что query_cache_size требует как минимум 40 КБ для размещения своих структур. Выделенное здесь значение выравнивается по ближайшему 1024-байтовому блоку – то есть фактическое значение может немного отличаться от установленного вами.

MySQL определяет запросы к кэшу, изучая переменную query_cache_type. Значение 0 или OFF отключает кэширование или извлечение кэшированных запросов. Вы также можете установить значение 1, чтобы включить кэширование для всех запросов, кроме тех, которые начинаются с SELECT SQL_NO_CACHE. Значение 2 позволяет кэшировать только те запросы, которые начинаются с SELECT SQL_CACHE.

Переменная query_cache_wlock_invalidate решает, должен ли MySQL извлекать результаты из кэша, если используемая в запросе таблица заблокирована. Ее значением по умолчанию является OFF.

Примечание: Переменная query_cache_wlock_invalidate устарела с версии MySQL 5.7.20. Вполне возможно, что вы не увидите ее в своем выводе (в зависимости от версии MySQL, которую вы используете).

Изучив системные переменные, которые управляют кэшем запросов MySQL, мы можем проверить, как работает MySQL без кэша.

3: Тестирование MySQL без кэша запросов

Цель этого мануала – оптимизировать сервер MySQL с помощью кэширования запросов. Чтобы увидеть разницу в скорости, мы оценим производительность обработки запросов до и после внедрения кэша.

Сейчас мы создадим тестовую БД и вставим туда какие-нибудь данные, чтобы посмотреть, как MySQL работает без кэша запросов.

Создайте базу данных и назовите ее sample_db, выполнив следующую команду:

Create database sample_db;
Query OK, 1 row affected (0.00 sec)

Перейдите в эту БД:

Use sample_db;
Database changed

Создайте здесь таблицу с двумя столбцами, customer_id и customer_name. Давайте назовем таблицу customers:

Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;
Query OK, 0 rows affected (0.01 sec)

Запустите следующие команды, чтобы вставить тестовые данные:

Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE');
Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE');
Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE');
Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE');
Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE');
Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE');
Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH');
Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS');
Insert into customers(customer_id, customer_name) values ('9', 'JANE POE');
Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE');
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
...

Теперь нужно запустить профайлер MySQL, аналитический сервис для мониторинга производительности запросов MySQL. Чтобы включить профиль для текущего сеанса, выполните следующую команду, установив значение 1 (что значит «включено»):

SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

Select * from customers;

Вы получите следующий вывод:

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | JANE DOE      |
|           2 | JANIE DOE     |
|           3 | JOHN ROE      |
|           4 | MARY ROE      |
|           5 | RICHARD ROE   |
|           6 | JOHNNY DOE    |
|           7 | JOHN SMITH    |
|           8 | JOE BLOGGS    |
|           9 | JANE POE      |
|          10 | MARK MOE      |
+-------------+---------------+
10 rows in set (0.00 sec)

Затем запустите команду SHOW PROFILES, чтобы получить информацию о производительности для только что выполненного запроса SELECT:

SHOW PROFILES;

Вы получите примерно такой вывод:

+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00044075 | Select * from customers |
+----------+------------+-------------------------+
1 row in set, 1 warning (0.00 sec)

Выходные данные показывают общее время, потраченное MySQL на извлечение записей из БД. Позже мы сравним эти данные с результатом, полученным при извлечении записей с включенным кэшем запросов. Для этого запишите текущее значение Duration. Предупреждение в выводе можно проигнорировать: оно просто сообщает, что команда SHOW PROFILES будет удалена в следующем релизе MySQL и заменена схемой производительности (Performance Schema).

Выйдите из интерфейса командной строки MySQL.

quit;

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

4: Настройка кэша запросов

Сейчас у нас есть тестовая БД, где мы уже запустили SELECT при отключенном кэше запросов. Давайте теперь включим этот кэш, отредактировав конфигурационный файл MySQL.

Откройте файл в редакторе:

sudo nano /etc/mysql/my.cnf

Добавьте следующие строки в конец файла:

...
[mysqld] query_cache_type=1
query_cache_size = 10M
query_cache_limit=256K

Мы включили кэш запросов, установив для параметра query_cache_type значение 1. Также мы установили индивидуальный предельный размер запроса в 256 КБ и выделили 10 мегабайт для кэша запросов (значение параметра query_cache_size равно 10 МБ).

Сохраните и закройте файл. Затем перезапустите сервер MySQL, чтобы изменения вступили в силу:

sudo systemctl restart mysql

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

5: Тестирование MySQL с поддержкой кэша запросов

Теперь давайте повторим тот запрос, который мы выполнили в разделе 3, чтобы посмотреть, как кэш запросов оптимизировал производительность сервера MySQL.

Сначала подключитесь к серверу MySQL как root:

sudo mysql -u root -p

Введите свой root пароль для сервера базы данных и нажмите Enter, чтобы продолжить.

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

show variables like 'query_cache_%' ;

Вы увидите следующий вывод:

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 262144   |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 10485760 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.01 sec)

Переменная query_cache_type имеет значение ON; это подтверждает, что вы включили кэш запросов с параметрами, которые определили на предыдущем этапе.

Перейдите в базу данных sample_db, которую вы создали ранее.

Use sample_db;

Запустите профайлер:

SET profiling = 1;

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

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

Select * from customers;
Select * from customers;

Затем запросите информацию о профилях:

SHOW PROFILES;

Вы получите такой вывод:

+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00049250 | Select * from customers |
|        2 | 0.00026000 | Select * from customers |
+----------+------------+-------------------------+
2 rows in set, 1 warning (0.00 sec)

Как видите, время, затраченное на выполнение запроса, резко сократилось с 0,00044075 (без кэша запросов ) до 0,00026000 (обратите внимание на второй запрос).

Вы можете оценить преимущества кэширования запросов с помощью детального профилирования первого запроса:

SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000025 |
| Waiting for query cache lock   | 0.000004 |
| starting                       | 0.000003 |
| checking query cache for query | 0.000045 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000014 |
| init                           | 0.000018 |
| System lock                    | 0.000008 |
| Waiting for query cache lock   | 0.000002 |
| System lock                    | 0.000018 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000013 |
| preparing                      | 0.000010 |
| executing                      | 0.000003 |
| Sending data                   | 0.000048 |
| end                            | 0.000004 |
| query end                      | 0.000006 |
| closing tables                 | 0.000006 |
| freeing items                  | 0.000006 |
| Waiting for query cache lock   | 0.000003 |
| freeing items                  | 0.000213 |
| Waiting for query cache lock   | 0.000019 |
| freeing items                  | 0.000002 |
| storing result in query cache  | 0.000003 |
| cleaning up                    | 0.000012 |
+--------------------------------+----------+
25 rows in set, 1 warning (0.00 sec)

Выполните следующую команду, чтобы отобразить информацию о втором запросе:

SHOW PROFILE FOR QUERY 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| Waiting for query cache lock   | 0.000003 |
| starting                       | 0.000002 |
| checking query cache for query | 0.000006 |
| checking privileges on cached  | 0.000003 |
| checking permissions           | 0.000027 |
| sending cached result to clien | 0.000187 |
| cleaning up                    | 0.000008 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)

Выводы профайлера показывают, что MySQL потратил меньше времени на обработку второго запроса, потому что он мог извлечь данные из кэша запросов, а не с диска. Вы можете сравнить два вывода для каждого из запросов. Если вы посмотрите информацию о профиле для QUERY 2, состояние sending cached result to client показывает, что данные были прочитаны из кэша и таблицы не открывались (статуса Opening tables не будет).

Если на вашем сервере включена функция кэширования запросов MySQL, вы сможете улучшить скорость чтения данных.

Заключение

Вы настроили кэш запросов для ускорения работы сервера MySQL в Ubuntu 18.04. Эта функция может повысить скорость работы вашего веб-сайта или приложения. Кэширование устраняет обработку лишних операторов SQL и является очень и популярным методом оптимизации базы данных.

Читайте также: Настройка удаленной базы данных MySQL для оптимизации производительности сайта в Ubuntu 18.04

Tags: , ,

Добавить комментарий