Практическая оптимизация и масштабируемость MySQL InnoDB на больших объёмах данных

    Данный пост не будет рассказывать про индексы, планы запросов, триггеры для построения агрегатов и прочие общие способы оптимизации запросов и структуры БД. Так же не будет рассказывать про оптимальные настройки с префиксом innodb_. Возможно прочитав текст ниже вы лучше поймёте смысл некоторых из них. В данном посте речь пойдёт об InnoDB и его функционирование.

    Какие проблемы может помочь решить этот пост?


    • Что делать если у вас в списке процессов множественные селекты которым казалось бы никто не мешает?
    • Что делать если всё хорошо настроено, запросы пролетают как ракеты и список процессов постоянно пустой, но на сервере высокий LA и запросы начинают работать немного медленнее, ну например вместо 100мс получается 500мс ?
    • Как быстро масштабировать систему, когда нет возможности всё переделать?
    • У вас коммерческий проект в конкурентной среде и проблему надо решать немедленно?
    • Почему один и тот же запрос работает то быстро то медленно?
    • Как организовать быстрый кеш и поддерживать его в актуальном состояние?


    Как обычно выглядит работа с БД


    Приблизительно схема работы обычно такая
    1. Запрос
    2. План запроса
    3. Поиск по индексу
    4. Получение данных из таблиц
    5. Отправка данных клиенту

    Даже если вы не делали 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;


    Итого мы получаем следующие временные затраты на выполнение операции чтения или записи в БД
    1. Время на загрузка данных в память с жёсткого диска
    2. Время на обработку данных в памяти
    3. Время на запись данных на жёсткий диск если это требуется (тут стоит отметить, что не все данные сразу пишутся на диск, главное чтобы они были зафиксированы в журнале)


    Думаю интуитивно понятно, что если данные находятся в пуле 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 предоставляет простой механизм репликации просто пишущий данные в указанные таблицы. Это и даёт широкие возможности по разворачиванию дополнительных сервисов содержащих целостные части БД.

    UPD. В случае большого количества мелких транзакций в БД можно попробовать изменить значение innodb_flush_log_at_trx_commit, это обеспечит уменьшение нагрузки на жёсткий диск, но будьте внимательны! теоретически это может привести к потере данных. Увидеть результат работы этой переменной достаточно легко, т.к. можно установить её через SET GLOBAL.

    UPD2. Удачная декомпозиция системы позволяет варьировать настройки отдельных серверов в зависимости от выполняемых ими задач. Например на сервере платежей можно иметь SERIALIZABLE и innodb_flush_log_at_trx_commit = 1. А на сервере постов READ COMMITTED и innodb_flush_log_at_trx_commit = 2. Всё зависит от выполняемых задач, критичности данных и вероятности сбоя.
    Поделиться публикацией

    Похожие публикации

    Комментарии 46

      –8
      Меня всегда смущает mysql и «большие объемы данных» стоящие рядом…
        +14
        Скажите это Facebook :)
          +5
          И гуглю за одно.
            –5
            Да бросьте, у них вроде даже есть собственная файловая система для своих тёмных делишек нужд.
              0
              Тогда скажите это Википедии ;-)
                0
                Это не мешает использовать MySQL как быстрое транзакционное хранилище с sql интерфейсом.
              –1
              Facebook не использует нативную репликацию (свои механизмы уровнем выше).
              Facebook не использует сложные структуры и связи между таблицами, фактически, просто key-value хранилище.

              Называть это «Facebook использует MySQL» у меня язык не поворачивается.
                +1
                А что он делает тогда с MySQL? :)
                  0
                  Использует зачем-то. Так же как и php, который там конвертится в C++, а потом компилится. Странные они в этом фейсбуке.
                    –1
                    Судя по тому что рассказывал их человек на Highload++ они поначалу взяли мускуль и стали дорабатывать напильником под свои нужды, с тех под прошло много лет. Теперь это такой мутант что папа с мамой не узнают. Репликацию они уж точно свою приделали.
                    +1
                    Минусующие почитали б хоть, не знаю…

                    www.insight-it.ru/masshtabiruemost/arkhitektura-facebook/
                      0
                      В то же время, через MySQL проходит почти всё взаимодействие пользователей[1]: likes, shares, status updates, alerts, requests, etc.

                      [1]: http://gigaom.com/cloud/facebook-shares-some-secrets-on-making-mysql-scale/
                        0
                        И?

                        > And, Callaghan noted, the HBase engineering team at Facebook is quite a bit larger than the MySQL engineering team, suggesting that tuning HBase to meet Facebook’s needs is more resource-intensive process than is tuning MySQL at this point.

                        Про внутреннюю структуру подробнее по моей ссылке. Тут больше лирики, чем информации.
                          +1
                          Facebook не использует сложные структуры и связи между таблицами, фактически, просто key-value хранилище.
                          Вашей ссылке уже больше, чем полтора года, кстати. Кроме key-value там так же упоминается и хранение графа.
                          Facebook не использует нативную репликацию (свои механизмы уровнем выше).
                          Facebook использует нативную репликацию, расширенную доп. запросами (команды очистки кеша и др.). В дополнение к ней они используют всякие хитрости для обхода ограниений репликации в один поток, вроде page prefetching на слейве.

                          Если бы они выкинули всю нативную репликацию и реляционность, то, и вправду, смысла в MySQL не было бы.
                  0
                  А что бы Вы поставили рядом с «Большие объемы данных» вместо MySQL?
                    0
                    PostgreSQL хотя бы.
                      –1
                      Вот вчера немного копал тему «MySQL vs. PostgreSQL», и там история говорит что изначально MySQL делался для скорости, а PostgreSQL для поддержки большего количества фич и соответствия стандартам. Поэтому если говорим о больших объемах данных, подразумевая скорость работы с большими объемами данных, то в этом вопросе как раз MySQL смотрится получше.
                        0
                        главное с уровнем изолированности не ошибиться и движком БД :)
                  0
                  хм… а где эта самая 5.6.5 версия mysql-я?
                    +1
                    dev.mysql.com/downloads/mysql#downloads во вкладке Development Releases. На мастере конечно milestone не поставишь, а вот на слейвах вполне
                      0
                      благодарствую)
                      0
                      Кстати она достаточно стабильно работает.
                      +7
                      Статье не хватает более подробного описания буферного кэша и работы с ним, принципа разбиения страниц на блоки, принципа расположения блоков на ФС, принципа освобождения блоков в страницах и повторной записи в них, ну и других теоретических основ :)
                        0
                        Ещё интересно было бы посмотреть на Galera replication. Насколько она способна помочь в случае высокой нагрузки.
                          0
                          Удивлены не увидев innodb_flush_log_at_trx_commit — на некоторых БД результат на порядок может улучшить, реально, при чем достаточно даже 2-ки бывает.
                            0
                            к сожалению это нарушает ACID :) поэтому рекомендовать такое значение у меня мышка не поворачивается :)
                              0
                              A и D нарушит, однако начали-то Вы статью с уровней изоляции, которые прямое отношение имеют к C и I :)
                                0
                                А чем READ_COMMITTED нарушает согласованность и изолированность?
                                  0
                                  Вы намекаете на использование READ COMMITTED, это все же не одно и то же что REPEATABLE READ.
                                    0
                                    Да, т.к. для большинства задач REPEATABLE READ не нужен. Скорее его удобно включать непосредственно в транзакции требующей такой уровень изолированности. Но ACID READ COMMITTED не нарушает.
                                      0
                                      для большинства задач REPEATABLE READ не нужен.
                                      Для многих задач и innodb_flush_log_at_trx_commit=2 не создаст проблем. Если 2-ка родит проблему, то вряд ли она будет самой большой проблемой при таком фэиле.

                                      А чем READ_COMMITTED нарушает согласованность и изолированность? ACID READ COMMITTED не нарушает.
                                      InnoDB supports each of the transaction isolation levels described here using different locking strategies. You can enforce a high degree of consistency with the default REPEATABLE READ level, for operations on crucial data where ACID compliance is important. Or you can relax the consistency rules with READ COMMITTED or even READ UNCOMMITTED
                                        0
                                        2-ка нарушает D пусть и на 1 секунду, что касаемо I то если верить английской вики это

                                        Isolation property ensures that the concurrent execution of transactions results in a system state that could have been obtained if transactions are executed serially i.e. one after the other.

                                        Под это определение кмк только Serializable. A Repeatable reads и Read committed по сути на равных. У первого phantom reads у второго non-repeatable reads :)
                                          0
                                          2-ка нарушает D пусть и на 1 секунду,
                                          innodb_flush_log_at_trx_commit=2 не сильно больше (если кэш на диск часто скидывается, а обычно это именно так), да и вообще — дело в принципе же, а не в размере и частоте:)
                                            0
                                            При большом количестве мелких транзакций скорее всего будет выигрыш в производительности для не RO транзакций :)
                                              0
                                              С 2-кой? Будет вплоть до разницы на порядок.
                                              В практических задачах это очень заметно на грабберах и/или во время импорта сложных данных в базу.
                                                0
                                                Добавил UPD. в пост. Будет возможность проверю во время активной фазы не очень критического процесса.
                                                  0
                                                  спасибо за upd, хотя конечно немного отжигаете:)

                                                  Более точно было бы сказать, что это может привести к сильному росту производительности при частых апдейтах (а не к потере данных:) — выглядит как чистый негатив), а так же указать, что потеря данных возможна только в случае внезапной проблемы на сервере (при 2-ке данные пишутся в кэш диска, кэш ОС, поэтому что бы они утерялись — должно произойти что-то весьма эпическое).

                                                  Настройка эта очень популярна на битриксе, в т.ч. она рекомендуется разработчиками, импорт больших данных реально ускоряет.
                                                    0
                                                    весьма эпическое — типа потери питания?
                                                    или умершего харда?
                                                      0
                                                      Харды всегда в зеркале, а питание у современных ДЦ вроде достаточно надёжно. Тут уже скорее вопрос трезвой оценки требований к хранилищу.
                                          0
                                          я насколько помню у них repetable read по дефолту т.к. репликация была на базе sql запросов, а не построчная в первой версии.
                              0
                              >>Чем длиннее ваш запрос или транзакция, тем больше «старых» данных продолжает накапливать MySQL и есть основания предполагать, что происходит это с активным использованием основного пула памяти.

                              я ошибаюсь или MySQL — блокировочник? Он не накапливает данные, как это делают упомянутые Вами версионники оракл и постгр, он блокирует модификацю данных для получения согласованного результата. По этой причине и запись блокирует чтение и чтение блокирует запись. В оракле, постгре нет такого понятия как блокировки по чтению, они накапливают версии, скорее этим обуславливается преимущество этих СУБД, о котором вы упоминаете, нежели уровень изоляции транзакции по умолчанию. К слову, надо заметить, что ораклиный и постгровский read commited, за счет такой реализации, не в полной мере вписывается в стандарт ansi

                              Я ошибаюсь или действительно в MySQL открытая неявно транзакция закрывается сразу же после выполнения стейтмента? Если нет, то в чем разница блокировок, кинутых запросом в read commited и в repeatable read? Могу ошибаться, полагая, что стратегия блокирования на этих уровнях одинакова, разница лишь в том, что в read commited блокировки отпускаются сразу при завершении работы стейтмента, а в read commmited они отпускаются лишь при завершении транзакции. Если у нас транзакция закрывается сразу после выполнения запроса, разница, как бы, получается, не особо и проглядывается.
                                0
                                Мы говорим про InnoDB. В MyIsam совсем нет транзакций.

                                Вы можете легко проверить что ошибаетесь :) открываете одну транзакцию с REPETABLE READ берёте 1-у запись из таблицы. В другой транзакции делаете вставку в эту таблицу, коммит и затем в первой делаете чтение последней записи из таблицы.

                                Таблица в первой транзакции не изменилась. На практике при большом количестве длинных транзакций и определённой модификации данны, БД просто влипает в селектах при REPETABLE READ, т.к. фиксируется версия любой таблицы задетой в транзакции. Транзакция закрывается в конце транзакции, у нас например OpenSessionInView что делает все операции во время одного HTTP запроса одной транзакцией.
                                  0
                                  А зачем при уровне изоляции read commited накапливать версии?? И откуда у вас информация что MySQL блокировочник? вы можете легко проверить свои домыслы установив mysql и открыв 2-е сессии.
                                    0
                                    >>А зачем при уровне изоляции read commited накапливать версии??
                                    Чтобы обеспечить согласованность результата на время выполнения запроса, если данные, которые попадают в выборку в это самое время меняются другой транзакцией.
                                    >>И откуда у вас информация что MySQL
                                    Всю жизнь считал что это так.
                                    Однако ж, похоже, я очень заблуждался. >>>
                                    Прям таки разрыв шаблона

                                    Спасибо!
                                  0
                                  >>открываете одну транзакцию
                                  Сессию или транзакцию? Если транзакцию открывать явно то да, ясно. Я говорил о неявных транзакциях — тех, которые открываются не start transaction, а которые система сама система, для выполнения стайтмента, если транзакция явно не открыта. Или я бред говорю, и такого нет в MySQL?

                                  В случае же с явными транзакциями тут тонкий вопрос. Транзакцию явно открывают вполне для определенных целях. Да, дотошный программист самостоятельно установит нужный ему уровень изоляции. Но может и забыть, и на старуху бывает… И если приложение протестировано в одном уровне изоляции, приложение прошло проверку временем, то смена уровня изоляции может оказаться очень черевата для таких явно открытых тразнакций. Такое решение требует тщательного тестирования, а тестирование конкурирующих процессов весьма не просто организоываать.

                                  >>Вы можете легко проверить что ошибаетесь
                                  К сожалению не могу, в ареоле моего обитания нет ни одного инстанса MySQL. Но тема мне очень интересна.

                                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                  Самое читаемое