Description:
While testing the performance of HANDLER syntax in MySQL5.7. I noticed that the HANDLER READ statement still need to acquire mdl lock even the table has been locked by HANDLER OPEN.
Backtrace quoted from output of pt-pmp
52 __lll_lock_wait(libpthread.so.0),_L_lock_854(libpthread.so.0),pthread_mutex_lock(libpthread.so.0),native_mutex_lock(thr_mutex.h:84),rw_pr_wrlock(thr_mutex.h:84),inline_mysql_prlock_wrlock(mysql_thread.h:1003),MDL_context::try_acquire_lock_impl(mysql_thread.h:1003),MDL_context::acquire_lock(mdl.cc:3566),Sql_cmd_handler_read::execute(sql_handler.cc:561),mysql_execute_command(sql_parse.cc:5061),mysql_parse(sql_parse.cc:5785),dispatch_command(sql_parse.cc:1496),do_command(sql_parse.cc:1004),handle_connection(connection_handler_per_thread.cc:300),pfs_spawn_thread(pfs.cc:2188),start_thread(libpthread.so.0),clone(libc.so.6)
ref: Sql_cmd_handler_read::execute in sql/sql_handler.cc
I have checked the code of MySQL5.6 and it doesn't need to acquire mdl lock on table after HANDLER OPEN is invoked.
I made some modification to make it behaves as 5.6 does, QPS increases from 199205 to 243425
How to repeat:
use sysbench, 10 tables, each with 200,000 records, 128 threads
lua script:
$cat sysbench/tests/db/handler.lua
pathtest = string.match(test, "(.*/)")
if pathtest then
dofile(pathtest .. "common.lua")
else
require("common")
end
function thread_init(thread_id)
set_vars()
for i=1,oltp_tables_count
do
rs = db_query("handler sbtest"..i.." open")
end
end
function event(thread_id)
rs = db_query("handler sbtest"..sb_rand_uniform(1, oltp_tables_count).." read `primary`= ("..sb_rand(1, oltp_table_size)..")")
end
Suggested fix:
.
Description: While testing the performance of HANDLER syntax in MySQL5.7. I noticed that the HANDLER READ statement still need to acquire mdl lock even the table has been locked by HANDLER OPEN. Backtrace quoted from output of pt-pmp 52 __lll_lock_wait(libpthread.so.0),_L_lock_854(libpthread.so.0),pthread_mutex_lock(libpthread.so.0),native_mutex_lock(thr_mutex.h:84),rw_pr_wrlock(thr_mutex.h:84),inline_mysql_prlock_wrlock(mysql_thread.h:1003),MDL_context::try_acquire_lock_impl(mysql_thread.h:1003),MDL_context::acquire_lock(mdl.cc:3566),Sql_cmd_handler_read::execute(sql_handler.cc:561),mysql_execute_command(sql_parse.cc:5061),mysql_parse(sql_parse.cc:5785),dispatch_command(sql_parse.cc:1496),do_command(sql_parse.cc:1004),handle_connection(connection_handler_per_thread.cc:300),pfs_spawn_thread(pfs.cc:2188),start_thread(libpthread.so.0),clone(libc.so.6) ref: Sql_cmd_handler_read::execute in sql/sql_handler.cc I have checked the code of MySQL5.6 and it doesn't need to acquire mdl lock on table after HANDLER OPEN is invoked. I made some modification to make it behaves as 5.6 does, QPS increases from 199205 to 243425 How to repeat: use sysbench, 10 tables, each with 200,000 records, 128 threads lua script: $cat sysbench/tests/db/handler.lua pathtest = string.match(test, "(.*/)") if pathtest then dofile(pathtest .. "common.lua") else require("common") end function thread_init(thread_id) set_vars() for i=1,oltp_tables_count do rs = db_query("handler sbtest"..i.." open") end end function event(thread_id) rs = db_query("handler sbtest"..sb_rand_uniform(1, oltp_tables_count).." read `primary`= ("..sb_rand(1, oltp_table_size)..")") end Suggested fix: .