MySQL Handlersocket

Thank you for reading this post, don't forget to subscribe!

Когда приложение отправляет запрос на MySQL сервер, происходит две основные операции:

Разбор, анализ и подготовка плана выполнения для SQL запроса.
Выполнение нужных операций в движке таблицы и возврат результата.

Движок InnoDB позволяет включить специальный интерфейс для работы без использования SQL прослойки. Этот интерфейс называется HandlerSocket. Он предоставляет протокол работы с данными по принципу NoSQL.

Использование этого протокола может значительно ускорить простые запросы типа:

SELECT * FROM users WHERE id = 7

1	SELECT * FROM users WHERE id = 7

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

Настройка

Для установки необходимо поставить сборку Percona MySQL:

apt-get install percona-server-server-5.5

1	apt-get install percona-server-server-5.5

После этого, указать настройки портов протокола для чтения и записи данных в файле my.cnf:

loose_handlersocket_port = <b>9998</b>
loose_handlersocket_port_wr = <b>9999</b>
loose_handlersocket_threads = 16
loose_handlersocket_threads_wr = 1
open_files_limit = 65535

loose_handlersocket_port = 9998

loose_handlersocket_port_wr = 9999

loose_handlersocket_threads = 16

loose_handlersocket_threads_wr = 1

open_files_limit = 65535

После перезагрузки сервера, нужно установить плагин:

mysql&gt; install plugin handlersocket soname 'handlersocket.so';

1	mysql> install plugin handlersocket soname 'handlersocket.so';

Готово. Проверить установку можно с помощью команды:

mysql&gt; show processlist;
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+-----------+---------------+-----------+
| Id | User        | Host            | db            | Command | Time | State                                     | Info             | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+-----------+---------------+-----------+
|  1 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         0 |
|  2 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         0 |
|  3 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         0 |
|  <b>4 | system user | connecting host | handlersocket | Connect | NULL | handlersocket: mode=wr, 0 conns, 0 active | NULL</b>             |         0 |             0 |         0 |
|  5 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         0 |
|  6 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         0 |
|  7 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         0 |
|  8 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |         0 |             0 |         0 |
...

mysql> show processlist;

+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+-----------+---------------+-----------+

+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+-----------+---------------+-----------+

...

# Если плагин включен, он будет виден в процессах

PHP и HandlerSocket

Для работы из PHP существует библиотека php-handlersocket. Она устанавливается, как расширение:

wget https://php-handlersocket.googlecode.com/files/php-handlersocket-0.3.1.tar.gz
tar -xvf php-handlersocket-0.3.1.tar.gz
cd php-handlersocket-0.3.1
phpize
./configure
make
make install

wget https://php-handlersocket.googlecode.com/files/php-handlersocket-0.3.1.tar.gz

tar -xvf php-handlersocket-0.3.1.tar.gz

cd php-handlersocket-0.3.1

phpize

./configure

make

make install

После этого необходимо подключить расширение в php.ini:

extension=handlersocket.so

1	extension=handlersocket.so

Проверить установку можно так:

root@sh:~# php -i | grep handlersocket
<b>handlersocket</b>

1 2	root@sh:~# php -i \| grep handlersocket <b>handlersocket</b>

# Если расширение установлено, оно будет найдено в информации о php

Работа из приложения

HandlerSocket предоставляет работу с данными в движке InnoDB с помощью инициализации индекса и колонок, с которыми будут происходить манипуляции. Перед тем, как начать работу, необходимо подготовить таблицу:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `text` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

CREATE TABLE `test` (

`id` int(11) NOT NULL,

`text` varchar(32) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB

# Создаем таблицу test в базе данных test

Запись данных

Запишем в эту таблицу данные:

&lt;?
<span class="comment">
# указываем хост и порт для записи
</span>$hs = new HandlerSocket('localhost', 9999);

<span class="comment">
# Открываем индекс и инициализируем работу с двумя колонками: id и text
</span>$hs-&gt;openIndex(1, 'test', 'test', '', 'id,text');

<span class="comment">
# Вставляем данные
</span>$hs-&gt;executeInsert(1, array('1', 'absde'));

# указываем хост и порт для записи

$hs = new HandlerSocket('localhost', 9999);

# Открываем индекс и инициализируем работу с двумя колонками: id и text

$hs->openIndex(1, 'test', 'test', '', 'id,text');

# Вставляем данные

$hs->executeInsert(1, array('1', 'absde'));

Обратите внимание, что для записи используется порт, установленный в параметре loose_handlersocket_port_wr. Установленное значение можно проверить обычным SQL-запросом:

mysql&gt; select * from test;
+----+-------+
| id | text  |
+----+-------+
|  1 | absde |
+----+-------+

mysql> select * from test;

+----+-------+

| id | text |

+----+-------+

| 1 | absde |

+----+-------+

Чтение данных

Прочитать данные из таблицы можно таким образом:

&lt;?
<span class="comment">
# подключаемся к порту для чтения данных
</span>$hs = new HandlerSocket('localhost', 9998);

<span class="comment">
# открываем первичный ключ таблицы
</span>$hs-&gt;openIndex(1, 'test', 'test', HandlerSocket::PRIMARY, 'id,text');

<span class="comment">
# выполняем операцию получения данных первичного ключа со значением 1
</span>$data = $hs-&gt;executeSingle(1, '=', array('1'), 1, 0);
print_r($data);

# подключаемся к порту для чтения данных

$hs = new HandlerSocket('localhost', 9998);

# открываем первичный ключ таблицы

$hs->openIndex(1, 'test', 'test', HandlerSocket::PRIMARY, 'id,text');

# выполняем операцию получения данных первичного ключа со значением 1

$data = $hs->executeSingle(1, '=', array('1'), 1, 0);

print_r($data);

Производительность

Сравним два запроса на получение данных из таблицы — один через классический SQL, второй — через HandlerSocket:

&lt;?

<span class="comment">
# Отправляем 200 тыс. запросов на чтение данных через HandlerSocket
</span>$hs = new HandlerSocket('localhost', 9998);
$hs-&gt;openIndex(2, 'test', 'test', HandlerSocket::PRIMARY, 'id,text');

$t = microtime(1);
for ( $i = 0; $i &lt; 200000; $i++ )
{
        $retval = $hs-&gt;executeSingle(1, '=', array( mt_rand(1, 1000) ), 1, 0);
}
echo 'Spent ' . (microtime(1) - $t) . 's' . "\n";

<span class="comment">
# Отправляем 200 тыс. таких же запросов, только через SQL-интерфейс
</span>mysql_connect('localhost', 'root', '');

$t = microtime(1);
for ( $i = 0; $i &lt; 200000; $i++ )
{
        $q = mysql_query('SELECT text FROM test.test WHERE id = ' . mt_rand(1, 1000));
        $r = mysql_fetch_assoc($q);

}
echo 'Spent ' . (microtime(1) - $t) . 's' . "\n";
mysql_close();

# Отправляем 200 тыс. запросов на чтение данных через HandlerSocket

$hs = new HandlerSocket('localhost', 9998);

$hs->openIndex(2, 'test', 'test', HandlerSocket::PRIMARY, 'id,text');

$t = microtime(1);

for ( $i = 0; $i < 200000; $i++ )

{

$retval = $hs->executeSingle(1, '=', array( mt_rand(1, 1000) ), 1, 0);

}

echo 'Spent ' . (microtime(1) - $t) . 's' . "\n";

# Отправляем 200 тыс. таких же запросов, только через SQL-интерфейс

mysql_connect('localhost', 'root', '');

$t = microtime(1);

for ( $i = 0; $i < 200000; $i++ )

{

$q = mysql_query('SELECT text FROM test.test WHERE id = ' . mt_rand(1, 1000));

$r = mysql_fetch_assoc($q);

}

echo 'Spent ' . (microtime(1) - $t) . 's' . "\n";

mysql_close();

Результаты на небольшом облачном сервере:

Spent <b>6.9966340065002s</b>
Spent 19.924588918686s

1 2	Spent <b>6.9966340065002s</b> Spent 19.924588918686s

# Handlersoket быстрее

SQL оказался в 2.5 раза медленнее. Важно понимать, что такое преимущество будет получено, только если большинство данных помещается в память (читайте об эффективной настройке MySQL). Если существует большое количество операций с диском, выигрыш в скорости будет незаметным.

Применение на практике

Замена кэширования

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

$q = mysql_query('SELET name, age FROM users WHERE id = 7');
$data = mysql_fetch_assoc($q);
memcache_set('user7', $data);

$q = mysql_query('SELET name, age FROM users WHERE id = 7');

$data = mysql_fetch_assoc($q);

memcache_set('user7', $data);

# Классическое кэширования записей по первичному ключу

Имеет смысл использовать HandlerSocket вместо SQL для получения таких данных. Тогда кэшировать их не понадобится:

$hs = new HandlerSocket('localhost', 9998);
$hs-&gt;openIndex(1, 'db', 'users', HandlerSocket::PRIMARY, 'id,name,age');
$data = $hs-&gt;executeSingle(1, '=', array('7'), 1, 0);

$hs = new HandlerSocket('localhost', 9998);

$hs->openIndex(1, 'db', 'users', HandlerSocket::PRIMARY, 'id,name,age');

$data = $hs->executeSingle(1, '=', array('7'), 1, 0);

# Получаем данные пользователя без SQL

Во втором случае мы:

уменьшим расход памяти, т.к. не будет необходимости сохранять данные в кэш
упростим код, т.к. данные будут храниться в едином месте, и не будет необходимости синхронизировать данные в кэше и базе
уменьшим риск резкого роста нагрузки при сбое кэширующих серверов

Флаги

Другой вариант применения — это работа с key-value данными. Например, различные флаги для пометки состояния пользователя:

&lt;?
$hs = new HandlerSocket('localhost', 9999);
$hs-&gt;openIndex(1, 'test', 'flags', '', 'id,name,value');
$hs-&gt;executeInsert(1, array('1', 'is_moderator', 1));

$hs = new HandlerSocket('localhost', 9999);

$hs->openIndex(1, 'test', 'flags', '', 'id,name,value');

$hs->executeInsert(1, array('1', 'is_moderator', 1));

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

Счетчики

Есть поддержка атомарных операций, в том числе инкремент/декремент:

$hs = new HandlerSocket('localhost', 9999);
$hs-&gt;openIndex(1, 'test', 'counters', '', '<b>value</b>');
$hs-&gt;executeSingle(1, '=', array('1'), 1, 0, '+', array('<b>1</b>'));

$hs = new HandlerSocket('localhost', 9999);

$hs->openIndex(1, 'test', 'counters', '', 'value');

$hs->executeSingle(1, '=', array('1'), 1, 0, '+', array('1'));

# Увеличиваем колонку value в таблице counters на 1

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

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

Пн	Вт	Ср	Чт	Пт	Сб	Вс
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

Centos

MySQL Handlersocket

Настройка

PHP и HandlerSocket

Работа из приложения

Запись данных

Чтение данных

Производительность

Применение на практике

Замена кэширования

Флаги

Счетчики

https://github.com/midnight47/