emil89, can you please test the recently released MariaDB Server 10.6.13? It includes some performance fixes, such as MDEV-26055 and MDEV-26827. Those fixes seemed to help a lot when I tried to reproduce MDEV-30000.

There is one more fix MDEV-29967 that missed the 10.6.13 release and could be relevant here. If 10.6.13 does not fix your problem for you, it would be nice if you could test a development snapshot of 10.6 from https://ci.mariadb.org/35012/. Because I do not know how long these builds will be retained, that particular build might not be available when you are ready to try it. I got the number by navigating to end of the page https://buildbot.mariadb.org/#/grid?branch=10.6 and clicking on the leftmost "build successful" link next to "tarball-docker". The URL and the heading of that page ends in the build number (35012).

If you use such a snapshot, please indicate the version_source_revision; it should be part of the startup message:

2023-05-11 11:06:30 0 [Note] Starting MariaDB 10.6.14-MariaDB-log source revision c271057288f71746d1816824f338f2d9c47f67c1 as process 24114

@marko - I can't test your fixes outside of official releases because this is my production server. I can't even update these kinds of things too often, because it upsets clients when there are frequent technical interruptions. At the moment, I still have version 10.6.10 installed... But when I update in some time to 10.6.13 or maybe even 10.6.14, then I'll let you know what the situation looks like on my end However, from another server, I can see that 10.6.13 does seem to be faster, even post-installation.

Ok, I updated 10.6.10 to 10.6.13 and I can confirm two things:

1. MariaDB stops now within few seconds
2. "InnoDB: Buffer pool(s) load" - it took now 1 minute, earlier it was 60 minutes lol

What not improved:
1. Backups - still taking more than in earlier versions like 10.4.x

What I didn't checked:
1. Command "/usr/bin/mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --skip-write-binlog --optimize --all-in-1 --all-databases"

I checked command "/usr/bin/mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --skip-write-binlog --optimize --all-in-1 --all-databases" and it works faster!

About backups - immediately it's slower ~5minutes per backup for me than before upgrade.

In MDEV-30986 I just found out that setting innodb_random_read_ahead=ON may improve read performance for some workloads. It is disabled by default. However, I would not expect that setting to speed up any logical backups, which basically are SELECT * FROM tablename (sequential scan of the InnoDB clustered index pages).

Related to MDEV-30986, I have implemented a prototype of logical read ahead, which could augment or replace the current linear read-ahead. That would hopefully speed up index or table scans. I believe that the linear read-ahead could only work well if the clustered index leaf pages are in sequential order. OPTIMIZE TABLE should help in achieving that. I suppose that it is what your mysqlcheck command would invoke.

Do you get acceptable read performance when setting innodb_flush_method=fsync? Note that with that setting, the InnoDB buffer pool will typically be extended by the Linux file system cache. It might be better to allocate more memory to the InnoDB buffer pool. One of the purported use cases of InnoDB buffer pool resizing was to temporarily increase the buffer pool size for some heavy shifting, without having to restart the server.