Where does HandlerSocket really save you time?

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!

Related

Author

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.