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はかなり通信料が少ないですね。
テキストデータがそのまま通信されるのは気になるところ。