HandlerSocket has really generated a lot of interest because of the dual promises of ease-of-use and blazing-fast performance. The performance comes from eliminating CPU consumption. Akira Higuchi’s HandlerSocket presentation from a couple of months back had some really good profile results for libmysql versus libhsclient (starting at slide 15). Somebody in the audience at Percona Live asked about the profile results when using prepared statements and I’m just getting around to publishing the numbers now; I’ll reproduce the original numbers here, for reference:
| libmysql (Akira’s Numbers) | ||
|---|---|---|
| samples | % | symbol name |
| 748022 | 7.7355 | MYSQLParse(void*) |
| 219702 | 2.2720 | my_pthread_fastmutex_lock |
| 205606 | 2.1262 | make_join_statistics(…) |
| 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(…) |
– Parsing SQL is slow
| HandlerSocket (Akira’s Numbers) | ||
|---|---|---|
| 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(…) |
| 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 |
– Most CPU time is spent inside InnoDB (this is great because there have been lots of InnoDB improvements & will continue to be)
| libmysql (5.1.56) | ||
|---|---|---|
| samples | % | symbol name |
| 57390 | 2.4548 | MYSQLparse(void*) |
| 42091 | 1.8004 | String::copy(…) |
| 32543 | 1.3920 | __read_nocancel |
| 30536 | 1.3062 | btr_search_guess_on_hash |
| 24630 | 1.0535 | my_wc_mb_latin1 |
| 24407 | 1.0440 | memcpy |
| 23911 | 1.0228 | MYSQLlex(void*, void*) |
| 22392 | 0.9578 | pthread_mutex_lock |
| 21973 | 0.9399 | fcntl |
| 20529 | 0.8781 | my_utf8_uni |
– Parsing SQL is slow
| libmysql w/ prepared statements (5.1.56) | ||
|---|---|---|
| samples | % | symbol name |
| 18054 | 4.1415 | String::copy(…) |
| 14223 | 3.2627 | make_join_statistics(…) |
| 11934 | 2.7376 | JOIN::optimize() |
| 10140 | 2.3261 | my_wc_mb_latin1 |
| 7152 | 1.6407 | my_utf8_uni |
| 7092 | 1.6269 | Protocol::send_fields(List |
| 6530 | 1.4980 | JOIN::prepare(…) |
| 6175 | 1.4165 | Protocol::store_string_aux(…) |
| 5748 | 1.3186 | create_ref_for_key(…) |
| 5325 | 1.2215 | mysql_execute_command(THD*) |
– Still lots of time with SQL overhead (not parsing)
| HandlerSocket (5.1.56) | ||
|---|---|---|
| samples | % | symbol name |
| 18946 | 2.9918 | btr_search_guess_on_hash |
| 15853 | 2.5034 | vfprintf |
| 8745 | 1.3810 | row_search_for_mysql |
| 7021 | 1.1087 | buf_page_get_known_nowait |
| 5212 | 0.8230 | __find_specmb |
| 5116 | 0.8079 | dena::dbcontext::cmd_find_internal(…) |
| 5100 | 0.8054 | build_template(…) |
| 4866 | 0.7684 | _IO_default_xsputn |
| 4794 | 0.7570 | dena::hstcpsvr_worker::run_one_ep() |
| 4538 | 0.7166 | send |
– Most of the time is in InnoDB & HandlerSocket
I won’t comment too much on this because they’re fairly self-explanatory. I’ve intentionally omitted any timing information because the point of these numbers are to indicate what HandlerSocket avoids doing when compared with standard SQL access. Next steps are to test with 5.5!
