При настройке серверов MySQL встречается ряд ошибок, которые ведут к снижению производительности. Вы можете столкнуться с ситуацией, когда повышая значение параметров, которые не должны негативно влиять на производительность, столкнетесь именно с таким поведением СУБД.
Избегайте произвольного увеличения размера буферов MySQL.
Статья является переводом на русский англоязычной статьи.
Непонятно откуда пошла такая практика, но стало нормой увеличивать размер и значение почти каждого параметра в my.cnf без особых на то причин. Давайте посмотрим на некоторые важные параметры my.cnf, где это не только ухудшит производительность MySQL, но и приведет к потере памяти сервера и, как следствие, уменьшит общую емкость и пропускную способность MySQL.
Буферы, такие как join_buffer_size, sort_buffer_size, read_buffer_size и read_rnd_buffer_size выделяются для каждого соединения. Поэтому установка read_buffer_size = 1M и max_connections = 150 настраивает MySQL для выделения 150 MB — по 1 МБ на соединение x 150 соединений.
В течение почти двух десятилетий значение read_buffer_size по умолчанию составляет 128 КБ. Увеличение значения по умолчанию является не только пустой тратой серверной памяти, но часто не способствует повышению производительности. Почти во всех случаях лучше использовать значения по умолчанию, удалив или закомментировав четыре вышеописанные строки конфигурации буфера.
Постепенно уменьшите текущие большие значения, чтобы освободить израсходованную оперативную память, продолжайте сокращать их до значений по умолчанию. Часто можно видеть улучшение пропускной способности за счет уменьшения этих буферов. Избегайте их произвольного увеличения!
Настройка join_buffer_size
Буфер для объединения join_buffer_size выделяется для каждой операции полного объединения двух таблиц. В документации MySQL join_buffer_size описывается следующим образом: «Минимальный размер буфера, который используется для сканирования простого индекса, сканирования индекса диапазона и объединений, которые не используют индексы и, таким образом, выполняют полное сканирование таблицы».
Далее говорится: « Время выделения памяти может привести к значительному падению производительности, если глобальный размер больше, чем необходимо для большинства запросов, использующих его ».
Буфер объединения выделяется для кэширования строк таблицы, когда объединение не может использовать индекс. Если ваши базы данных страдают от многих объединений, выполняемых без индексов, это не может быть решено простым увеличением join_buffer_size.
Проблема заключается в том, что «объединения выполняются без индексов», и поэтому решение для более быстрых объединений заключается в добавлении индексов.
Настройка sort_buffer_size
Если у вас нет данных, указывающих на обратное, вам следует избегать произвольного увеличения и sort_buffer_size. Память здесь также выделяется на каждое соединение. Документация MySQL предупреждает: «В Linux существуют пороговые значения выделения памяти, равные 256 КБ и 2 МБ, большие значения могут значительно замедлить выделение памяти, поэтому вам следует подумать о том, чтобы оставаться ниже одного из этих значений».
Избегайте увеличения sort_buffer_size выше 2M, поскольку вероятно снижение производительности, которое может нивелировать выгоды.
Эмпирическое правило при настройке MySQL
Хорошее практическое правило: если вы не можете назватть вескую причину увеличения любого из этих буферов, оставьте для них значения по умолчанию. К сожалению, эти четыре параметра конфигурации часто необоснованно увеличиваются при начальной настройке MySQL.
Если вы используете MariaDB, вы в большинстве случаев должны отдавать предпочтения таблицам типа Aria, а не MyISAM и InnoDB.
Надеемся, вы нашли эти советы по быстрой настройке MySQL полезными.