HandlerSocket plugin for MySQL

handlersocket plugin for mysql 2010/06/29 Tech セミナー @ 代々木株式会社 DeNA システム統括本部 IT 基盤部樋口　証 <higuchi dot akira at dena dot jp>

Who am I? ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

handlersocket plugin について

handlersocket plugin とは ,[object Object]

ねらい ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

handlersocket plugin の概要 ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

構成 mysqld client app Handler Interface Innodb MyISAM Other storage engines … SQL Layer Handlersocket Plugin Listener for libmysql libmysql libhsclient Applications

参考 : 非 SQL for MySQL ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

性能比較 ( 参照クエリ ) ,[object Object],[object Object]

handlersocket の機能 ( 参照系 ) ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

handlersocket の機能 ( 更新系 ) ,[object Object],[object Object],[object Object],[object Object],[object Object]

実行例 ,[object Object],[object Object],$ telnet localhost 9998 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. P 0 db1 table1 PRIMARY k,v 0 1 0 = 1 234 0 2 234 foo 0 = 1 678 0 2 678 bar db1.table1 の PK を開く k = 234 を検索 k = 678 を検索

何故速くなるのか ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

oprofile – libmysql/mysqld ,[object Object],samples| %| ------------------ 9669940 53.1574 mysqld 4438098 24.3970 vmlinux 1835976 10.0927 libpthread-2.5.so 1680656 9.2389 libc-2.5.so 397970 2.1877 e1000e 89136 0.4900 oprofiled 42881 0.2357 oprofile

oprofile – libmysql/mysqld ,[object Object],samples % symbol name 748022 7.7355 MYSQLparse(void*) 219702 2.2720 my_pthread_fastmutex_lock 205606 2.1262 make_join_statistics(JOIN*, TABLE_LIST*, 198234 2.0500 btr_search_guess_on_hash 180731 1.8690 JOIN::optimize() 177120 1.8317 row_search_for_mysql 171185 1.7703 lex_one_token(void*, void*) 162683 1.6824 alloc_root 131823 1.3632 read_view_open_now 122795 1.2699 mysql_select(THD*, Item***, TABLE_LIST*, 100276 1.0370 open_table(THD*, TABLE_LIST*, st_mem_root*, 99575 1.0297 mem_pool_fill_free_list 96434 0.9973 build_template(row_prebuilt_struct*, THD*, 86349 0.8930 get_hash_symbol(char const*, unsigned int,

oprofile – libmysql/mysqld ,[object Object],samples % symbol name 204393 4.6054 schedule 118648 2.6734 tcp_sendmsg 115832 2.6099 tcp_recvmsg 106537 2.4005 tcp_v4_rcv 103915 2.3414 tcp_ack 103534 2.3328 system_call 93864 2.1150 dev_queue_xmit 86831 1.9565 __mod_timer 85891 1.9353 tcp_rcv_established 84083 1.8946 .text.task_rq_lock

oprofile – libmysql/mysqld ,[object Object],[object Object],[object Object],[object Object]

oprofile – handlersocket ,[object Object],samples| %| ------------------ 1919039 51.0453 vmlinux 811998 21.5987 mysqld 421215 11.2041 libpthread-2.5.so 207166 5.5105 e1000e 191566 5.0955 handlersocket.so 188618 5.0171 libc-2.5.so 13622 0.3623 oprofiled 5707 0.1518 oprofile

oprofile – handlersocket ,[object Object],samples % symbol name 119684 14.7394 btr_search_guess_on_hash 58202 7.1678 row_search_for_mysql 46946 5.7815 mutex_delay 38617 4.7558 my_pthread_fastmutex_lock 37707 4.6437 buf_page_get_known_nowait 36528 4.4985 rec_get_offsets_func 34625 4.2642 build_template(row_prebuilt_struct*, THD*, TABLE*, 20024 2.4660 row_sel_store_mysql_rec 19347 2.3826 btr_cur_search_to_nth_level 16701 2.0568 row_sel_convert_mysql_key_to_innobase 13343 1.6432 cmp_dtuple_rec_with_match 11381 1.4016 ha_innobase::index_read(unsigned char*, 11176 1.3764 dict_index_copy_types 10762 1.3254 mtr_memo_slot_release 10734 1.3219 ha_innobase::init_table_handle_for_HANDLER()

oprofile – handlersocket ,[object Object],samples % symbol name 129038 6.7241 tcp_sendmsg 80080 4.1729 tcp_v4_rcv 69658 3.6298 dev_queue_xmit 66171 3.4481 .text.skb_release_data 63316 3.2994 __qdisc_run 60279 3.1411 tcp_recvmsg 59703 3.1111 ip_output 58462 3.0464 .text.skb_release_head_state 48876 2.5469 tcp_ack 48733 2.5394 __alloc_skb 45660 2.3793 ip_queue_xmit 44671 2.3278 tcp_transmit_skb

oprofile – handlersocket ,[object Object],[object Object],[object Object],[object Object],[object Object]

並列処理モデル比較 ,[object Object],[object Object],[object Object],[object Object],[object Object]

並列処理モデル比較 ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

handlersocket スレッドの動作各接続からリクエストを読む DB ロック、 readview 取得各接続からのリクエストを実行 DB ロック解除各接続へ結果を返すここの処理が (1/ 接続数 ) 回程度で済む handlersocket の参照系ワーカースレッドの動作

handlersocket スレッドの動作各接続からリクエストを読む DB ロック、トランザクション開始各接続からのリクエストを実行コミット、 DB ロック解除各接続へ結果を返す handlersocket の更新系ワーカースレッドの動作複数のリクエストを一つのトランザクション内で実行

更新処理の性能について ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

handlersocket の排他制御 ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

C/S プロトコル比較 ,[object Object],[object Object],libmysql/mysqld でこのクエリを実行すると… SELECT column0, column1, column2, column3, column4 FROM db_1.table_1 where k = 15

C/S プロトコル比較 write(3, "1=115", 9) = 9 read(3, "0501234", 8192) = 14 handlersocket で同等のクエリを実行すると… 14 bytes 327 bytes response 9 bytes 80 bytes request handlersocket libmysql

mysqld/libmysql のプロトコル ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

クライアントライブラリ

libhsclient ,[object Object],[object Object],[object Object]

DB::HandlerSocket ,[object Object],[object Object],my $cli = new DB::HandlerSocket( {host => ‘localhost’, port => 9999}); $cli->open_index(1, ‘db1’, ‘table1’, ‘PRIMARY’, ‘k,v’); my $res = $cli->exec_multi([ [ 1, ‘=‘, [ ’33’ ], 1, 0 ], [ 1, ‘=‘, [ ’44’ ], 1, 0, ‘U’, [ ’44’, ‘hoge’ ] ], [ 1, ‘>=‘, [ ’55’ ], 10, 20 ], ]);

handlersocket の設定 ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

その他の設定 ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

その他の設定 ,[object Object],[object Object],[object Object],[object Object],[object Object]

durability に関連する設定 ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

ベンチマーク ,[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

スループット ( 書き込み )

課題 ,[object Object],[object Object],[object Object],[object Object]

今後の予定 (?) ,[object Object],[object Object],[object Object],[object Object],[object Object]

HandlerSocket plugin for MySQL

Recommended

Recommended

More Related Content

What's hot

What's hot (20)

Viewers also liked

Viewers also liked (14)

Similar to HandlerSocket plugin for MySQL

Similar to HandlerSocket plugin for MySQL (20)

Recently uploaded

Recently uploaded (9)

HandlerSocket plugin for MySQL