Что нужно настроить в mySQL сразу после установки?
- MySQL
- Перевод
Вольный перевод довольно старой статьи с MySQL Performance Blog о том, что лучше сразу же настроить после установки базовой версии mySQL.
Удивительно, сколько народу устанавливает mySQL на свои сервера и оставляют его с настройками по умолчанию.
Несмотря на то, что в mySQL существует довольно много настроек, которые Вы можете изменить, есть набор действительно очень важных характеристик, которые обязательно нужно оптимизировать под собственный сервер. Обычно после такой небольшой настройки производительность сервера заметно увеличивается.
- key_buffer_size - крайне важная настройка при использовании MyISAM-таблиц. Установите её равной около 30-40% от доступной оперативной памяти, если используете только MyISAM. Правильный размер зависит от размеров индексов, данных и нагрузки на сервер - помните, что MyISAM использует кэш операционной системы (ОС), чтобы хранить данные, поэтому нужно оставить достаточно места в ОЗУ под данные, и данные могут занимать значительно больше места, чем индексы. Однако обязательно проверьте, чтобы всё место, отводимое директивой key_buffer_size под кэш, постоянно использовалось - нередко можно видеть ситуации, когда под кэш индексов отведено 4 ГБ, хотя общий размер всех.MYI-файлов не превышает 1 ГБ. Делать так совершенно бесполезно, Вы только потратите ресурсы. Если у Вас практически нет MyISAM-таблиц, то key_buffer_size следует выставить около 16-32 МБ - они будут использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске.
- innodb_buffer_pool_size - не менее важная настройка, но уже для InnoDB, обязательно обратите на неё внимание, если собираетесь использовать в основном InnoDB-таблицы, т.к. они значительно более чувствительны к размеру буфера, чем MyISAM-таблицы. MyISAM-таблицы в принципе могут неплохо работать даже с большим количеством данных и при стандартном значении key_buffer_size , однако mySQL может сильно «тормозить» при неверном значении innodb_buffer_pool_size . InnoDB использует свой буфер для хранения и индексов, и данных, поэтому нет необходимости оставлять память под кэш ОС - устанавливайте innodb_buffer_pool_size в 70-80% доступной оперативной памяти (если, конечно, используются только InnoDB-таблицы). Относительно максимального размера данной опции - аналогично key_buffer_size - не стоит увлекаться, нужно найти оптимальный размер, найдите лучшее применение доступной памяти.
- innodb_additional_mem_pool_size - данная опция практически никак не влияет на производительность mySQL, однако рекомендую оставлять для InnoDB около 20 МБ (или чуть больше) под различные внутренние нужды.
- innodb_log_file_size - крайне важная настройка в условиях баз данных с частыми операциями записи в таблицы, в особенности при больших объёмах. Бо льшие размеры увеличивают быстродействие, однако будьте осторожны - увеличится и время восстановления данных. Я обычно выставляю значение около 64-512 МБ в зависимости от размера сервера.
- innodb_log_buffer_size - стандартное значение данной опции вполне подойдёт для большинства систем со средним количеством операций записи и небольшими транзакциями. Если же в Вашей системе бывают всплески активности, или Вы активно работаете с BLOB-данными, то рекомендую немного увеличить значение innodb_log_buffer_size . Однако не переусердствуйте - слишком большое значение будет пустой тратой памяти: буфер сбрасывается каждую секунду, поэтому Вам не понадобится больше места, чем требуется в течение этой секунды. Рекомендуемое значение - около 8-16 МБ, а для небольших баз - и того меньше.
- - жалуетесь, что InnoDB работает в 100 раз медленнее MyISAM? Вероятно, Вы забыли про настройку innodb_flush_log_at_trx_commit . Значение по умолчанию «1» означает, что каждая UPDATE-транзакция (или аналогичная команда вне транзакции) должна сбрасывать буфер на диск, что достаточно ресурсоёмко. Большинство приложений, в особенности ранее использовавшие таблицы MyISAM, будут хорошо работать со значением «2» (т.е. «не сбрасывать буфер на диск, только в кэш ОС»). Лог, однако, всё равно будет сбрасываться на диск каждые 1-2 секунды, поэтому в случае аварии Вы потеряете максимум 1-2 секунды обновлений. Значение «0» повысит производительность, но Вы рискуете потерять данные даже при аварийной остановке mySQL-сервера, в то время как при установке значение innodb_flush_log_at_trx_commit в «2» Вы потеряете данные только при аварии всей операционной системы.
- table_cache - открытие таблиц может быть весьма ресурсоёмко. К примеру, MyISAM-таблицы помечают заголовки.MYI файлов как «используемые в текущий момент». Обычно не рекомендуется открывать таблицы слишком часто, поэтому лучше, чтобы кэш был достаточных размеров, чтобы держать все Ваши таблицы открытыми. Для этого используется некоторое количество ресурсов ОС и оперативной памяти, однако это обычно не является существенной проблемой для современных серверов. Если у Вас несколько сотен таблиц, то стартовым значением для опции table_cache может быть«1024» (помните, что каждое соединение требует свой собственный дескриптор). Если у Вас ещё больше таблиц или очень много соединений - увеличьте значение параметра. Я видел mySQL сервера со значением table_cache равной 100 000.
- thread_cache - создание/уничтожение потоков также является ресурсоёмкой операцией, которая происходит при каждой установке соединения и каждом разрыве соединения. Я обычно выставляю эту опцию равную 16. Если у Вашего приложения могут быть скачки количество конкурентных соединений и по переменной Threads_Created виден быстрый рост количества потоков, то стоит увеличить значение thread_cache . Цель - не допускать создания новых потоков в условиях нормального функционирования сервера.
- query_cache_size - если Ваше приложение много и часто читает данные, и при этом у Вас нет кэша на уровне приложения, эта опция может очень помочь. Не ставьте здесь слишком большое значение, так как обслуживание большого кэша запросов будет само по себе затратным. Рекомендуемое значение - от 32 до 512 МБ. Не забудьте проверить, насколько хорошо используется кэш запросов - в некоторых условиях (при небольшом количестве хитов в кэше, т.е. когда практически не выбираются одинаковые данные) использование большого кэша может ухудшить производительность.
PS: инсталляция mySQL идёт с несколькими предустановленными файлами my.cnf, рассчитанными под разную нагрузку. Если Вам некогда настраивать сервер вручную, то обычно лучше использовать их, чем стандартный конфигурационный файл, выбрав тот, что больше подойдёт под нагрузку Вашего сервера.
Начиная с версии 3.22 MySQL может считывать принятые по умолчанию параметры запуска для сервера и клиентов из файлов параметров. В Unix считывание принятых по умолчанию параметров MySQL производится из следующих файлов:
DATADIR является каталогом данных MySQL (обычно "/usr/local/mysql/data" для бинарной установки или "/usr/local/var" для установки из исходных текстов). Обратите внимание, что это тот каталог, который был задан во время настройки, а не указанный при помощи –datadir при запуске mysqld! (–datadir не оказывает влияния на просмотр файлов параметров сервером, так как их просмотр происходит до обработки аргументов командной строки).
MySQL пытается прочитать файлы параметров в указанном выше порядке. Если существует несколько таких файлов, то параметр, указанный в файле, идущем позже, имеет преимущество над таким же параметром, указанным в файле, расположенном ранее. Параметры, указанные в командной строке, обладают более высоким приоритетом по отношению к параметрам, указанным в любом из файлов параметров. Некоторые параметры можно задавать при помощи переменных окружения. Параметры, указанные в командной строке или в файлах параметров, обладают преимуществом по отношению к переменным окружения.
Приводим список программ, поддерживающих файлы параметров: mysql, mysqladmin, mysqld, mysqld_safe, mysql.server, mysqldump, mysqlimport, mysqlshow, mysqlcheck, myisamchk и myisampack.
Любой параметр, который может быть задан в командной строке при запуске программы MySQL, может быть также задан в файле параметров (без предваряющего двойного слэша). Чтобы получить список доступных параметров, следует запустить программу с параметром –help.
Параметры my.cnf MySQL 5.5 (кодировки UTF8)
Что такое utf8mb4? utf8mb4 - набор символов, используемый для хранения 4 байта в MySQL, внедрён в 2010 году начиная с версии 5.5.3. Главное отличие utf8mb4 от utf8 в том, что utf8mb4 задействует более полные возможности кодировки UTF8, позволяя поддерживать все языки и специальные символы, не поддерживающие utf8 (например японский язык или смайлики из ios - emoji). Однако, как можно догадаться, если utf8mb4 использует для хранения 1 символа 4 байта, то база данных может увеличиться в размере, если сравнивать с точно такой же базой данных в utf8. В наше время немного увеличенный размер базы данных не является существенно проблему, по этому, если вы стоите перед выбором использовать utf8 или utf8mb4 набор символов - используйте utf8mb4.
Некоторые параметры my.cnf в MySQL 5.5.22 устарели (deprecated) и были заменены другими и удалены. Например, изменение кодировки по умолчанию в my.cnf в секции будет выглядит так:
#... character_set_server = utf8 # ранее default-character-set = utf8 и character_set_server = utf8 collation-server = utf8_unicode_ci # ранее collation_server = utf8_unicode_ci
collation-server = utf8_unicode_ci или collation-server = utf8_general_ci? utf8_unicode_ci поддерживает expansions в отличии от utf8_general_ci, то есть умеет сопоставлять один символ нескольким (например - в Германии ß = ss). Подобнее Unicode Character Sets .
Сравнение utf8_unicode_ci _ci без учета регистра, utf8_unicode_bin _bin с учетом регистра.
Параметры my.cnf
> ee /etc/my.cnf # The following options will be passed to all MySQL clients #password = your_password port = 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M default-character-set = utf8 character_set_server = utf8 collation_server = utf8_unicode_ci bind-address = 127.0.0.1 # Don"t listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking #...
Настройка MySQL сводится, в основном, к редактированию главного конфигурационного файла (/etc/my.cnf в FreeBSD). Перед настройкой следует учесть, что в MySQL 5.6 названия некоторых параметров и их наличие отличается от тех, которые использовались в предидущих версиях.
MySQL 5.6 - конфигурирование my.cnf
Для того, чтобы изменения в файле my.cnf вступили в силу, необходимо перезагрузить сервер MySQL:
/usr/local/etc/rc.d/mysqld restart
Проверить, восприняты ли новые настройки сервером, можно с помощью запроса к БД:
mysql> SHOW WARIABLES;
Чтобы просмотреть только определенные настройки, нужно конкретизировать запрос. Например, чтобы увидеть параметр max_connections нужно отправить в MySQL такой запрос: mysql> SHOW VARIABLES LIKE "max_ conn% " ;
Если после перезагрузки, изменения применились частично или не воспринимаются сервером MySQL, проверьте, возможно отредактирован не тот файл или MySQL дополнительно подгружает другой конфигурационный файл, директивы которого переназначают измененные вами параметры. Например, при установке панели управления хостингом DirectAdmin, сервер MySQL устанавливается автоматически и содержит 2 конфигурационных файла: /etc/my.cnf и дополнительно подгружаемый /usr/local/mysql/my.cnf. Изменяя параметр sql_mode в /etc/my.cnf я долго не мог понять, почему он не применяется к в MySQL сервере, как оказалось, он переопределялся в /usr/local/mysql/my.cnf (FreeBSD) или /usr/my.cnf (CentOS). Как найти список всех файлов my.cnf использующихся в MySQL можно посмотреть, введя запрос в поисковой системе: "my.cnf location".
Полный список настроек, которые используются в my.cnf можно посмотреть в официальном руководстве пользователя MySQL (eng), в колонке Option File.
Настройки в разделе
local_infile
Этой переменной можно разрешить (ON или 1 - по умолчанию) или запретить (OFF или 0) использовать LOCAL в запросе LOAD DATA. Если вы не знаете точно что это и зачем нужно, настоятельно рекомендуется переключить local_infile в OFF (local_infile=OFF ) из соображения безопасности сервера в целом.
skip_external_locking
skip_external_locking - параметр отвечающий за внешнюю блокировку файлов баз данных типа MyISAM (по умолчанию установлен в ON - блокировка включена). Рекомендуется не менять этот параметр из соображений быстродействия сервера MySQL.
skip_name_resolve
Если параметр skip_name_resolve установлен в ON или 1 (skip_name_resolve=OFF - по умолчанию), то при внешнем подключении к MySQL сервер пытается перевести название домена в IP-адрес, что заметно снижает скорость обработки запроса. Для повышения быстродействия, рекомендуется установить skip_name_resolve в OFF, в этом случае в качестве хоста при подключении к MySQL можно будет использовать только IP-адрес или localhost.
low_priority_updates
По умолчанию, такие операторы MySQL как INSERT, REPLACE, UPDATE, DELETE имеют более высокий приоритет, чем, например, SELECT, и параметр low_priority_updates, соответственно, установлен в OFF. Если Ваш сервер больше посылает запросов на чтение, чем изменение данных таблиц, можно установить low_priority_updates в ON. Следует отметить, что low_priority_updates применяется только к типам таблиц MyISAM, MEMORY и MERGE.
sql_mode
От параметров, указанных в sql_mode сильно зависит работа сервера MySQL. Не правильное указание настроек может полностью остановить работу сайта, использующего MySQL привести к вставке некорректных параметров в БД и другим проблемам. Подробнее об sql_mod можно прочитать тут: , Server SQL Modes 5.6 (eng) .
По умолчанию, в MySQL 5.6.6 и более поздних версиях значение sql_mode установлено в NO_ENGINE_SUBSTITUTION (sql_mode=NO_ENGINE_SUBSTITUTION ), что будет достаточно для большинства сайтов, но все же для понимания работы MySQL следует знать и о других способах работы MySQL, задаваемых в sql_mode.
max_connections
Этот параметр отвечает за максимально-допустимое кол-во одновременных подключений к MySQL. По умолчанию его значение равно 151 и может быть изменено в пределах от 1 до 100000. Увеличивать это значение следует, если появляется ошибка "Too many connections" или администратор уверен, что значения по умолчанию будет не достаточно.
query_cache_type
Значение query_cache_type включает (ON) или выключает (OFF) кеширование запросов. Кеширование - хороший способ снизить нагрузку, если сервер обрабатывает много одинаковых запросов. Использовать query_cache_type следует практически всегда, за исключением случаев, когда запросы MySQL кеширует memcached.
query_cache_size
Размер кеша запросов MySQL. Значение можно записать в Mb - query_cache_size=32M .
Настройки для таблиц MyISAM
key_buffer_size
Если используются только таблицы MyISAM , размер буфера следует установить в размере около 30-35% от размера доступной оперативной памяти. Если же MyISAM-таблиц очень мало или нет совсем, то key_buffer_size можно установить значение 32 МБ, место будет использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске. Выбор объема памяти для key_buffer_size зависит от размеров индексов, данных и нагрузки на сервер. Следует знать, что MyISAM использует кэш операционной системы, чтобы хранить там данные, поэтому нужно оставить достаточно места в ОЗУ под них. Данные могут занимать значительно больше места, чем индексы. Однако стоит проверить, что вся память, указанная в key_buffer_size под кэш, постоянно используется, иначе это будет расходование ресурсов в никуда.
Настройки для таблиц InnoDB
innodb_buffer_pool_size
innodb_buffer_pool_size - размер буфера таблиц InnoDB. Таблицы типа InnoDB используют свой буфер для хранения индексов и данных, поэтому нет необходимости оставлять память под кэш операционной системы, устанавливайте innodb_buffer_pool_size в 75% доступной оперативной памяти, если планируется использовать только таблицы с типом InnoDB. Рекомендации по максимальному размеру данной опции аналогичны key_buffer_size для MyISAM: не стоит устанавливать максимальный размер, нужно найти оптимальный вариант, а доступной ОЗУ можно найти применение и в других задачах.
В MySQL существует 2 основных движка: InnoDB
и MyISAM
. Таблицы баз данных обоих типов могут существовать на одном сервере. При необходимости их можно конвертировать, использование MyISAM оправдано при преобладающем количестве операций с данными одного вида: например SELECT или INSERT.
Во всех остальных случаях выгоднее использовать InnoDB — этот движок обеспечивает лучшую сохранность данность и более высокую скорость работы с ними (блокируется при операции с таблицей строка, а не вся таблица как в случае с MyISAM).
Любой тюнинг MySQL — любая тонкая настройка должна начинаться с определения преобладающего количества таблиц определенного типа.
Определение типа таблиц в MySQL
Делается это при помощи запроса вида (для innodb):
WHERE engine = "innodb";
Или (для myisam):
SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = "myisam";
Стоит выполнить оба запроса и оценить количество таблиц, относящихся к каждому движку.
Конфигурация MySQL в my.cnf
Ниже рассматриваются основные опции при конфигурации MySQL с преимущественным использованием InnoDB .
Все дальнейшие модификации производятся в конфигурационном файле /etc/mysql/my.cnf
innodb_buffer_pool_size
— размер буфера под InnoDB таблицы и индексы. При преобладании InnoDB таблиц стоит устанавливать значение равным 80% общего количества ОЗУ (8 Гб для сервера с 10 Гб RAM является нормой). Для более мощных серверов данное значение можно увеличивать еще вплоть до 95% доступной RAM.
innodb_buffer_pool_instances — очень важный параметр, определяющий количество инстансов, которые могут существовать, по умолчанию значение параметра равно 1, более оправданным является значение вычисляемое как сумма innodb_buffer_pool_size в гигабайтах и ядер CPU, деленная пополам.
innodb_buffer_pool_size in Gb + CPUs)/2
innodb_flush_log_at_trx_commit - значение устанавливается в 0, 1, 2. 0 означает, что лог сбрасывается на диск раз в секунду, вне зависимости от транзакций. При 1 лог сбрасывается при каждой завершенной транзакции. 2 — лог хранится в ОЗУ. Быстрее всего сервер баз данных будет работать при 0.
innodb_log_buffer_size - размер буфера лога 1-8 Мб являются хорошими значениями
innodb_log_file_size - максимальный размер каждого лог-файла. Можно увеличивать значение, это даст большую производительность поскольку не нужно будет ротировать лог слишком часто, открывать и закрывать новые файлы.
Общие параметры (не имеющие отношения к типу движка)
max_connections=2000 — устанавливаем параметр минимальным возможным при необходимости экономить ресурсы сервера, при возникновении в логе записей вида «Too many connections…» увеличиваем значение. 4000 клиентов является максимумом. Можно довести максимальное количество клиентов до 7000, но для стандартных сборок 4000 является пределом.
key_buffer=1024M — размер буфера под индексы в оперативной памяти. Оптимальное значение 20-25% доступной RAM. Если значение параметра слишком маленькое — данные начнут писаться в SWAP, что снизит скорость работы в разы.
Памяти под индексы выделяется достаточно если отношение значений Key_reads/Key_read_request оказывается < 0,01
Узнать значения параметров можно выполнив в консоли сервера баз данных запрос
SHOW STATUS LIKE «Key%»;
Также в выводе будут значения Key_write_requests и Key_writes
table_cache=2048 – максимальное число открытых таблиц для всех потоков.
Увеличение параметра означает увеличение файловых дескрипторов, используемых для работы MySQL.
Чтобы выявить необходимое значение нужно выполнить запрос
SHOW STATUS LIKE «Opened_tables%»;
Затем установить значение переменной несколько больше значения в выводе:
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Opened_tables | 1756 |
+—————+——-+
open_files_limit = 2048
Утсанавливать значение стоит опирясь на существующее количество открытых файлов MySQL
В конфигурационном файле задается большее значение.
sort_buffer=128M – ускоряет запросы ORDER BY или GROUP BY. Устанавливается не очень большим (как правило — в зависимости от среды)
Выделяемое количество ОЗУ будет определяться как значение sort_buffer, умноженное на Threads_running
SHOW STATUS LIKE «Threads_running%»;
record_buffer=720M — хорошим значеинем будет эквивалент sort_buffer умноженный на 4-6.
query_cache_limit=2M – максимальный размер результата выборки (или другого запроса), который будет кэшироваться. Значение можно увеличивать, по умолчанию установлен 1 Мб.
max_join_size=1000000 — максимальное количество записей, которые могут обрабатываться единовременно. Своего рода «защита от дупака» при использовании JOIN.
thread_cache_size=64 — задает колчество потоков обработки данных в кэше, т.е. количество потоков которые не создаются вновь при каждом запросе. Можно увеличивать параметр, это положительно скажется на быстродействии
