Where does HandlerSocket really save you time?

PREVIOUS POST
NEXT POST

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
7480227.7355MYSQLParse(void*)
2197022.2720my_pthread_fastmutex_lock
2056062.1262make_join_statistics(…)
1982342.0500btr_search_guess_on_hash
1807311.8690JOIN::optimize()
1771201.8317row_search_for_mysql
1711851.7703lex_one_token(void*,void*)
1626831.6824alloc_root
1318231.3632read_view_open_now
1227951.2699mysql_select(…)

– Parsing SQL is slow

HandlerSocket (Akira’s Numbers)
samples%symbol name
11968414.7394btr_search_guess_on_hash
582027.1678row_search_for_mysql
469465.7815mutex_delay
386174.7558my_pthread_fastmutex_lock
377074.6437buf_page_get_known_nowait
365284.4985rec_get_offsets_func
346254.2642build_template(…)
200242.4660row_sel_store_mysql_rec
193472.3826btr_cur_search_to_nth_level
167012.0568row_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
573902.4548MYSQLparse(void*)
420911.8004String::copy(…)
325431.3920__read_nocancel
305361.3062btr_search_guess_on_hash
246301.0535my_wc_mb_latin1
244071.0440memcpy
239111.0228MYSQLlex(void*, void*)
223920.9578pthread_mutex_lock
219730.9399fcntl
205290.8781my_utf8_uni

– Parsing SQL is slow

libmysql w/ prepared statements (5.1.56)
samples%symbol name
180544.1415String::copy(…)
142233.2627make_join_statistics(…)
119342.7376JOIN::optimize()
101402.3261my_wc_mb_latin1
71521.6407my_utf8_uni
70921.6269Protocol::send_fields(List*, unsigned int)
65301.4980JOIN::prepare(…)
61751.4165Protocol::store_string_aux(…)
57481.3186create_ref_for_key(…)
53251.2215mysql_execute_command(THD*)

– Still lots of time with SQL overhead (not parsing)

HandlerSocket (5.1.56)
samples%symbol name
189462.9918btr_search_guess_on_hash
158532.5034vfprintf
87451.3810row_search_for_mysql
70211.1087buf_page_get_known_nowait
52120.8230__find_specmb
51160.8079dena::dbcontext::cmd_find_internal(…)
51000.8054build_template(…)
48660.7684_IO_default_xsputn
47940.7570dena::hstcpsvr_worker::run_one_ep()
45380.7166send

– 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!

PREVIOUS POST
NEXT POST

Leave a Reply

Your email address will not be published. Required fields are marked *