Настройка других опций MySQL
transaction_isolation
Уровень изоляции транзакций. Значение REPEATABLE-READ не покажет внутри транзакции новые данные, добавленные в другой транзакции. READ-COMMITED - наоборот, позволяет читать внутри транзакции данные, изменённые в других транзакциях.
Большинству приложений важна целостность и непротиворечивость данных, поэтому они используют базу данных как транзакционную (OLTP). В таком случае лучше использовать значение REPEATABLE-READ.
Если же приложение использует базу данных для аналитических запросов, то лучше подойдёт значение READ-COMMITED.
Бывают и приложения со смешанной логикой. Например, Zabbix хранит в базе данных как собственную конфигурацию, что больше соответствует OLTP, так и исторические данные со значениями определённых показателей в конкретные моменты времени, что больше соответствует OLAP. Но, т.к. нужно обеспечить непротиворечивость данных конфигурации, то запросы к таблицам истории тоже придётся выполнять на уровне изоляции транзакций REPEATABLE-READ.
transaction_isolation = REPEATABLE-READ
Для уверенности стоит поискать настройки, рекомендуемые разработчиками приложения. Если информации найти не удалось, более безопасным выбором будет REPEATABLE-READ.
innodb_file_per_table
Настройка, предписывающая хранить каждую таблицу базы данных в отдельном файле. Перед её выставлением необходимо сделать резервную копию всех баз данных. Чтобы настройка вступила в силу, нужно:
- остановить MySQL,
- удалить файлы ibdata1, ib_logfile0 и ib_logfile1,
- запустить MySQL снова,
- восстановить базы данных из резервных копий.
При восстановлении данных MySQL поместит каждую таблицу в отдельный файл.
Когда данные таблиц находятся в отдельных файлах, можно сравнительно легко вернуть в файловую систему место, освободившееся в таблице при удалении данных. Для этого достаточно запустить команду OPTIMIZE TABLE или ALTER TABLE ... FORCE над таблицей, которую нужно ужать. В случае, если используется общее хранилище для всех таблиц, неиспользуемое место никогда не возвращается на диск.
Есть у раздельного хранения данных таблиц и отрицательная сторона: при большом количестве таблиц (и секций таблиц, если они есть) увеличивается время запуска сервера MySQL.
Также операционная система обычно ограничивает количество одновременно открытых одним пользователем файлов, то серверу MySQL может потребоваться закрывать неиспользуемые файлы, чтобы открыть нужные и уложиться в этот лимит. Этот недостаток можно смягчить использованием настройки table_cache, описанной ниже, и изменением ограничений со стороны операционной системы.
Если у базы данных небольшой размер, а работать MySQL придётся на каком-нибудь микрокомпьютере, то возможно не стоит хранить данные таблиц в отдельных файлах. Ещё лучше в подобных случаях будет воспользоваться какой-нибудь встраиваемой базой данных, например, SQLite.
innodb_file_per_table = 1
table_open_cache и innodb_open_files
Опция table_open_cache ограничивает количество одновременно открытых файлов таблиц. Увеличение значения позволяет уменьшить количество открытий-закрытий файлов. Стоит оценить количество файлов в каталоге, где хранятся файлы с данными MySQL и выставить значение равного порядка.
table_open_cache = 512
Опция innodb_open_files имеет такой же смысл, что и опция table_open_cache, но относится только к движку InnoDB. По умолчанию эта опция принимает значение 300, а если включена опция innodb_file_per_table, то принимает значение, равное table_open_cache.
Стоит учитывать, что операционная система ограничивает количество одновременно открытых одним пользователем файлов и значение, указанное в опции, не должно быть больше разрешённого операционной системой лимита.
skip_name_resolve
При каждом подключении сервер MySQL выполняет поиск доменного имени компьютера клиента по его IP-адресу. Это может вызывать задержки при подключении. Для отключения поиска доменного имени по IP-адресу можно выставить в настройках сервера MySQL опцию следующего вида:
skip_name_resolve = 1
Стоит, однако, учитывать, что эту опцию можно безболезненно включать только если у каждого пользователя прописан IP-адрес, с которого он может подключаться, или указано одно из значений: % - любой узел или localhost - локальное подключение. Перед включением опции стоит выяснить IP-адреса пользователей и заменить доменные имена ими.
event_scheduler
Настройка, включающая встроенный в сервер MySQL планировщик задач. Позволяет по расписанию запускать запросы или хранимые процедуры:
event_scheduler = 1
query_cache_size и query_cache_type
Кэш результатов прошлых запросов. Если содержимое таблиц, фигурирующих в запросе, не менялось с момента кэширования результата предыдущего такого же запроса, то этот кэш позволит серверу сразу выдать клиенту результат запроса из кэша:
query_cache_size = 64M
Если содержимое таблиц постоянно меняется, а вероятность повторного выполнения запроса низка, то отключение этого кэша никак не скажется на производительности СУБД, но позволит сэкономить немного оперативной памяти. Для отключения кэша запросов можно указать такие опции:
query_cache_type = 0
query_cache_size = 0
general_log_file
Общий журнал. Содержит, например, сведения о запусках и остановках сервера.
general_log_file = /var/log/mysql/mysql.log
log_error
Журнал ошибок:
log_error = /var/log/mysql/mysql.err
log_warnings и log_error_verbosity
Не выводить предупреждающие сообщения в журнале ошибок:
log_warnings = 0
Начиная с MySQL 5.7.2 опция log_warnings объявлена устаревшей и вместо неё следует использовать опцию log_error_verbosity, которая принимает значения от 1 до 3:
- Выводить только сообщения об ошибках,
- Выводить сообщения об ошибках и предупреждения,
- Выводить сообщения об ошибках, предупреждения и информационные сообщения.
Таким образом, для отключения вывода в журнал предупреждений можно воспользоваться такой опцией:
log_error_verbosity = 1
slow_query_log, slow_query_log_file, long_query_time, log_queries_not_using_indexes, log_slow_admin_statements
Сервер может вести журнал медленных запросов. Для этого нужно включить журнал медленных запросов, указать его расположение и указать минимальную длительность запросов, которые будут попадать в журнал, в секундах:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slowquery.log
long_query_time = 10
Для поиска запросов, не использующих индексы, можно включить соответствующую опцию:
log_queries_not_using_indexes = 1
Для того, чтобы в журнал медленных запросов попадали запросы DDL, манипулирующие структурой таблиц и индексами, можно включить соответствующую опцию:
log_slow_admin_statements = 1
character-set-server и collation-server
Настройка кодировки сервера по умолчанию и настроек сортировки и сравнения символов:
character-set-server = utf8
collation-server = utf8_general_ci