[MDEV-31668] With certain queries, 10.6 is slower than 10.3 Created: 2023-07-11  Updated: 2023-07-22  Resolved: 2023-07-20

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.5
Fix Version/s: 10.3.39

Type: Bug Priority: Major
Reporter: Rex Johnston Assignee: Rex Johnston
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

Ubuntu

Attachments: File perf.10.3.svg     File perf.10.6.svg    
Issue Links:
Relates
relates to MDEV-20487 Set innodb_adaptive_hash_index=OFF by... Closed

 Description   

Ubuntu 20.04.6 LTS is the default docker environment supplied with "docker pull mariadb:10.X"
libaio is the default asynchronous IO library.
Observed with libaio in docker, slowdown in query execution from 10.3 to 10.6.

 Comments   
Comment by Rex Johnston [ 2023-07-11 ]

attached perf flamegraphs

recorded with

for 10.3

perf record -F 99 -a -g /usr/sbin/mariadbd

for 10.6

perf record -F 99 -a -g /usr/sbin/mariadbd --innodb_flush_method=fsync

within docker environment

created with

root@f968792d83ba:~/src/FlameGraph# perf script -f | ./stackcollapse-perf.pl > out.perf-folded
 
root@f968792d83ba:~/src/FlameGraph# ./flamegraph.pl out.perf-folded > /tmp/perf.10.6.svg

Comment by Marko Mäkelä [ 2023-07-12 ]

Please see MDEV-31227, MDEV-30986 and MDEV-24854. Does the difference go away if you empty the file system cache and limit the use of it (see MDEV-30986 for some examples)? In that case, 10.3 should not be able to simply memcpy() most InnoDB data pages from the kernel’s file system cache.

Comment by Rex Johnston [ 2023-07-20 ]

Slowdown tracked to innodb_adaptive_hash_index flag change.

Generated at Thu Feb 08 10:25:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.