Настройка Linux для MySQL

Содержание

Общие рекомендации

Рекомендации по настройке Linux для MySQL:

• использовать свежие ядра Linux,
• использовать файловую систему ext4 или xfs,
• использовать опции монтирования noatime и nodiratime.

Уменьшение резерва блоков ext4

По умолчанию в файловой системе резервируется 5% блоков, которые доступны только пользователю root. В настоящее время диски стали очень большими, так что 5% зачастую составляет довольно внушительный объём:

# tune2fs -l /dev/vg0/srv | fgrep 'lock count'
Block count:              301989888
Reserved block count:     14578718

Размер одного блока можно узанать следующим образом:

# tune2fs -l /dev/vg0/srv | fgrep 'Block size'
Block size:               4096

Для того, чтобы оставить в резерве только 1% блоков, можно воспользоваться командой следующего вида:

# tune2fs -m 1 /dev/vg0/srv 
tune2fs 1.47.0 (5-Feb-2023)
Setting reserved blocks percentage to 1% (3019898 blocks)

Если же вы посчитали резервный объём самостоятельно, то задать количество резервных блоков можно с помощью команды следующего вида:

# tune2fs -r 3019898 /dev/vg0/srv
tune2fs 1.47.0 (5-Feb-2023)
Setting reserved blocks count to 3019898

Убедимся, что количество зарезервированных блоков уменьшилось до указанного числа:

# tune2fs -l /dev/vg0/srv | fgrep 'lock count'
Block count:              301989888
Reserved block count:     3019898

Изменение планировщика ввода-вывода

Рекомендуется поменять планировщик ввода-вывода с Completely Fair Queueing (CFQ) на Noop или Deadline. Посмотреть активный планировщик на каждом из дисков системы можно следующим образом:

$ cat /sys/block/sd*/queue/scheduler

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

# for d in /sys/block/sd*/queue/scheduler ; do echo deadline > $d ; done

Чтобы планировщик deadline использовался по умолчанию после загрузки системы, нужно открыть файл /etc/default/grub и добавить в переменную GRUB_CMDLINE_LINUX опцию elevator=deadline. Затем нужно выполнить команду для обновления конфигурации загрузчика:

# update-grub

Изменение регулятора процессора

Рекомендуется избегать использования регулятора ondemand. Проверим, какой регулятор настроен на каждом из ядер процессора:

$ cat /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor

Для достижения максимальной производительности можно воспользоваться регулятором performance. Настроить его для всех ядер процессоров можно с помощью следующей команды:

# for c in /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor ; do echo performance > $c ; done

Для того, чтобы регулятор performance использовался по умолчанию после загрузки системы, нужно открыть файл /etc/default/grub и добавить в переменную GRUB_CMDLINE_LINUX опцию cpufreq.default_governor=performance. Затем нужно выполнить команду для обновления конфигурации загрузчика:

# update-grub

Защита от вытеснения в область подкачки

Настройка переменной ядра

Первым делом выставим переменную ядра vm.swappiness, указывающую стоимость ввода-вывода с областью подкачки относительно стоимости ввода-вывода с файловой системой. Переменная должна принимать значение от 0 до 200, где 100 означает равную стоимость ввода-вывода с областью подкачки и стоимость ввода-вывода с файловой системой. Значение по умолчанию равно 60. Значение 0 предписывает использовать раздел подкачки только в случае нехватки оперативной памяти. Такое значение использовать не рекомендуется, т.к. для подкачки будут использоваться области памяти разделяемых библиотек и выполняемых файлов, которые в любой момент можно загрузить с диска, в ущерб вытеснению неиспользуемых областей данных. Выставим переменной vm.swappiness значение 1:

# sysctl -w vm.swappiness=1

И пропишем это значение в файл /etc/sysctl.conf, чтобы оно восстанавливалось при загрузке системы:

vm.swappiness = 1

Настройка прямого ввода-вывода

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

innodb_flush_method = O_DIRECT

Чтобы новые настройки вступили в силу, можно перезапустить MySQL:

# systemctl restart mysql

Настройка чередования областей памяти

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

Обычно в таких многопроцессорых системах в слоты каждого из процессоров устанавливают равный объём оперативной памяти. При запуске MySQL может запросить для хранения буферного пула InnoDB объём памяти, превышающий объём оперативной памяти, соответствующей одному процессору. В таком случае в первую очередь запрос удовлетворяется за счёт областей памяти, доступных первому процессору, а остальной объём удовлетворяется за счёт областей памяти следующих процессоров. В результате оказывается, что у первых процессоров весь объём памяти оказывается распределён, а свободная память остаётся только у последних процессоров.

Когда потоку MySQL, выполняющемуся на первом процессоре, понадобится выделить дополнительную область памяти, она будет распределена в объёме оперативной памяти, соответствющем одному из последних процессоров. В итоге поток будет вынужден работать с медленной оперативной памятью, которая доступна не через локальный контроллер оперативной памяти, а через контроллер оперативной памяти другого процессора.

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

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

innodb_numa_interleave = ON

Чтобы новые настройки вступили в силу, можно перезапустить MySQL:

# systemctl restart mysql

Фиксация областей памяти от вытеснения

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

memlock = ON

К сожалению, в документации MySQL написано, что для её использования процесс MySQL должен быть запущен с правами пользователя root. К счастью, в Linux существует механизм Capabilities, с помощью которого можно разрешить пользователю выполнять отдельные операции, доступны только пользователю root. В данном случае для выполнения операций фиксации областей памяти в оперативной памяти необходима привилегия CAP_IPC_LOCK. Включить её можно воспользовавшись средствами systemd. Отредактируем имеющийся в системе service-файл с помощью следующей команды:

# systemctl edit --full mysql

Имеющийся системный файл /lib/systemd/system/mysql.service будет скопирован в /etc/systemd/system/mysql.service, после чего для его редактирования будет вызван редактор по умолчанию. Найдём в файле секцию [Service] и добавим в неё опцию:

AmbientCapabilities=CAP_IPC_LOCK

Теперь нужно сообщить systemd о появлении обновлений в файлах конфигурации, для чего выполним следующую команду:

# systemctl daemon-reload

Чтобы новые настройки вступили в силу, можно перезапустить MySQL:

# systemctl restart mysql

Проверить, включилась ли опция memlock можно следующим образом:

# mysql -BNe "SHOW GLOBAL VARIABLES WHERE variable_name = 'locked_in_memory';"

Описанный выше способ включения этой опции по каким-то причинам не сработал.

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

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

Поскольку СУБД распределяет большие объёмы оперативной памяти, было бы неплохо воспользоваться возможностью увеличить размер страницы, уменьшив количество уровней в каталоге страниц. Таким образом будет с одной стороны уменьшена фрагментация оперативной памяти, а с другой стороны - увеличена скорость поиска физических адресов по виртуальным. И такая возможность поддерживается как самим микропроцессором, так и ядром операционной системы. Сократив глубину каталога страниц на один уровень, можнео перейти от страниц размером 4 килобайта к страницам размером 2 мегабайта. Сократив глубину каталога ещё на один уровень, можно перейти к использованию страниц размером 1 гигабайт.

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

Посмотрим на состояние огромных страниц в системе:

$ grep -i ^HugePages /proc/meminfo
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

Здесь:

• HugePages_Total - общее количество огромных страниц в пуле,
• HugePages_Free - количество свободных огромных страниц,
• HugePages_Rsvd - количество зарезервированных огромных страниц, которые были уже выделены, но пока свободны,
• HugePages_Surp - количество огромных страниц, которые были сформированы из пула обычных страниц,
• Hugepagesize - размер одной огромной страницы.

Узнаем объём буферного пула InnoDB:

$ mysql -BNe "SHOW GLOBAL VARIABLES WHERE variable_name='innodb_buffer_pool_size'" | awk '{ print $2; }'

Посчитаем требуемое общее количество огромных страниц в пуле как innodb_buffer_pool_size / Hugepagesize. Как показала практика, к получившемуся значению нужно добавить ещё 1/16, в противном случае в журнале ошибок MySQL /var/log/mysql/error.log будет небольшое количество ошибок следующего вида:

2023-10-18T05:15:51.577314Z 0 [Warning] InnoDB: Failed to allocate 140509184 bytes. errno 12
2023-10-18T05:15:51.577367Z 0 [Warning] InnoDB: Using conventional memory pool

Присвоим получившееся значение переменной ядра vm.nr_hugepages:

# sysctl -w vm.nr_hugepages=69632

При выполнении этой команды страницы из общего пула будут собраны в огромные страницы и переведены в пул огромных страниц. Убедитесь в том, что в системе есть достаточный объём оперативной памяти или места в области подкачки или запускайте команду при остановленном MySQL.

Узнаем идентификатор группы mysql с помощью следующей команды:

$ id -g mysql

И назначим этот идентификатор в качестве группы владельца пула огромных страниц:

$ sysctl -w vm.nr_hugepages=111

Вместо этого можно дать пользователю MySQL права CAP_IPC_LOCK, отредактировав service-файл с помощью следующей команды:

# systemctl edit --full mysql

Имеющийся системный файл /lib/systemd/system/mysql.service будет скопирован в /etc/systemd/system/mysql.service, после чего для его редактирования будет вызван редактор по умолчанию. Найдём в файле секцию [Service] и добавим в неё опцию:

AmbientCapabilities=CAP_IPC_LOCK

Теперь нужно сообщить systemd о появлении обновлений в файлах конфигурации, для чего выполним следующую команду:

# systemctl daemon-reload

Описанные выше расчёты и настройки можно произвести с помощью следующего скрипта:

#!/bin/sh

innodb_buffer_pool_size=`mysql -BNe "SHOW GLOBAL VARIABLES WHERE variable_name='innodb_buffer_pool_size'" | awk '{ print $2; }'`
hugepage_size=`awk '/^Hugepagesize: / { print $2 * 1024; } ' < /proc/meminfo`
hugepages=$((innodb_buffer_pool_size * 17 / hugepage_size / 16))
mysql_group_id=`id -g mysql`

echo "Add or update follow lines in /etc/sysctl.conf:"
sysctl -w vm.nr_hugepages=$hugepages
sysctl -w vm.hugetlb_shm_group=$mysql_group_id

Чтобы эти настройки применялись при загрузке системы, нужно прописать их в файле /etc/sysctl.conf.

Далее в файле конфигурации MySQL пропишем опцию, предписывающую использовать огромные страницы:

large_pages = ON

И теперь можно перезапустить MySQL, чтобы буферный пул InnoDB начал использовать огромные страницы:

# systemctl restart mysql

Убедиться в том, что огромные страницы начали использоваться, можно сравнив значения HugePages_Total, HugePages_Free и HugePages_Rsvd. Количество свободных страниц должно быть меньше общего количества, а количество зарезервированных - меньше количества свободных.

Отключение прозрачных огромных страниц

При использовании прозрачных огромных страниц система сама пытается сформировать из обычных страниц огромную страницу, для чего внутри ядра Linux предназначены потоки с именами kswapd, defrag, kcompactd, khugepaged. Формирование огромных страниц из обычных может создавать дополнительную нагрузку на центральный процессор, а сами такие страницы не защищены от вытеснения в область подкачки. К тому же, при выгрузке в область подкачки, огромная страница снова разбивается на обычные страницы.

Отключим прозрачную поддержку огромных страниц (Transparent Hugepages). Для этого выполним следующие команды:

# echo never > /sys/kernel/mm/transparent_hugepage/enabled
# echo never > /sys/kernel/mm/transparent_hugepage/defrag

Для того, чтобы прозрачная поддержка огромных страниц была отключена в процесе загрузки системы, нужно открыть файл /etc/default/grub и добавить в переменную GRUB_CMDLINE_LINUX опцию transparent_hugepage=never. Затем нужно выполнить команду для обновления конфигурации загрузчика:

# update-grub

Резервирование свободной памяти

В ситуациях острой нехватки оперативной памяти, например, из-за неправильно настроенного объёма огромных страниц и буферного пула MySQL, операционная система может войти в состояние пробуксовки, вытесняя по очереди в область подкачки страницы памяти, к которым происходят частые обращения. Для того, чтобы операционная система не вошла в такое состояние, рекомендуется выставить настройки минимального объёма свободной оперативной памяти:

Для серверов с объёмом оперативной памяти 64 гигабайта рекомендуется зарезервировать 1 гигабайт оперативной памяти:

# sysctl -w vm.min_free_kbytes=1048576

Для серверов с объёмом оперативной памяти 128 гигабайта рекомендуется зарезервировать 2 гигабайта оперативной памяти:

# sysctl -w vm.min_free_kbytes=2097152

Для серверов с объёмом оперативной памяти 256 гигабайт рекомендуется зарезервировать 3 гигабайта оперативной памяти:

# sysctl -w vm.min_free_kbytes=3145728

Чтобы эти настройки применялись при загрузке системы, нужно прописать их в файле /etc/sysctl.conf.

Настройка соотношения стоимости освобождения области подкачки и файлового кэша

Ядро операционной системы может посчитать выгодным выгрузить в область подкачки редко используемые области памяти для того, чтобы разместить в освободившихся областях кэш файловой системы. Для управления склонностью ядра освобождать память для кэширования файловой системы предназначена переменная ядра vm.vfs_cache_pressure. По умолчанию её значение равно 100, при котором ядро одинаково оценивает стоимость освобождения кэша файловой системы и области подкачки. При меньших значениях ядро предпочитает удерживать в памяти кэш файловой системы, а при нуле вовсе перестаёт удалять из кэша данные, что может привести к утечкам оперативной памяти. При значениях больше 100 ядро предпочитает чаще очищать кэш файловой системы. Значение 1000 означает в 10 раз более агрессивную очистку кэша файловой системы, чем по умолчанию.

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

# sysctl -w vm.vfs_cache_pressure=1000

Чтобы эти настройки применялись при загрузке системы, нужно прописать их в файле /etc/sysctl.conf.

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

• Mattias Geniar. How to change the reserved blocks on EXT3 or EXT4 filesystem in Linux
• Alexander Rubin. Linux performance tuning tips for MySQL
• Muhammad Irfan. InnoDB Performance Optimization Basics
• Ibrar Ahmed. Settling the Myth of Transparent HugePages for Databases
• Wenbo Zhang. Transparent Huge Pages: Why We Disable It for Databases
• Peter Zaitsev. Best Practices for Configuring Optimal MySQL Memory Usage
• MySQL 5.7 Reference Manual / ... / Enabling Large Page Support
• MySQL 8.0 Reference Manual / ... / Enabling Large Page Support
• Using mlock ulimits for SHM_HUGETLB deprecated
• Installing Red Hat Ceph Storage on Red Hat Enterprise Linux / 2.5. Tuning considerations for the Linux kernel when running Ceph
• Documentation for /proc/sys/vm/vfs_cache_pressure

Дополнительные материалы

• CPU Performance Scaling
• Documentation for /proc/sys/vm/swappiness
• В защиту swap'а [в Linux]: распространенные заблуждения
• Джастин Эллингвуд. Использование Systemctl для управления службами и блоками Systemd
• HugeTLB Pages