Regression Analysis and hunting of ps-covered-id in mariadb 10.5.29 as reported by MarC in his blogpost

Reference: https://smalldatum.blogspot.com/2026/04/cpu-bound-sysbench-on-large-server.html Issue: ps-covered-pk - select id from sbtest where id in ( 100 random ids) , regressed between 10.5.29 release to 10.4.34 release Conclusion: "Remove multiple InnoDB buffer pool instances (MDEV-15058)" commit: 1a6f708ec594ac0ae2dd30db926ab07b100fa24b , did cause regression in ps-ps-covered-pk Investigation: Two major changes AHI=OFF and BP instance=1 ( hard coded) were put in 10.5.29 which could have impacted ps-covered-id. AHI=on does improve qps by 5-10% in case of ps-covered-id but I kept AHI=off for comparison of 10.4 vs 10.5 upto 10% regression , I am able to get on 6 core laptop with BP=2 GB , PFS=1 and other min config
Article content I used Dimstat to monitor detail and found that cpu% utilization were roughly similar for 10.4 and 10.5 as you can see in below graph
Article content Now, the question was why qps was less for 16 thread when hit 100% cpu. In order to nail down, I enabled below in config: performance-schema-instrument='wait/synch/mutex/innodb/%=ON' to know whether any mutex contention was causing this slowness. I tried , perf diff bewteen 10.4 and 10.5 and looked into report but could get teh clue but PFS mutex diff did give me clue. avg wait time of in 10.4.34: 33085 ( bp instance:2 for 2 GB bp) avg wait time of in 10.5.29: 740407 ( bp instance:1 fixed) In this ps-covered-pk was executed for 10000 events with 16 threads 10.4.34 --------------------------------------------------------------------- EVENT_NAME COUNT_STAR SUM_TIMER_WAIT wait/synch/mutex/innodb/dict_sys_mutex 106613 63681182360 wait/synch/mutex/innodb/trx_mutex 107050 11981067598 wait/synch/mutex/innodb/srv_sys_mutex 1246 11146911076 wait/synch/mutex/innodb/fil_system_mutex 11077 411494340 wait/synch/mutex/innodb/buf_dblwr_mutex 5736 387934508 wait/synch/mutex/innodb/buf_pool_mutex 8130 268981438 wait/synch/mutex/innodb/flush_list_mutex 5689 162600970 wait/synch/mutex/innodb/srv_threads_mutex 558 95473510 wait/synch/mutex/innodb/log_sys_write_mutex 2433 69806538 wait/synch/mutex/innodb/trx_sys_mutex 151 26911188 wait/synch/mutex/innodb/redo_rseg_mutex 370 20920890 wait/synch/mutex/innodb/purge_sys_pq_mutex 103 18392322 wait/synch/mutex/innodb/log_sys_mutex 228 13385356 wait/synch/mutex/innodb/trx_pool_mutex 32 3722614 wait/synch/mutex/innodb/lock_wait_mutex 6 2327888 wait/synch/mutex/innodb/trx_pool_manager_mutex 16 1685712 wait/synch/mutex/innodb/log_flush_order_mutex 10 1394726 wait/synch/mutex/innodb/srv_innodb_monitor_mutex 1 491666 wait/synch/mutex/innodb/ibuf_mutex 1 321088 10.5.29 --------------------------------------------------------------------- EVENT_NAME COUNT_STAR SUM_TIMER_WAIT wait/synch/mutex/innodb/buf_pool_mutex 3176165 2351656699744 wait/synch/mutex/innodb/dict_sys_mutex 94487 21459063590 wait/synch/mutex/innodb/trx_mutex 95936 8126396124 wait/synch/mutex/innodb/flush_list_mutex 29251 645718002 wait/synch/mutex/innodb/fil_system_mutex 9478 311595836 wait/synch/mutex/innodb/buf_dblwr_mutex 271 18673274 wait/synch/mutex/innodb/srv_threads_mutex 156 11539100 wait/synch/mutex/innodb/trx_sys_mutex 98 9732980 wait/synch/mutex/innodb/log_sys_mutex 343 7575670 wait/synch/mutex/innodb/trx_pool_mutex 72 6953562 wait/synch/mutex/innodb/redo_rseg_mutex 168 4966830 wait/synch/mutex/innodb/trx_pool_manager_mutex 36 3431628 wait/synch/mutex/innodb/log_flush_order_mutex 109 3190812 wait/synch/mutex/innodb/purge_sys_pq_mutex 26 983332

Comments

Post a Comment

Popular posts from this blog

MySQL- Crash Recovery Performance Assessment

Why we need to assess the performance of   crash recovery in MySQ and here is my explanation:  It’s not just WLs or bug fixes — any change in redo format or code changes around it, flushing behavior, or recovery concurrency can silently slow things down and if you have huge volume of data to recover than it could be painful. MySQL errorlog is the best place to look into details like whats happening during recovery and which phase of recovery is time consuming. Basically , I measured the recovery  timing  - from “Starting crash recovery” to “Apply batch completed , in MySQLD error log. Refer this about recovery in Innodb here: https://dev.mysql.com/doc/refman/8.4/en/innodb-recovery.html#innodb-crash-recovery Let me share some of my hack to measure the time taken for crash-recovery experiment. 1.  I used ` innodb_checkpoint_disabled ` which is strictly a testing mechanism. It forces maximum redo accumulation and doesn’t reflect real‑world recovery .   in...
Read more

PGO in MySQL community 9.7

Image
  PGO ( Profile-guided optimization) in mysql community edition of 9.7.0 -------------------------------------------------------------------------------------------------------------------------- Straightforward ,  30% gain   we have seen in eneterprise edition of mysql   in read only sysbench test like point-select ( memory bound) .  I am curious to know whether similar gain , we see in pgo community build and what kind of test cases it use to train data? If pgo in community edition gives 30 percentage gain then it makes sense to use this feature by many other databases based on MySQL because it comes free without doing any extra tuning of mysqld config. As you can see,9.7.0 gives better performance wrt 9.6.0 and it's because of just pgo being put in. I referred INFO_BIN and 9.7 has fprofile compiler option whereas 9.6.0 doesn't have So, far I have seen release notes and I know, there is no other major changes would have contributed such gain. Its go...
Read more

Mariadb Performance : Openssl and wolfssl

Image
Why this SSL performance assessment of Mariadb ? My first objective was to assess new openssl 3.5 or 3.6 and see whether it's improving for Mariadb  or not and second , I could not find any comparative analysis of ssl for Mariabd in public space. Earlier in MySQL, I had done exhaustive assessment of  wolfssl and openssl's different version so thought of doing some against Mariadb which is another popular database and moving fast in db ranking. I used  memory bound test and sysbench's point-select without prepared statement to stress TLS layer the most. Another test , I picked is connect/disconnect which fires point-select query for each connection like PHP and this also stressess TLS layer the most. Key Findings In case of sysbench's point-select query like " SELECT c FROM sbtest1 WHERE id = 12345 ;" , wolfssl (5.8.4 default) is the best. openssl-1.1.1l/3.0.19/3.5.5/3.6.1 are less upto 9% wrt wolfssl. In case of sysbench's point-select query like "...
Read more