In this blog post, we’ll discuss how the Query Rewrite plugin can harm performance.
MySQL 5.7 comes with Query Rewrite plugin, which allows you to modify queries coming to the server. (You can view the details here:Â https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin.html.)
It is based on the audit plugin API, and unfortunately it suffers from serious scalability issues (which seems to be the case for all API-based audit plugins).
I want to share the results for sysbench OLTP RO with and without the query rewrite plugin — but with one very simple rewrite rule, which doesn’t affect any queries. This is the rule from the documentation:
|
1 2 |
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) -> VALUES('SELECT ?', 'SELECT ? + 1'); |
There are results for both cases:
As you can see, the server with the Query Rewrite plugin can’t scale after 100 threads.
When we look at the PMP profile, it shows the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 |
So clearly it’s related to a mutex acquired in the audit plugin API code. I filed a bug (https://bugs.mysql.com/bug.php?id=81298), but it’s discouraging to see that while the InnoDB code is constantly being improved for better scaling, other parts of the server can still suffer from global mutexes.
About the Author
Vadim TkachenkoVadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.
Replication features also suffered from plugin mutex contention.
Vadim, would you mind sharing the machine details and the sysbench options ? Seems like a pretty big box you’ve got there …
My config is here
https://github.com/Percona-Lab-results/201605-OLTP-RW/tree/master/cnf
It is 56-core box, with workload similar to
https://github.com/Percona-Lab/benchmark-results/blob/201603-mysql55-56-57-RO/remote-OLTP/summary/remote-OLTP.md
I just installed plugin and activated one rule, as described in
https://www.percona.com/blog/2016/05/10/query-rewrite-plugin-can-harm-performance/
Frankly it was very easy to repeat.
I get the same results with audit null plugin which comes with MySQL.
Hi Vadim,
I personally have never heard of the Query Rewrite Plugin before. Would it be possible to provide an example on where this plugin would be helpful (or can you provide a link)? The example you provided above doesn’t really highlight the need of such a plugin.
I will link to this post http://mysqlserverteam.com/the-query-rewrite-plugins/
it has a real-world example
A good use would be for Shard-Query. The server intercepts the SQL, passes it to the rewrite layer, the rewrite layer rewrites the SQL as a CALL shard_query.sq_helper(…) call, and that returns a resultset to the client. This is basically functionally equivalent to the MySQL proxy LUA script.
It might be useful for others to know that this is resolved now 🙂