Description:
while testing point select on partition table, I found ha_innopart::external_lock is hotest which cost 4.62% cpu time
+ 4.62% mysqld mysqld [.] ha_innopart::external_lock ▒
+ 3.05% mysqld mysqld [.] cmp_dtuple_rec_with_match_low ▒
+ 1.82% mysqld mysqld [.] page_cur_search_with_match ▒
+ 1.51% mysqld libjemalloc.so.1 [.] malloc ▒
+ 1.50% mysqld mysqld [.] ha_innopart::clear_blob_heaps ▒
+ 1.30% mysqld mysqld [.] btr_cur_search_to_nth_level ▒
+ 1.30% mysqld libc-2.17.so [.] __memcpy_ssse3_back ▒
+ 1.27% mysqld mysqld [.] buf_page_hash_get_low ▒
+ 1.25% mysqld mysqld [.] row_search_mvcc ▒
+ 1.17% mysqld mysqld [.] dispatch_command ▒
+ 1.03% mysqld libjemalloc.so.1 [.] free ▒
+ 1.03% mysqld mysqld [.] rw_lock_s_lock ▒
+ 0.97% mysqld mysqld [.] PolicyMutex<TTASEventMutex<GenericPolicy> >::enter ▒
+ 0.93% mysqld mysqld [.] JOIN::optimize
The root cause is it iterates all partitions(256 parts in my test) in function ha_innopart::external_lock which is unnessesary. I slightly changed the function(take out conditions outside for-loop) and the hot function is gone from perf report. tps from 94.9w to 100w.
How to repeat:
create table and load data with sysbench:
./sysbench --mysql-port=13306 --mysql-host=127.0.0.1 --mysql-user=xx --mysql-db=sb2 --table-size=30000000 --tables=1 --time=1800 --threads=1 --report-interval=1 lua/oltp_point_select.lua prepare
alter table to partition table:
alter table sbtest1 partition by hash(id) partitions 256;
run sysbench:
./sysbench --mysql-port=13306 --mysql-host=127.0.0.1 --mysql-user=xx --mysql-db=sb2 --table-size=30000000 --tables=1 --time=1800 --threads=256 --report-interval=1 lua/oltp_point_select.lua run
Suggested fix:
take condition out of for-loop in function ha_innopart::external_lock
