[MDEV-16691] benchmark the impact of innodb_lock_schedule_algorithm Created: 2018-07-05  Updated: 2018-07-11  Resolved: 2018-07-11

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: 10.3.8

Type: Task Priority: Major
Reporter: Axel Schwenke Assignee: Axel Schwenke
Resolution: Fixed Votes: 0
Labels: None

Attachments: File MDEV-16691.ods    

 Description   

feedback is needed for MDEV-16664. I wonder if we ever tested the performance impact of innodb_lock_schedule_algorithm. If the 10.2 default vats is not improving performance much, then maybe we should immediately change to the safe default fcfs, and later fix the bug in the vats algorithm at our leisure.

 Comments   
Comment by Axel Schwenke [ 2018-07-05 ]

See https://arxiv.org/abs/1602.01871 (via https://github.com/MariaDB/server/pull/248)

Comment by Axel Schwenke [ 2018-07-11 ]

I benchmarked the difference between VATS and FCFS with MariaDB 10.3.8 using sysbench OLTP read/write. I had to tweak the setting quite a bit in order to see any difference:

  • use a small dataset and non-uniform RNG to create hotspots in the tables
  • adjust the workload to have a high writing portion
  • use very long transactions (100s of queries, transaction time approaching seconds)

Only then I could see a very small (order of 1%) impact for normal thread counts growing to significant impact (25..75%) for heavy thread counts (512 threads on 32 cores - 16-times overloaded) when using FCFS instead of the supposedly superior VATS.

IMHO it's ok to make FCFS the default.

Comment by Axel Schwenke [ 2018-07-11 ]

Spread sheet with numbers attached.

Generated at Thu Feb 08 08:30:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.