We're updating the issue view to help you get more done. 

Performance: my_hash_sort_bin is called too often

Description

According to various profiler reports, my_hash_sort_bin() is among heaviest functions for simple SELECT queries.

Firstly my_hash_sort_bin() is called too often:
1. twice while acquiring MDL lock (same key)
2. once while acquiring table from table cache (almost same key)
3. once while releasing MDL lock (same key)

For all the above cases it is sufficient to calculate value only once. Though table cache may need a bit more complex solution.

Secondly my_hash_sort_bin() seem to do a lot memory accesses, more details here: http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/5107.1.537

perf report for non-patched version:

1 2 3 4 5 6 7 8 9 10 11 12 3,77% mysqld mysqld [.] MYSQLparse(THD*) 1,93% mysqld mysqld [.] make_join_statistics(JOIN*, List<TABLE_LIST>&, Item*, st_dynamic_array*) 1,70% mysqld [vdso] [.] 0x00000000000008d8 1,68% mysqld [kernel.kallsyms] [k] __ticket_spin_lock 1,37% mysqld libpthread-2.17.so [.] pthread_mutex_lock 1,35% mysqld [kernel.kallsyms] [k] enqueue_entity 1,18% mysqld [kernel.kallsyms] [k] unix_stream_recvmsg 1,15% mysqld mysqld [.] dispatch_command(enum_server_command, THD*, char*, unsigned int) 1,00% mysqld mysqld [.] lex_one_token(void*, THD*) 0,94% mysqld mysqld [.] JOIN::optimize_inner() 0,88% mysqld mysqld [.] JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*) 0,84% mysqld mysqld [.] my_hash_sort_bin

perf report for patched version:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 4,40% mysqld mysqld [.] MYSQLparse(THD*) 1,63% mysqld [kernel.kallsyms] [k] __ticket_spin_lock 1,63% mysqld [kernel.kallsyms] [k] enqueue_entity 1,58% mysqld [vdso] [.] 0x000000000000070f 1,49% mysqld libpthread-2.17.so [.] pthread_mutex_lock 1,43% mysqld mysqld [.] make_join_statistics(JOIN*, List<TABLE_LIST>&, Item*, st_dynamic_array*) 1,28% mysqld [kernel.kallsyms] [k] unix_stream_recvmsg 1,16% mysqld mysqld [.] dispatch_command(enum_server_command, THD*, char*, unsigned int) 1,00% mysqld mysqld [.] lex_one_token(void*, THD*) 0,90% mysqld mysqld [.] st_select_lex::add_table_to_list(THD*, Table_ident*, st_mysql_lex_string*, unsigned long, thr_lock_type, enum_mdl_type, List<Index_hint>*, List<String>*, st_mysq▒ 0,85% mysqld mysqld [.] malloc 0,84% mysqld mysqld [.] JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*) ... 0,40% mysqld mysqld [.] my_hash_sort_bin

perf report for patched version + reuse hash value in table cache:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 3,59% mysqld mysqld [.] MYSQLparse(THD*) 1,89% mysqld mysqld [.] make_join_statistics(JOIN*, List<TABLE_LIST>&, Item*, st_dynamic_array*) 1,74% mysqld [kernel.kallsyms] [k] __ticket_spin_lock 1,55% mysqld [kernel.kallsyms] [k] enqueue_entity 1,48% mysqld [vdso] [.] 0x00000000000008e6 1,44% mysqld libpthread-2.17.so [.] pthread_mutex_lock 1,18% mysqld [kernel.kallsyms] [k] unix_stream_recvmsg 1,03% mysqld mysqld [.] dispatch_command(enum_server_command, THD*, char*, unsigned int) 0,99% mysqld mysqld [.] JOIN::optimize_inner() 0,98% mysqld mysqld [.] JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*) 0,88% mysqld mysqld [.] malloc 0,85% mysqld mysqld [.] lex_one_token(void*, THD*) ... 0,18% mysqld mysqld [.] my_hash_sort_bin

According to simple SELECT tests QPS went up from 14593 to 14843 (over 1.5%):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 CREATE TABLE IF NOT EXISTS t1(a INT) ENGINE=MEMORY; let $1=1000000; --disable_query_log --disable_result_log let $1=10000; while ($1) { SELECT * FROM t1; dec $1; } let $1=1000000; SELECT TIME_TO_SEC(NOW(4)) INTO @start_test; while ($1) { SELECT * FROM t1; dec $1; } --enable_result_log --enable_query_log SELECT ROUND(TIME_TO_SEC(NOW(4)) - @start_test, 4); DROP TABLE t1;

Environment

None

Status

Assignee

Sergey Vojtovich

Reporter

Sergey Vojtovich

Labels

None

External issue ID

None

External issue ID

None

Fix versions

Affects versions

10.0.8

Priority

Major