Description:
The operation of truncating a partitioned table affects database performance,
TPS went down from over 30,000 to a few thousand
How to repeat:
1:Database configuration
innodb_buffer_pool_size=32G
innodb_buffer_pool_instances=16
innodb_adaptive_hash_index=1
2:create table and insert data(see private comment)
3:run sysbench
/home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off --mysql-db=sysbench --mysql-password=123456 --oltp-tables-count=30 --oltp-distinct-ranges=0 --oltp-index-updates=1 --oltp-non-index-updates=1 --oltp-order-ranges=0 --oltp-point-selects=9 --oltp-simple-ranges=0 --oltp-sum-ranges=0 --oltp_delete_inserts=0 --oltp-table-size=5000000 --num-threads=512 --max-requests=0 --max-time=60000 --oltp-auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --oltp-user-delay-min=10 --oltp-user-delay-max=100 --report-interval=1 --forced-shutdown=1 run
4:stop sysbench
5:run sysbench
/home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off --mysql-db=sysbench --mysql-password=123456 --oltp-tables-count=29 --oltp-distinct-ranges=0 --oltp-index-updates=1 --oltp-non-index-updates=1 --oltp-order-ranges=0 --oltp-point-selects=9 --oltp-simple-ranges=0 --oltp-sum-ranges=0 --oltp_delete_inserts=0 --oltp-table-size=5000000 --num-threads=512 --max-requests=0 --max-time=60000 --oltp-auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --oltp-user-delay-min=10 --oltp-user-delay-max=100 --report-interval=1 --forced-shutdown=1 run
6:truncate sbtest30;
Suggested fix:
Locks all hash tables before executing the function(buf_LRU_flush_or_remove_pages)
btr_search_s_lock_all();
DEBUG_SYNC_C("simulate_buffer_pool_scan");
buf_LRU_flush_or_remove_pages_nohash(id, BUF_REMOVE_ALL_NO_WRITE, 0);
Lock the hash table affects performance;
Is the hash table locked just to prevent the adaptive hash from being closed?
Can I use a new lock to prevent closing adaptive hashes? This improves performance
.
Description: The operation of truncating a partitioned table affects database performance, TPS went down from over 30,000 to a few thousand How to repeat: 1:Database configuration innodb_buffer_pool_size=32G innodb_buffer_pool_instances=16 innodb_adaptive_hash_index=1 2:create table and insert data(see private comment) 3:run sysbench /home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off --mysql-db=sysbench --mysql-password=123456 --oltp-tables-count=30 --oltp-distinct-ranges=0 --oltp-index-updates=1 --oltp-non-index-updates=1 --oltp-order-ranges=0 --oltp-point-selects=9 --oltp-simple-ranges=0 --oltp-sum-ranges=0 --oltp_delete_inserts=0 --oltp-table-size=5000000 --num-threads=512 --max-requests=0 --max-time=60000 --oltp-auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --oltp-user-delay-min=10 --oltp-user-delay-max=100 --report-interval=1 --forced-shutdown=1 run 4:stop sysbench 5:run sysbench /home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off --mysql-db=sysbench --mysql-password=123456 --oltp-tables-count=29 --oltp-distinct-ranges=0 --oltp-index-updates=1 --oltp-non-index-updates=1 --oltp-order-ranges=0 --oltp-point-selects=9 --oltp-simple-ranges=0 --oltp-sum-ranges=0 --oltp_delete_inserts=0 --oltp-table-size=5000000 --num-threads=512 --max-requests=0 --max-time=60000 --oltp-auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --oltp-user-delay-min=10 --oltp-user-delay-max=100 --report-interval=1 --forced-shutdown=1 run 6:truncate sbtest30; Suggested fix: Locks all hash tables before executing the function(buf_LRU_flush_or_remove_pages) btr_search_s_lock_all(); DEBUG_SYNC_C("simulate_buffer_pool_scan"); buf_LRU_flush_or_remove_pages_nohash(id, BUF_REMOVE_ALL_NO_WRITE, 0); Lock the hash table affects performance; Is the hash table locked just to prevent the adaptive hash from being closed? Can I use a new lock to prevent closing adaptive hashes? This improves performance .