Использование MySQL как NoSQL 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, одном из крупнейшем провайдере социальных игр в Японии.

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

[matsunobu@host ~]$ mysqlslap --query="select user_name,.. 
from test.user where user_id=1" \ 
--number-of-queries=10000000 --concurrency=30 --host=xxx –uroot

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

[matsunobu@host ~]$ mysqladmin extended-status -i 1 -r -uroot \
| grep -e "Com_select"
...
| Com_select | 107069 |
| Com_select | 108873 |
| Com_select | 108921 |
| Com_select | 109511 |
| Com_select | 108084 |
| Com_select | 108483 |
| Com_select | 108115 |
...

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

[matsunobu@host ~]$ vmstat 1
 r b swpd free buff cache in cs us sy id wa st
23 0 0 963004 224216 29937708 58242 163470 59 28 12 0 0
24 0 0 963312 224216 29937708 57725 164855 59 28 13 0 0
19 0 0 963232 224216 29937708 58127 164196 60 28 12 0 0
16 0 0 963260 224216 29937708 58021 165275 60 28 12 0 0
20 0 0 963308 224216 29937708 57865 165041 60 28 12 0 0

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

samples % app name symbol name
259130 4.5199 mysqld MYSQLparse(void*)
196841 3.4334 mysqld my_pthread_fastmutex_lock
106439 1.8566 libc-2.5.so _int_malloc
94583 1.6498 bnx2 /bnx2
84550 1.4748 ha_innodb_plugin.so.0.0.0 ut_delay
67945 1.1851 mysqld _ZL20make_join_statistics
P4JOINP10TABLE_LISTP4ItemP16st_dynamic_array
63435 1.1065 mysqld JOIN::optimize()
55825 0.9737 vmlinux wakeup_stack_begin
55054 0.9603 mysqld MYSQLlex(void*, void*)
50833 0.8867 libpthread-2.5.so pthread_mutex_trylock
49602 0.8652 ha_innodb_plugin.so.0.0.0 row_search_for_mysql
47518 0.8288 libc-2.5.so memcpy
46957 0.8190 vmlinux .text.elf_core_dump
46499 0.8111 libc-2.5.so malloc

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":

CREATE TABLE user (
 user_id INT UNSIGNED PRIMARY KEY,
 user_name VARCHAR(50),
 user_email VARCHAR(255),
 created DATETIME 
) ENGINE=InnoDB;

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

mysql> SELECT user_name, user_email, created FROM user WHERE user_id=101;
+---------------+-----------------------+---------------------+
| user_name | user_email | created |
+---------------+-----------------------+---------------------+
| Yukari Takeba | yukari.takeba@dena.jp | 2010-02-03 11:22:33 |
+---------------+-----------------------+---------------------+
1 row in set (0.00 sec)

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

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

Загружаем исходный код HandlerSocket

Собираем HandlerSocket (клиентскую и серверную часть) обычным образом:
```
./configure --with-mysql-source=... --with-mysql-bindir=... ; make; make install 
```

Внедряем HandlerSocket в MySQL

mysql> INSTALL PLUGIN 'HandlerSocket' soname 'HandlerSocket.so';

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

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

#!/usr/bin/perl

use strict;
use warnings;
use Net::HandlerSocket;

#1. Устанавливаем соединение
my $args = { host => 'ip_to_remote_host', port => 9998 };
my $hs = new Net::HandlerSocket($args);

#2. Инициализируем индекс, который мы будем использовать в нашей логике
 # Таблица MySQL будет открыта в этом месте (если конечно она не была открыта раньше)
my $res = $hs->open_index(0, 'test', 'user', 'PRIMARY',
 'user_name,user_email,created');
die $hs->get_error() if $res != 0;

#3. Собственно основная работа 
#Поиск строк по идентификатору - execute_single (index id, cond, cond value, max rows, offset)
$res = $hs->execute_single(0, '=', [ '101' ], 1, 0);
die $hs->get_error() if $res->[0] != 0;
shift(@$res);
for (my $row = 0; $row < 1; ++$row) {
 my $user_name= $res->[$row + 0];
 my $user_email= $res->[$row + 1];
 my $created= $res->[$row + 2];
 print "$user_name\t$user_email\t$created\n";
}

#4. закрытие соединения
$hs->close();

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

[matsunobu@host ~]$ perl sample.pl
Yukari Takeba yukari.takeba@dena.jp 2010-02-03 11:22:33

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

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

[matsunobu@host ~]$ telnet 192.168.1.2 9998

Trying 192.168.1.2...
Connected to xxx.dena.jp (192.168.1.2).
Escape character is '^]'.
P 0 test user PRIMARY user_name,user_email,created
0 1
0 = 1 101
0 3 Yukari Takeba yukari.takeba@dena.jp 2010-02-03 11:22:33

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

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

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

 Запросов в сек использование CPU
Обычный SQL запрос 105,000 %us 60% %sy 28%
memcached 420,000 %us 8% %sy 88%
HandlerSocket 750,000 %us 45% %sy 53%

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

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

samples % app name symbol name
984785 5.9118 bnx2 /bnx2
847486 5.0876 ha_innodb_plugin.so.0.0.0 ut_delay
545303 3.2735 ha_innodb_plugin.so.0.0.0 btr_search_guess_on_hash
317570 1.9064 ha_innodb_plugin.so.0.0.0 row_search_for_mysql
298271 1.7906 vmlinux tcp_ack
291739 1.7513 libc-2.5.so vfprintf
264704 1.5891 vmlinux .text.super_90_sync
248546 1.4921 vmlinux blk_recount_segments
244474 1.4676 libc-2.5.so _int_malloc
226738 1.3611 ha_innodb_plugin.so.0.0.0 _ZL14build_template
P19row_prebuilt_structP3THDP8st_tablej
206057 1.2370 HandlerSocket.so dena::hstcpsvr_worker::run_one_ep()
183330 1.1006 ha_innodb_plugin.so.0.0.0 mutex_spin_wait
175738 1.0550 HandlerSocket.so dena::dbcontext::
cmd_find_internal(dena::dbcallback_i&, dena::prep_stmt const&, 
ha_rkey_function, dena::cmd_exec_args const&)
169967 1.0203 ha_innodb_plugin.so.0.0.0 buf_page_get_known_nowait
165337 0.9925 libc-2.5.so memcpy
149611 0.8981 ha_innodb_plugin.so.0.0.0 row_sel_store_mysql_rec
148967 0.8943 vmlinux generic_make_request

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

$ mysqladmin extended-status -uroot -i 1 -r | grep "InnoDB_rows_read"
...
| Innodb_rows_read | 750192 |
| Innodb_rows_read | 751510 |
| Innodb_rows_read | 757558 |
| Innodb_rows_read | 747060 |
| Innodb_rows_read | 748474 |
| Innodb_rows_read | 759344 |
| Innodb_rows_read | 753081 |
| Innodb_rows_read | 754375 |
...

Ну и напоследок несколько слов о сервере, на котором были получены эти результаты:
В тестах memcached и HandlerSocket сетевая подсистема стала узким местом. Когда я проводил тесты с единственной гигабитной картой, результаты несколько отличались, и я получил около 260 тысяч запросов в секунду для HandlerSocket и 220 тысяч для memcached.
Очень высокая производительность

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

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