SQL_NO_CACHE and SQL_CACHE
There are two aspects to the query cache: placing a query in the cache, and retrieving it from the cache.
- Adding a query to the query cache. This is done automatically for cacheable queries (see () when the system variable is set to , or and the query contains no SQL_NO_CACHE clause, or when the system variable is set to , or , and the query contains the SQL_CACHE clause.
- Retrieving a query from the cache. This is done after the server receives the query and before the query parser. In this case one point should be considered:
When using SQL_NO_CACHE, it should be after the first SELECT hint, for example :
SELECT SQL_NO_CACHE .... FROM (SELECT SQL_CACHE ...) AS temp_table
instead of
SELECT SQL_CACHE .... FROM (SELECT SQL_NO_CACHE ...) AS temp_table
Настройка кэша запросов
Если MariaDB не был специально создан без кеша запросов, кеш запросов всегда будет доступен, хотя и неактивен. переменного сервера покажет , имеется ли кэш запросов.
SHOW VARIABLES LIKE 'have_query_cache'; + | Variable_name | Value | + | have_query_cache | YES | +
Если для него установлено значение , вы не сможете включить кеш запросов, если не перестроите или не переустановите версию MariaDB с доступным кешем.
Чтобы узнать, включен ли кеш, просмотрите серверную переменную . Он включен по умолчанию в версиях MariaDB до 10.1.6, но отключен, начиная с MariaDB 10.1.7 — при необходимости включите его, установив на .
Хотя этот параметр включен в версиях до MariaDB 10.1.7 , по умолчанию равен 0 КБ, что эффективно отключает кеш запросов. Начиная с версии 10.1.7 размер кэша по умолчанию равен 1 МБ. При необходимости установите для кеша достаточно большой размер, например:
SET GLOBAL query_cache_size = 1000000;
Начиная с MariaDB 10.1.7 , автоматически устанавливается в ON, если сервер запущен с параметром , установленным в ненулевое (и не по умолчанию) значение.
Дополнительные сведения см. В разделе « ниже.
Timeout and Mutex Contention
When searching for a query inside the query cache, a try_lock function waits with a timeout of 50ms. If the lock fails, the query isn’t executed via the query cache. This timeout is hard coded (MDEV-6766 include two variables to tune this timeout).
From the sql_cache.cc, function «try_lock» using TIMEOUT :
struct timespec waittime;
set_timespec_nsec(waittime,(ulong)(50000000L)); /* Wait for 50 msec */
int res= mysql_cond_timedwait(&COND_cache_status_changed,
&structure_guard_mutex, &waittime);
if (res == ETIMEDOUT)
break;
When inserting a query inside the query cache or aborting a query cache insert (using the KILL command for example), a try_lock function waits until the query cache returns; no timeout is used in this case.
When two processes execute the same query, only the last process stores the query result. All other processes increase the status variable.
Встроенный механизм кэширования запросов в MySQL.
MySQL содержит встроенный механизм кэширования запросов, который, однако не включен по умолчанию. Вот такие параметры выставленны по умолчанию в MySQL 5.0:
mysql> show variables like ‘query_cache%’;
+——————————+———+
| Variable_name | Value |
+——————————+———+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+——————————+———+
Чтобы включить кэш запросов и выделить под него 32 мегабайта памяти можно выполнить запрос set @@global.query_cache_size=32*1024*1024; с правами суперпользователя, а чтобы сделать эту настройку постоянной, следует добавить в my.cnf в подраздел строчку query_cache_size=32M. Второй полезный параметр — query_cache_limit задает максимальный объем результата выполнения запроса, который может быть помещен в кэш
После включения кэш работает автоматически:
- При каждом запросе типа SELECT вычисляет хэш-сумму строки запроса и ищет ее в кэше. Если находит — возвращает рузельтат из кэша, если нет — выполняет запрос, а результат заносит в кэш (если результат не больше значения query_cache_limit).
- При каждом запросе типа UPDATE, REPLACE, INSERT, DELETE, TRUNCATE или ALTER, удаляет из кэша все запросы, использующие таблицу, подвергшуюся обновлению.
Отметим следующие особенности работы кэша:
- Различие запросов определяется буквально, сравнение чувствительно к реестру. Поэтому SELECT * FROM news и select * FROM news будут для кэша двумя разными запросами.
- В кэш всегда попадает результат выполнения запроса целиком, результаты выполнения подзапросов не кэшируются.
- Кэш работает одинаково для запросов к таблицам с различными механизмами хранения. MySQL также кэширует запросы SELECT к представлениям (VIEW).
- Ряд запросов не подлежит кэшированию:
- Запросы, содержащие одну из недетерминированных функций: NOW(), SLEEP(), RAND(), CURTIME(), LAST_INSERT_ID() и.др.
- Запросы, использующие функции или хранимые процедуры, определенные пользователем.
- Запросы, использующие значения пользовательских или локальных переменных.
- Запросы, обращающиеся к базам данных mysql, INFORMATION_SCHEMA или performance_schema.
- Запросы, обращающиеся к таблицам, разбитым на партиции (начиная с MySQL версий 5.1.63, 5.5.23, 5.6.5).
- Запросы типа SELECT … FOR UPDATE, SELECT … IN SHARE MODE, SELECT … INTO OUTFILE, SELECT … INTO DUMPFILE, SELECT * FROM … WHERE autoincrement_col IS NULL.
- Запросы, использующие временные таблицы.
- Запросы, не обращающиеся к таблицам.
- Запросы, которые генерируют предупреждения (warnings).
- В случае, если пользователь имеет права не на всю таблицу, а только на определенные колонки таблицы. Это исключение — следствие того, что кэш запросов один для всех пользователей, а права доступа средствами кэша проверяются лишь на уровне таблиц.
В качестве примера запроса, который может быть кэширован, приведем запрос, формирующий список обсуждений на главной странице webew.ru:
SELECT t.*, (SELECT body FROM entities WHERE id=t.lastid) body
FROM
(SELECT e2.type,e2.id,e2.title,e2.active,
count(*)-IF(e2.type=3,1,) cnt, MAX(e.id) lastid, MAX(e.created) lastcreated
FROM entities e
LEFT JOIN entities e2
ON e2.id=IF(e.foreparent,e.foreparent,e.id)
WHERE e.active = 1 AND e2.active = 1 AND e.type=3
GROUP BY e2.id
ORDER BY MAX(e.created) DESC LIMIT 10) tORDER BY lastcreated DESC
Запрос выполняет сортировку статей и тем по дате последнего комментария. Обращения к главной странице сайта происходят чаще, чем добавление комментариев или статей, поэтому кэш достаточно эффективен.
2: Проверка стандартных переменных кэша запросов
В MySQL кэшем запросов управляет ряд переменных. Сейчас нам нужно проверить значения этих переменных, которые поставляются с MySQL по умолчанию, и разобраться, что контролирует каждая из них.
Чтобы проверить эти переменные, используйте следующую команду:
В выводе вы увидите переменные:
Значение 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 без кэша.
Query Cache Fragmentation
The Query Cache uses blocks of variable length, and over time may become fragmented. A high relative to may indicate fragmentation. will defragment the query cache without dropping any queries :
FLUSH QUERY CACHE;
After this, there will only be one free block :
SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 6101576 | | Qcache_hits | 31981126 | | Qcache_inserts | 43002404 | | Qcache_lowmem_prunes | 34696486 | | Qcache_not_cached | 655607 | | Qcache_queries_in_cache | 4197 | | Qcache_total_blocks | 8833 | +-------------------------+----------+
Increase Open Files Limit
To ensure good server performance, the total number of client connections, database files, and log files must not exceed the maximum file descriptor limit on the operating system (ulimit -n). Linux systems limit the number of file descriptors that any one process may open to 1,024 per process. On active database servers (especially production ones) it can easily reach the default system limit.
To increase this, edit /etc/security/limits.conf and specify or add the following:
This requires a system restart. Afterwards, you can confirm by running the following:
Optionally, you can set this via mysqld_safe if you are starting the mysqld process thru mysqld_safe,
or if you are using systemd,
Setting Up the Query Cache
Unless MariaDB has been specifically built without the query cache, the query cache will always be available, although inactive. The server variable will show whether the query cache is available.
SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+
If this is set to , you cannot enable the query cache unless you rebuild or reinstall a version of MariaDB with the cache available.
To see if the cache is enabled, view the server variable. It is enabled by default in MariaDB versions up to 10.1.6, but disabled starting with MariaDB 10.1.7 — if needed enable it by setting to .
Although enabled in versions prior to MariaDB 10.1.7, the is by default 0KB there, which effectively disables the query cache. From 10.1.7 on the cache size defaults to 1MB. If needed set the cache to a size large enough amount, for example:
SET GLOBAL query_cache_size = 1000000;
Starting from MariaDB 10.1.7, is automatically set to ON if the server is started with the set to a non-zero (and non-default) value.
See below for details.
Сжатие и оптимизация таблиц InnoDB
Файлы ibdata1 и ib_log
Большинство проектов с таблицами InnoDB имеют проблемы с большими файлами ibdata1 и ib_log. В большинстве случаев это связано с неправильной конфигурацией MySQL/MariaDB или архитектурой БД. Вся информация из таблиц InnoDB хранится в файле ibdata1, пространство которого само не используется. Я предпочитаю хранить данные таблицы в отдельных файлах ibd*. Для этого добавьте в my.cnf следующую строку:
innodb_file_per_table
или
innodb_file_per_table = 1
Если ваш сервер настроен и у вас есть продуктивные базы данных с таблицами InnoDB, сделайте следующее:
- Сделайте резервную копию всех баз данных на вашем сервере (кроме mysql и performance_schema). Вы можете получить дамп базы данных с помощью этой команды:
- После создания резервной копии базы данных остановите сервер mysql/mariadb;
- Измените настройки в my.cfg;
- Удалите файлы ibdata1 и ib_log;
- Запустите демон mysql/mariadb;
- Восстановить все базы из резервной копии:
После этого все таблицы InnoDB будут храниться в отдельных файлах, и ibdata1 перестанет экспоненциально расти.
Сжатие таблиц InnoDB
Вы можете сжимать таблицы с текстовыми данными / данными BLOB и экономить довольно много места на диске.
У меня есть база данных innodb_test, содержащая таблицы, которые потенциально могут быть сжаты, и поэтому я могу освободить место на диске. Прежде чем что-либо делать, я рекомендую сделать резервную копию всех баз данных. Подключитесь к серверу mysql:
# mysql -u root -p
Выберите нужную базу данных в консоли mysql:
# use innodb_test;
Чтобы отобразить список таблиц и их размеры, используйте следующий запрос:
SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)" FROM information_schema.TABLES WHERE table_schema = "innodb_test" ORDER BY (data_length + index_length) DESC;
Где innodb_test — имя вашей базы данных.
Некоторые таблицы могут быть сжаты. Возьмем для примера таблицу b_crm_event_relations. Запустите этот запрос:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;
После его запуска вы можете увидеть, что размер таблицы уменьшился с 26 МБ до 11 МБ из-за сжатия.
Сжимая таблицы, вы можете сэкономить много дискового пространства на вашем хосте. Однако при работе со сжатыми таблицами нагрузка на процессор возрастает. Используйте сжатие для таблиц db, если у вас нет проблем с ресурсами процессора, но есть проблема с дисковым пространством.
Стандартные конфигурации
Для удобства используйте одну из указанных конфигураций для вашего сервера:
- Для 1Гб оперативной памяти
- Для 2Гб оперативной памяти
- Для 4Гб оперативной памяти
- Для 8Гб оперативной памяти
- Для 16Гб оперативной памяти
- Для 32Гб оперативной памяти
- Для 64Гб оперативной памяти
- Для 128Гб оперативной памяти
Описание, рекомендации и значение параметра innodb_log_file_size
Описание, рекомендации и значение параметра slow_query_log
Описание, рекомендации и значение параметра innodb_buffer_pool_size
Описание, рекомендации и значение параметра thread_cache
Описание, рекомендации и значение параметра max_binlog_size
Описание, рекомендации и значение параметра thread_cache_size
Описание, рекомендации и значение параметра query_cache_size
Простой способ выбрать индексы для Mysql
DROP INDEX в Mysql
Просмотр профиля запросов в Mysql
Запросы для выборки самых больших таблиц
Создание индексов на рабочих MySQL таблицах без головной боли
И как правильно работать с длительными соединениями в MySQL
Как исправить ошибку доступа к базе 1045 Access denied for user
Запрос для определения версии Mysql: SELECT version()
Как создать индекс в Mysql
Обновление до новой версии Mysql
Включение и работа с логом медленных запросов в Mysql
Включение или восстановление репликации на работащих Mysql серверах
Что значит и как это починить
Описание, рекомендации и значение параметра log_slow_queries
Что делать, если во время репликации встречается: Error ‘Duplicate entry ‘115846’ for key ‘PRIMARY” on query. Default database: ‘db’. Query: ‘INSERT INTO some_table .
5 параметров, которые стоит оптимизировать в Mysql для wordpress
Оптимизация записи на диск в Mysql с помощью innodb_flush_method
Один из важных моментов в скорости работы сайта, это правильная оптимизация конфигурационного файла базы данных my.cnf, с которым многие сталкиваются в процессе оптимизации системных ресурсов. Зачастую многие приведенные в сети мануалы рассказывают о настройках которые были применены к слабым конфигурациям и это не дает нужного ответа.
Я обычно использую сервера на уровне ксеонов с 32 гигабайтами памяти, поэтому найти нужную конфигурацию настроек mysql достаточно трудно, отсюда и попытки настроить самому методом тыка и проб. Что из этого получилось сегодня я постараюсь рассказать.
Оптимизация MySQL
Конфигурация MySQL достаточно сложная, но, к счастью, вам не нужно в нее сильно углубляться. Есть специальный скрипт под названием MySQLTunner, который анализирует работу MySQL и дает советы какие параметры нужно изменить и какие значения для них установить. Скрипт поддерживает большинство версий MariaDB, MySQL и Percona XtraDB. Нам понадобится загрузить три файла с помощью wget:
Первый из них — это сам скрипт, написанный на Perl, второй и третий — база данных простых паролей и уязвимостей. Они позволяют обнаружить проблемы с безопасностью. Дальше можно переходить к тестированию. Я использую сервер с настройками mysql по умолчанию, установленными панелью управления VestaCP.
Буквально за несколько минут скрипт выдаст полную статистику по работе MySQL. Количеству запросов, занимаемому объему памяти и эффективности работы буферов. Вы можете ознакомиться со всем этим, чтобы лучше понять в чем причина проблем. Проблемные места обозначены красными восклицательными знаками. Например, здесь мы видим, что размер буфера движка таблиц InnoDB (InnoDB buffer pool) намного меньше, чем должен быть для оптимальной работы:
Кроме того, в самом конце вывода утилита предоставит список рекомендаций как исправить ситуацию. Мы рассмотрим все сообщения утилиты из этого примера и почему нужно использовать именно их, а не другие.
Все параметры нужно добавлять в /etc/my.cnf. Еще раз замечу, что вы не копируете статью, а смотрите что вам выдала утилита. Начнем с query-cache.
Скрипт рекомендует отключить кэш запросов. Query Cache — это кэш вызовов SELECT. Когда базе данных отправляется запрос, она выполняет его и сохраняет сам запрос и результат в этом кэше. И все бы ничего, но при использовании его вместе с InnoDB при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных — его надежнее отключить.
Оба параметра устанавливают размер памяти, которая используется для внутренних временных таблиц MySQL. Утилита рекомендует использовать объем больше 16 мегабайт, просто установите это ваше значение для обоих переменных, если у вас достаточно памяти, то можно выделить 32 или даже 64
Но важно чтобы оба значения совпадали, иначе будет использоваться минимальное
Этот параметр отвечает за количество потоков, которые будут закэшированны. После того, как работа с подключением будет завершена, база данных не разорвет его, а закэширует, если количество кэшированных потоков не превышает ограничение. Утилита рекомендует больше четырех, например, 16.
Указывает, что не нужно пытаться определить доменное имя для подключений извне. Ускоряет работу, так как не тратится время на DNS запросы.
Этот параметр определяет размер буфера InnoDB в оперативной памяти, от этого размера очень сильно зависит скорость выполнения запросов. Значение зависит от размера ваших таблиц и количества данных в них. Если памяти недостаточно, запросы будут обрабатываться дольше. У меня используется стандартный объем 128, а нужно больше 652.
Размер файла лога innodb должен составлять 25% от размера буфера. В случае 800 мегабайт это будет 200М. Но тут есть одна проблема. Чтобы изменить размер лога нужно выполнить несколько действий. Поскольку мы изменили все нужные параметры перейдем к перезагрузке сервера. Для нашего лога нужно остановить сервис:
Затем переместите файлы лога в /tmp:
И запустите сервис:
Когда размер лога меняется сервис видит поврежденный лог, выдает ошибку и не запускается. Поэтому сначала нужно удалить старый. После этого смотрите есть ли сообщения об ошибках:
Creating an Optimal MariaDB Instance
Store Data On A Separate Volume
It is always ideal to separate your database data on a separate volume. This volume is specifically for those types of fast storage volumes such as SSD, NVMe, or PCIe cards. For example, if your entire system volume will fail, you’ll have your database volume safe and rest assured not affected in case your storage hardware will fail.
innodb_buffer_pool_size
The primary value to adjust on a database server with entirely/primarily XtraDB/InnoDB tables, can be set up to 80% of the total memory in these environments. If set to 2 GB or more, you will probably want to adjust innodb_buffer_pool_instances as well. You can set this dynamically if you are using MariaDB >= 10.2.2 version. Otherwise, it requires a server restart.
tmp_memory_table_size/max_heap_table_size
For tmp_memory_table_size (tmp_table_size), if you’re dealing with large temporary tables, setting this higher provides performance gains as it will be stored in the memory. This is common on queries that are heavily using GROUP BY, UNION, or sub-queries. Although if max_heap_table_size is smaller, the lower limit will apply. If a table exceeds the limit, MariaDB converts it to a MyISAM or Aria table. You can see if it’s necessary to increase by comparing the status variables Created_tmp_disk_tables and Created_tmp_tables to see how many temporary tables out of the total created needed to be converted to disk. Often complex GROUP BY queries are responsible for exceeding the limit.
While max_heap_table_size, this is the maximum size for user-created MEMORY tables. The value set on this variable is only applicable for the newly created or re-created tables and not the existing ones. The smaller of max_heap_table_size and tmp_table_size also limits internal in-memory tables. When the maximum size is reached, any further attempts to insert data will receive a «table … is full» error. Temporary tables created with CREATE TEMPORARY will not be converted to Aria, as occurs with internal temporary tables, but will also receive a table full error.
innodb_log_file_size
Large memories with high-speed processing and fast I/O disk aren’t new and has its reasonable price as it recommends. If you are preferring more performance gains especially during and handling your InnoDB transactions, setting the variable innodb_log_file_size to a larger value such as 5Gib or even 10GiB is reasonable. Increasing means that the larger transactions can run without needing to perform disk I/O before committing.
join_buffer_size
In some cases, your queries tend to lack use of proper indexing or simply, there are instances that you need this query to run. Not unless it’s going to be heavily called or invoked from the client perspective, setting this variable is best on a session level. Increase it to get faster full joins when adding indexes is not possible, although be aware of memory issues, since joins will always allocate the minimum size.
MySQL Query Cache config & Monitoring
Here’s my query cache config used on another server to host this blog (This server also hosts a few other small to medium WordPress blogs. In total serves about 2 million page views per month)
This blog is now on hosted on its own tiny StackLinux VPS, database storage engine is InnoDB and query cache has been disabled (see above two config lines to disable if needed):
query_cache_type = 1 query_cache_limit = 256K query_cache_min_res_unit = 2k query_cache_size = 80M
To make sure MySQL Query Cache is enabled use:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
To monitor query cache stats use:
mysql> SHOW STATUS LIKE 'Qcache%';
Not sure what to do with the stats returned? I recommend using mysqltuner.pl. This script will help you to avoid the most obvious MySQL performance pitfalls (including query cache sizing) and get you to where at least your database isn’t poorly configured. Also, see MariaDB (MySQL) Memory Allocation.
Here’s a screenshot where I used Netdata (listed on the 100 Top Server Monitoring & Application Monitoring Tools) for query cache overview…
Oracle’s MySQL Query Cache sizing recommendation
So again, why is it recommended to keep MySQL’s query_cache_size small? Well, here’s what MySQL’s reference manual says about sizing the query cache:
Also have a look at MariaDB’s (alternative MySQL drop-in replacement ) help page: .
Now, due to all the variations of MySQL setups, hardware specs, and the fact that databases differ in demand, query types, etc etc, you will have to play around with your config and use the 200M max recommendation as a guideline to finding your query_cache_size sweet-spot. A MySQL query cache size of 200 megabytes may be fast on one server and begin to slow things down on another. As such, you’ll need to investigate. Setup slow-query logging and/or set up full query logging for a few minutes (depending on how busy your database is) and see how much time is spent in the query cache.
Query caching can give significant performance improvements when used correctly and/or in conjunction with Memcached or Redis cache. As mentioned, the key is, when you start tuning your MySQL query cache size, start small. You should adjust your “query_cache_limit” because the default of 1 megabyte may be too large. Allowing your cache to fill up too fast creates lots of cache prunes. Also, have a look at adjusting the “query_cache_min_res_unit” tuning parameter to combat cache fragmentation. Many times you won’t be able to store all cacheable queries in the query cache, but you can still make good use of it. Test, test, test!
Функция explain
Если запросы к БД довольно предсказуемы, проанализируйте запросы и создайте индексы для всех возможных столбцов. Для этого можно использовать функцию explain.
Для примера импортируйте образец БД MySQL:
Откройте сессию MySQL, чтобы создать несколько запросов:
Сначала нужно отключить использование кэша MySQL, чтобы определить точное время выполнения этих задач.
Запустите простой запрос большого объёма данных:
Чтобы просмотреть, как MySQL выполняет запрос, добавьте ключевое слово explain перед запросом:
Обратите внимание, поле key имеет значение NULL. Значит, для этого поля не используется индекс
Добавьте индекс для key и снова запустите запрос:
Как видите, это значительно улучшает обработку запросов.
Также при использовании индексов следует обратить внимание на слияние таблиц mysql. При этом нужно создать индексы и указать тот же тип данных для всех столбцов, которые будут использоваться для слияния
К примеру, у вас есть таблица по имени cheeses, и есть таблица ingredients. Эти таблицы можно объединить при помощи поля ingredient_id. Чтобы ускорить слияние таблиц, нужно создать индексы для этих полей.
Разбор параметров тюнинга Mysql
Разберёмся по порядку с каждым параметром настройки и вопросами которые есть при этом. Итак по пунктам.
key_buffer = 2Gkey_buffer_size = 4G
Так и не смог я понять, различаются ли эти два параметра или первый является устаревшим значением второго.
max_connections = 500 и thread-cache-size = 500
По замерам выходило, что не более 90 одновременных подключений, так и поставил 500 с запасом. Тут следует учесть что следующий параметр thread-cache-size должен быть одинаковым числом с максимальным соединением. Поэтому там также стоит 500.
table-cache = 120000 и table-open-cache = 120000
Здесь я поставил по 120000, так как таблиц у меня достаточно много, если у вас не много сайтов, то этот параметр можно не повышать.
interactive-timeout = 360
Установил в 360, чтобы снимались запросы, которые находятся без активности 6 минут или 360 секунд.
query_cache_limit = 12Mquery_cache_size = 4Gjoin_buffer_size = 512M
Следующие три параметра настроил исходя из следующих наблюдений. Пробовал ставить query_cache_size от 2 до 6 гигабайт, в итоге оптимально показалось 4. Обработка запросов до 12 мегабайт мне вполне хватало, поэтому оставил 12. Но есть такое мнение, что большой query_cache_size на самом деле сильно грузит систему и желательно держать кеш в memcashed, на практике я не заметил особо, чтобы он забирал мощность, а вот при проверке кеша, обнаружил, что много запросов проходит через него.
sort_buffer_size = 1Gread_buffer_size = 1Gread_rnd_buffer_size = 2G
Буфера поставил побольше, так как несколько баз имеют большой размер, хотя есть риск переполнения памяти, тем не менее они настолько не забивали память.
