Оптимальная настройка Mysql
Дефолтные конфигурационные параметры в Mysql рассчитаны на микроскопические базы данных, работающие под малыми нагрузками на скромном железе.
Настройка некоторых параметров может повысить производительность базы данных в сотни раз!
Процесс оптимальной настройки Mysql состоит из двух частей — первоначальная настройка и корректировка параметров во время работы. Корректировка параметров в рабочем режиме во многом зависит от специфики Вашей системы и ее мониторинга. Разберемся с параметрами и рекомендациями по установке их значений.
Настройки нужно вносить в my.cnf.
innodb_buffer_pool_size
Если Вы используете только InnoDB таблицы, устанавливайте это значение максимально возможным для Вашей системы. Буфер InnoDB кеширует и данные и индексы. Поэтому значение этого ключа стоит устанавливать в 70%. 80% всей доступной памяти.
# При том, что на нашем сервере 32Гб оперативной памяти
innodb_log_file_size
Эта опция влияет на скорость записи. Она устанавливает размер лога операций (так операции сначала записываются в лог, а потом применяются к данным на диске). Чем больше этот лог, тем быстрее будут работать записи (т.к. их поместится больше в файл лога). Файлов всегда два, а их размер одинаковый. Значением параметра задается размер одного файла:
# Так два файла дадут размер лога в 2x512M = 1G
Стоит понимать , что увеличение этого параметра увеличит и время восстановления системы при сбоях. Это происходит потому, что при запуске системы все данные из логов будет накатываться на данные. Однако с каждой новой версией, производительность этого процесса растет. Подумайте над использованием реплик для обеспечения доступности, чтобы не зависеть от времени восстановления базы данных.
innodb_log_buffer_size
Это размер буфера транзакций, которые не были еще закомичены. Значение этого параметра стоит менять в случаях, если вы используете большие поля вроде BLOB или TEXT.
# Значения по умолчанию в 1М должно быть достаточно для большинства случаев
innodb_file_per_table
Если включить эту опцию, Innodb будет сохранять данные всех таблиц в отдельных файлах (вместо одного файла по умолчанию). Прироста в производительности не будет, однако есть ряд преимуществ:
- При удалении таблиц, диск будет освобождаться. По умолчанию общий файл данных может только расширяться, но не уменьшаться.
- Использование компрессионного формата таблиц потребует включить этот параметр.
# С версии 5.6 этот параметр включен по умолчанию
innodb_flush_method
Этот параметр определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узов, вы будете выбирать между O_DSYNC и O_DIRECT :
# Помните об обязательном использовании резервных узлов (например, реплик)
innodb_flush_log_at_trx_commit
Изменение этого параметра может повысить пропускную способность записи данных в базу в сотни раз . Он определяет, будет ли Mysql сбрасывать каждую операцию на диск (в файл лога).
Тут следует руководствоваться такой логикой:
- innodb_flush_log_at_trx_commit = 1 для случаев, когда сохранность данных — это приоритет номер один.
- innodb_flush_log_at_trx_commit = 2 для случаев, когда небольшая потеря данных не критична (например, вы используете дублирование и сможете восстановить небольшую потерю). В этом случае транзакции будут сбрасываться в лог на диск только раз в секунду.
Устанавливайте значение на свое усмотрение, однако в большинстве случаев подойдет второй вариант:
# Значительное ускорение записи в базу, однако это потребует механизмов дублирования данных
query_cache_size
Значение этого параметра определяет сколько памяти стоит использовать под кеш запросов. Самый правильный подход — не полагаться на этот механизм. На практике он работает очень неэффективно. Так, весь кеш запросов для определенной таблицы сбрасывается всякий раз, когда в таблицу вносится хотя бы одно изменение. Это может привести к тому, что включение кеширования даже замедлит базу данных :
# Однако убедитесь, что используете индексы для обеспечения высокой скорости работы запросов
max_connections
Не следует изменять значение этого параметра на старте. Однако, если вы получаете ошибки «Too many connections» , эту опцию стоит поднимать. Она определяет максимальное количество одновременных соединений с базой данных:
# Поднимайте значение постепенно при появлении ошибок соединений
Настройки по умолчанию скорее всего не подойдут. Поэтому обязательно стоит пройтись по указанным параметрам в статье и подобрать для них значения. Если совсем лень — генератор настроек Mysql.
Check-unused-keys для определения неиспользуемых индексов в базе данных
Повышение скорости работы запросов с MySQL Handlersocket
Что такое индексы в Mysql и как их использовать для оптимизации запросов
Синтаксис и оптимизация Mysql LIMIT
Оптимизация постраничного вывода данных
Эффективная замена ORDER BY RAND()
Сравнение Vertica и Mysql на практике
Включение и использование логов ошибок, запросов и медленных запросов, бинарного лога для проверки работы MySQL
Правила выбора типов данных для максимальной производительности в Mysql
Ускорение репликации в Mysql 5.6+
Как восстановить данные, если MySQL упал и не поднимается
Быстрая альтернатива Mysqldump для больших таблиц без блокировок и выключений.
Анализ медленных запросов с помощью EXPLAIN
Правильный поиск по тексту в Mysql (full-text search)
Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit
3 примера установки индексов в JOIN запросах
Рекомендации по настройке Redis для оптимизации ресурсов и повышения стабильности на производственном сервере
Использование партиций для ускорения сложных удалений
Сравнение двух движков и когда стоит использовать каждый из них
И как правильно работать с длительными соединениями в MySQL
Проверка работы Mysql под нагрузкой Sysbench
Настройка Master-Slave репликации на MySQL за 6 простых шагов
Настройки для улучшения производительности Postgres
Примеры использования колоночной базы данных Vertica
Источник
Настройка MySQL
Основное конфигурирование MySQL осуществляется с помощью файла /etc/my.cnf или /etc/mysql/my.cnf
Указание кодировок и collation
В секцию [mysqld] добавим строки:
character-set-server=utf8
collation-server=utf8_general_ci
init-connect=»SET NAMES utf8″
- character-set-server — кодировка для всего сервера;
- collation-server — порядок символов и строк на основе алфавитного порядка и классов эквивалентности;
- init-connect — строка, выполняемая для каждого клиента при соединении.
Ограничение количества, таймауты и источники соединений
bind-address=localhost
# Отключаем определение доменного имени для IP-адресов
skip-name-resolve
# Максимальное количество соединений
max_connections = 250
# Отключаем использованием symbolic-links
symbolic-links=0
# Таймауты
interactive_timeout=60
wait_timeout=60
Увеличение числа открытых файлов
В большинстве Linux-систем по умолчанию лимит открытия файловых дескрипторов установлен в 1024, для работы этого недостаточно.
Проверим текущие опции:
Внесем требуемые лимиты в /etc/security/limits.conf
Динамически изменим текущие лимиты:
Проверим soft limit:
Текущие лимиты в MySQL проверим SQL-запросом:
SHOW VARIABLES LIKE ‘%open_files%’
Оптимизация MySQL для MyISAM
Оптимизация параметров MySQL позволяет значительно увеличить производительность MyISAM.
Буферы
Основными параметрами являются key_buffer_size (буфер для работы с ключами и индексами) и sort_buffer (буфер для сортировки).
key_buffer_size = 64M
sort_buffer_size = 32M
При наличии 16Гб памяти и более, рекомендуется увеличить key_buffer_size до 128M-256M. Если Вы не используете MyISAM таблицы, рекомендуется установить размер key_buffer_size в 32Мб для хранения индексов временных таблиц.
Кэш запросов указывается в опции query_cache_size, ограничение на кэшируемый элемент в query_cache_limit, кэш открытых таблиц в table_open_cache.
table_open_cache = 2048
query_cache_limit = 2M
query_cache_size = 128M
query_cache_type = 1
thread_cache_size = 16
max_heap_table_size = 128M
tmp_table_size = 128M
Будьте внимательны при установке завышенного значения query_cache_size, т.к. это может привести к ожиданию блокировок (Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache during updates, you may see lock contention issues with a very large cache). Мы не рекомендуем устанавливать значение больше 256M.
Параметр thread_cache_size указывает количество тредов (threads), уходящих в кеш при отключении клиента. При новом подключении тред не создается, а берется из кеша, что позволяет экономить ресурсы при больших нагрузках. При наличии 32Гб памяти и более рекомендуем увеличить thread_cache_size до 32, table_open_cache в диапазон 4096-8192, query_cache_size до 256M.
Перенос временных файлов MySQL в память
Проверяем наличие /dev/shm:
Настройки размещаются в /etc/fstab , рекомендуем указать размер, например, 1G:
none /dev/shm tmpfs defaults,size=1G 0 0
Если внесли изменения, то перемонтируем:
mount -o remount /dev/shm
В конфигурационном файле указываем:
В случае, если используется Apparmor, то внесите используемый путь (/dev/shm или /run/mysql) в конфигурационный файл /etc/apparmor.d/usr.sbin.mysqld, например:
service apparmor restart
Оптимизация MySQL для InnoDB
Стандартно все таблицы и индексы хранятся в одном файле, мы рекомендуем использовать опцию innodb_file_per_table для установки хранения каждой таблицы в отдельном файле. Дополнительно необходимо корректно рассчитать параметр innodb_open_files, до версии MySQL 5.6.6 он устанавливался в значение 300, с версии MySQL 5.6.6 рассчитывается автоматически и имеет значение по умолчанию -1.
Значение innodb_open_files и table_open_cache рассчитывается как количество таблиц во всех базах, умноженное на 2, ориентировочно рекомендуем устанавливать обе опции в 4096 или 8192.
innodb_file_per_table = 1
table_open_cache = 4096
innodb_open_files = 4096
При использовании только InnoDB часть опций требует корректировки:
key_buffer_size = 32M
max_allowed_packet = 1M
sort_buffer_size = 32M
read_buffer_size = 256K
read_rnd_buffer_size = 1M
thread_stack = 128K
query_cache_limit = 1M
query_cache_size = 0
query_cache_type = 1
thread_cache_size = 32
max_heap_table_size = 128M
tmp_table_size = 128M
Обратите внимание, при работе с InnoDB мы отключаем query_cache_size установкой его значения в 0, исключающее лишние действия по работе с кэшем, что особенно важно при активной работе с большими объемами данных. С версии MySQL 5.6 query_cache_size отключен, а с версии 8.0 удален.
Важнейшей настройкой MySQL при работе с InnoDB является innodb_buffer_pool_size, устанавливается по принципу «чем больше, тем лучше». Рекомендуется выделять до 70-80% оперативной памяти сервера под innodb_buffer_pool_size. Не забудьте провести расчет использования памяти Apache и дополнительным программным обеспечением для исключения сваливания системы в swap. Для сервера с 16Гб устанавливается в диапазоне 10-12G и разделяем его на 4 секции, для сервера с 32Гб устанавливаем значение в диапазоне 20-24G и разделяем его на 8 секций, :
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 4
Установка большого размера innodb_log_file_size может привести к увеличению быстродействия, при этом увеличится время восстановления данных, выберите от 258M до 1G.
innodb_log_file_size = 256M
innodb_log_buffer_size = 32M
При установке значения в 2 буфер не сбрасывается на диск, а только в кэш операционной системы. Установка значения в 0 увеличит быстродействие, однако возможна потеря последних данных при аварийном выключении mysql-сервера.
Количество потоков ввода/вывода файлов в InnoDB задается опциями innodb_read_io_threads, innodb_write_io_threads, обычно этому параметру присваивается значение 4 или 8, на быстрых SSD-дисках установите в 16. Значение innodb_thread_concurrency установите в количество ядер * 2.
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_thread_concurrency = 32
Для ускорения работы с INFORMATION_SCHEMA, SHOW TABLE STATUS или SHOW INDEX отключим обновление статистики при выполнении таких операций, с версии MySQL 5.6.6 innodb_stats_on_metadata отключено по умолчанию:
Для версии MySQL 5.7+ укажите дополнительные опции:
performance_schema = OFF
skip-log-bin
sync_binlog = 0
После применения изменений перезагрузите MySQL:
service mysqld restart
Динамическое изменение размера innodb_buffer_pool_size
С версии MySQL 5.7.5 допускается динамическое изменение размера innodb_buffer_pool_size, однако необходимо помнить, что размер innodb_buffer_pool_size должен быть пропорционален innodb_buffer_pool_chunk_size.
Запросим размер innodb_buffer_pool_chunk_size:
Получим значение, например, 128 мегабайт. Нам требуется динамически установить размер innodb_buffer_pool_size в 16G, соответсвенно 16G / 128M = 125, установим значение:
SET GLOBAL innodb_buffer_pool_size = (SELECT @@innodb_buffer_pool_chunk_size) * 125
Источник
Настройка mysql для большой нагрузки
Внимание! Данная статья относится к серверам MySQL ветки 5.0. Пожалуйста, перед применением информации из этой статьи, удостоверьтесь, что на Вашем сервере используется MySQL 5.0, это можно сделать с помощью команды:
mysql —version
В случае, если Вы будете обновлять сервер MySQL до версии 5.1 или выше — пожалуйста, измените директивы в конфигурационном файле в соответствии с документацией по обновлению MySQL с 5.0 до 5.1.
Для высоконагруженных VPS серверов с количеством оперативной памяти от 1,5-2Гб (это тарифы VPS VIP или архивные DDS-2,3 и выше) мы рекомендуем для оптимальной работы сервера БД MySQL использовать следующий конфигурационный файл:
[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
default-character-set=cp1251
character-set-server=cp1251
collation-server=cp1251_general_ci
init-connect=»SET NAMES cp1251″
skip-character-set-client-handshake
set-variable = innodb_buffer_pool_size=32M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_log_buffer_size=16M
set-variable = innodb_thread_concurrency=2
skip-external-locking
max_connections = 400
thread_cache_size = 128
wait_timeout=30
connect_timeout=10
thread_concurrency=4
query_cache_type=1
interactive_timeout=100
log_long_format
#log_warnings
#long_query_time = 3
#log-slow-queries = /var/log/mysql-slow-query.log
key_buffer = 256M
max_allowed_packet = 16M
thread_stack = 192K
table_cache = 1536
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer=16M
sort_buffer_size = 16M
join_buffer_size = 16M
query_cache_limit = 2M
query_cache_size = 128M
tmp_table_size = 128M
read_buffer_size = 32M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-bdb
set-variable = innodb_buffer_pool_size=32M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_log_file_size=100M
set-variable = innodb_flush_log_at_trx_commit=0
set-variable = innodb_log_buffer_size=16M
set-variable = innodb_thread_concurrency=2
Сам файл на сервере находится по адресу:
После внесения изменений необходимо перезапустить MySQL командой UNIX shell:
service mysqld restart
или же Вы можете сделать это, перезапустив VPS сервер целиком.
Источник