При большом значении переменной рекомендуется увеличить размер кэша таблиц
Обновлено: 07.07.2024
Несмотря на то, что в mySQL существует довольно много настроек, которые Вы можете изменить, есть набор действительно очень важных характеристик, которые обязательно нужно оптимизировать под собственный сервер. Обычно после такой небольшой настройки производительность сервера заметно увеличивается.
Любой тюнинг MySQL — любая тонкая настройка должна начинаться с определения преобладающего количества таблиц определенного типа.
Определение типа таблиц в MySQL
Делается это при помощи запроса вида (для innodb):
Стоит выполнить оба запроса и оценить количество таблиц, относящихся к каждому движку.
Правила выбора значения
- Чем больше, тем лучше.
- Если сервер уходит в SWAP, стоит уменьшать значения.
Общие параметры (не имеющие отношения к типу движка)
max_connections=2000 — устанавливаем параметр минимальным возможным при необходимости экономить ресурсы сервера, при возникновении в логе записей вида «Too many connections…» увеличиваем значение. 4000 клиентов является максимумом. Можно довести максимальное количество клиентов до 7000, но для стандартных сборок 4000 является пределом.
key_buffer=1024M — размер буфера под индексы в оперативной памяти. Оптимальное значение 20-25% доступной RAM. Если значение параметра слишком маленькое — данные начнут писаться в SWAP, что снизит скорость работы в разы.
Памяти под индексы выделяется достаточно если отношение значений Key_reads/Key_read_request оказывается < 0,01
Узнать значения параметров можно выполнив в консоли сервера баз данных запрос
Также в выводе будут значения Key_write_requests и Key_writes
table_cache=2048 – максимальное число открытых таблиц для всех потоков.
Увеличение параметра означает увеличение файловых дескрипторов, используемых для работы MySQL.
Чтобы выявить необходимое значение нужно выполнить запрос
Затем установить значение переменной несколько больше значения в выводе:
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Opened_tables | 1756 |
+—————+——-+
open_files_limit = 2048
Устанавливать значение стоит опираясь на существующее количество открытых файлов MySQL
В конфигурационном файле задается большее значение.
sort_buffer=128M – ускоряет запросы ORDER BY или GROUP BY. Устанавливается не очень большим (как правило — в зависимости от среды)
Выделяемое количество ОЗУ будет определяться как значение sort_buffer, умноженное на Threads_running
record_buffer=720M — хорошим значением будет эквивалент sort_buffer умноженный на 4-6.
query_cache_limit=2M – максимальный размер результата выборки (или другого запроса), который будет кэшироваться. Значение можно увеличивать, по умолчанию установлен 1 Мб.
max_join_size=1000000 — максимальное количество записей, которые могут обрабатываться единовременно. Своего рода «защита от дупака» при использовании JOIN.
thread_cache_size=64 — задает колчество потоков обработки данных в кэше, т.е. количество потоков которые не создаются вновь при каждом запросе. Можно увеличивать параметр, это положительно скажется на быстродействии
Вычисляется как Connections — Threads_created (должно быть примерно равно — чуть больше значения Max_used_connections)
SHOW STATUS LIKE «Max_used_connections%»;
Настройки для MyISAM
Буфер для создания индексов и REPAIR, а также ALTER TABLE в myisam таблицах
myisam_sort_buffer_size=128М
Тюнинг MySQL сводится к подбору оптимальных значений в конфигурационном файле сервера баз данных, которые соответствуют выделяемым серверу ресурсам и типу таблиц, которые используются.
Параметры кэширования запросов, которые могут задаваться в my.cnf
thread_cache_size (default 0)
количество потоков, которое сервер должен кэшировать для повторного использования, хорошее значение для начала — 4
table_cache/tables_open_cache(default 64)
количество открытых таблиц для всех потоков
query_cache_limit (default 1M)
максимальный размер запроса который может быть помещен к кеш
query_cache_size (default 0)
поскольку здесь 0, следующий параметр работать не будет
4-8М хорошие значения, количество памяти для кэширования запросов
query_cache_type (default 1=on)
0=off, 2=on if needed
Буферы MySQL
key_buffer_size (default 8M)
буфер для индексов таблиц MYISAM, 24-48М нормальные значения
read_buffer_size (default 128K)
последовательный буфер потоков
join_buffer_size (default 128K)
используется под JOIN без индексов, лучшим решением (вместо использования параметра) является ораганизация базы с джойнами которые могут быть проиндексированы — если такой возможности нет джойны ускоряются параметром join_buffer_size
Другие переменные
connect_timeout (MySQL pre-5.1.23: default 5, MySQL 5.1.23+: default 10)
количество секунд по прошествии которых сервер баз данных будет выдавать ошибку, при активном веб-сервере значение можно уменьшать чтобы увеличить скорость работы, на медленной машине — можно увеличивать
max_connect_errors (default 10)
максимальное количество единовременных соединений с сервером баз данных с хоста запрос блокируется если он прерывается запросами с того же хоста до момента окончания обработки запроса)
блокируются навсегда, очистить можно только из командной оболочки MySQL:
В случае атаки на сервер нужно уменьшать (5) чтобы отсекать попытки соединения, при большой активности веб-сервера можно увеличивать
max_allowed_packet (default 1M)
максимальный размер пакета, при подключении
tmp_table_size (system-specific default)
16М — довольно много
максимальный размер памяти выделяемой под хранение временных данных
Переменные, использование которых не требуется
thread_concurrency
используется только на Solaris, на linux mysql 5.5+ при использовании переменной MySQL не сможет запуститься
innodb_thread_concurrency
похожая на предыдущую переменная, является, тем не менее, совсем другой и относится к innodb
skip-locking
сейчас параметр называется skip-external-locking (по умолчанию используется начиная с MySQL 4.0 — непосредственное указание в конфиге не требуется)
Образцы конфигурационных файлов MySQL
Найти их можно в каталоге /usr/share/mysql
my-huge.cnf
my-large.cnf
my.small.cnf
my-innodb-heavy-4G.cnf
my-medium.cnf
Использование этих шаблонов не всегда оправдано поскольку в них применяется очень большое количество переменных использование которых в конфиге никак не объясняется, при недостаточном понимании структуры файла шаблоны лучше не использовать.
Также в шаблонах по умолчанию применяются thread_concurrency, что означает невозможность запуска MySQL с шаблонных конфигом в принципе
Также возможно использование скрипта: MySQLTunner.pl (для его правильной работы возможно понадобится дополнительно установить пакет bc)
Общая оптимизация
mysqltuner.pl
Сервер баз данных должен работать минимум 24 часа до запуска данной утилиты
mysqlidxchk
Анализирует общие логи или логи медленных запросов и ищет неиспользуемые индексы, их потребуется удалить — это позволит увеличить скорость работы базы данных
chmod 755 mysqlidxchk* mysqlidxchk
./mysqlidxchk —general /var/lib/mysql/ general.log
Парсинг лог-файлов
mysqlsla
Определяет на основании анализа логов медленных запросов и общих логов пользователей которые используют базу больше всего (в процентном отношении)
Использование (логирование должно быть включено)
./mysqlsla -lt general /var/lib/mysql/general.log
Формирование отчетов о статусе
mysqlreport
Утилита интерпретирует вывод SHOW STATUS и составляет на основании собранной статистики отчет о работе MySQL
Исопльзование
./mysqlreport
Инструмент довольно сложен в использовании и предполагает хорошие знания MySQL
Ограничение количества, таймауты и источники соединений
Увеличение числа открытых файлов
В большинстве 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.
С версии MySQL 8.0.3 опции query_cache_limit, query_cache_size и query_cache_type исключены!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 часть опций требует корректировки:
Обратите внимание, при работе с 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
Здравствуйте. Ситуация следующая. Когда на сайте одновременных пользователей 100-150, то mysql загружает даже не на 100, а на 170-200% процессор, это в top. Как результат, все это бегом в apache, и забивается вся память, сайт начинает медленно работать, долго грузятся страницы. (базируется на Yii). Сервер 8гб, 4ядра, ssd, php 5.5, Nginx + Apache + fastcgi. Debian 7 x64.
ORDER BY wall_entry.id DESC LIMIT 10;
Slow_queries 43 Количество запросов, выполнявшихся более long_query_time секунд.
Innodb_buffer_pool_reads 2.6 k Количество последовательных запросов на чтение, которые InnoDB не смог выполнить из буферного пула и использовал постраничное чтение.
Innodb_row_lock_waits 5 Общее количество ожиданий блокировки строк.
Handler_read_rnd 43.9 k Количество запросов, на чтение строки, основанных на ее позиции. Большое значение переменной может быть обусловлено частым выполнением запросов использующих сортировку результата, выполнением большого числа запросов требующих полного сканирования таблиц, наличием объединений не использующих индексы надлежащим образом.
Handler_read_rnd_next 6.2 G Количество запросов на чтение следующей строки из файла данных. Данное значение будет высоким, при частом сканировании таблиц. Обычно это означает, что таблицы не проиндексированы надлежащим образом или запросы не используют преимущества индексов.
Created_tmp_disk_tables 103.6 k Количество временных таблиц, автоматически созданных сервером на диске, во время выполнения SQL-выражений. Если значение Created_tmp_disk_tables велико, следует увеличить значение переменной tmp_table_size, чтобы временные таблицы располагались в памяти, а не на жестком диске.
Opened_tables 440 Общее количество открывавшихся таблиц. При большом значении переменной рекомендуется увеличить размер кеша таблиц (table_cache).
забивается вся память, сайт начинает медленно работать
Надо настроить апач и мускул так, чтобы в любом случае не уходило в своп
Более полезным был бы его explain
goingUp ★★★★★ ( 09.12.14 14:09:47 )Последнее исправление: goingUp 09.12.14 14:11:18 (всего исправлений: 2)
Удваиваю explain всех медленных запросов. И ещё можно добавить логгирование всех запросов не использующих индекс (кажется это директива log-queries-not-using-indexes).
Что там с использованием памяти? Если есть излишек (с 8 гигов он вполне может быть) возможно стоит настроить мускуль на то что-бы он это излишек использовал под кеши.
Покажи free -m и ps ax -o rss,cmd | sort -n
P.S. а зачем apache?
вся статистика это в данный момент, когда особой нагрузки нету. Но периодически сам по себе взлетает проц mysql. По поводу apache, хочу перейти на php-fmp, но пока это сделать невозможно, из-за панели Vesta cp, которая не поддерживает php-fpm, разработчики активно занимаются внедрением готового шаблона с fpm для vesta.
free -m total used free shared buffers cached Mem: 8006 6352 1653 0 182 4559 -/+ buffers/cache: 1611 6395 Swap: 0 0 0
skip-name-resolve - Не производится разрешения имен хостов. Все значения в столбце Host в таблицах привилегий должны быть IP-адресами или значениями localhost. Это сильно увеличивая быстродействие запросов за счет выключения постоянных DNS запросов при “внешних” соединениях с mysql
skip-locking – Запрет внешней блокировки существенно повысит скорость работы. Нельзя использовать когда с одной базой работают одновременно 2 сервера.
Важно: при запрете внешней блокировки нельзя будет использовать несколько серверов для работы с теми же базами данных
low-priority-updates — эта опция снижает приоритет операций INSERT/UPDATE по сравнению с SELECT. По умолчанию UPDATE запросы имеют высший приоритет, чем SELECT. Актуально, если данные важно быстрее прочитать, чем быстрее записать. Рекомендуется использовать эту опцию.
skip-networking — не использовать сеть, т.е. вообще не обрабатывать TCP/IP соединения. Общение с сервером при этом будет происходить исключительно через сокет. Рекомендуется, если нет ПО, которое использует только TCP/IP для связи с сервером.
Ограничения
bind-address — интерфейс, который будет слушать сервер. В целях безопасности лучше установить здесь 127.0.0.1, если клиент не использует внешние соединения с сервером
max_allowed_packet — максимальный размер данных, в одном запосе. Следует увеличить, если появляется ошибка «Packet too large». Не следует делать чрезмерно большого размера, в большинстве случаев достаточно 8M
max_connections — максимальное количество параллельных соединений к серверу. Следует увеличить , если появляется проблема «Too many connections». Для VPS VDS параемерт не должен превышать 300
max_join_size — запрещает SELECT операторы, которые предположительно будут анализировать более указанного числа строк или больше указанного числа поисков по диску. Значение по умолчанию более 4 миллиардов, поэтому следует его уменьшить до 200 000 - 300 000 на один процессор
max_sort_length – Защита от кривых архитектур БД, когда не стоят адекватные лимиты по индексам сортировки текстовых полей. Параметр определяет, сколько байтов следует использовать при сортировке значений BLOB или TEXT (обрабатываются только первые max_sort_length байтов каждого значения, остальные игнорируются). Рекомендуется выставлять значение 256.
wait_timeout – Время в секундах, на протяжении которого сервер ожидает активности соединения прежде, чем закрыть его. Рекомендуется установить значение равное времени выполнения php скрпитов.
Настройки потоков
thread_cache_size=12 Определяет, сколько потоков должно сохраняться в кэше для повторного использования. После отключения клиента потоки клиента помещаются в кэш, если там не больше потоков, чем thread_cache_size. Все новые потоки сначала берутся из кэша, и только когда кэш становится пустым, создаются новые потоки. Значение этой переменной можно увеличить, чтобы повысить производительность, если создается много новых соединений (если потоки хорошо организованы, обычно заметного улучшения производительности не наблюдается). Насколько эффективен текущий кэш потоков, можно определить по разнице между Connections и Threads_created. Если есть возможность, рекомендуется установить это значение не меньше, чем значение переменной Max_used_connections. Если значение этой переменной больше 128, рекомендуется ограничиться этим значением
Буферы и Кэширование запросов
query_cache_limit — максимальный размер кэшируемого запроса. Результаты, превышающие это значение, не кэшируются (по умолчанию – 1Мб). Рекомендованно выставлять значения от 2 до 6Мб
query_cache_min_res_unit — минимальный размер хранимого в кэше блока, определяется как
query_cache_size — размер кэша. 0 отключает использование кэша. Для выбора оптимального значения необходимо наблюдать за переменной состояния Qcache_lowmem_prunes и добиться, чтобы ее значение увеличивалось незначительно. Также нужно помнить, что излишне большой кэш будет создавать ненужную нагрузку
query_cache_type —
0 - MySQL сервер кэширует запросы и не извлекает результаты из кэша. Однако, это не освобождает буфер кэша запросов, для этого следует установить переменную query_cache_size в значение 0. 1 - MySQL-сервер будет кэшировать все запросы, кроме начинающихся с конструкции SELECT SQL_NO_CACHE. 2 - MySQL-сервер будет кэшировать только запросы, начинающихся с конструкции SELECT SQL_CACHEkey_buffer_size — размер буфера, выделяемого под индексы и доступного всем потокам. Весьма важная настройка, влияющая на производительность. Значение по умолчанию 8 МБ, его однозначно стоит увеличить. Рекомендуется 15-30% от общего объема ОЗУ, однако нет смысла устанавливать больше, чем общий размер всех .MYI файлов. Наблюдайте за переменными состояния Key_reads и Key_read_requests, отношение Key_reads/Key_read_requests должно быть как можно меньше (< 0,01). Если это отношение велико, то размер буфера стоит увеличить. Если нет времени на анализ нету то можно воспользоваться формулой
key_buffer = 0,2 * Объему ОЗУ
myisam_sort_buffer_size — размер буфера, выделяемого MyISAM для сортировки индексов при REPAIR TABLE или для создания индексов при CREATE INDEX, ALTER TABLE. Значение по умолчанию 8 МБ, его стоит увеличить вплоть до 15-25% ОЗУ. Выигрыш в производительности соответственно будет только при выполнении вышеупомянутых запросов
net_buffer_length — объем памяти, выделяемый для буфера соединения и для буфера результатов на каждый поток. Буфер соединения будет указанного размера и буфер результатов будет такого же размера, т.е. на каждый поток будет выделен двойной размер net_buffer_length. Размер по умолчанию 16 КБ. В случае ограниченной памяти или использования только небольших запросов значение можно уменьшить.
read_buffer_size — каждый поток при последовательном сканировании таблиц выделяет указанный объем памяти для каждой таблицы. Как показывают тесты, это значение не следует особо увеличивать. Размер по умолчанию 128 КБ, оптимальными являются 32КБ 64КБ 128КБ для очень больших запросов 256КБ
read_rnd_buffer_size — актуально для запросов с «ORDER BY», т.е. для запросов, результат которых должен быть отсортирован и которые обращаются к таблице, имеющей индексы. Значение по умолчанию 256 КБ, рекомендуется его увеличить до 1 МБ или выше, если позволяет память. Следует учесть, что указанное значение памяти также выделяется на каждый поток
sort_buffer_size — каждый поток, производящий операции сортировки (ORDER BY) или группировки (GROUP BY), выделяет буфер указанного размера. Значение по умолчанию 2 МБ, если вы используете указанные типы запросов и если позволяет память, то значение стоит увеличить. Большое значение переменной состояния Sort_merge_passes указывает на необходимость увеличения sort_buffer_size.
table_cache (table_open_cache с версии 5.1.3) — количество кэшированных открытых таблиц для всех потоков. Открытие файла таблицы может быть достаточно ресурсоемкой операцией, поэтому лучше держать открытые таблицы в кэше. Значение по умолчанию 64, его лучше всего увеличить до общего количества таблиц, если их количество в допустимых рамках. Переменная состояния Opened_tables позволяет отслеживать число таблиц, открытых в обход кэша, желательно, чтобы ее значение было как можно ниже
tmp_table_size — максимальный размер памяти, выделяемой для временных таблиц, создаваемых MySQL для своих внутренних нужд. Это значение также ограничивается переменной max_heap_table_size, поэтому в итоге будет выбрано минимальное значение из max_heap_table_size и tmp_table_size, а остальные временные таблицы будут создаваться на диске. Значение по умолчанию зависит от системы, можно установить его равным 32 МБ и понаблюдать за переменной состояния Created_tmp_disk_tables, значение должно быть как можно меньше
Читайте также: