Bug #94370 Performance regression of btr_cur_prefetch_siblings
Submitted: 18 Feb 3:19 Modified: 18 Feb 12:15
Reporter: zhai weixiang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[18 Feb 3:19] zhai weixiang
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
[18 Feb 3:39] zhai weixiang
a simple proof-of-concept patch, not fully tested

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: sibling_fix.diff (application/octet-stream, text), 3.29 KiB.

[18 Feb 12:15] Umesh Shastry
Hello zhai,

Thank you for the report and contribution.

regards,
Umesh