読者です 読者をやめる 読者になる 読者になる

handlersocketの通信量を調べてみる

MySQLプラグインのhandlersocketは高速なR/Wで有名です。
その性能に寄与しているもののひとつが、通信パケットの少なさです。
気になったので、簡単調べてみます。

検証方法

handlersocketとSQLで同等の処理を行う参照系クエリを発行し、tcpdumpでパケット量の 違いを比較する。
テーブルには以下のデータが格納されており、これを取得プログラムを実行する。

+----+---------+
| id | name    |
+----+---------+
|  1 | Tsumugi |
|  2 | Azusa   |
|  3 | Ritsu   |
|  4 | Yui     |
|  5 | Mio     |
+----+---------+

検証プログラム(handlersocket)

#! /usr/bin/perl

use warnings;
use strict;
use Net::HandlerSocket;
use Data::Dumper;

my $args = {
    host => 'server',
    port => 9998
};
my $hs = new Net::HandlerSocket($args);

my $index = 0;
my $dbname = 'hoge';
my $tblname = 'fuga';
my $res = $hs->open_index(0, $dbname, $tblname, 'PRIMARY', 'id,name');
if ($res) {
    die $hs->get_error();
}

$res = $hs->execute_single($index, '<=', [5], 5, 0);
if ($res->[0]) {
    die $hs->get_error();
}

shift(@$res);
print Dumper $res;

検証プログラム(SQL)

#! /usr/bin/perl

use warnings;
use strict;
use DBI;
use Data::Dumper;

my $dbname = 'hoge';
my $hostname = 'server';
my $port = '5520';
my $dsn = "DBI:mysql:$dbname:$hostname:$port";
my $dbh = DBI->connect($dsn, 'msandbox', 'msandbox', {RaiseError => 1});

my $sql = "SELECT
                id,
                name
           FROM
                fuga
           WHERE
                id <= 5";
my $sth = $dbh->prepare($sql);
$sth->execute;
while (my $ref = $sth->fetchrow_arrayref) {
    print Dumper $ref;
}

$sth->finish;
$dbh->disconnect;

実行結果(handlersocket)

クライアント→サーバ

21:53:35.117778 IP client.hoge.net.36311 > server.hoge.net.9998: Flags [S], seq 2219888833, win 65535, options [mss 1460,nop,wscale 3,sackOK,TS val 24880405 ecr 0], length 0
        0x0000:  4500 003c 1d28 4000 4006 9517 c0a8 0364  E..<.(@.@......d
        0x0010:  c0a8 03c8 8dd7 270e 8450 d0c1 0000 0000  ......'..P......
        0x0020:  a002 ffff 0eff 0000 0204 05b4 0103 0303  ................
        0x0030:  0402 080a 017b a515 0000 0000            .....{......
21:53:35.119972 IP client.hoge.net.36311 > server.hoge.net.9998: Flags [.], ack 2406877479, win 8326, options [nop,nop,TS val 24880407 ecr 1697790], length 0
        0x0000:  4500 0034 1d29 4000 4006 951e c0a8 0364  E..4.)@.@......d
        0x0010:  c0a8 03c8 8dd7 270e 8450 d0c2 8f76 0927  ......'..P...v.'
        0x0020:  8010 2086 9c79 0000 0101 080a 017b a517  .....y.......{..
        0x0030:  0019 e7fe                                ....
21:53:35.120668 IP client.hoge.net.36311 > server.hoge.net.9998: Flags [P.], seq 0:30, ack 1, win 8326, options [nop,nop,TS val 24880407 ecr 1697790], length 30
        0x0000:  4500 0052 1d2a 4000 4006 94ff c0a8 0364  E..R.*@.@......d
        0x0010:  c0a8 03c8 8dd7 270e 8450 d0c2 8f76 0927  ......'..P...v.'
        0x0020:  8018 2086 dc4f 0000 0101 080a 017b a517  .....O.......{..
        0x0030:  0019 e7fe 5009 3009 686f 6765 0966 7567  ....P.0.hoge.fug
        0x0040:  6109 5052 494d 4152 5909 6964 2c6e 616d  a.PRIMARY.id,nam
        0x0050:  650a                                     e.
21:53:35.123423 IP client.hoge.net.36311 > server.hoge.net.9998: Flags [P.], seq 30:41, ack 5, win 8326, options [nop,nop,TS val 24880411 ecr 1697791], length 11
        0x0000:  4500 003f 1d2b 4000 4006 9511 c0a8 0364  E..?.+@.@......d
        0x0010:  c0a8 03c8 8dd7 270e 8450 d0e0 8f76 092b  ......'..P...v.+
        0x0020:  8018 2086 0a5e 0000 0101 080a 017b a51b  .....^.......{..
        0x0030:  0019 e7ff 3009 3c3d 0931 0935 0935 0a    ....0.<=.1.5.5.
21:53:35.134419 IP client.hoge.net.36311 > server.hoge.net.9998: Flags [F.], seq 41, ack 47, win 8326, options [nop,nop,TS val 24880421 ecr 1697791], length 0
        0x0000:  4500 0034 1d38 4000 4006 950f c0a8 0364  E..4.8@.@......d
        0x0010:  c0a8 03c8 8dd7 270e 8450 d0eb 8f76 0955  ......'..P...v.U
        0x0020:  8011 2086 9c12 0000 0101 080a 017b a525  .............{.%
        0x0030:  0019 e7ff                                ....
21:53:35.136490 IP client.hoge.net.36311 > server.hoge.net.9998: Flags [.], ack 48, win 8325, options [nop,nop,TS val 24880423 ecr 1697794], length 0
        0x0000:  4500 0034 1d39 4000 4006 950e c0a8 0364  E..4.9@.@......d
        0x0010:  c0a8 03c8 8dd7 270e 8450 d0ec 8f76 0956  ......'..P...v.V
        0x0020:  8010 2085 9c0d 0000 0101 080a 017b a527  .............{.'
        0x0030:  0019 e802                                ....

サーバ→クライアント

21:53:36.050254 IP server.hoge.net.9998 > client.hoge.net.36311: Flags [S.], seq 2406877478, ack 2219888834, win 5792, options [mss 1460,sackOK,TS val 1697790 ecr 24880405,nop,wscale 5], length 0
        0x0000:  4500 003c 0000 4000 4006 b23f c0a8 03c8  E..<..@.@..?....
        0x0010:  c0a8 0364 270e 8dd7 8f76 0926 8450 d0c2  ...d'....v.&.P..
        0x0020:  a012 16a0 7797 0000 0204 05b4 0402 080a  ....w...........
        0x0030:  0019 e7fe 017b a515 0103 0305            .....{......
21:53:36.053150 IP server.hoge.net.9998 > client.hoge.net.36311: Flags [.], ack 31, win 181, options [nop,nop,TS val 1697791 ecr 24880407], length 0
        0x0000:  4500 0034 eed9 4000 4006 c36d c0a8 03c8  E..4..@.@..m....
        0x0010:  c0a8 0364 270e 8dd7 8f76 0927 8450 d0e0  ...d'....v.'.P..
        0x0020:  8010 00b5 bc2b 0000 0101 080a 0019 e7ff  .....+..........
        0x0030:  017b a517                                .{..
21:53:36.053780 IP server.hoge.net.9998 > client.hoge.net.36311: Flags [P.], ack 31, win 181, options [nop,nop,TS val 1697791 ecr 24880407], length 4
        0x0000:  4500 0038 eeda 4000 4006 c368 c0a8 03c8  E..8..@.@..h....
        0x0010:  c0a8 0364 270e 8dd7 8f76 0927 8450 d0e0  ...d'....v.'.P..
        0x0020:  8018 00b5 5b0c 0000 0101 080a 0019 e7ff  ....[...........
        0x0030:  017b a517 3009 310a                      .{..0.1.
21:53:36.055898 IP server.hoge.net.9998 > client.hoge.net.36311: Flags [P.], ack 42, win 181, options [nop,nop,TS val 1697791 ecr 24880411], length 42
        0x0000:  4500 005e eedb 4000 4006 c341 c0a8 03c8  E..^..@.@..A....
        0x0010:  c0a8 0364 270e 8dd7 8f76 092b 8450 d0eb  ...d'....v.+.P..
        0x0020:  8018 00b5 df84 0000 0101 080a 0019 e7ff  ................
        0x0030:  017b a51b 3009 3209 3509 4d69 6f09 3409  .{..0.2.5.Mio.4.
        0x0040:  5975 6909 3309 5269 7473 7509 3209 417a  Yui.3.Ritsu.2.Az
        0x0050:  7573                                     us
21:53:36.066865 IP server.hoge.net.9998 > client.hoge.net.36311: Flags [F.], seq 47, ack 43, win 181, options [nop,nop,TS val 1697794 ecr 24880421], length 0
        0x0000:  4500 0034 eedc 4000 4006 c36a c0a8 03c8  E..4..@.@..j....
        0x0010:  c0a8 0364 270e 8dd7 8f76 0955 8450 d0ec  ...d'....v.U.P..
        0x0020:  8011 00b5 bbdf 0000 0101 080a 0019 e802  ................
        0x0030:  017b a525                                .{.%

実行結果(SQL)

クライアント→サーバ

21:58:05.189818 IP client.hoge.net.58437 > server.hoge.net.5520: Flags [S], seq 3653256188, win 65535, options [mss 1460,nop,wscale 3,sackOK,TS val 25150703 ecr 0], length 0
        0x0000:  4500 003c 1dc6 4000 4006 9479 c0a8 0364  E..<..@.@..y...d
        0x0010:  c0a8 03c8 e445 1590 d9c0 43fc 0000 0000  .....E....C.....
        0x0020:  a002 ffff e185 0000 0204 05b4 0103 0303  ................
        0x0030:  0402 080a 017f c4ef 0000 0000            ............
21:58:05.191654 IP client.hoge.net.58437 > server.hoge.net.5520: Flags [.], ack 2729139775, win 8326, options [nop,nop,TS val 25150754 ecr 1765308], length 0
        0x0000:  4500 0034 1dc7 4000 4006 9480 c0a8 0364  E..4..@.@......d
        0x0010:  c0a8 03c8 e445 1590 d9c0 43fd a2ab 5e3f  .....E....C...^?
        0x0020:  8010 2086 fec2 0000 0101 080a 017f c522  ..............."
        0x0030:  001a efbc                                ....
21:58:05.203652 IP client.hoge.net.58437 > server.hoge.net.5520: Flags [P.], seq 0:91, ack 79, win 8326, options [nop,nop,TS val 25150765 ecr 1765308], length 91
        0x0000:  4508 008f 1dc8 4000 4006 941c c0a8 0364  E.....@.@......d
        0x0010:  c0a8 03c8 e445 1590 d9c0 43fd a2ab 5e8d  .....E....C...^.
        0x0020:  8018 2086 8cae 0000 0101 080a 017f c52d  ...............-
        0x0030:  001a efbc 5700 0001 0fa2 0e00 0000 0040  ....W..........@
        0x0040:  0800 0000 0000 0000 0000 0000 0000 0000  ................
        0x0050:  0000 0000 0000 0000 616b 696f 6d69 0014  ........akiomi..
        0x0060:  a608 5220 e839 4405 976a 50b8 d237 6c8e  ..R..9D..jP..7l.
        0x0070:  ac53 abf4 686f 6765 006d 7973 716c 5f6e  .S..hoge.mysql_n
        0x0080:  6174 6976 655f 7061 7373 776f 7264 00    ative_password.
21:58:05.207210 IP client.hoge.net.58437 > server.hoge.net.5520: Flags [P.], seq 91:112, ack 90, win 8326, options [nop,nop,TS val 25150769 ecr 1765311], length 21
        0x0000:  4508 0049 1dc9 4000 4006 9461 c0a8 0364  E..I..@.@..a...d
        0x0010:  c0a8 03c8 e445 1590 d9c0 4458 a2ab 5e98  .....E....DX..^.
        0x0020:  8018 2086 fcaa 0000 0101 080a 017f c531  ...............1
        0x0030:  001a efbf 1100 0000 0373 6574 2061 7574  .........set.aut
        0x0040:  6f63 6f6d 6d69 743d 31                   ocommit=1
21:58:05.209564 IP client.hoge.net.58437 > server.hoge.net.5520: Flags [P.], seq 112:244, ack 101, win 8326, options [nop,nop,TS val 25150772 ecr 1765312], length 132
        0x0000:  4508 00b8 1dca 4000 4006 93f1 c0a8 0364  E.....@.@......d
        0x0010:  c0a8 03c8 e445 1590 d9c0 446d a2ab 5ea3  .....E....Dm..^.
        0x0020:  8018 2086 bd50 0000 0101 080a 017f c534  .....P.........4
        0x0030:  001a efc0 8000 0000 0353 454c 4543 540a  .........SELECT.
        0x0040:  2020 2020 2020 2020 2020 2020 2020 2020  ................
        0x0050:  6964 2c0a 2020 2020 2020 2020 2020 2020  id,.............
        0x0060:  2020 2020 6e61 6d65 0a20 2020 2020 2020  ....name........
        0x0070:  2020 2020 2046 524f 4d0a 2020 2020 2020  .....FROM.......
        0x0080:  2020 2020 2020 2020 2020 6675 6761 0a20  ..........fuga..
        0x0090:  2020 2020 2020 2020 2020 2057 4845 5245  ...........WHERE
        0x00a0:  0a20 2020 2020 2020 2020 2020 2020 2020  ................
        0x00b0:  2069 6420 3c3d 2035                      .id.<=.5
21:58:05.229111 IP client.hoge.net.58437 > server.hoge.net.5520: Flags [P.], seq 244:249, ack 270, win 8326, options [nop,nop,TS val 25150791 ecr 1765313], length 5
        0x0000:  4508 0039 1ddf 4000 4006 945b c0a8 0364  E..9..@.@..[...d
        0x0010:  c0a8 03c8 e445 1590 d9c0 44f1 a2ab 5f4c  .....E....D..._L
        0x0020:  8018 2086 fa8a 0000 0101 080a 017f c547  ...............G
        0x0030:  001a efc1 0100 0000 01                   .........
21:58:05.229143 IP client.hoge.net.58437 > server.hoge.net.5520: Flags [F.], seq 249, ack 270, win 8326, options [nop,nop,TS val 25150791 ecr 1765313], length 0
        0x0000:  4508 0034 1de0 4000 4006 945f c0a8 0364  E..4..@.@.._...d
        0x0010:  c0a8 03c8 e445 1590 d9c0 44f6 a2ab 5f4c  .....E....D..._L
        0x0020:  8011 2086 fc91 0000 0101 080a 017f c547  ...............G
        0x0030:  001a efc1                                ....
21:58:05.236114 IP client.hoge.net.58437 > server.hoge.net.5520: Flags [.], ack 271, win 8325, options [nop,nop,TS val 25150797 ecr 1765318], length 0
        0x0000:  4508 0034 1de1 4000 4006 945e c0a8 0364  E..4..@.@..^...d
        0x0010:  c0a8 03c8 e445 1590 d9c0 44f7 a2ab 5f4d  .....E....D..._M
        0x0020:  8010 2085 fc86 0000 0101 080a 017f c54d  ...............M
        0x0030:  001a efc6                                ....

サーバ→クライアント

21:58:06.356172 IP server.hoge.net.5520 > client.hoge.net.58437: Flags [S.], seq 2729139774, ack 3653256189, win 5792, options [mss 1460,sackOK,TS val 1765308 ecr 25150703,nop,wscale 5], length 0
        0x0000:  4500 003c 0000 4000 4006 b23f c0a8 03c8  E..<..@.@..?....
        0x0010:  c0a8 0364 1590 e445 a2ab 5e3e d9c0 43fd  ...d...E..^>..C.
        0x0020:  a012 16a0 da11 0000 0204 05b4 0402 080a  ................
        0x0030:  001a efbc 017f c4ef 0103 0305            ............
21:58:06.358272 IP server.hoge.net.5520 > client.hoge.net.58437: Flags [P.], ack 1, win 181, options [nop,nop,TS val 1765308 ecr 25150754], length 78
        0x0000:  4508 0082 b183 4000 4006 006e c0a8 03c8  E.....@.@..n....
        0x0010:  c0a8 0364 1590 e445 a2ab 5e3f d9c0 43fd  ...d...E..^?..C.
        0x0020:  8018 00b5 501a 0000 0101 080a 001a efbc  ....P...........
        0x0030:  017f c522 4a00 0000 0a35 2e35 2e32 3000  ..."J....5.5.20.
        0x0040:  2000 0000 3423 514d 6f68 3e39 00ff f708  ....4#QMoh>9....
        0x0050:  0200                                     ..
21:58:06.370006 IP server.hoge.net.5520 > client.hoge.net.58437: Flags [.], ack 92, win 181, options [nop,nop,TS val 1765311 ecr 25150765], length 0
        0x0000:  4508 0034 b184 4000 4006 00bb c0a8 03c8  E..4..@.@.......
        0x0010:  c0a8 0364 1590 e445 a2ab 5e8d d9c0 4458  ...d...E..^...DX
        0x0020:  8010 00b5 1ddd 0000 0101 080a 001a efbf  ................
        0x0030:  017f c52d                                ...-
21:58:06.370569 IP server.hoge.net.5520 > client.hoge.net.58437: Flags [P.], ack 92, win 181, options [nop,nop,TS val 1765311 ecr 25150765], length 11
        0x0000:  4508 003f b185 4000 4006 00af c0a8 03c8  E..?..@.@.......
        0x0010:  c0a8 0364 1590 e445 a2ab 5e8d d9c0 4458  ...d...E..^...DX
        0x0020:  8018 00b5 16c6 0000 0101 080a 001a efbf  ................
        0x0030:  017f c52d 0700 0002 0000 0002 0000 00    ...-...........
21:58:06.373013 IP server.hoge.net.5520 > client.hoge.net.58437: Flags [P.], ack 113, win 181, options [nop,nop,TS val 1765312 ecr 25150769], length 11
        0x0000:  4508 003f b186 4000 4006 00ae c0a8 03c8  E..?..@.@.......
        0x0010:  c0a8 0364 1590 e445 a2ab 5e98 d9c0 446d  ...d...E..^...Dm
        0x0020:  8018 00b5 16a2 0000 0101 080a 001a efc0  ................
        0x0030:  017f c531 0700 0001 0000 0002 0000 00    ...1...........
21:58:06.376746 IP server.hoge.net.5520 > client.hoge.net.58437: Flags [P.], ack 245, win 215, options [nop,nop,TS val 1765313 ecr 25150772], length 169
        0x0000:  4508 00dd b187 4000 4006 000f c0a8 03c8  E.....@.@.......
        0x0010:  c0a8 0364 1590 e445 a2ab 5ea3 d9c0 44f1  ...d...E..^...D.
        0x0020:  8018 00d7 b974 0000 0101 080a 001a efc1  .....t..........
        0x0030:  017f c534 0100 0001 0226 0000 0203 6465  ...4.....&....de
        0x0040:  6604 686f 6765 0466 7567 6104 6675 6761  f.hoge.fuga.fuga
        0x0050:  0269                                     .i
21:58:06.399408 IP server.hoge.net.5520 > client.hoge.net.58437: Flags [F.], seq 270, ack 251, win 215, options [nop,nop,TS val 1765318 ecr 25150791], length 0
        0x0000:  4508 0034 b188 4000 4006 00b7 c0a8 03c8  E..4..@.@.......
        0x0010:  c0a8 0364 1590 e445 a2ab 5f4c d9c0 44f7  ...d...E.._L..D.
        0x0020:  8011 00d7 1c3b 0000 0101 080a 001a efc6  .....;..........
        0x0030:  017f c547                                ...G

結果

やっぱり、handlersocketはかなり通信料が少ないですね。
テキストデータがそのまま通信されるのは気になるところ。