EmergencyEMERGENCY? Get 24/7 Help Now!

Where does HandlerSocket really save you time?

 | March 16, 2011 |  Posted In: Insight for DBAs, MySQL

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
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*, unsigned int)
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!

PREVIOUS POST
NEXT POST
Ryan Lowe

Ryan was a principal consultant and team manager at Percona until July 2014. He has experience with many database technologies in industries such as health care, telecommunications, and social networking.

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.