Ubuntu mysql оптимизировать бд

Обновлено: 04.07.2024

У Вас тормозит сайт. Вы не можете качественно отдать контент. Абоненты не могут нормально работать с закрытыми частями сайта. Служба поддержки получает звонки от разгневаных пользователей. Кто виноват и что делать? Ответ как правило в следующем:

некачественный код
не оптимизирована база
не оптимально настроен сервер БД
все вышесказанное вместе
не справляется железо (этот пункт следует рассматривать только после оптимизации пп. 1-3)

Оставим рефакторинг кода на откуп программистам и посмотрим, как можно ускорить mysql сервер. Все написаное ниже верно и для mariadb

Настройка mysql

Установить последнюю версию mysql
Запустить mysqltunner и дать ему поработать 24+ часов. Выполнить его рекомендации
Настроить query_cache_size, query_cache_limit
Для движка MyISAM настроить key_buffer_size
Для движка ARIA настроить aria-pagecache-buffer-size (только для mariadb)
Значение innodb_buffer_pool_size должно быть 70-80% от обьема RAM
Для innodb-таблиц установить innodb_file_per_table=1. Для изменений потребуется сдампить и удалить (переименовать) базу. Накатить базу из дампа по-новой
Посмотреть на параметр innodb_flush_log_at_trx_commit. Возможно имеет смысл отключить сброс данных на диск
Выяснить, что используется чаще SELECT или UPDATE. Если SELECT то low-priority-updates=1, если UPDATE то low-priority-updates=0. Есть смысл сделать два инстанса mysql на разных сокетах/портах и разделить базы по приоритету использования SELECT/UPDATE
Возможно потребуется увеличить wait_timeout если у конечного пользователя наблюдаются ошибки соединения
Для временных таблиц использовать tmpfs вместо дисковой фс
Если возможно, для обмена данными приложения и БД использовать сокет. Такой обмен работает быстрее, чем обмен через tcp-соединение

Настройка БД

Запустить mytop либо mtop и отследить медленные запросы
Включить slow.log. Оптимизировать медленные запросы. Использовать EXPLAIN
Проверить индексы в таблицах. Там, где используется поиск однозначно должен быть индекс
Установить правильные тип и размер полей. Это уменьшит размер таблицы
Использовать партиционирование если в таблице есть старые данные, которые нужны, но к ним редко обращаются
Регулярный optimize table таблиц с движком ARIA/MyISAM
Пробовать persistant connection в базу. Возможно полегчает, возможно нет. Это индивидуально

Железо и система

Системный администратор. В сисадминстве с 2000 года. Участник cyberforum

Сегодня речь пойдет опять и снова про mySQL. Разберемся в оптимизации и поговорим про множество параметров сервера.
Давайте приступать.

Начало

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

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

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

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

Выведем htop :

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

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

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

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

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

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

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

Не будем заниматься бездумной подстановкой, а пройдемся по параметрам 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, обычно этому параметру присваивается значение 4 или 8 , на быстрых SSD -дисках установите в 16. Значение innodb_thread_concurrency установите в количество ядер * 2 .

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

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

Заключение

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

Кэш запросов – это функция MySQL, которая позволяет ускорить извлечение данных из БД. Это делается путем сохранения в памяти операторов SELECT с извлеченными записями; если позже клиент отправляет идентичный запрос, данные выводятся быстрее, поскольку не нужно повторно выполнять команды в БД.

По сравнению с данными, хранящимися на диске, кэшированные данные из RAM (Random Access Memory) можно получить быстрее, что улучшает операции ввода/вывода (I/O). Например, благодаря кэшу запросов производительность сайтов WordPress или e-commerce порталов с большим количеством операций чтения и почти не меняющимися данными может значительно вырасти.

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

Примечание: Кэш запросов устарел в версии MySQL 5.7.20 и был удален в MySQL 8.0, но он по-прежнему является мощным инструментом и его можно использовать в тех версиях MySQL, которые поддерживают его. В более новых версиях MySQL можно использовать альтернативные сторонние инструменты для оптимизации производительности базы данных MySQL (такие как ProxySQL).

Требования

Сервер Ubuntu 18.04, настроенный по этому мануалу.
Сервер MySQL (смотрите мануал Установка MySQL в Ubuntu 18.04).

1: Проверка кэша запросов

Перед настройкой кэша запросов нужно проверить, поддерживает ли ваша версия MySQL эту функцию. Сначала подключитесь по ssh к серверу Ubuntu 18.04:

Затем выполните следующую команду, чтобы войти на сервер MySQL как пользователь root:

sudo mysql -u root -p

При появлении запроса введите root пароль сервера MySQL, а затем нажмите Enter, чтобы продолжить.

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

show variables like 'have_query_cache';

Вы должны получить такой вывод:

Если в have_query_cache установлено значение YES, это означает, что кэш запросов поддерживается вашей версией СУБД. Если ваша версия не поддерживает кэш, можно использовать альтернативные сторонние инструменты для оптимизации производительности MySQL (такие как ProxySQL).

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

2: Проверка стандартных переменных кэша запросов

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

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

show variables like 'query_cache_%' ;

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

+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
5 rows in set (0.00 sec)

Значение query_cache_limit определяет максимальный размер отдельных результатов запроса, которые могут быть кэшированы. Значение по умолчанию составляет 1 048 576 байт, что эквивалентно 1 МБ.

MySQL не обрабатывает кэшированные данные целиком; он обрабатывает их блоками. Минимальный объем памяти, выделяемый каждому блоку, определяется переменной query_cache_min_res_unit. Значение по умолчанию составляет 4096 байт, или 4 КБ.

Переменная query_cache_size контролирует общий объем памяти, выделенной для кэша запросов. Если в ней установлено значение 0, это означает, что кэш запросов отключен. В большинстве случаев по умолчанию может быть установлено значение 16 777 216 (около 16 МБ). Кроме того, следует иметь в виду, что query_cache_size требует как минимум 40 КБ для размещения своих структур. Выделенное здесь значение выравнивается по ближайшему 1024-байтовому блоку – то есть фактическое значение может немного отличаться от установленного вами.

MySQL определяет запросы к кэшу, изучая переменную query_cache_type. Значение 0 или OFF отключает кэширование или извлечение кэшированных запросов. Вы также можете установить значение 1, чтобы включить кэширование для всех запросов, кроме тех, которые начинаются с SELECT SQL_NO_CACHE. Значение 2 позволяет кэшировать только те запросы, которые начинаются с SELECT SQL_CACHE.

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

Примечание: Переменная query_cache_wlock_invalidate устарела с версии MySQL 5.7.20. Вполне возможно, что вы не увидите ее в своем выводе (в зависимости от версии MySQL, которую вы используете).

Изучив системные переменные, которые управляют кэшем запросов MySQL, мы можем проверить, как работает MySQL без кэша.

3: Тестирование MySQL без кэша запросов

Цель этого мануала – оптимизировать сервер MySQL с помощью кэширования запросов. Чтобы увидеть разницу в скорости, мы оценим производительность обработки запросов до и после внедрения кэша.

Сейчас мы создадим тестовую БД и вставим туда какие-нибудь данные, чтобы посмотреть, как MySQL работает без кэша запросов.

Создайте базу данных и назовите ее sample_db, выполнив следующую команду:

Create database sample_db;
Query OK, 1 row affected (0.00 sec)

Перейдите в эту БД:

Use sample_db;
Database changed

Создайте здесь таблицу с двумя столбцами, customer_id и customer_name. Давайте назовем таблицу customers:

Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;
Query OK, 0 rows affected (0.01 sec)

Запустите следующие команды, чтобы вставить тестовые данные:

Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE');
Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE');
Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE');
Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE');
Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE');
Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE');
Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH');
Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS');
Insert into customers(customer_id, customer_name) values ('9', 'JANE POE');
Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE');
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
.

Теперь нужно запустить профайлер MySQL, аналитический сервис для мониторинга производительности запросов MySQL. Чтобы включить профиль для текущего сеанса, выполните следующую команду, установив значение 1 (что значит «включено»):

SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Затем запустите следующий запрос, чтобы извлечь список клиентов:

Select * from customers;

Вы получите следующий вывод:

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | JANE DOE |
| 2 | JANIE DOE |
| 3 | JOHN ROE |
| 4 | MARY ROE |
| 5 | RICHARD ROE |
| 6 | JOHNNY DOE |
| 7 | JOHN SMITH |
| 8 | JOE BLOGGS |
| 9 | JANE POE |
| 10 | MARK MOE |
+-------------+---------------+
10 rows in set (0.00 sec)

Затем запустите команду SHOW PROFILES, чтобы получить информацию о производительности для только что выполненного запроса SELECT:

Вы получите примерно такой вывод:

Выходные данные показывают общее время, потраченное MySQL на извлечение записей из БД. Позже мы сравним эти данные с результатом, полученным при извлечении записей с включенным кэшем запросов. Для этого запишите текущее значение Duration. Предупреждение в выводе можно проигнорировать: оно просто сообщает, что команда SHOW PROFILES будет удалена в следующем релизе MySQL и заменена схемой производительности (Performance Schema).

Выйдите из интерфейса командной строки MySQL.

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

4: Настройка кэша запросов

Сейчас у нас есть тестовая БД, где мы уже запустили SELECT при отключенном кэше запросов. Давайте теперь включим этот кэш, отредактировав конфигурационный файл MySQL.

Откройте файл в редакторе:

sudo nano /etc/mysql/my.cnf

Добавьте следующие строки в конец файла:

.
[mysqld] query_cache_type=1
query_cache_size = 10M
query_cache_limit=256K

Мы включили кэш запросов, установив для параметра query_cache_type значение 1. Также мы установили индивидуальный предельный размер запроса в 256 КБ и выделили 10 мегабайт для кэша запросов (значение параметра query_cache_size равно 10 МБ).

Сохраните и закройте файл. Затем перезапустите сервер MySQL, чтобы изменения вступили в силу:

sudo systemctl restart mysql

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

5: Тестирование MySQL с поддержкой кэша запросов

Теперь давайте повторим тот запрос, который мы выполнили в разделе 3, чтобы посмотреть, как кэш запросов оптимизировал производительность сервера MySQL.

Сначала подключитесь к серверу MySQL как root:

sudo mysql -u root -p

Введите свой root пароль для сервера базы данных и нажмите Enter, чтобы продолжить.

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

show variables like 'query_cache_%' ;

Вы увидите следующий вывод:

+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 262144 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 10485760 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
5 rows in set (0.01 sec)

Переменная query_cache_type имеет значение ON; это подтверждает, что вы включили кэш запросов с параметрами, которые определили на предыдущем этапе.

Перейдите в базу данных sample_db, которую вы создали ранее.

SET profiling = 1;

Затем выполните запрос, чтобы вывести список всех клиентов как минимум два раза (чтоб собрать достаточно информации для профилирования).

После того как вы выполните первый запрос, MySQL создаст кэш результатов, и, следовательно, вы должны повторить запрос еще раз, чтобы вызвать значение из кэша, если все работает правильно:

Select * from customers;
Select * from customers;

Затем запросите информацию о профилях:

Вы получите такой вывод:

Как видите, время, затраченное на выполнение запроса, резко сократилось с 0,00044075 (без кэша запросов ) до 0,00026000 (обратите внимание на второй запрос).

Вы можете оценить преимущества кэширования запросов с помощью детального профилирования первого запроса:

SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000025 |
| Waiting for query cache lock | 0.000004 |
| starting | 0.000003 |
| checking query cache for query | 0.000045 |
| checking permissions | 0.000008 |
| Opening tables | 0.000014 |
| init | 0.000018 |
| System lock | 0.000008 |
| Waiting for query cache lock | 0.000002 |
| System lock | 0.000018 |
| optimizing | 0.000003 |
| statistics | 0.000013 |
| preparing | 0.000010 |
| executing | 0.000003 |
| Sending data | 0.000048 |
| end | 0.000004 |
| query end | 0.000006 |
| closing tables | 0.000006 |
| freeing items | 0.000006 |
| Waiting for query cache lock | 0.000003 |
| freeing items | 0.000213 |
| Waiting for query cache lock | 0.000019 |
| freeing items | 0.000002 |
| storing result in query cache | 0.000003 |
| cleaning up | 0.000012 |
+--------------------------------+----------+
25 rows in set, 1 warning (0.00 sec)

Выполните следующую команду, чтобы отобразить информацию о втором запросе:

SHOW PROFILE FOR QUERY 2;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000024 |
| Waiting for query cache lock | 0.000003 |
| starting | 0.000002 |
| checking query cache for query | 0.000006 |
| checking privileges on cached | 0.000003 |
| checking permissions | 0.000027 |
| sending cached result to clien | 0.000187 |
| cleaning up | 0.000008 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)

Выводы профайлера показывают, что MySQL потратил меньше времени на обработку второго запроса, потому что он мог извлечь данные из кэша запросов, а не с диска. Вы можете сравнить два вывода для каждого из запросов. Если вы посмотрите информацию о профиле для QUERY 2, состояние sending cached result to client показывает, что данные были прочитаны из кэша и таблицы не открывались (статуса Opening tables не будет).

Если на вашем сервере включена функция кэширования запросов MySQL, вы сможете улучшить скорость чтения данных.

Заключение

Вы настроили кэш запросов для ускорения работы сервера MySQL в Ubuntu 18.04. Эта функция может повысить скорость работы вашего веб-сайта или приложения. Кэширование устраняет обработку лишних операторов SQL и является очень и популярным методом оптимизации базы данных.

Любой успешный проект рано или поздно сталкивается с проблемой роста. Число посетителей веб-сайта увеличивается, веб-сервер обрабатывает бóльшее количество соединений, растёт поток запросов к базе данных. В определённый момент времени отзывчивость сайта снижается: страницы загружаются медленнее, что, согласно многочисленным исследованиям, влияет на конверсию.

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

В первую очередь следует выяснить характер нагрузки на диски. В этом поможет утилита iostat. В Ubuntu она устанавливается с пакетом sysstat:

iostat - очень мощный и удобный инструмент для просмотра статистики ввода/вывода и показателей загрузки блочных устройств. Его использование - тема для отдельной статьи. В нашей ситуации с его помощью следует определить соотношение чтения/записи, чтобы выяснить дальнейшее направление работы.

Как ускорить чтение

Допустим, диски загружены запросами на чтение. Что можно сделать, чтобы ускорить отдачу данных? Закэшировать данные в памяти. MySQL предоставляет возможность использования разных хранилищ, или движков (storage engines), для доступа к данным, поэтому подход к кэшированию разный. Рассмотрим два наиболее популярных движка: MyISAM и InnoDB.

Движок InnoDB имеет встроенный кэш для данных и индексов - так называемый Buffer Pool. Его размер регулируется переменной innodb_buffer_pool_size. В идеале размер Buffer Pool должен быть как минимум такого объёма, чтобы в нём полностью можно было разместить все данные и индексы плюс 30%-60% от их размера. Дополнительная память используется для служебных нужд и Insert Buffer, а также для обеспечения запаса памяти на будущее. Переменная innodb_buffer_pool_size - не динамическая, поэтому после её изменения в конфигурационном файле потребуется перезапуск MySQL.

Движок MyISAM не имеет кэша для данных. Но мы по-прежнему можем ускорить чтения из таблиц MyISAM. Дело в том, что ядро Linux кэширует все прочитанные файлы в области оперативной памяти, которая называется pagecache. Разумеется, файлы с таблицами MyISAM также попадают в этот кэш. Объём pagecache можно узнать из вывода команды free:

Максимальной производительности чтения можно добиться, если объём pagecache равен объёму данных MyISAM.

По умолчанию под pagecache выделяется почти вся незанятая процессами память, поэтому увеличить его объём можно лишь установкой дополнительных планок RAM. Однако память - недорогой по сравнению с ЦПУ и дисками ресурс, при этом эффект от увеличения кэша может привести к значительному увеличению производительности. Ниже представлен график %iowait - доли времени, в течение которого ЦПУ ожидает ввода/вывода. График снят с рабочего нагруженного сервера. Думаю, комментарии здесь излишни.

Как ускорить запись

Увеличить производительность MySQL при большом объёме записи можно с помощью тонкой настройки параметров сервера.

По умолчанию InnoDB сбрасывает изменённые данные на диск с помощью системного вызова fsync(). При этом операционная система не гарантирует, что данные попадут в хранилища сию секунду, т.к. данные сперва проходят через буфер, поддерживаемый ядром. Буферизация необходима для ускорения ввода/вывода.

Однако если datadir MySQL расположен на аппаратном RAID-массиве, то есть возможность задействовать для такой буферизации NVRAM-кэш RAID-контроллера, что намного эффективнее. Следует только убедиться, что контроллер оснащён BBU (Battery Backup Unit) - отдельным источником питания для кэша. При внезапном отключении электропитания у контроллера должно быть время, чтобы сбросить содержимое кэша на диски, иначе данные в массиве останутся в неконсистентном состоянии.

При задействовании кэша RAID-контроллера повысить производительность операций записи в БД можно, отключив ненужную буферизацию на уровне операционной системы. Для этого требуется выставить переменную MySQL innodb_flush_method в значение O_DIRECT, после чего перезагрузить систему управления базы данных. Снизить нагрузку на диски также может изменение переменной innodb_flush_log_at_trx_commit. Для соответствия требованиям ACID движок InnoDB хранит логи транзакций, или redo-логи, в которые записываются все запросы на изменение данных. Эти логи используются в процессе восстановления после аварийного останова системы управления базами данных.

Значение по умолчанию (1) предполагает, что буфер redo-логов, расположенный в памяти InnoDB, записывается на диск после каждого коммита транзакции. Это наиболее безопасный режим работы, обеспечивающий сохранность каждой транзакции даже в случае “падения” сервера. Можно выставить innodb_flush_log_at_trx_commit в значение 2, тогда логи будут записываться также после каждого коммита, но fsync() - сброс данных на диск - будет выполняться лишь раз в секунду (начиная с версии MySQL 5.6.6 этот интервал определяется переменной innodb_flush_log_at_timeout). Аварийное завершение работы СУБД не приведёт к потере транзакций, однако отключение самого сервера может привести к потере последней секунды транзакций. Значение 0 подразумевает ещё более быстрый режим записи - данные и записываются, и синхронизируются раз в секунду, безотносительно коммитов транзакций. Однако innodb_flush_log_at_trx_commit=0 может привести к потере транзакций даже при падении процесса. Администратору базы данных нужно сделать выбор исходя из текущей нагрузки и бизнес-требований.

Оптимизировать дисковые операции записи помогает правильный выбор размера redo-логов. Для этого есть несложное правило. Достаточно замерить объём данных, который записан в лог за одну минуту. Эту операцию нужно выполнять в момент дневной пиковой нагрузки:

Из примера видно, что за минуту в лог InnoDB записывается 2,44 Мб данных. Объём лога следует подбирать таким образом, чтобы в него умещался объём данных за час. В таком случае у InnoDB будет достаточно времени, чтобы изменить порядок запросов на ввод/вывод для достижения последовательной записи. В нашем примере за один час через redo-логи проходит 150 Мб данных, поэтому переменную innodb_log_file_size следует выставить в значение не менее 75M. Если объём лога выбрать слишком большим, то увеличится время InnoDB Crash Recovery, что увеличит даунтайм при аварийном перезапуске (стоит отметить, что в MySQL 5.5 время Crash Recovery зависит от размера InnoDB-лога в меньшей степени).

Вывод

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

Читайте также: