Bug #81298 query rewrite plugin suffers scalability issues
Submitted: 3 May 2016 18:46 Modified: 31 May 2016 14:54
Reporter: Vadim Tkachenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S5 (Performance)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[3 May 2016 18:46] Vadim Tkachenko
Description:
I see serious scalability problems with query rewrite plugin. It does not scale with threads.
You can see my results here
http://rpubs.com/vadimtk/query-rewrite

As it appears it is the same problem for any plugin based on audit plugin.

PMP traces are:
    170 __lll_lock_wait,__GI___pthread_mutex_lock,native_mutex_lock,my_mutex_lock,inline_mysql_mutex_lock,plugin_unlock_list,mysql_a
udit_release,handle_connection,pfs_spawn_thread,start_thread,clone
    164 __lll_lock_wait,__GI___pthread_mutex_lock,native_mutex_lock,my_mutex_lock,inline_mysql_mutex_lock,plugin_foreach_with_mask,m
ysql_audit_acquire_plugins,mysql_audit_notify,invoke_pre_parse_rewrite_plugins,mysql_parse,dispatch_command,do_command,handle_connec
tion,pfs_spawn_thread,start_thread,clone
     77 __lll_lock_wait,__GI___pthread_mutex_lock,native_mutex_lock,my_mutex_lock,inline_mysql_mutex_lock,plugin_lock,acquire_plugin
s,plugin_foreach_with_mask,mysql_audit_acquire_plugins,mysql_audit_notify,invoke_pre_parse_rewrite_plugins,mysql_parse,dispatch_comm
and,do_command,handle_connection,pfs_spawn_thread,start_thread,clone
     12 __lll_unlock_wake,__pthread_mutex_unlock_usercnt,__GI___pthread_mutex_unlock,native_mutex_unlock,my_mutex_unlock,inline_mysq
l_mutex_unlock,plugin_unlock_list,mysql_audit_release,handle_connection,pfs_spawn_thread,start_thread,clone                         
     10 __lll_unlock_wake,__pthread_mutex_unlock_usercnt,__GI___pthread_mutex_unlock,native_mutex_unlock,my_mutex_unlock,inline_mysq
l_mutex_unlock,plugin_lock,acquire_plugins,plugin_foreach_with_mask,mysql_audit_acquire_plugins,mysql_audit_notify,invoke_pre_parse_
rewrite_plugins,mysql_parse,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone                       
     10 __lll_unlock_wake,__pthread_mutex_unlock_usercnt,__GI___pthread_mutex_unlock,native_mutex_unlock,my_mutex_unlock,inline_mysq
l_mutex_unlock,plugin_foreach_with_mask,mysql_audit_acquire_plugins,mysql_audit_notify,invoke_pre_parse_rewrite_plugins,mysql_parse,
dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone                                                                                                                                            
      7 __lll_lock_wait,__GI___pthread_mutex_lock,native_mutex_lock,my_mutex_lock,inline_mysql_mutex_lock,Table_cache::lock,open_tab
le,open_and_process_table,open_tables,open_tables_for_query,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command
,do_command,handle_connection,pfs_spawn_thread,start_thread,clone                                                                   
      6 __GI___pthread_mutex_lock,native_mutex_lock,my_mutex_lock,inline_mysql_mutex_lock,plugin_unlock_list,mysql_audit_release,han
dle_connection,pfs_spawn_thread,start_thread,clone                                                                                  
      6 __GI___pthread_mutex_lock,native_mutex_lock,my_mutex_lock,inline_mysql_mutex_lock,plugin_foreach_with_mask,mysql_audit_acqui
re_plugins,mysql_audit_notify,invoke_pre_parse_rewrite_plugins,mysql_parse,dispatch_command,do_command,handle_connection,pfs_spawn_t
hread,start_thread,clone 

How to repeat:
run oltp ro sysbench
[5 May 2016 17:00] Sinisa Milivojevic
Vadim,

Thank you for your report. I have analyzed seriously a backtrace and the results and I agree with you.

Verified as reported.
[31 May 2016 14:54] Paul Dubois
Posted by developer:
 
Noted in 5.7.14 changelog.

Audit log plugins (including query rewrite plugins, which use the 
audit API) were being acquired and released per statement, negatively
affecting scalability. To improve performance, these plugins now are
acquired once and released only when the connection ends.