Оптимальная настройка Mysql

На одном из web-серверов клиента по случайным обстоятельствам упал MySQL. Сервер работает под управлением Debian 7, MySQL используется последней версии. Стандартные попытки перезагрузки сервиса проблему не решили. Попытка найти ответ в логах также не увенчалась успехом. Размер файла с логом был равен 0. Поскольку MySQL использовался сугубо для web-приложений, было принято решение сразу изменить созданный конфигурационный файл. На многих своих серверах я использую проверенный временем конфигурационный файл MySQL . В нем остается лишь изменить пару параметров, которые зависят от объема установленной на сервере памяти.

Пример оптимального конфигурационного файла MySQL

Здесь пример конфига для серверов с 512 мегабайтами ОЗУ. Если у вас больше, то правим key_buffer (всегда выделяю третью часть размера ОЗУ) и max_connections.

Port = 3306 socket = /var/run/mysqld/mysqld.sock socket = /var/run/mysqld/mysqld.sock nice = 0 user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking skip-name-resolve low-priority-updates bind-address = 127.0.0.1 key_buffer = 170M thread_stack = 512K thread_cache_size = 32 table_cache = 60000 thread_concurrency = 12 net_buffer_length = 32 myisam-recover = BACKUP query_cache_limit = 2M query_cache_size = 128M myisam_sort_buffer_size = 128M tmp_table_size = 128M max_heap_table_size = 128M read_buffer_size = 2M read_rnd_buffer_size = 4M sort_buffer_size = 2M join_buffer_size = 4M max_allowed_packet = 128M max_sort_length = 512 max_connections = 90 max_connect_errors = 50 max_user_connections = 15 log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1 expire_logs_days = 10 max_binlog_size = 300M innodb_buffer_pool_size = 64M innodb_additional_mem_pool_size = 32M innodb_file_io_threads = 8 innodb_lock_wait_timeout = 50 innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT transaction-isolation = READ-COMMITTED quick quote-names max_allowed_packet = 256M #no-auto-rehash key_buffer = 32M !includedir /etc/mysql/conf.d/

Перезапуск MySQL и проверка таблиц

После обновления конфига пробую перезапустить MySQL. Ввожу стандартную команду:

Service mysql restart

и служба магическим образом воскресает из мертвых, но выдает предупреждение:

Checking for tables which need to upgrade, are corrupt or were not closed cleanly

Есть подозрение на повреждение таблиц, поэтому лучше сразу выполнить проверку таблиц. Сделать проверку таблиц в MySQL совсем не сложно:

Mysqlcheck --check-upgrade --all-databases --auto-repair -u root -p mysql_upgrade --force -u root -p

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

A customer called me today asking for help with locating the configuration file used by one of their production MySQL instances. From the description I was given it appeared that their server had at least six different copies of my.cnf file in different locations on disk. And all were similar enough that each could actually be the one. All superfluous files were the result of a bit negligent system administration. So what turned to be the quickest and the least destructive way to find the correct one?

Initially suspecting the server was simply running more than just one MySQL instance, I logged in to take a deeper look. But I found only one mysqld process and, indeed, several configuration files.

All of them seemed good candidates:

/etc/my.cnf /etc/mysql/my.cnf /var/lib/mysql/my.cnf ...

In many cases you could simply check system process list using ps:

Server ~ # ps ax | grep "[m]ysqld" 10801 ? Ssl 0:27 /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock

In many cases, because it doesn"t really have to work every time. If configuration file was not specified explicitly by an init script starting the MySQL instance, then database would used the compiled-in default and such information would not be visible in the ps output. This could happen if for example the database instance was launched "by hand" from shell. The file information would also not be visible if the process line was truncated for any.

An alternative method could be examining information in /proc . /proc is the place where Linux kernel exposes a lot of internal information about itself, hardware and running processes through a bunch of virtual files and directories. Specifically each process has its own directory there that takes the name after the process id (or PID). Learning MySQL PID is as easy as running pidof mysqld .

One of the files we need is called cmdline . It contains the full command that started certain process.

Server ~ # cat /proc/$(pidof mysqld)/cmdline | tr "\0" "\n" /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf

The configuration information is clearly visible. The tr command simply converts any \0 characters into line breaks and is there just for readability.

Yet another approach could be browsing the process environment information. It can also be found in /proc in a file called environ . Sometimes a startup script may leave some information there:

Server ~ # tr "\0" "\n" < /proc/$(pidof mysqld)/environ | grep -i cnf MY_CNF=/etc/mysql/my.cnf

Finally you can try figuring out the compiled-in defaults, but it won"t necessarily tell you which configuration was actually used. This method is also not 100% safe as it means attempting to start another MySQL instance, even if only to print help message, because MySQL does not seem to handle this very well and it may produce some conflicts:

Server ~ # /usr/sbin/mysqld --help --verbose --skip-networking --pid-file=$(tempfile) 2> /dev/null | grep -A1 "Default options are read" Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

Specifying --pid-file here is essential as otherwise the new mysqld may overwrite the PID file of the running instance.

All in all I was able to help my customer. But there is no foolproof way. It might happen that in certain circumstances figuring out the true my.cnf location may not be possible.

Дефолтные конфигурационные параметры в Mysql рассчитаны на микроскопические базы данных, работающие под малыми нагрузками на скромном железе.

Настройка некоторых параметров может повысить производительность базы данных в сотни раз!

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

innodb_buffer_pool_size

Если Вы используете только InnoDB таблицы, устанавливайте это значение максимально возможным для Вашей системы. Буфер InnoDB кеширует и данные и индексы. Поэтому значение этого ключа стоит устанавливать в 70%...80% всей доступной памяти.

Innodb_buffer_pool_size = 24G

# При том, что на нашем сервере 32Гб оперативной памяти

innodb_log_file_size

Эта опция влияет на скорость записи. Она устанавливает размер лога операций (так операции сначала записываются в лог, а потом применяются к данным на диске). Чем больше этот лог, тем быстрее будут работать записи (т.к. их поместится больше в файл лога). Файлов всегда два, а их размер одинаковый. Значением параметра задается размер одного файла:

Innodb_log_file_size = 512M

# Так два файла дадут размер лога в 2x512M = 1G

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

innodb_log_buffer_size

Это размер буфера транзакций, которые не были еще закомичены. Значение этого параметра стоит менять в случаях, если вы используете большие поля вроде BLOB или TEXT.

Innodb_log_buffer_size = 2M

# Значения по умолчанию в 1М должно быть достаточно для большинства случаев

innodb_file_per_table

Если включить эту опцию, Innodb будет сохранять данные всех таблиц в отдельных файлах (вместо одного файла по умолчанию). Прироста в производительности не будет, однако есть ряд преимуществ:

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

innodb_file_per_table = ON

# С версии 5.6 этот параметр включен по умолчанию

innodb_flush_method

Этот параметр определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узов, вы будете выбирать между O_DSYNC и O_DIRECT :

Innodb_flush_method = O_DSYNC

# Помните об обязательном использовании резервных узлов (например, реплик)

innodb_flush_log_at_trx_commit

Изменение этого параметра может повысить пропускную способность записи данных в базу в сотни раз. Он определяет, будет ли Mysql сбрасывать каждую операцию на диск (в файл лога).

Тут следует руководствоваться такой логикой:

innodb_flush_log_at_trx_commit = 1 для случаев, когда сохранность данных — это приоритет номер один.
innodb_flush_log_at_trx_commit = 2 для случаев, когда небольшая потеря данных не критична (например, вы используете дублирование и сможете восстановить небольшую потерю). В этом случае транзакции будут сбрасываться в лог на диск только раз в секунду.

Устанавливайте значение на свое усмотрение, однако в большинстве случаев подойдет второй вариант:

Innodb_flush_log_at_trx_commit = 2

# Значительное ускорение записи в базу, однако это потребует механизмов дублирования данных

query_cache_size

Значение этого параметра определяет сколько памяти стоит использовать под кеш запросов. Самый правильный подход — не полагаться на этот механизм. На практике он работает очень неэффективно. Так, весь кеш запросов для определенной таблицы сбрасывается всякий раз, когда в таблицу вносится хотя бы одно изменение. Это может привести к тому, что включение кеширования даже замедлит базу данных:

Query_cache_size = 0

# Однако убедитесь, что используете индексы для обеспечения высокой скорости работы запросов

max_connections

Не следует изменять значение этого параметра на старте. Однако, если вы получаете ошибки "Too many connections" , эту опцию стоит поднимать. Она определяет максимальное количество одновременных соединений с базой данных:

Max_connections = 256

# Поднимайте значение постепенно при появлении ошибок соединений

TL;DR

Настройки по умолчанию скорее всего не подойдут. Поэтому обязательно стоит пройтись по указанным параметрам в статье и подобрать для них значения. Если совсем лень — .