In this post, we’ll look at a scalability fix for issues the query rewrite plugin had on performance.
Several months ago, Vadim blogged about the impact of a query rewrite plugin on performance. We decided to re-evaluate the latest release of 5.7(5.7.14), which includes fixes for this issue.
I reran tests for MySQL 5.7.13 and 5.7.14 using the same setup and the same test: sysbench OLTP_RO without and with the query rewrite plugin enabled.
MySQL 5.7.14 performs much better, with almost no overhead. Let’s check PMP for these runs:
|
1 |
206 __lll_lock_wait(libpthread.so.0),pthread_mutex_lock(libpthread.so.0),plugin_unlock_list,mysql_audit_release,handle_connection,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)<br> 152 __lll_lock_wait(libpthread.so.0),pthread_mutex_lock(libpthread.so.0),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(libpthread.so.0),clone(libc.so.6)<br> 97 __lll_lock_wait(libpthread.so.0),pthread_mutex_lock(libpthread.so.0),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(libpthread.so.0),clone(libc.so.6)<br> 34 __io_getevents_0_4(libaio.so.1),LinuxAIOHandler::collect,LinuxAIOHandler::poll,os_aio_handler,fil_aio_wait,io_handler_thread,start_thread(libpthread.so.0),clone(libc.so.6)<br> 18 send(libpthread.so.0),vio_write,net_write_packet,net_flush,net_send_eof,THD::send_statement_status,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)<br> 9 recv(libpthread.so.0),vio_read,net_read_raw_loop,net_read_packet,my_net_read,Protocol_classic::read_packet,Protocol_classic::get_command,do_command,handle_connection,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)<br> 8 poll(libc.so.6),vio_io_wait,vio_socket_io_wait,vio_read,net_read_raw_loop,net_read_packet,my_net_read,Protocol_classic::read_packet,Protocol_classic::get_command,do_command,handle_connection,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)<br> |
|
1 |
309 send(libpthread.so.0),vio_write,net_write_packet,net_flush,net_send_eof,THD::send_statement_status,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)<br> 43 recv(libpthread.so.0),vio_read,net_read_raw_loop,net_read_packet,my_net_read,Protocol_classic::read_packet,Protocol_classic::get_command,do_command,handle_connection,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)<br> 34 __io_getevents_0_4(libaio.so.1),LinuxAIOHandler::collect,LinuxAIOHandler::poll,os_aio_handler,fil_aio_wait,io_handler_thread,start_thread(libpthread.so.0),clone(libc.so.6)<br> 15 poll(libc.so.6),vio_io_wait,vio_socket_io_wait,vio_read,net_read_raw_loop,net_read_packet,my_net_read,Protocol_classic::read_packet,Protocol_classic::get_command,do_command,handle_connection,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)<br> 7 send(libpthread.so.0),vio_write,net_write_packet,net_flush,net_send_ok,Protocol_classic::send_ok,THD::send_statement_status,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)<br> 7 pthread_cond_wait,os_event::wait_low,srv_worker_thread,start_thread(libpthread.so.0),clone(libc.so.6)<br> 7 pthread_cond_wait,os_event::wait_low,buf_flush_page_cleaner_worker,start_thread(libpthread.so.0),clone(libc.so.6)<br> |
No sign of extra locks with the plugin API in PMP for MySQL 5.7.14. Good job!
Also, note that the fix for this issue should help to improve performance for any other audit based API plugins.
