Настройка количества потоков MySQL

max_connections

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

В случае веб-приложений нет особого смысла выставлять эту настройку намного больше количества процессов сервера приложения. Если в php-fpm или uwsgi для работы приложения выделено 16 процессов, то как правило каждый процесс будет устанавливать не более одного подключения к базе данных.

max_connections = 140

Для настройки можно воспользоваться значением переменных Max_used_connections и Threads_connected, первая из которых фиксирует максимальное когда-либо использованное количество подключений, а вторая содержит текущее количество подключений. Если Max_used_connections равно значению max_connections, то бывали случаи, когда использовались все доступные подключения к серверу. Для более обоснованного решения об увеличении максимального количества подключений стоит понаблюдать за графиком Threads_connected.

Для получения значений переменных Max_used_connections и Threads_connected можно воспользоваться, например, следующими запросами:

SHOW GLOBAL STATUS WHERE Variable_name = 'Max_used_connections';
SHOW GLOBAL STATUS WHERE Variable_name = 'Threads_connected';

max_user_connections

Максимальное количество подключений, которое может быть установлено одним и тем же пользователем.

thread_cache_size

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

Для подбора оптимального значения лучше обратить внимание на соотношение счётчиков Threads_created и Threads_cached. Эти чётчики увеличиваются на единицу при каждом создании нового потока и повторном использовании потока из кэша соответственно. Если это соотношение намного больше единицы, стоит увеличить значение опции thread_cache_size. Посмотреть эти значения можно, например, с помощью следующих запросов:

 SHOW GLOBAL STATUS WHERE Variable_name = 'Threads_created';
 SHOW GLOBAL STATUS WHERE Variable_name = 'Threads_cached';

Значение thread_cache_size не используется, если активирован алгоритм pool-of-threads (см. ниже описание опции thread handling) и не может превышать max_connections.

thread_handling

Опция настраивает метод назначения потоков клиентским подключениям и может принимать одно из следующих значений:

  • one-thread-per-connection - метод, используемый по умолчанию, когда каждому клиентскому подключению выделяется по одному выделенному потоку,
  • pool-of-threads (в MariaDB и Percona) или loaded-dynamically (в Oracle MySQL) - адаптивный метод, порождающий новые потоки, так чтобы на каждом процессорном ядре компьютера обрабатывался по меньшей мере один запрос,
  • no-threads - используется только один поток для обслуживания всех клиентских подключений, этот режим предназначен только для разработчиков в целях отладки.

thread_pool_size

Опция thread_pool_size задаёт максимальное количество одновременно работающих потоков для алгоритма pool-of-threads (см. выше описание опции thread handling). По умолчанию равно количеству процессорных ядер в системе, может принимать значения от 1 до 128.

Для настройки значения этой опции можно воспользоваться переменными Threadpool_threads и Threadpool_idle_threads, первая из которых содержит количество потоков в пуле, а вторая содержит количество простаивающих потоков.

SHOW GLOBAL STATUS WHERE Variable_name = 'Threadpool_threads';
SHOW GLOBAL STATUS WHERE Variable_name = 'Threadpool_idle_threads';

innodb_read_io_threads и innodb_write_io_threads

Чтением и записью данных на диск занимаются выделенные потоки MySQL. По умолчанию настроены 4 потока для чтения и 4 потока для записи. Можно настроить другое количество потоков в пределах от 1 до 64. Если файлы базы данных находятся на хранилище, состоящем из множества дисков, а компьютер обладает большим количеством процессорных ядер, то эти значения можно увеличить. Для одноплатных компьютеров, на которых в качестве хранилища используется SD-карта, можно наоборот уменьшить эти значения:

innodb_read_io_threads = 16
innodb_write_io_threads = 16

Для настройки можно воспользоваться переменными Innodb_data_pending_reads и Innodb_data_pending_writes, которые соответствуют количеству потоков, ожидающих выполнения операций чтения и записи соответственно. Понаблюдав за графиками их изменения, можно подобрать более обоснованные значения. Получить их можно, например, при помощи следующих запросов:

SHOW GLOBAL STATUS WHERE Variable_name = 'Innodb_data_pending_reads';
SHOW GLOBAL STATUS WHERE Variable_name = 'Innodb_data_pending_writes';

innodb_page_cleaners

Количество потоков очистки страниц, по умолчанию 1, начиная с версии 5.7.8 - 4, но не более innodb_buffer_pool_instances. См. также innodb_lru_scan_depth.

innodb_lru_scan_depth

Опция указывает, насколько глубоко нужно просматривать список последних изменений в буферном пуле InnoDB в поисках страниц для сборса на диск. По умолчанию просматривается 1024 изменения, минимальное значение равно 100. На системах с низкой интенсивностью изменения данных может оказаться полезным уменьшить значения этой опции.

innodb_thread_concurrency

Опция ограничивает количество одновременно работающих потоков. По умолчанию 0, то есть ограничений нет. Это приводит к деградации производительности. Рекомендуется подбирать значение, ориентируясь на сумму количества ядер и дисков в массиве. Можно попробовать удвоить одно из слагаемых.

Опция с похожим названием thread_concurrency имеет значение только для Solaris ниже 9 и не оказывает никакого влияния на Linux.

Использованные материалы