Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.8
-
None
-
None
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:
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:
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:
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%):
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;
|