При установке MySQL на Linux нет возможности выбрать схему с большим объёмом памяти или серверную схему MySQL, как это можно сделать при установке в Windows, поэтому оптимизацию и настройку можно проводить только после завершения установки.

Подготовительные действия

1. Остановите MySQL сервис. Выполните команду /etc/init.d/mysql stop. Если вы используете LNMP, вы можете просто выполнить lnmp mysql stop.
2. Найдите конфигурационный файл MySQL. Для LNMP он находится в /etc/my.cnf. Если у вас другой способ установки, используйте команду find / -name my.cnf, чтобы найти конфигурационный файл MySQL.

Оптимизация конфигурации

Цель оптимизации конфигурации MySQL состоит в том, чтобы повысить производительность MySQL без увеличения аппаратных ресурсов. Ниже приведены некоторые рекомендации по оптимизации MySQL на сервере с 16 ГБ оперативной памяти:

1. Настройка буферного пула

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

• innodb_buffer_pool_size: Этот параметр используется движком InnoDB для хранения данных таблиц и индексов. Его следует установить примерно на 70% от доступной памяти. На сервере с 16 ГБ оперативной памяти можно установить около 12 ГБ.
• key_buffer_size: Если вы используете движок MyISAM, установите этот параметр на 10% - 25% от доступной памяти.
• query_cache_size: Этот параметр используется для кэширования результатов запросов, что позволяет быстро возвращать результаты. Однако кэш запросов может негативно повлиять на производительность больших баз данных. На сервере с 16 ГБ памяти адекватный размер кэша запросов составляет от 128 МБ до 256 МБ.

2. Настройка количества подключений

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

• max_connections: Этот параметр указывает максимальное количество клиентских подключений, обрабатываемых одновременно. Обычно его следует устанавливать на значение, равное доступной памяти, делённой на приблизительное количество памяти, необходимое для одного подключенного клиента. Например, если вы хотите, чтобы каждое подключение использовало 500 КБ памяти, можно установить max_connections на 32.

3. Настройка параметров журналирования

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

• innodb_log_file_size: Этот параметр устанавливает размер файлов журналов транзакций для движка InnoDB. Рекомендуется установить его на 25% - 50% от размера буферного пула.
• innodb_flush_log_at_trx_commit: Этот параметр определяет частоту, с которой InnoDB сбрасывает буфер журнала на диск при выполнении транзакций. Если у вас нет строгих требований к согласованности данных, вы можете установить его на 0 или 2 для увеличения производительности.

4. Прочие настройки

• tmp_table_size и max_heap_table_size: Эти параметры определяют максимальный размер временных таблиц, используемых для операций, таких как сортировка. Если выделение памяти не удаётся, MySQL создаст эти таблицы на диске, что приведёт к снижению производительности. Рекомендуется установить их на 5% - 10% от стабильной максимальной доступной памяти.
• table_open_cache: Этот параметр задаёт размер кэша таблиц. По умолчанию MySQL позволяет открывать 64 таблицы. Если у вас много таблиц, вы можете увеличить это значение для повышения производительности.

5. Запуск MySQL

В конце выполните команду /etc/init.d/mysql start, а если вы используете LNMP, просто выполните lnmp mysql start.

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