MetrikaYandex

Оптимизация производительности MySQL / MariaDB

0
7824
Оптимизация производительности MySQL / MariaDB

Настройки MySQL по-умолчанию зачастую не оптимальны, они предназначены для начальных конфигураций с малыми ресурсами, около 512 МБ. Именно поэтому всегда настройки необходимо оптимизировать под каждую конкретную инсталляцию. Процесс оптимальной настройки Mysql состоит из двух частей — первоначальная настройка и корректировка параметров во время работы. Настройки нужно вносить в файл my.cnf по мере их уточнения.

Любой тюнинг MySQL должна начинаться с определения преобладающего количества таблиц определенного типа — InnoDB или MyISAM. Существуют настройки общие, а существуют и специфичные для разных типов таблиц. Для начала разберемся, как MySQL работает с памятью.

к содержанию ↑

Выделение памяти в MySQL

Работа с потоками

Работа с памятью играют значительную роль для скорости и эффективности обработки параллельных транзакций и исполнения больших SQL запросов. Каждый поток (тред) использует память для клиентских соединений, и это потоки используют общую базовую память. Есть переменные thread_stack (стек потоков), net_buffer_length (для буфера соединений и буфера результата), которые динамически увеличивают свое значение до значения max_allowed_packet, когда это требуется и не влияют на общую утилизацию памяти. Буфер результата урезается до значения net_buffer_length после каждого SQL выражения. Когда поток больше не требуется, память, выделенная на данный поток, освобождается и возвращается системе до момента, пока поток не вернется в кэш потоков. Каждое поток соединения также использует память для для вычисления дайджестов SQL выражений. Параметр max_digest_length в байтах указывает на количество памяти, которое сервер выделяет на сессию.

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

к содержанию ↑

Работа с таблицами

MySQL использует память и дескрипторы для кеширования таблиц. Структуры обработчиков для всех используемых таблиц сохраняются в кэше таблиц и управляются как «первым пришел — первым ушел» (FIFO). Системная переменная table_open_cache определяет начальный размер кэша таблицы. MySQL также требует памяти для кеша дескрипторов таблиц. Системная переменная table_definition_cache определяет количество дескрипторов таблиц, которые могут храниться в кэше дескрипторов таблиц. Если вы используете большое количество таблиц, вы можете создать большой кэш дескрипторов таблиц, чтобы ускорить открытие таблиц. Кэш дескрипторов таблицы занимает меньше места и не использует файловые дескрипторы, в отличие от кеша таблицы.

MySQL также выделяет память для временных таблиц, если она не становится слишком большой (определяется tmp_table_size и max_heap_table_size). Если внутренняя временная таблица в памяти становится слишком большой (определено в системных переменных tmp_table_size и max_heap_table_size), MySQL автоматически преобразует таблицу из формата в памяти в формат на диске. Если вы используете таблицы MEMORY и для переменной max_heap_table_size установлено очень большое значение, это приводит к потреблению большого объема памяти, поскольку системная переменная max_heap_table_size определяет, насколько велика таблица, и преобразование в дисковый формат отсутствует.

Все джойны (JOIN) выполняются за один проход, и большинство из них может быть выполнено даже без использования временной таблицы. Большинство временных таблиц представляют собой хеш-таблицы на основе памяти. Временные таблицы с большой длиной строки (рассчитанной как сумма длин всех столбцов) или содержащие столбцы BLOB хранятся на диске. Большинство запросов, выполняющих сортировку (SORT), выделяют буфер сортировки и от нуля до двух временных файлов в зависимости от размера набора результатов.

к содержанию ↑

Пулы и буферы

Запросы JOIN, запросы к кэшу, сортировки, кэш таблиц, дескрипторы таблиц потребляют память постоянно, но они настраиваются с помощью системных переменных. В большинстве случаев, такие переменные привязаны к типу хранилища — InnoDB или MyISAM. Когда экземпляр mysqld появляется в хост-системе, MySQL выделяет буферы и кеши для повышения производительности операций с базой данных на основе значений, установленных в конкретной конфигурации.

Например, наиболее распространенными переменными, которые каждый администратор баз данных устанавливает в InnoDB, являются переменные innodb_buffer_pool_size и innodb_buffer_pool_instances, которые связаны с распределением памяти пула буферов, в котором хранятся кэшированные данные для таблиц InnoDB. Желательно, если у вас большой объем памяти и вы ожидаете обрабатывать большие транзакции, установив innodb_buffer_pool_instances для улучшения параллелизма путем разделения пула буферов на несколько экземпляров пула буферов.

В то время как для MyISAM вы должны иметь дело с key_buffer_size, чтобы обрабатывать объем памяти, который будет обрабатывать буфер ключей. MyISAM также выделяет буфер для каждого параллельного потока, который содержит структуру таблицы, структуры столбцов для каждого столбца и буфер размером 3 * N (где N — максимальная длина строки, не считая столбцов BLOB). MyISAM также поддерживает один дополнительный буфер строк для внутреннего использования.

Каждый запрос, который выполняет последовательное сканирование таблицы, выделяет буфер для чтения. Переменная read_buffer_size определяет размер такого буфера. При чтении строк в произвольной последовательности (например, после сортировки) может быть выделен буфер случайного чтения, чтобы избежать обращений к диску. Системная переменная read_rnd_buffer_size определяет размер буфера.

к содержанию ↑

Мониторинг

MySQL также имеет Performance Schema, которая является функцией для мониторинга действий MySQL на низком уровне. Как только она включена (а он настройка по-умолчанию), то она постепенно динамически выделяет память, масштабируя использование памяти до фактической нагрузки сервера, вместо того, чтобы выделять требуемую память во время запуска сервера. После выделения памяти она не освобождается до перезапуска сервера. Именно поэтому, Performance Schema постоянно отъедает все больший кусок памяти и его не возвращает, что может сказаться на производительности сервера. Многие рекомендуют отключать Performance Schema полностью.

к содержанию ↑

Буферы и настройки InnoDB

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

  • InnoDB выделяет память для всего пула буферов при запуске сервера. Системная переменная innodb_buffer_pool_size определяет размер пула буферов. Обычно рекомендуемое значение innodb_buffer_pool_size составляет от 50 до 75% системной памяти (при условии, что она не используется другими сервисами). innodb_buffer_pool_size можно настраивать динамически, пока сервер работает без его перезагрузки.
  • В системах с большим объемом памяти вы можете улучшить параллелизм, разделив пул буферов на несколько экземпляров пула буферов. Системная переменная innodb_buffer_pool_instances определяет количество экземпляров буферного пула. По умолчанию значение параметра равно 1, но более оправданным является значение вычисляемое как сумма innodb_buffer_pool_size в гигабайтах и ядер CPU, деленная пополам: (innodb_buffer_pool_size in Gb + CPUs)/2
  • Слишком маленький пул буферов может вызвать чрезмерное перемешивание, поскольку страницы удаляются из пула буферов только для того, как они могут потребоваться снова через короткое время.
  • Слишком большой пул буферов может вызвать своппинг из-за конкуренции за память. Если сервер уходит в SWAP, стоит уменьшать значения.

Прочие параметры:

  • innodb_additional_mem_pool_size — данная опция практически никак не влияет на производительность mySQL, однако рекомендую оставлять для InnoDB около 20 МБ (или чуть больше) под различные внутренние нужды.
  • innodb_log_file_size — крайне важная настройка в условиях баз данных с частыми операциями записи в таблицы, в особенности при больших объёмах. Она устанавливает размер лога операций (так операции сначала записываются в лог, а потом применяются к данным на диске). Чем больше этот лог, тем быстрее будут работать записи (т.к. их поместится больше в файл лога). Файлов всегда два, а их размер одинаковый.  Большие размеры увеличивают быстродействие, однако будьте осторожны — увеличится и время восстановления данных. Подумайте над использованием реплик для обеспечения доступности, чтобы не зависеть от времени восстановления базы данных. Я обычно выставляю значение около 64-512 МБ в зависимости от размера сервера. Так, при значении
    innodb_log_file_size = 512M

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

  • innodb_log_buffer_size — это размер буфера транзакций, которые не были еще закомичены. Значение этого параметра стоит менять в случаях, если вы используете большие поля вроде BLOB или TEXT. Стандартное значение данной опции 1M вполне подойдёт для большинства систем со средним количеством операций записи и небольшими транзакциями. Если же в Вашей системе бывают всплески активности, или Вы активно работаете с BLOB-данными, то рекомендую немного увеличить значение innodb_log_buffer_size. Однако не переусердствуйте — слишком большое значение будет пустой тратой памяти: буфер сбрасывается каждую секунду, поэтому Вам не понадобится больше места, чем требуется в течение этой секунды. Рекомендуемое значение — около 8-16 МБ, а для небольших баз — 1-2М.
    innodb_log_buffer_size = 2M
  • innodb_flush_log_at_trx_commit — жалуетесь, что 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» вы потеряете данные только при аварии всей операционной системы.
  • innodb_flush_method — Этот параметр определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узов, вы будете выбирать между O_DSYNC и O_DIRECT. Помните об обязательном использовании резервных узлов (например, реплик)

innodb_flush_method = O_DSYNC

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

innodb_file_per_table = ON

к содержанию ↑

Настройки MyISAM

  • key_buffer_size (default 8M) — крайне важная настройка при использовании MyISAM-таблиц. Установите её равной около 20-40% от доступной оперативной памяти, если используете только MyISAM. Правильный размер зависит от размеров индексов, данных и нагрузки на сервер — помните, что MyISAM использует кэш операционной системы (ОС), чтобы хранить данные, поэтому нужно оставить достаточно места в ОЗУ под данные, и данные могут занимать значительно больше места, чем индексы. Однако обязательно проверьте, чтобы всё место, отводимое директивой key_buffer_size под кэш, постоянно использовалось — нередко можно видеть ситуации, когда под кэш индексов отведено 4 ГБ, хотя общий размер всех .MYI-файлов не превышает 1 ГБ. Делать так совершенно бесполезно, Вы только потратите ресурсы. Если у Вас практически нет MyISAM-таблиц, то key_buffer_size следует выставить около 16-32 МБ — они будут использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске. Памяти под индексы выделяется достаточно если отношение значений Key_reads/Key_read_request оказывается < 0,01.

    Узнать значения параметров можно выполнив в консоли сервера баз данных запрос

    SHOW STATUS LIKE «Key%»;

    Также в выводе будут значения Key_write_requests и Key_writes.

  • table_cache / tables_open_cache (default 64)— количество открытых таблиц для всех потоков. Открытие таблиц может быть весьма ресурсоёмко. К примеру, MyISAM-таблицы помечают заголовки .MYI файлов как «используемые в текущий момент». Обычно не рекомендуется открывать таблицы слишком часто, поэтому лучше, чтобы кэш был достаточных размеров, чтобы держать все Ваши таблицы открытыми. Для этого используется некоторое количество ресурсов ОС и оперативной памяти, однако это обычно не является существенной проблемой для современных серверов. Если у Вас несколько сотен таблиц, то стартовым значением для опции table_cache может быть«1024» (помните, что каждое соединение требует свой собственный дескриптор). Если у Вас ещё больше таблиц или очень много соединений — увеличьте значение параметра. Я видел mySQL сервера со значением table_cache равной 100 000.
    Чтобы выявить необходимое значение нужно выполнить запрос

     

    SHOW STATUS LIKE «Opened_tables%»;

    Затем установить значение переменной несколько больше значения в выводе:
    +—————+——-+
    | Variable_name | Value |
    +—————+——-+
    | Opened_tables | 1756 |
    +—————+——-+

    Например, для 200 конкурентных соединений, укажите размер кэша таблиц не менее 200 * N, где N — максимальное количество таблиц на соединение в любом из выполняемых вами запросов. Вы также должны зарезервировать некоторые дополнительные файловые дескрипторы для временных таблиц и файлов. Если у вас в запросе джойнятся 4 таблицы, то вам нужно значение не менее 200*4 = 800. Подробнее в официальной документации.

  • thread_cache — создание/уничтожение потоков также является ресурсоёмкой операцией, которая происходит при каждой установке соединения и каждом разрыве соединения. Я обычно выставляю эту опцию равную 16. Если у Вашего приложения могут быть скачки количество конкурентных соединений и по переменной Threads_Created виден быстрый рост количества потоков, то стоит увеличить значение thread_cache. Цель — не допускать создания новых потоков в условиях нормального функционирования сервера.
  • thread_cache_size (default 0) — количество потоков, которое сервер должен кэшировать для повторного использования (т.е. количество потоков которые не создаются вновь при каждом запросе), хорошее значение для начала — 4.Вычисляется как [Connections — Threads_created] (должно быть примерно равно или чуть больше значения Max_used_connections)

    SHOW STATUS LIKE «Max_used_connections%»;

  • query_cache_size (default 0) — если Ваше приложение много и часто читает данные, и при этом у Вас нет кэша на уровне приложения, эта опция может очень помочь. Не ставьте здесь слишком большое значение, так как обслуживание большого кэша запросов будет само по себе затратным. Рекомендуемое значение — от 4 до 512 МБ, 4-8 МБ достаточно для начала. Не забудьте проверить, насколько хорошо используется кэш запросов — в некоторых условиях (при небольшом количестве хитов в кэше, т.е. когда практически не выбираются одинаковые данные) использование большого кэша может ухудшить производительность. если значение = 0, то следующий параметр работать не будет.
    Есть также рекомендация не полагаться на этот механизм и оставить значение = 0. На практике он работает очень неэффективно. Так, весь кеш запросов для определенной таблицы сбрасывается всякий раз, когда в таблицу вносится хотя бы одно изменение.
  • query_cache_type (default 1=on) — включение кэша запросов. 0=off, 2=on if needed
  • query_cache_limit (default 1M) — максимальный размер запроса который может быть помещен к кеш.
  • myisam_sort_buffer_size=128М — Буфер для создания индексов и REPAIR, а также ALTER TABLE в myisam таблицах.
к содержанию ↑

Общие буферы MySQL

Большинство из этих настроек стоит трогать, если вы понимаете, как устроена ваша база, и какие к ней делается запросы.

  • sort_buffer=128M – ускоряет запросы ORDER BY или GROUP BY. Устанавливается не очень большим (как правило — в зависимости от среды). Выделяемое количество ОЗУ будет определяться как значение sort_buffer, умноженное на  Threads_running.
    SHOW STATUS LIKE «Threads_running%»;
  • record_buffer=720M  — хорошим значением будет эквивалент sort_buffer умноженный на 4-6.
  • read_buffer_size (default 128K) — последовательный буфер чтения потоков.
  • join_buffer_size (default 128K) — используется под JOIN без индексов, лучшим решением (вместо использования параметра) является ораганизация базы с джойнами которые могут быть проиндексированы — если такой возможности нет джойны ускоряются параметром join_buffer_size.
  • max_join_size=1000000 — максимальное количество записей, которые могут обрабатываться единовременно. Своего рода «защита от дурака» при использовании JOIN.
к содержанию ↑

Общие настройки

max_connections=64 — устанавливаем параметр минимальным возможным при необходимости экономить ресурсы сервера, при возникновении в логе записей вида «Too many connections…» увеличиваем значение. Не следует изменять значение этого параметра на старте. 4000 клиентов является максимумом. Можно довести максимальное количество клиентов до 7000, но для стандартных сборок 4000 является пределом.

open_files_limit = 2048 Устанавливать значение стоит опираясь на существующее количество открытых файлов MySQL:

lsof -u mysql | wc -l

В конфигурационном файле задается большее значение.

connect_timeout (MySQL pre-5.1.23: default 5, MySQL 5.1.23+: default 10) — количество секунд по прошествии которых сервер баз данных будет выдавать ошибку, при активном веб-сервере значение можно уменьшать чтобы увеличить скорость работы, на медленной машине — можно увеличивать.

max_connect_errors
 (default 10) — максимальное количество единовременных соединений с сервером баз данных с хоста запрос блокируется если он прерывается запросами с того же хоста до момента окончания обработки запроса)
блокируются навсегда, очистить можно только из командной оболочки MySQL:

FLUSH HOSTS;

В случае атаки на сервер нужно уменьшать (5) чтобы отсекать попытки соединения, при большой активности веб-сервера можно увеличивать

max_allowed_packet (default 1M) — 
максимальный для буфера соединений и буфера результата при исполнении SQL инструкций. Каждый тред имеет свой буфер. Хорошим значением для начала будет 16М.

tmp_table_size (system-specific default)
 — максимальный размер памяти выделяемой под хранение временных таблиц. 16М — довольно много.

Примеры готовых конфигураций для разных объёмов памяти можно посмотреть здесь.

Чтобы посомореть значения переменных можно воспользоваться SQL запросом:

mysql> SHOW SERVER STATUS\G

или для конкретных переменных:

mysql> SHOW SERVER STATUS WHERE variable_name IN ('<var1>','var2'...);

Чтобы проверить мониториг InnoDB, используте:

mysql> SHOW ENGINE INNODB STATUS\G

Чтобы узнать, не свопается ли память, используйте команду и смотрите строку swap:

[root@node1 ~]# free -m

 

к содержанию ↑

Начало

Сервер у нас пусть будет на ​ CentOS​. Оптимизировать будем методом правки конфига ​my.cnf​ .

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

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

Для этого возьмем ​ htop​ (как красивый и наглядный инструмент):

yum install htop

Выведем ​ htop​ :

htop

Получаем нечто такое:
Запишем себе в ​my.cnf​:

# 3 ядра, 4гб оперативной памяти 

Теперь давайте узнаем количество таблиц и их типы.
Для этого возьмем ​mysql tuner​:

wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

Запустим:

perl mysqltuner.pl

Вывод примерно:

image

Запишем себе в ​my.cnf:

# 64M myisam, 770M innoDB

Типовой конфиг обычно рекомендуют какой-то такой:

[client] 
port                        = 3306 
socket                      = /var/run/mysqld/mysqld.sock 

[mysqld_safe] 
socket                      = /var/run/mysqld/mysqld.sock 
nice                        = 0 
 
[mysqld] 
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 
old_passwords               = 0 
bind-address                = 127.0.0.1 
 
skip-external-locking 
 
max_allowed_packet          = 16M 
key_buffer_size             = 16M 
innodb_buffer_pool_size     = 2048M 
innodb_file_per_table       = 1 
innodb_flush_method         = O_DIRECT 
innodb_flush_log_at_trx_commit  = 0 
 
max_connections             = 144    <a 
href="https://ruhighload.com/query_cache_size+%d0%bf%d0%b0%d1
%80%d0%b0%d0%bc%d0%b5%d1%82%d1%80+%d0%b2+mysql" 
target="_blank" style="color: rgb(232, 95, 99);">query_cache_size</a> 
= 0 slow_query_log              = /var/log/mysql/mysql-slow.log 
long_query_time             = 1 
 
expire_logs_days            = 10 
max_binlog_size             = 100M 
 
[mysqldump] 
quick 
quote-names 
max_allowed_packet          = 16M

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

к содержанию ↑

Оптимизация и конфиг

Для начала можно пролистать в конец вывода ​mysql tuner​ и посмотреть, что же он там рекомендует. В нашем случае это выглядит как-то так:

wget 
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl 
perl mysqltuner.pl

image

Не будем заниматься бездумной подстановкой, а пройдемся по параметрам ​mysql​ , которые могут нас интересовать в первую очередь. Что к чему:
skip-external-locking​, — убирает внешнюю блокировку, что быстрее;
skip-name-resolve​ , — позволяет ​MySQL ​ избегать ответа на запрос DNS ​ при проверке подключения клиентов к серверу ​MySQL​ .

Таким образом, сервер ​MySQL ​ будет использовать только
IP​ -адреса, а не имена хостов, что немного, но быстрее.

binlog_cache​ _ ​ size​, — размер кэша для хранения изменений в двоичном журнале. Задает размер только для кэша транзакций. Сделаем ​ 100M​ — больше не нужно.

innodb_stats_on_metadata​ =​ 0 (OFF),​ — для ускорения работы с
INFORMATION_SCHEMA​, ​ SHOW TABLE STATUS​ или ​ SHOW INDEX​ отключим обновление статистики при выполнении таких операций

quer​ y ​ _cache_size ​ = ​ 128M ​ и ​ query_сache_type​
​ = ​ 1
,​ ​ — ​ кэши запросов. ​ 1​ — в принципе включен, ​ 128M​ ограничение. Не
рекомендуется ставить выше ​ 256M​ , т.к это может привести к блокировке.

Так как у нас больше​InnoDB​ таблиц, то зануляем cache​ _ ​ size​ .
С версии MySQL 5.6 ​ query_cache_size​ отключен, а с версии 8.0 удален

Стандартно все таблицы и индексы хранятся в одном файле, поэтому используем ​ innodb_file_per_table = 1.

Значение ​ innodb_open_files​ и ​ table_open_cache​ — рекомендуется устанавливать обе опции в ​ 4096 ​ или ​ 8192​ . А вообще рассчитывается как количество таблиц во всех базах, умноженное на ​ 2​ , ориентировочно.

При работе с ​ InnoDB ​ является важнейшим параметр innodb_buffer_pool_size​ , ​ он устанавливается по принципу «чем больше, тем лучше». Рекомендуется выделять до ​ 70-80% оперативной памяти сервера.

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

ВНИМАНИЕ!️При изменении параметра innodb_log_file_size остановите MySQL, сделайте резервную копию файлов ib_logfile-n (файлы чаще всего лежат в /var/lib/mysql/), измените значение параметра innodb_log_file_size и запустите MySQL. В результате
MySQL создаст новый лог-файл указанного в конфигурации размера.

Установка большого размера ​ innodb_log_file_size​ может привести к увеличению быстродействия, но при этом увеличится время восстановления данных, выберите от ​ 256M​ до​ 1G​ .

innodb_log​ _ ​ buffer_size​ — размер буфера транзакций. Обычно рекомендуется не применять, если не используете ​ BLOB ​ и ​ TEXT больших размеров.

innodb_flush​ _ ​ method,​ — определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узлов, вы будете выбирать между ​ ODSYNC​ и ​ ODIRECT, — первый параметр быстрее, второй безопаснее.

key_buffer​ _ ​ size​ — буфер для работы с ключами и индексами, и sort_buffer​ — буфер для сортировки. Если Вы не используете MyISAM ​ таблицы, рекомендуется установить размер key_buffer_size ​ в ​ 32Мб ​ для хранения индексов временных
таблиц.

Параметр ​ thread_cache​ _ ​ size​ указывает количество тредов (threads), уходящих в кеш при отключении клиента. При новом подключении тред не создается, а берется из кеша, что позволяет экономить ресурсы при больших нагрузках.

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

innodb_flush_log_at_trx_commit = 1​ используется для случаев,
когда сохранность данных — это приоритет номер один.

innodb_flush_log_at_trx_commit = 2​ для случаев, когда небольшая потеря данных не критична. Есть еще 0 (ноль) — самый производительный, но небезопасный вариант.

max_connections ​ — если вы получаете ошибки «​ Too many connections​ «, эту опцию стоит увеличить. А так большой пользы в оптимизации от неё нет.

Количество потоков ввода/вывода файлов в InnoDB задается опциями ​ innodb_read_io_threads​ , ​ innodbwrite_io_threads​, обычно этому параметру присваивается значение ​ ​ или ​ 8​ , на быстрых ​ SSD​ -дисках установите в ​ 16​. Значение innodb_thread_concurrency​ установите в количество ядер ​ * 2​ .

Конфиг получается вот такой:

[client] 
port                        = 3306 
socket                      = /var/run/mysqld/mysqld.sock 
 
[mysqld_safe] 
socket                      = /var/run/mysqld/mysqld.sock nice                        = 0 
 
[mysqld] 
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 
old_passwords               = 0 
bind-address                = 127.0.0.1 
 
skip-external-locking  
skip-name-resolve 
 
binlog_cache_size = 100M 
thread_cache_size = 32 
 
innodb_stats_on_metadata = OFF 
 
query_cache_limit = 1M 
query_cache_size = 0 query_cache_type = 1 
 
innodb_buffer_pool_size = 3G 
innodb_log_file_size = 256М 
innodb_log_buffer_size = 6M 
innodb_additional_mem_pool_size = 16M 
innodb_flush_method = O_DSYNC 
innodb_flush_log_at_trx_commit = 0 
innodb_thread_concurrency = 6 
innodb_file_per_table = 1 

 
key_buffer_size = 32M 
tmp_table_size = 64M 
max_connections = 350 
sort_buffer_size = 16M read_buffer_size = 1M 
read_rnd_buffer_size = 1M 
join_buffer_size = 8M 
thread_stack = 1M 
binlog_cache_size = 8M 
 
tmp_table_size = 128M 
table_open_cache = 2048 
 
[mysqldump] quick 
quote-names 
max_allowed_packet = 16M

Ну и напоследок можно посмотреть рекомендации тюнера и последовать им.

к содержанию ↑

Заключение

Вот такой вот интересный конфиг получился. Если Вам сложно, то на первых порах стоит пользоваться ​mySQL ​ калькулятором, который подскажет основные параметры и позволит не выходить за пределы доступной памяти — как-никак всё упирается в неё:

Спасибо за внимание. Присоединяйтесь к обсуждению.

 

В статье будет рассмотрено несколько рекомендаций по тюнингу и улучшении работы mysql. Замечу сразу, что численные значения для каждого сервера будут свои (подбираются путём экспериментов) и простое бездумное копирование, может не только ничего не дать, но и напротив – ухудшить производительность.

1) Создание индексов.

В 99% это действительно так. Ещё нужно использовать кеширование индексов (key_buffer). Здесь очень неплохая подборка по работе с индексами

2) Переход на InnoDB.

Этот тип таблиц менее подвержен сбоям (а так же имеет много преимуществ), нежели MyIsam и уже является типом таблиц по умолчанию в версиях 5.5 и выше. Если же нужен полнотекстовый поиск, то используйте дополнительно sphinks.

3) Использовать проверку тюнинг скриптов, benchmarks.

Особое внимание следует уделить тому, что собственно нужно бенчмаркить: либо только mysql либо в целом приложение.

Full-stack benchmarking tools:

– ab (http://httpd.apache.org/docs/2.0/programs/ab.html)
– http_load (http://www.acme.com/software/http_load/)
 JMeter (http://jakarta.apache.org/jmeter/)

Single tools:

– mysqlslap (http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html): симулирует нагрузку на сервер и создаёт отчёт. Входит в состав mysql, начиная с версии 5.1
– MySQL Benchmark Suite (sql-bench) (http://dev.mysql.com/doc/en/mysql-benchmarks.html/) собственный бенчмарк от mysql
– Super Smack (http://vegan.net/tony/supersmack/)
– Database Test Suite (OSDLand hosted on SourceForge at http://sourceforge.net/projects/osdldbt/)
– Percona’s TPCC-MySQL Tool (https://launchpad.net/perconatools)
– sysbench (https://launchpad.net/sysbench)
– maatkit (ниже, пункт 10)

Вот 2 скрипта для тюнинга: mysqltuner.pl (http://mysqltuner.pl/mysqltuner.pl) и tuning-primer.sh (https://launchpad.net/mysql-tuning-primer). Их нужно запустить и следовать рекомендациям отчётов.

Так же можно попробовать прогнать вашу БД sqlmap – тулза для поиска уязвимостей.

4) Просмотр нагрузки в realtime.

Есть 2 замечательные утилиты для этих целей mtop и mytop. С их помощью вы сможете посмотреть в режиме реального времени, какие запросы нагружают БД.

5) Включить логгирование медленных запросов:

log_error=/var/log/mysql/error.log
log_slow_queries=/var/log/mysql/slow.log
long_query_time = 5
log-queries-not-using-indexes

и периодически смотреть лог.

6) Соединение по сокету.

Использовать там, где это возможно соединение по сокету, вместо IP:port

socket=/tmp/mysql.sock
skip-networking

7) skip-name-resolve

Использовать  для skip-name-resolve отключения резолвинга. Этим вы можете выиграть до 20% производительности.

Но будьте внимательны: при включении этой опции localhost тоже не будет резолвится, поэтому если у вас в настройках сайта и в правах используется localhost – замените его на 127.0.0.1. В частности такое наблюдается на Debian Lenny

8) Включение кеширования.

Действительно, это позволяет несколько ускорить работу БД. Включаем кеширование:

query_cache_type = 1

Что бы правильно подобрать параметры query_cache_size, query_cache_limit, а так же остальные параметры кеширования используйте утилиты проверки тюнинга из пунктов 3 и 4.

9) Приоритеты SELECT.

Если у вас запросы SELECT составляют 90% и более всех запросов, то имеет смысл добавить опцию low-priority-updates, которая повышает приоритет запросов select.

10) Отключение неиспользуемых типов хранилищ

Если вы не используете, например, InnoDB или DBD, то их можно отключить. Это также сэкономит немного ресурсов:

skip-bdb
skip-innodb

Примечание

Если вы собрали mysql, например, без хранилища bdb, то при использовании опции skip-bdb получите такое сообщение:

[ERROR] /usr/local/libexec/mysqld: unknown option '--skip-bdb'

Поэтому лучше сначала посмотреть список доступных хранилищ.

ПС. Посмотреть все доступные типы хранилищ можно так:

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

11) Количество потоков

Формула такова

Try number of CPU's*2 for thread_concurrency
thread_cache_size = 8

12) Советы по оптимизации от oracle

Почитать на официальном сайте про оптимизацию http://dev.mysql.com/doc/refman/5.1/en/optimization.html

13) Включаем авто recover для MyIsam

myisam_recover_options=BACKUP

OPTION DESCRIPTION
DEFAULT Recovery without backup, forcing, or quick checking.
OFF Recovery without backup, forcing, or quick checking.
BACKUP If the data file was changed during recovery, save a backup of the tbl_name.MYD file as tbl_name-datetime.BAK.
FORCE Run recovery even if we would lose more than one row from the .MYD file.
QUICK Do not check the rows in the table if there are not any delete blocks.

14) Хранить каждую таблицу в отдельном файл (InnoDB)

Если размер файла с базой ibdata1 слишком большой, можно включить опцию

innodb_file_per_table=1

но это изменение коснется только новых таблиц.

15) Использование плагина InnoDB вместо встроенного InnoDB.

О том как это сделать, описано здесь http://www.opennet.ru/base/dev/innodb_buffer_size.txt.html

16) Использовать готовые шаблоны my*.cnf

По умолчанию при установке mysql устанавливаются и примеры конфигурационных файлов для различных ситуаций. Во FreeBSD они лежат здесь /usr/share/doc/mysql/:

  • my-small.cnf — для систем с малым обьемом памяти (<=64Mb), в которых MySQL используется редко.
  • my-medium.cnf — если памяти мало (32-64Mb) или MySQL используется совместно с другими приложениями (например Apache) и памяти около 128Mb.
  • my-large.cnfmy-huge.cnf — для систем с большим обьемом памяти (512Mb, 1-2Gb), где MySQL играет главную роль.
  • my-innodb-heavy-4G.cnf — 4Gb памяти, InnoDB, MySQL играет главную роль.

17) mysql_secure_installation

После установки (или уже потом) выполнить скрипт mysql_secure_installation. Правда во FreeBSD его нет. Но можно и вручную. Вот список команд:

UPDATE mysql.user SET Password=PASSWORD('NEWROOTPASSWORD') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host!='localhost';
DROP DATABASE test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

18) Компрессия таблиц

В InnoDB есть формат данных называемый Baracuda. Так вот, он поддерживает компрессию. Это позволяет снизить нагрузку на IO (диски) путём использования сжатия. Так же как рекомендация можно использовать размер блока записи 16Кб. Вот пример alter’a:

SET GLOBAL innodb_file_format=BARRACUDA;
ALTER TABLE `t1` ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;

19) MaatKit

Этот набор инструментов из разряда must have. Скачать его можно отсюда. Очень хорошее описание инструментов можно найти здесь

20) Горизонтальное масштабирование.

Есть такой проект http://prestodb.io/ , который позволяет распаралеливать 1 запрос на несколько серверов. Его используют google, facebook, twitter.

Можно так же использовать federated таблицы или партицирование.

21) Другие советы

http://www.percona.com/blog/2014/12/01/faster-restarts-for-mysql-and-percona-server-5-6-21/
http://www.percona.com/blog/2014/11/20/sys-schema-mysql-5-6-5-7/
http://www.percona.com/blog/2014/12/02/tips-from-the-trenches-for-over-extended-mysql-dbas/

22) Safe-Updates Mode

При включении данного режима нельзя выполнить операции Update / Delete без явного указания where/limit. Так сказать, защита от “чайника”. Подробнее

 

Тюнинг MySQL — подбор опции в зависимости от движка (MyISAM или InnoDB) и количества ресурсов, которым располагает сервер.

В MySQL существует 2 основных движка: InnoDB и MyISAM. Таблицы баз данных обоих типов могут существовать на одном сервере. При необходимости их можно конвертировать, использование  MyISAM оправдано при преобладающем количестве операций с данными одного вида: например SELECT или INSERT.

 

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

Любой тюнинг MySQL — любая тонкая настройка  должна начинаться с определения преобладающего количества таблиц определенного типа.

 

к содержанию ↑

Определение типа таблиц в MySQL

 

Делается это при помощи запроса вида (для innodb):

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = ‘innodb’;

 

И (для myisam):

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = ‘myisam’;

MySQL server настройка, тюнинг mysql

 

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

 

к содержанию ↑

Конфигурация в my.cnf при тюнинге MySQL для InnoDB

 

Ниже подробно рассматриваются основные опции при конфигурации MySQL с преимущественным использованием InnoDB. Он применяется гораздо чаще, затем идет блок про общие для любого движка параметры. В конце статьи есть краткая информация по MyISAM.

 

Все дальнейшие модификации производятся в конфигурационном файле /etc/mysql/my.cnf (или другом файле, который подключается в /etc/mysql/my.cnf, можно найти нужный файл рекурсивным поиском по [mysqld])

 

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

lsof -u mysql | wc -l

В конфигурационном файле задается большее значение.

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 — максимальное количество записей, которые могут обрабатываться единовременно.

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 сводится к подбору оптимальных значений в конфигурационном файле сервера баз данных, которые соответствуют выделяемым серверу ресурсам и типу таблиц, которые используются.

Оставьте свой ответ