[MDEV-27805] tpcc workload shows regression with MDB-10.6 Created: 2022-02-11  Updated: 2022-09-16  Resolved: 2022-02-11

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.6.5, 10.6.6, 10.7.2, 10.8.1, 10.6.7, 10.7.3, 10.8.2
Fix Version/s: 10.6.8, 10.7.4, 10.8.3

Type: Bug Priority: Major
Reporter: Krunal Bauskar Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: race, regression-10.6, statistics

Attachments: PNG File arm - tpcc workload.png     PNG File x86 - tpcc workload.png    
Issue Links:
Blocks
Problem/Incident
is caused by MDEV-25919 InnoDB reports misleading lock wait t... Closed
Relates
relates to MDEV-27629 Performance problems due to incorrect... Closed
relates to MDEV-28327 InnoDB persistent statistics fail to ... Closed

 Description   

Steps to reproduce:

1. Load TPCC data set (for the experiment I have used --tables=10 --scale=40) given my 24 cores VM.

2. Run the TPCC workload.

Retry (1) and (2) with 10.5 and 10.6. You will observe the significant performance difference.
check the graph for reference.

If you try to do (1) with 10.5 (loading with 10.5) and (2) with 10.6 you would see that performance is restored back to the original level as set by 10.5.

What is going wrong?
1. there have been multiple changes in the recent 10.6.5 timeframe (#25919) around stats recalculation. It has been observed that with 10.6.5 during/post-loading background stats calculation is not taking place. (This could be confirmed by querying mysql.innodb_table_stats post loading).

2. Behavior is intermittent and also could also get affected by the configuration of load and machine though there is no set pattern observed. [I was able to reproduce it majority of the times (9 out of 10 times). One suggestion to improve reproducibility: load the machine enough to keep CPU busy with loading of the data].

 Comments   
Comment by Krunal Bauskar [ 2022-02-11 ]

Work-around for the said problem: run Analyze table and re-run the queries things should be back to normal.
(if you are upgrading old data-directory to use new MDB version things you will not observe the problem).

Comment by Krunal Bauskar [ 2022-02-11 ]

MDEV-27805: tpcc workload shows regression with MDB-10.6

  • regression got revealed while running tpcc workload.
  • as part of MDEV-25919 changes logic for statistics computation was revamped.
  • if the table has changed to certain threshold then table is added to
    statistics recomputation queue (dict_stats_recalc_pool_add)
  • after the table is added to queue the background statistics thread is
    notified
  • during revamp the condition to notify background statistics threads was
    wrongly updated to check if the queue/vector is empty when it should
    check if there is queue/vector has entries to process.
  • vec.begin() == vec.end() : only when vector is empty
  • also accessing these iterator outside the parallely changing vector is not
    safe
  • fix now tend to notify background statistics thread if the logic adds
    an entry to the queue/vector.

(patch: https://github.com/MariaDB/server/pull/2014)

Generated at Thu Feb 08 09:55:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.