NoSQL в MySQL: разгон MySQL до 750 000 запросов в секунду

Yoshinori Matsunobu 20 октября в своем блоге опубликовал интересную (на мой взгляд) статью – компания, в которой он работает разработала и успешно использует MySQL плагин, который позволяет обрабатывать более 750 тысяч запросов на вполне обычном железе. Решение – очень красивое, при этом позволяет использовать как обычные SQL запросы, так и достигать производительности, которая не доступна даже NoSQL решениям. Конечно, результаты тестов впечатляют, но слишком все сладко – возможно, где-то есть и подводные камни. Надо тестировать. Если все пройдет гладко, то постараюсь внедрить – как раз есть проект, на который это решение ложится идеально – в нем требуется как производительность, так и высоки требования к надежной системе хранения данных.

Многие высоконагруженные веб-приложения используют связку MySQL + memcached. Некоторые другие используют NoSQL, например, TokyoCabinet/Tyrant. В некоторых случаях разработчики списывают MySQL и переходят на NoSQL. Одной из основных причин для такого перехода является то, что говорят, что NoSQL-решения более производительные, чем основанные на MySQL. Например, тот же выборка по ключу. Для большинства обычных для веб-приложений запросов — это выглядит вполне разумным решением.
Как и многие другие высоконагруженные сайты, мы в DeNA (*) решали эту проблему несколько лет. Но в итоге пришли к неожиданному выводу. Мы используем “только MySQL”. Да, мы используем memcached для кеширования на стороне front-end (например, предварительно обработанный HTML, рассчитанную или сводную информацию), но мы не используем memcached для кеширования отдельных строк. Мы не используем NoSQL. Вообще. Почему? Потому что мы смогли получить от MySQL такую производительность, которую нам не дают другие NoSQL решения. В наших тестах мы смогли получить выборки со скоростью более 750 000 запросов в секунду на обычном сервере (MySQL/InnoDB 5.1). Так же отличная производительность была получена и для продакшен-серверов.
Возможно, вы не можете поверить этим результатам, но это чистая правда. В этой статье я хотел бы поделиться нашим опытом.
(*) Для тех, кто не в курсе – я покинул Oracle в августе 2010. Сейчас я работаю в DeNA, одном из крупнейшем провайдере социальных игр в Японии.

Действительно ли SQL так хорош при поиске по первичному ключу?

Как часто вашему приложению приходится осуществлять поиск по первичному ключу? Например наши приложения, делают это очень часто — найти пользователя по его идентификатору или выбрать информацию о дневнике по идентификатору. Казалось бы, memcached и NoSQL как раз и подходят для такого рода операций. Если вы запустите простой многопоточный тест “memcached get”, вы можете получить более 400 тысяч операций в секунду для удаленных клиентов. Когда я тестировал последую версию libmemcached я получил 420 тысяч выборок в секунду на 8 ядерном процессоре (Nehalem), работающем на частоте 2.5ГГц с гигабитной сетевой картой.
Что мы можем получить от MySQL в аналогичной ситуации? Проведем очень простой тест. Запустим несколько одновременных запросов из-под sysbench, super-smack, mysqlslap, или чего-то подобного:

Вы можете легко проверить сколько строк InnoDB читает в секунду:

Более 100,000 запросов в секунду — совсем не так плохо, но все же значительно медленнее, чем в случае с memcached. Чем же MySQL в реальности занимается, что работает так медленно?
Вывод vmstat показывает, что и %user и %system так же слишком большие:

Вывод OProfile расскажет нам, на что же были потрачены ресурсы:

MYSQLparse() и MYSQLlex() были вызваны в процессе разбора SQL запроса. make_join_statistics() и JOIN::optimize() были вызваны в фазе оптимизации запроса. Это и есть “SQL-оверхед”. Очевидно, что падение происходит собственно на уровне работы с SQL, а не на уровне движка хранилища (в нашем случае – InnoDB). MySQL вынужден сделать много чего такого, что не нужно делать в случае с memcached/NoSQL. А именно:

  • Разобрать SQL выражение
  • Выполнить открытие и блокировку таблиц
  • Подготовить план выполнения запроса
  • Разблокировать и закрыть таблицы
MySQL так же требуются ресурсы на управление одновременными запросами. Например, fcntl() будут вызываться много раз в течении отправки/получения сетевых пакетов. Глобальные мютексы, такие как LOCK_open и LOCK_thread_count так же будут взяты и отпущены очень много раз. Именно поэтому my_pthread_fastmutex_lock() находится на втором месте среди потребителейресурсов. И в команде самого MySQL и комьюнити прекрасно знают о проблемах возникающих при одновременных запросах. Некоторые проблемы были решены в версии 5.5. И я очень рад, что так много исправлений было внесено.
Так же очень важно отметить, что %user достигало 60%. Мьютексы могли повлиять на увеличение %system, но не на %user. Т.е. даже если все проблемы с мьютексами внутри MySQL будут решены, то мы все равно не сможем ожидать результата выше 300 тысяч запросов в секунду.
Возможно, вы слышали или даже использовали оператор HANDLER. К сожалению, использование HANDLER не приносит значительного увеличения пропускной способности. Поскольку по прежнему необходим анализ запросов, открытие/закрытие таблиц.

Эффективность работы CPU очень важна

Если некоторый объем данных разместить в памяти, то SQL оверхед становится незначительным. Это довольно просто объясняется, поскольку стоимость дисковых операций ввода-вывода очень высока. И нам уже не нужно беспокоится о стоимости SQL-операций в данном случае.

На некоторых наших нагруженных MySQL серверах почти все данные помещаются в памяти и все напрямую зависит только от эффективности работы CPU. При этом результаты профилирования очень похожи на те, что я приводил выше — на уровне SQL были потрачено слишком много ресурсов. Нам необходим часто осуществлять выборки по первичному ключу (типа SELECT x FROM t WHERE id=?), или по диапазону значений. И хотя 70-80% запросов являются простым поиском по первичному ключу в некоторой таблице (разница фактически только в значении в условии WHERE), каждый раз MySQL вынужден выполнять эту последовательность – разобрать-открыть-блокировать-разблокировать-закрыть. Которая, как мы выяснили выше, крайне негативно влияет на время выполнения нашего запроса.

Cлышали что-то про NDBAPI?

Есть ли какое-то решение, позволяющее уменьшить пожирание ресурсов CPU в SQL слое MySQL? Если вы использовали MySQL Cluster, NDBAPI является именно таким решением. Когда я работал в MySQL/Sun/Oracle как консультант, многие клиенты, были крайне разочарованы в производительности SQL-ноды + NDB, но после того как они переходили на NDBAPI, производительность увеличивалась в несколько раз. Вы вполне можете использовать как NDBAPI, так и обычный SQL для MySQL кластера. Более того, настоятельно рекомендуется использовать именно NDBAPI для частых запросов, а для не стандартных или не очень частых запросов использовать связку SQL + MySQL + NDB.

Казалось бы это должно было стать именно тем решением, что мы так искали. Мы хотели получить быстрое API, но мы так же хотели иметь возможность использовать привычный SQL для построения сложных запросов. Но в DeNA, как и во многих других проектах, мы используем InnoDB. Поэтому переход на NDB оказался бы не таким простым для нас. А встраиваемая InnoDB не поддерживает ни SQL, ни сетевого подключения. Так что это решения явно не для нашего случая.

Разработка “HandlerSocket Plugin” — плагина, реализующего NoSQL протокол для MySQL

Мы подумали и решили, что лучшим решениям являлся бы встроенный в MySQL сервер NoSQL, который был бы доступен по сети. Т.е. написать сетевой сервис, MySQL плагин, который будет принимать запросы на определенных портах, используя NoSQL протокол, а изнутри получать доступ к InnoDB используя внутренний MySQL API для движков хранилищ. Это очень похоже на NDBAPI, только работает с InnoDB.

Этот подход был опробован Kazuho Oku из Cybozu Labs в прошлом году. Он написал MyCached UDF, которая реализовывала memcached протокол. Мой коллега Akira Higuchi разработал другой плагин — HandlerSocket. Картинка ниже показывает, что же такое HandlerSocket.

Таким образом, HandlerSocket это MySQL плагин, который позволяет обращаться к MySQL так же как к NoSQL. Самым большим плюсом HandlerSocket является то, что он позволяет использовать InnoDB таблицы, при этом избегая накладных расходов, связанных SQL. При работе с MySQL-таблицей, конечно же не обходится без операций открытия/закрытия таблиц. Но HandlerSocket не делает это каждый раз. Он оставляет таблицы открытыми для повторного использования. Открытие/закрытие таблиц достаточно дорогие операции, поэтому такой подход позволяет достичь хорошей производительности. Конечно же HandlerSocket закроет таблицы, когда трафик упадет, таким образом он не будет блокировать административные команды (DDL).

В чем же отличие от варианта MySQL + memcached? Сравнивая рисунки 1 и 2, я думаю, что вы сами найдете массу отличий. На рисунке 2 представлен типичный пример использования memcached и MySQL. Memcached используется для активного кеширования отдельных выбранных записей из базы данных. Это делается по причине того, что операция получения данных из memcached гораздо быстрее, чем операции поиска по первичному ключу в MySQL. Если HandlerSocket позволяет получать записи быстрее чем из memcached, мы уже не нуждаемся в подобного рода кешировании.

Использование HandlerSocket

Теперь о главном. Допустим у нас есть таблица “user”:

и нам нужно получить информацию о пользователе, по его идентификатору (user_id). В MySQL это можно сделать, используя простой запрос вида:

Попробуем сделать тоже самое, используя HandlerSocket.

Для начала нам необходимо установить HandlerSocket. Не будем останавливаться на подробном описании процесса установки, остановимся только на основных моментах:

  1. Загружаем исходный код HandlerSocket
  2. Собираем HandlerSocket (клиентскую и серверную часть) обычным образом:
  3. Внедряем HandlerSocket в MySQL

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

Теперь напишем HandlerSocket клиента. Для этого существует С++ и Perl библиотека. Ниже представлен Perl код, получающий информацию о пользователе по первичному ключу:

Код выше получает user_name, user_email и и выводит данные из таблицы table, для user_id=101. Т.е. Мы получили тот же результат, что и приведенный выше SELECT запрос.

Для большинства веб-приложений правильным подходом будет держать открытыми (persistent connections) установленные HandlerSocket-соединения. И, таким образом, сосредоточится собственно на основной логике приложения (пункт 3 в нашем коде).

Протокол HandlerSocket является простым текстовым протоколом, как в случае с memcached, вы можете получить доступ к HandlerSocket используя telnet:

Тесты производительности

А теперь, самое интересное — результаты тестирования. Я использовал приведенную выше таблицу пользователей, и посмотрел сколь много выборок я смогу получить, на удаленных клиентах, запущенных в несколько потоков. Все данные помещались в памяти (я тестировал на таблице с 1 миллионом записей). Так же я протестировал аналогичный вариант используя memcached (использовался libmemcached и memcached_get() для получения пользовательских данных). В тесте с обычным SQL-запросом использовался запрос вида «SELECT user_name, user_email, created FROM user WHERE user_id=?». Коды обоих клиентов (memcached и HandlerSocket) были написаны на C/C++. Все клиенты располагались на удаленной машине и соединялись с MySQL/memcached через TCP/IP.

Результаты распределились следующим образом:

Доступ через HandlerSocket получился почти в 7.5 раз быстрее чем обычный SQL запрос, хотя процент использования CPU был в соотношении 3/4. Это еще раз показывает, что SQL уровень в MySQL очень дорогой и его исключение резко повышает производительность. Так же очень интересен тот факт, что работа через HandlerSocket на 178% быстрее чем memcached, и memcached потребляет значительное количество системных ресурсов. Безусловно, memcached является отличным продуктом, однако есть куда стремится в плане оптимизации.

Ниже представлены результаты вывода oprofile, полученные при выполнении тестов с HandlerSocket. Как видно, ресурсы CPU были потрачены на основные операции, такие как обработка сетевых пакетов, выборка строк и т. д. (bnx2 это драйвер сетевого устройства):

Теперь, когда HandlerSocket работает внутри MySQL и может выбирать данные из InnoDB-таблиц, вы можете получить статистику используя обычный запрос SHOW GLOBAL STATUS. А там, и вправду есть на что посмотреть — более 750 тысяч Innodb_rows_read.

Ну и напоследок несколько слов о сервере, на котором были получены эти результаты:
CPU: Nehalem 8 cores, E5540 @ 2.53GHz
RAM: 32GB (все данные помещаются в пул буфера)
MySQL: 5.1.50 с поддержкой InnoDB
memcached/libmemcached: 1.4.5(memcached), 0.44(libmemcached)
Сеть: Broadcom NetXtreme II BCM5709 1000Base-T x 3
В тестах memcached и HandlerSocket сетевая подсистема стала узким местом. Когда я проводил тесты с единственной гигабитной картой, результаты несколько отличались, и я получил около 260 тысяч запросов в секунду для HandlerSocket и 220 тысяч для memcached.

Возможности и особенности использования HandlerSocket

HandlerSocket располагает множеством функций, многие из которых крайне полезны. Ниже приведены основные преимущества.

Поддержка множества моделей запросов

HandlerSocket поддерживает поиск по первичному и уникальному ключу, поиск по неуникальному ключу, поддерживается указание диапазона выборки (range scan) и конструкции LIMIT. Поддерживаются операции INSERT/UPDATE/DELETE. Операции, не использующие ключи — не поддерживаются. Операции, возвращающие несколько строк за один запрос (конструкции вида IN(1,2,3..) ) так же поддерживаются. Подробности см. в документации

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

HandlerSocket соединение очень легкие. HandlerSocket использует epoll() и worker-thread/thread-pooling архитектуру, количество потоков ограничено, и может быть настроено используя параметр handlersocket_threads в my.cnf. Таким образом вы можете использовать тысячи или даже десятки тысяч сетевых соединений без потери стабильности работы вашего приложения (что в случае «обычного» MySQL может вызывать проблемы с памятью — см. bug#26590, bug#33948, bug#49169 ).

Очень высокая производительность

Как уже говорилось, HandlerSocket позволяет получить вполне конкурентоспособные значения производительности по сравнению с другими NoSQL решениями. Однако, я на практике не видел ни одного решения, позволяющего обрабатывать более 750 тысяч запросов от удаленных клиентов по TCP/IP.

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

Не большие сетевые пакеты. Протокол HandlerSocket очень прост и намного проще, чем стандартный протокол MySQL. Что существенно уменьшает объемы передаваемых данных.
Имеется возможность ограничивать количество внутренних потоков в MySQL

Группировка клиентских запросов. Когда к HandlerSocket приходит множество запросов, каждый рабочий поток собирает и в обработку столько запросов, сколько он может обработать за один раз. Таким образом увеличивается производительность, хотя несколько ухудшается среднее время отклика. За счет этого можно получить следующие бонусы:

– уменьшается количество вызовов fsync()
– уменьшаются задержки в репликации данных

Нет дублирования кеша

Когда вы используете memcached для кеширования записей MySQL, записи кешируются в том числе и на уровне буферов InnoDB. Такое дублирование не эффективно (память по прежнему дорогая). Когда HandlerSocket обращается к хранилищу InnoDB, запись будет закеширована только буфере, и будет доступна для использования другими SQL запросами.

Данные всегда актуальны

Так как данные хранятся только в одном месте (в InnoDB), отпадает необходимость в синхронизации данных между memcached и MySQL.

Защита от сбоев/потерь данных

Поскольку хранилищем выступает InnoDB, то данные безопасно используются в рамках транзакций и защищены от потерь во время сбоев. Даже если вы используете innodb-flush-log-at-trx-commit не равный 1, вы можете потерять данные не более чем за 1 секунду.

Может использоваться обычным MySQL клиентом

Вообще говоря, во многих случаях удобнее использовать SQL запросы (например, при построении каких-то сводных отчетов). При этом многие NoSQL решения не позволяют использовать SQL синтаксис.

HandlerSocket — просто плагин для MySQL. Вы можете использовать как обычный SQL синтаксис из обычных клиентов, так и HandlerSocket протокол для получения высокой производительности.

Остаются все плюшки MySQL

Опять таки, HandlerSocket работает изнутри MySQL, поэтому все стандартные операции остаются нам доступны — резервное копирование, репликация, мониторинг — все по прежнему доступно и работает. Активность через HandlerSocket может мониторится обычными командами MySQL, такими как SHOW GLOBAL STAUTS, SHOW ENGINE INNODB STATUS, SHOW PROCESSLIST и другими.

Нет необходимости модифицировать или пересобирать MySQL

Поскольку это обычный плагин, он будет работать на всех сборках MySQL (как Community, так и для Enterprise Servers).

Независимость от хранилища

HandlerSocket разработан так, что может общаться с любым MySQL хранилищем. Хотя надо признать, что мы проводили тесты только для версий 5.1 и 5.5 InnoDB таблиц.

Примечания и ограничения

Придется разобраться и изучить HandlerSocket API

Вам придется написать немного кода, для реализации протокола. Хотя это довольно просто. Мы предоставляем только С++ и Perl реализации.

Нет безопасности

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

Нет выигрыша при высокой нагрузки дисковой подсистемы

При нагрузке на диски (когда узким местом становятся операции работы с дисковой подсистемой) вы не сможете достигнуть заявленной производительности. В таких случаях SQL уровень не будет являться узким местом системы, поэтому никакой выгоды от использования HandlerSocket вы не получите. Мы используем HandlerSocket только на серверах, на которых практически все данные помещаются в памяти.

DeNA использует HandlerSocket на продакшене

Мы уже используем HandlerSocket на наших продакшен серверах. Результаты просто потрясающие. Мы смогли существенно сократить количество memcached и ведомых (slave) серверов баз данных. В целом снизился и сетевой трафик. Пока мы не столкнулись с какими либо проблемами в производительности. Мы очень довольны полученными результатами.

Я думаю, что возможности MySQL сильно недооценены. MySQL имеет гораздо более давнюю историю развития, по сравнению со многими другими продуктами. Мои бывшие коллеги сделали для него множество усовершенствований, часто уникальных. Зная NDBAPI, могу сказать что MySQL имеет большой потенциал и как NoSQL решение. API хранилищ и интерфейс плагинов позволили Akira и DeNA сделать возможным разработку HandlerSocket. Как бывший сотрудник MySQL и давний пользователь MySQL, я хотел бы видеть как MySQL становится лучше и набирает популярность. Не только как РСУБД, но и как еще одно NoSQL решение.

Теперь HandlerSocket плагин доступен как Open Source решение, вы можете опробовать его в своих приложениях. Мы будем признательны вам, за любые отзывы.

Перевод статьи: Long

23 Comments

  1. Проверил статистику у себя с одним клиенте. Не увидел разницы. Может быть что-то в настройках конфига.

    mysqlslap –query=”select SQL_NO_CACHE user_name from user where user_id=1″ –number-of-queries=1000000 –concurrency=10 -uroot

    vs

    while($i){
    $retval = $hs->executeSingle(0, ‘=’, array(1));
    $i–;
    }

    и там и там по ~15000 запросов

    1. Вижу, что у вас SQL_NO_CACHE, но попробуйте сделать рандомный первичный ключ, чтобы исключить кэширование и повторить тесты.

      1. Тоже самое. проверил на 3 000 000 записей. 1 000 000 поиск по ключу с одним клиентом. ~ 55-60sec. Думаю, что все-таки не так все радужно. Покажите my.cnf для вашей машины, думаю проблема у меня там. какое-то объяснение должно быть.

        1. Возможно, на своей машине я не производил тесты. Я пока только установил. Думаю след. неделю посвещу тестированию, как будут конкретные результаты – запостю 🙂

  2. В общем так, я дошел до таких результатов.

    на 5 клиентах обгон 150%.
    на 10 mysqlslap давал 25.000 в секунду, HS – аж до 370.000, но 100.000 держал стабильно.

    1. А можете переслать на почту(netandreus@gmail.com) тестовые скрипты, методику тестирования, описание тестового стенда? Хочется у себя проверить!)

  3. Потестил HS при соединении клиента с сервером по TCP сокету в гигабитной сети, … вот что получилось:
    Perl + HandlerSocket: 2220 запросов в секунду.
    Perl + native MySQL: 1163 запросов в секунду.
    PHP + native MySQL: 1325 запросов в секунду.

    В тесте производилась выборка 100.000 случайных строк по первичному ключу из таблицы с общим количеством строк 5000.

    Все же семикратного ускорения по сравнению с обычным MySQL нет… может как-нибудь особенно тюнить нужно?

    1. Двукратное ускорение тоже неплохо 🙂 Надо понять, в чём различие между вашей методикой и методикой тестирования Йошинори (автора HS).

  4. Ага, неплохо!.. Похоже сервер mysql нужно все же тюнить под моё железо (Celeron 2.4ГГц + 512RAM)… я как поставил перкону, ничего в ней не меняя, за исключением запуска HS-плагина, первым делом приступил к тестам =)…

    А трафик HS очень неплохо оптимизирует, в моем случае объем пересылаемых данных от клиента к серверу уменьшился в три с лишним раза.

    1. Потюнил, потестил, пока без особых результатов…

      Похоже, производительность HS почти напрямую зависит от количества ядер процессора на сервере БД… На одноядерном процессоре (мой случай), если отключен кеш запросов (query_cache_size = 0 в my.cnf), то HS обгоняет MySQL примерно в 2 раза, но при включении кеша у HS производительность остается неизменной, а MySQL всего процентов на 5 отстает от HS. Но, это лишь синтетический тест. В реальной ситуации, видимо разница будет несколько большей.

      Все же интересно, возможно ли пренебречь дополнительным NoSQL-кешированием, используя лишь HS интерфейс к MySQL базе данных? Какое железо для этого необходимо?

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

    Perl + Memcached ~ 8900 q/s
    Perl + HandlerSocket ~ 7850 q/s
    Perl + MySQL ~ 1550 q/s

    В данном тесте клиент коннектится к серверу по TCP/IP в гигабитной сети. На сервере Celeron 2400MHz, 512Mb RAM. На клиенте запускаются 4 потока и каждый производит выборку 100000 случайных строк по первичному ключу из таблицы размером ~5000 строк (или ~5000 ключей в случае с memcached).

    Производительность HS в данном случае значительно выросла, но до Memcached малость не дотягивает… Будет возможность, потестирую еще на многоядерном 64-битном процессоре.

        1. Тут кстати еще потестил немного, и заметил одну характерную особенность — чем больше потоков запущено на клиенте, тем более высокую производительность показывает HS. Так при 10 одновременных потоках HS обрабатывает ~10000 q/s, в то время как Memcached в таких условиях уже немного отстает (~9800 q/s).

  6. а кто-нибудь интересовался как выполнять несколько запросов за один проход, что нибудь типо “множественной вставки” например?

  7. Андрей, на сегодняшний день HandlerSoket используете в продакшене?

    1. Нет, сейчас перешли на MongoDB. Ещё не полностью, но потихоньку слезаем с SQL-иглы 🙂

Leave a Reply to eastman Cancel reply