Данный пост не будет рассказывать про индексы, планы запросов, триггеры для построения агрегатов и прочие общие способы оптимизации запросов и структуры БД. Так же не будет рассказывать про оптимальные настройки с префиксом innodb_. Возможно прочитав текст ниже вы лучше поймёте смысл некоторых из них. В данном посте речь пойдёт об InnoDB и его функционирование.
Какие проблемы может помочь решить этот пост?
- Что делать если у вас в списке процессов множественные селекты которым казалось бы никто не мешает?
- Что делать если всё хорошо настроено, запросы пролетают как ракеты и список процессов постоянно пустой, но на сервере высокий LA и запросы начинают работать немного медленнее, ну например вместо 100мс получается 500мс ?
- Как быстро масштабировать систему, когда нет возможности всё переделать?
- У вас коммерческий проект в конкурентной среде и проблему надо решать немедленно?
- Почему один и тот же запрос работает то быстро то медленно?
- Как организовать быстрый кеш и поддерживать его в актуальном состояние?
Как обычно выглядит работа с БД
Приблизительно схема работы обычно такая
- Запрос
- План запроса
- Поиск по индексу
- Получение данных из таблиц
- Отправка данных клиенту
Даже если вы не делали start transaction каждый ваш отдельный запрос будет являться по сути транзакцией из одного запроса. Как известно у транзакций есть уровень изолированности который у MySQL по умолчанию REPEATABLE READ. А что это значит для нас? А то, что когда вы в транзакции «касаетесь любой таблицы» её версия на тот момент фиксируется и вы перестаёте видеть изменения сделанные в других транзакциях. Чем длиннее ваш запрос или транзакция, тем больше «старых» данных продолжает накапливать MySQL и есть основания предполагать, что происходит это с активным использованием основного пула памяти. Т.е. каждый ваш безобидный селект объединяющий 10 таблиц по первичному ключу, в случае активной работы с БД, начинает иметь достаточно тяжёлый побочный эффект. У PostgreSQL как и у Oracle уровень изолированности по умолчанию READ COMMITTED который функционирует гораздо проще, чем REPEATABLE READ. Само собой с уровнем изоляции READ COMMITTED вам придётся использовать построчную репликацию. Вы можете легко проверить уровни изолированности транзакций просто подключивших двумя клиентами к БД и поделать select, delete и update на одной и той же таблице. Это и есть ответ на вопрос про зависающие селекты, попробуйте сменить уровень изоляции БД, это может вам помочь.
Как InnoDB работает с данными
InnoDB хранит данные на жёстком диске в страницах. При обращение к нужной странице, она загружается в оперативную память и затем уже с ней происходят различные действия, будь то чтение запись или что-то ещё. Вот именно этой памятью и является Innodb_buffer_pool размер которого вы выставляете в innodb_buffer_pool_size. Схема работы вполне классическая и ничего необычного в ней нет. Посмотреть отчёты о работе InnoDB можно следующим образом:
SHOW VARIABLES like 'Innodb%';
SHOW GLOBAL STATUS like 'Innodb%';
SHOW ENGINE INNODB STATUS;
Итого мы получаем следующие временные затраты на выполнение операции чтения или записи в БД
- Время на загрузка данных в память с жёсткого диска
- Время на обработку данных в памяти
- Время на запись данных на жёсткий диск если это требуется (тут стоит отметить, что не все данные сразу пишутся на диск, главное чтобы они были зафиксированы в журнале)
Думаю интуитивно понятно, что если данные находятся в пуле InnoDB в момент запроса, то с диска они не загружаются, что сильно уменьшает время выполнения любых операций с БД.
Самый быстрый вариант работы БД это когда все данные и индексы легко помешаются в пул и по сути БД всегда работает из памяти. В версии 5.6.5 даже есть возможность сохранять весь пул на диск при перезапуске БД, что позволяет избежать холодного старта.
Теперь давайте рассмотрим немного другой вариант событий, когда объём данных на диске превышает размер пула памяти. Пусть размер пула у нас будет 4 страницы выглядеть это будет как [0,0,0,0] и 16 страниц данных 1..16 соответственно. Пользователи у нас чаще всего запрашивают страницы 15 и 16 т.к. в них самые свежие данные и они всегда находятся в памяти. Очевидно, что работает всё так же быстро как и в случае описанном выше.
Ну и неудачный вариант, когда у вас есть 2 страницы активно запрашиваемые пользователями и 8 страниц, которые постоянно используются внутренними скриптами и различными демонами. Таким образом за 4 странице в буфере постоянно идёт борьба которая превращается в вечное чтение с диска и замедление работы системы для пользователей т.к. демоны как правило гораздо активнее себя ведут.
В таком режиме вам может помочь настройка репликации с ещё одни МySQL сервером который может принять на себя часть запросов и снизить борьбу за пул памяти. Но как известно у репликации в MySQL есть существенный недостаток, а именно применение изменений в 1 поток. Т.е. при определённых условиях слейв у вас или начнёт отставать или даст совсем несущественный прирост производительности. В этой ситуации может помочь возможность создавать слейвов с ограниченным числом таблиц. Что позволит получить выигрыш как на применение изменений так и на использование пула памяти. Во многих случаях, когда известны данные которые пользователи запрашивают чаще всего, вы можете создать для них кеш хранящий данные только из необходимых таблиц. В удачном случае у вас получится кеш автоматически поддерживающий свою актуальность. Для тех кому интересно как быстро создать ещё одного слейва, предлагаю посмотреть в сторону
STOP SLAVE;
SHOW SLAVE STATUS;//Master_Log_File и Exec_Master_Log_Pos
Тут нужно сделать дамп таблиц которые вам понадобятся
START SLAVE;
После того как вставите данные в новый слейв сервер, нужно будет только сделать
CHANGE MASTER TO ... ;
START SLAVE;
Всё конечно зависит от объёма данных, но как правило поднять такого слейва можно достаточно быстро.
Декомпозиция системы на модули используя частичную репликацию
И так мы можем создавать частичные реплики основной БД, что позволяет нам контролировать распределение памяти между определёнными группами данных. Какие возможности это перед нам открывает?
Как вы можете выяснить опытным путём никто не мешает вам создавать свои таблицы на слейв сервере и даже создавать в них внешние ключи на реплицируемые данные. Т.е. вы можете иметь не только целостную основную БД, но и целостных слейвов с расширенным набором таблиц. Например, ваша главная БД содержит таблицу users и различные вспомогательные таблицы типа payments. Так же у вас есть сервис блогов, который позволяет пользователям писать сообщения. Вы реплицируете users в другую БД, в которой создаёте таблицу posts. Если на БД содержащую таблицу posts выпадает высокая нагрузка на чтение, вы создаёте реплики содержащие таблицы users и posts. Таким образом можно производить декомпозицию пока объём данных необходимого набора таблицы не станет превышать разумные пределы. В этом случае уже стоит посмотреть в сторону шардинга огромных таблиц, например по хешу идентификатора пользователя, а запросы к нужным воркерам направлять через MQ.
Итоги
MySQL предоставляет простой механизм репликации просто пишущий данные в указанные таблицы. Это и даёт широкие возможности по разворачиванию дополнительных сервисов содержащих целостные части БД.
Автор: dgreen
