Description:
While running sysbench to prepare data, I found that the perf shows that _raw_spin_lock is hottest.
I'm using simulated AIO, with configure:
innodb_read_io_threads=16
innodb_write_io_threads=16
Perf result:
- 57.14% 57.14% mysqld [kernel.kallsyms] [k] _raw_spin_lock ▒
- _raw_spin_lock ▒
- 50.55% futex_wake ▒
do_futex ▒
sys_futex ▒
system_call_fastpath ▒
- __lll_unlock_wake ▒
- 98.15% AIO::wake_simulated_handler_thread ▒
- os_aio_simulated_wake_handler_threads ▒
+ btr_cur_prefetch_siblings ▒
+ 1.16% os_aio_func ▒
+ 0.68% fil_aio_wait ▒
- 47.24% futex_wait_setup ▒
futex_wait ▒
do_futex ▒
sys_futex ▒
- system_call_fastpath ▒
- __lll_lock_wait ▒
- 97.69% AIO::wake_simulated_handler_thread ▒
- os_aio_simulated_wake_handler_threads ▒
+ btr_cur_prefetch_siblings ▒
+ 1.32% os_aio_func ▒
+ 0.97% fil_aio_wait ▒
+ 1.48% free_pcppages_bulk
And the insertion rate is 910,000 rows/second
After changing configuration to：
innodb_read_io_threads=4
innodb_write_io_threads=4
- 7.57% 7.57% mysqld [kernel.kallsyms] [k] _raw_spin_lock
And the insertion rate is 1,000,000+ rows/second
How to repeat:
time src/sysbench src/lua/oltp_read_write.lua --mysql-host=$host --mysql-port=$port --mysql-user='xx' --mysql-db=sb1 --tables=48 --table_size=5000000 --threads=48 prepare
main configuration:
max_connections=4000
table_open_cache=8000
table_open_cache_instances=16
max_prepared_stmt_count=512000
back_log=1500
default_password_lifetime=0
default_authentication_plugin=mysql_native_password
character_set_server=latin1
collation_server=latin1_swedish_ci
skip-character-set-client-handshake
ssl=0
skip_log_bin=1
transaction_isolation=REPEATABLE-READ
innodb_file_per_table
innodb_log_file_size=1024M
innodb_log_files_in_group=32
innodb_open_files=4000
innodb_log_buffer_size=64M
#innodb_log_buffer_size=24M
innodb_doublewrite=0
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=10
join_buffer_size=32K
sort_buffer_size=32K
innodb_use_native_aio=1
innodb_stats_persistent=1
innodb_spin_wait_delay=6
innodb_max_purge_lag_delay=300000
innodb_max_purge_lag=0
innodb_flush_method=O_DIRECT
innodb_checksum_algorithm=none
innodb_io_capacity=10000
innodb_io_capacity_max=40000
innodb_lru_scan_depth=9000
innodb_change_buffering=none
innodb_read_only=0
innodb_page_cleaners=16
innodb_undo_log_truncate=off
innodb_adaptive_flushing=1
innodb_flush_neighbors=0
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_purge_threads=4
innodb_adaptive_hash_index=0
innodb_monitor_enable='%'
performance_schema = ON
Suggested fix:
It's too expensive to wakeup all io threads if we have configured many IO threads. In btr_cur_prefetch_siblings we can just wakeuo the IO read threads that read the sibling page
Description: While running sysbench to prepare data, I found that the perf shows that _raw_spin_lock is hottest. I'm using simulated AIO, with configure: innodb_read_io_threads=16 innodb_write_io_threads=16 Perf result: - 57.14% 57.14% mysqld [kernel.kallsyms] [k] _raw_spin_lock ▒ - _raw_spin_lock ▒ - 50.55% futex_wake ▒ do_futex ▒ sys_futex ▒ system_call_fastpath ▒ - __lll_unlock_wake ▒ - 98.15% AIO::wake_simulated_handler_thread ▒ - os_aio_simulated_wake_handler_threads ▒ + btr_cur_prefetch_siblings ▒ + 1.16% os_aio_func ▒ + 0.68% fil_aio_wait ▒ - 47.24% futex_wait_setup ▒ futex_wait ▒ do_futex ▒ sys_futex ▒ - system_call_fastpath ▒ - __lll_lock_wait ▒ - 97.69% AIO::wake_simulated_handler_thread ▒ - os_aio_simulated_wake_handler_threads ▒ + btr_cur_prefetch_siblings ▒ + 1.32% os_aio_func ▒ + 0.97% fil_aio_wait ▒ + 1.48% free_pcppages_bulk And the insertion rate is 910,000 rows/second After changing configuration to： innodb_read_io_threads=4 innodb_write_io_threads=4 - 7.57% 7.57% mysqld [kernel.kallsyms] [k] _raw_spin_lock And the insertion rate is 1,000,000+ rows/second How to repeat: time src/sysbench src/lua/oltp_read_write.lua --mysql-host=$host --mysql-port=$port --mysql-user='xx' --mysql-db=sb1 --tables=48 --table_size=5000000 --threads=48 prepare main configuration: max_connections=4000 table_open_cache=8000 table_open_cache_instances=16 max_prepared_stmt_count=512000 back_log=1500 default_password_lifetime=0 default_authentication_plugin=mysql_native_password character_set_server=latin1 collation_server=latin1_swedish_ci skip-character-set-client-handshake ssl=0 skip_log_bin=1 transaction_isolation=REPEATABLE-READ innodb_file_per_table innodb_log_file_size=1024M innodb_log_files_in_group=32 innodb_open_files=4000 innodb_log_buffer_size=64M #innodb_log_buffer_size=24M innodb_doublewrite=0 innodb_thread_concurrency=0 innodb_flush_log_at_trx_commit=1 innodb_max_dirty_pages_pct=90 innodb_max_dirty_pages_pct_lwm=10 join_buffer_size=32K sort_buffer_size=32K innodb_use_native_aio=1 innodb_stats_persistent=1 innodb_spin_wait_delay=6 innodb_max_purge_lag_delay=300000 innodb_max_purge_lag=0 innodb_flush_method=O_DIRECT innodb_checksum_algorithm=none innodb_io_capacity=10000 innodb_io_capacity_max=40000 innodb_lru_scan_depth=9000 innodb_change_buffering=none innodb_read_only=0 innodb_page_cleaners=16 innodb_undo_log_truncate=off innodb_adaptive_flushing=1 innodb_flush_neighbors=0 innodb_read_io_threads=16 innodb_write_io_threads=16 innodb_purge_threads=4 innodb_adaptive_hash_index=0 innodb_monitor_enable='%' performance_schema = ON Suggested fix: It's too expensive to wakeup all io threads if we have configured many IO threads. In btr_cur_prefetch_siblings we can just wakeuo the IO read threads that read the sibling page