Query Rewrite Plugin: Scalability Fix in MySQL 5.7.14
In this post, we’ll look at a scalability fix for issues the query rewrite plugin had on performance.
by
Join the DZone community and get the full member experience.
Join For FreeIn 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:
MySQL 5.7.13
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)
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)
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)
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)
18send(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)
9recv(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)
8poll(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)MySQL 5.7.14
309send(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)
43recv(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)
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)
15poll(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)
7send(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)
7pthread_cond_wait,os_event::wait_low,srv_worker_thread,start_thread(libpthread.so.0),clone(libc.so.6)
7pthread_cond_wait,os_event::wait_low,buf_flush_page_cleaner_worker,start_thread(libpthread.so.0),clone(libc.so.6)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.
Published at DZone with permission of Alexey Stroganov, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments