Кэш запросов сервера MariaDB
Server Query Cache (QC) — это хорошо известная функция сервера MariaDB, которая кэширует операторы SQL и соответствующие наборы результатов. Если последующий запрос затем соответствует кэшированному оператору SQL, запрос может повторно использовать набор результатов. Кэш запросов также знает, как сделать недействительным кэш запросов, если таблица, соответствующая кэшированному запросу, изменена, например, с помощью UPDATE. Для получения дополнительной информации см. Кэш запросов в Базе знаний MariaDB .
В предыдущие десятилетия объем оперативной памяти был ограничен, диски были медленными, а процессоры имели одно ядро и один поток. Если вы хотели больше ядер, стоимость была ограничена — зарплата до двух лет для двух процессорных машин. В те дни Query Cache был хорошей идеей. Вероятность того, что данные будут храниться в кэш-памяти ограниченного размера, была небольшой. Получение данных с диска было плохой идеей, так как имело дело с медленной работой магнитных носителей. Сегодня у нас более быстрые твердотельные диски и больше оперативной памяти, поэтому кэш запросов менее важен. Уменьшение зависимости от кэша уменьшает его негативные последствия, такие как блокировка мьютекса.
Объект взаимного исключения (мьютекс) — это программный объект, который позволяет нескольким программным потокам совместно использовать ресурс (например, папку), но не одновременно. Mutex настроен на разблокировку, когда данные больше не нужны или когда процедура завершена. Mutex создает эффект узкого места. Блокировка означает, что только один запрос может одновременно просматривать кэш запросов, а другие запросы должны ждать. Запрос, который должен ждать, чтобы посмотреть в кэш только для того, чтобы обнаружить, что его нет в кэше, будет замедлен, а не ускорен.
Вы также можете быть заинтересованы в: Улучшение производительности MariaDB с помощью профилирования запросов
Query Cache Flags
Кэш запросов имеет три режима: ON, OFFи DEMAND. В OFFрежиме ничего не будет кешироваться. В любом из режимов ONили с помощью DEMANDфлагов можно определить, какие операторы кэшируются.
В DEMANDрежиме кэшируются только указанные операторы. Чтобы указать, что оператор должен кэшироваться, SQL_CACHEв SELECTоператоре устанавливается флаг :
SQL
1
SELECT SQL_CACHE id, name FROM products;
Этот SELECTоператор кэширует только текст SQL и соответствующий результат набора для указанных операторов. Три проблемы возникают с этим предположением:
- Приложение нуждается в модификации любого применимого
SELECTдля использования этого флага. - Если
SQL_CACHEвSELECTоператоре отсутствует, он не будет включен в кэш и не проверяет предыдущие кэшированные операторы; следовательно, он никогда не будет использовать Query Cache. - DML, такие как
INSERT,UPDATEиDELETEоператоры, необходимо проверить на предмет недействительности, что повлечет за собой блокирующие эффекты мьютекса Query Cache.
Если Query Cache есть ON, вы можете избежать кэширования оператора или его просмотра в кэше, включив SQL_NO_CACHEфлаг:
SQL
xxxxxxxxxx
1
SELECT SQL_NO_CACHE order_id, line_id, product, amt FROM order_item;
Query Cache Concerns
Помните, в предыдущие десятилетия были только один или два потока одновременно. Когда у вас был один процессор с одним потоком, мьютекс Query Cache не представлял особой проблемы, потому что было мало шансов, что другой поток будет работать одновременно. Мьютекс Query Cache не считался плохой вещью. Вы только что заметили, что когда Query Cache был включен, ваш сайт неожиданно работал в десять раз быстрее.
Сегодня, если вы используете современное оборудование с 16 потоками, даже без Query Cache, ваш сайт работает в сотни раз быстрее. Если Query Cache включен, мьютекс замедляет его до скорости, сравнимой с предыдущими десятилетиями. Тьфу.
Фильтр запросов MaxScale
Прокси-сервер базы данных MariaDB MaxScale предоставляет еще один кэш запросов в качестве части фильтра кеша. См. Кэш в Базе знаний MariaDB . Фильтр кэша преодолевает ограничения Query Cache внутри MariaDB Server, но добавляет несколько собственных препятствий.
Фильтр кэша MaxScale устраняет проблемы мьютекса; и он более настраиваемый по сравнению с кешем запросов сервера MariaDB. Вы можете указать, на какие таблицы, базы данных, столбцы и т. Д. Действует гибкий набор правил.
Возможность указать таблицы полезна, если мы не хотим, чтобы все таблицы кэшировались, но это усложняет настройку фильтра кэша. С такой сложностью, как работает аннулирование в фильтре запросов?
Мы могли бы проверить все DML, обработанные MariaDB MaxScale, а затем сделать недействительными соответствующие таблицы. Использование этого подхода не будет работать, потому что любая обработка внутри сервера MariaDB, которая модифицирует таблицу, не будет видна MaxScale, которая включает хранимые процедуры, функции и триггеры.
При использовании фильтра кеша каждый оператор имеет время жизни (TTL). Когда время ожидания истекает, кэш должен быть обновлен. Настройка сложна, потому что мы хотим кэшировать как можно дольше, поэтому нам не нужно повторно посещать сервер для обновления, но это означает, что возвращаемые данные могут быть устаревшими.
Фильтр запросов имеет расширенную конфигурацию времени выполнения для решения проблем настройки. MariaDB MaxScale полезен, если вы хотите предпринять это усилие, но его сложнее настроить и настроить, чем использовать Query Cache в MariaDB Server.
Mutex Work Around
Были предприняты попытки найти способ обойти мьютекс в Query Cache. К сожалению, поскольку мьютекс имеет центральное значение для внутренней обработки, он все еще не решен. Что если бы мы могли хотя бы указать, какие таблицы и операторы SQL кэшируются в кэше запросов сервера MariaDB? По-прежнему будет существовать проблема с заявлениями о недействительности кэша, но, по крайней мере, кэш фокусируется на таблицах, где имеет смысл его использовать, таких как таблицы с в основном статическим содержимым и где часто встречаются SELECTS (например, продукты на веб-сайте).
Выбор из таблицы продуктов будет быстрым, потому что мы можем кэшировать результат для многих запросов. Мы бы не увидели много недействительности кэша, так как набор продуктов редко меняется. Можем ли мы исправить это с помощью того, что доступно прямо сейчас? Да, давайте посмотрим.
Исправление кеша запросов
Когда тип Query Cache установлен на DEMAND, SELECTоператоры будут использовать Query Cache, если это явно указано SQL_CACHEфлагом. Это делается в файле конфигурации сервера MariaDB, /etc/my.cnf.d/server.cnf в разделе [mysqld] , где мы добавляем:
SQL
xxxxxxxxxx
1
query_cache_type=DEMAND
Теперь мы не хотим переписывать все наши приложения, чтобы выборочно использовать SQL_CACHEфлаг — мы будем использовать MariaDB MaxScale, чтобы сделать это для нас.
На этот раз используется фильтр регулярных выражений, так как это не фильтр запросов. Фильтр регулярных выражений интуитивно понятен и позволяет выполнять поиск и замену обрабатываемых операторов SQL. В этом случае SQL_CACHEдобавляется выбранные операторы. В приведенных ниже примерах показана простая таблица веб-сайтов с двумя таблицами ( товары и клиенты ), которые посещаются постоянно.
SQL
xxxxxxxxxx
1
CREATE TABLE `products` (
2
`id` int(11) NOT NULL,
3
`category` int(11) NOT NULL,
4
`name` varchar(255) DEFAULT NULL,
5
PRIMARY KEY (`id`)
6
) ENGINE=InnoDB;
7
CREATE TABLE `customers` (
9
`id` int(11) NOT NULL,
10
`name` varchar(255) DEFAULT NULL,
11
PRIMARY KEY (`id`)
12
) ENGINE=InnoDB;
1. Мы можем настроить фильтр регулярных выражений MaxScale для вставки SQL_CACHE в любой оператор, такой как:
SQL
xxxxxxxxxx
1
SELECT id, name FROM products WHERE category = 1;
или
SQL
xxxxxxxxxx
1
SELECT name FROM customer WHERE id = 42;
2. Эта конфигурация для фильтра регулярных выражений использует синтаксис PCRE2 для сопоставления:
SQL
xxxxxxxxxx
1
[QcOnDemand]
2
type=filter
3
module=regexfilter
4
match=(?i)^([[:space:]]*select)([[:space:]]+.*[[:space:]]+from[[:space:]]+(products|customers)([[:space:]]|$))
5
replace=$1 SQL_CACHE$2
Синтаксис регулярных выражений PCRE2 сложный, но мощный. Ниже приведено поэлементное объяснение приведенного выше регулярного выражения:
3. Следующие параметры конфигурации указывают маршрутизатору MariaDB MaxScale использовать созданный фильтр:
Файлы свойств
xxxxxxxxxx
1
[DefaultService]
2
type=service
3
router=readwritesplit
4
servers=server1
5
filters=QcOnDemand
4. После настройки перезапустите сервер MariaDB и MariaDB MaxScale. Теперь мы готовы к тестированию.
Тестирование исправленного кеша запросов
Чтобы проверить настроенный Query Cache, подключитесь к MariaDB через выбранный порт MariaDB MaxScale (в этом примере используется порт 4004).
1. Подключитесь к базе данных.
Джава
xxxxxxxxxx
1
$ mysql -h mydbhost -P 4004 -usomeuser -psomepassword test
2
Reading table information for completion of table and column names
3
You can turn off this feature to get a quicker startup with -A
4
5
Welcome to the MariaDB monitor. Commands end with ; or \g.
6
Your MariaDB connection id is 3
7
Server version: 10.4.6-MariaDB-log MariaDB Server
8
9
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
10
11
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
12
13
MariaDB [test]>
2. Просмотрите, что в настоящее время находится в кеше запросов.
Джава
xxxxxxxxxx
1
MariaDB [test]> show global status like 'qc%';
2
+-------------------------+---------+
3
| Variable_name | Value |
4
+-------------------------+---------+
5
| Qcache_free_blocks | 1 |
6
| Qcache_free_memory | 1031320 |
7
| Qcache_hits | 0 |
8
| Qcache_inserts | 0 |
9
| Qcache_lowmem_prunes | 0 |
10
| Qcache_not_cached | 265 |
11
| Qcache_queries_in_cache | 0 |
12
| Qcache_total_blocks | 1 |
13
+-------------------------+---------+
3. Обратите внимание, что таблицы order и order_line не должны кэшироваться в отличие от таблиц продуктов и клиентов .
Джава
xxxxxxxxxx
1
MariaDB [test]> select * from orders;
2
+----------+-------------+
3
| order_id | customer_id |
4
+----------+-------------+
5
| 1 | 1 |
6
| 2 | 7 |
7
| 9 | 2 |
8
+----------+-------------+
Столешницы попали в кеш запросов? Просмотрите следующее и обратите внимание, что в Query Cache ничего не было вставлено, и хитов не было.
Джава
xxxxxxxxxx
1
MariaDB [test]> show global status like 'qc%';
2
+-------------------------+---------+
3
| Variable_name | Value |
4
+-------------------------+---------+
5
| Qcache_free_blocks | 1 |
6
| Qcache_free_memory | 1031320 |
7
| Qcache_hits | 0 |
8
| Qcache_inserts | 0 |
9
| Qcache_lowmem_prunes | 0 |
10
| Qcache_not_cached | 485 |
11
| Qcache_queries_in_cache | 0 |
12
| Qcache_total_blocks | 1 |
13
+-------------------------+---------+
Далее, давайте проверим запрос по таблице продуктов .
Джава
xxxxxxxxxx
1
MariaDB [test]> select id, category, name from products where category = 1;
2
+----+----------+-----------------+
3
| id | category | name |
4
+----+----------+-----------------+
5
| 1 | 1 | Database system |
6
| 2 | 1 | Word processor |
7
+----+----------+-----------------+
Мы можем видеть продукты в таблице. Давайте еще раз посмотрим на состояние Query Cache.
Джава
xxxxxxxxxx
1
MariaDB [test]> show global status like 'qc%';
2
+-------------------------+---------+
3
| Variable_name | Value |
4
+-------------------------+---------+
5
| Qcache_free_blocks | 1 |
6
| Qcache_free_memory | 1029784 |
7
| Qcache_hits | 0 |
8
| Qcache_inserts | 1 |
9
| Qcache_lowmem_prunes | 0 |
10
| Qcache_not_cached | 737 |
11
| Qcache_queries_in_cache | 1 |
12
| Qcache_total_blocks | 4 |
13
+-------------------------+---------+
Была сделана вставка в кеш запросов. Когда мы снова запустим тот же запрос, будет ли у нас тот же результат?
Джава
xxxxxxxxxx
1
MariaDB [test]> select id, category, name from products where category = 1;
2
+----+----------+-----------------+
3
| id | category | name |
4
+----+----------+-----------------+
5
| 1 | 1 | Database system |
6
| 2 | 1 | Word processor |
7
+----+----------+-----------------+
8
9
MariaDB [test]> show global status like 'qc%';
10
+-------------------------+---------+
11
| Variable_name | Value |
12
+-------------------------+---------+
13
| Qcache_free_blocks | 1 |
14
| Qcache_free_memory | 1029784 |
15
| Qcache_hits | 1 |
16
| Qcache_inserts | 1 |
17
| Qcache_lowmem_prunes | 0 |
18
| Qcache_not_cached | 899 |
19
| Qcache_queries_in_cache | 1 |
20
| Qcache_total_blocks | 4 |
21
+-------------------------+---------+
Это сработало так, как должно было. В Query Cache есть одна вставка и один хит.
Далее давайте попробуем аннулировать кеш.
Джава
xxxxxxxxxx
1
MariaDB [test]> insert into products values(5,1,'Spreadsheet');
2
3
MariaDB [test]> show global status like 'qc%';
4
+-------------------------+---------+
5
| Variable_name | Value |
6
+-------------------------+---------+
7
| Qcache_free_blocks | 1 |
8
| Qcache_free_memory | 1031320 |
9
| Qcache_hits | 1 |
10
| Qcache_inserts | 1 |
11
| Qcache_lowmem_prunes | 0 |
12
| Qcache_not_cached | 1112 |
13
| Qcache_queries_in_cache | 0 |
14
| Qcache_total_blocks | 1 |
15
+-------------------------+---------+
Количество запросов в кеше равно 0, потому что кеш был признан недействительным. Давайте попробуем еще раз аннулировать кеш.
Джава
xxxxxxxxxx
1
MariaDB [test]> select id, category, name from products where category = 1;
2
+----+----------+-----------------+
3
| id | category | name |
4
+----+----------+-----------------+
5
| 1 | 1 | Database system |
6
| 2 | 1 | Word processor |
7
| 5 | 1 | Spreadsheet |
8
+----+----------+-----------------+
9
3 rows in set (0.001 sec)
10
11
MariaDB [test]> show global status like 'qc%';
12
+-------------------------+---------+
13
| Variable_name | Value |
14
+-------------------------+---------+
15
| Qcache_free_blocks | 1 |
16
| Qcache_free_memory | 1029784 |
17
| Qcache_hits | 1 |
18
| Qcache_inserts | 2 |
19
| Qcache_lowmem_prunes | 0 |
20
| Qcache_not_cached | 1232 |
21
| Qcache_queries_in_cache | 1 |
22
| Qcache_total_blocks | 4 |
23
+-------------------------+---------+
Оператор был вставлен в кеш, как и ожидалось ( Qcache_insertsтеперь 2). Один и тот же оператор был вставлен дважды, и набор результатов был признан недействительным между двумя исполнениями.
Счастливый SQL'ing