Status: Closed (View Workflow)
Affects Version/s: 10.5.13
Fix Version/s: N/A
Component/s: Storage Engine - InnoDB
Environment:Linux 5.10.91-gentoo #1 SMP x86_64 AMD EPYC 7451 AuthenticAMD GNU/Linux
Dedicated raid1 nvme0n1p1 nvme1n1p1 for /var/lib/mysql directory
galera (26.4.10) cluster configured, but only one node was started after upgrade.
Attempt to upgrade from 10.4.22 to 10.5.13 turned into huge performance issues and ended with rolling the upgrade back with restoring whole dataset from backup.
MariaDB configuration parameters:
Parameters changed due to upgrade (for incompatible changes mentioned in release notes):
The issue: Innodb doesn't support multiple buffer pool instances anymore
innodb_buffer_pool_instances is now deprecated option. This was known and announced, but the effect of violating the rule of is dramatic. Mariadb was not able to handle the load it used to.
innodb_read_io_threads and innodb_write_io_threads default values are 4+4, while there's the only buffer pool. Even limiting them to 1+1 will not solve the issue. This causes numerous query cross-blocks as pool instance is always single, as it was mentioned to be a measure to improve the performance.
Unfortunately, there's no replacement parameter to be adjusted in order to overcome the issue.
The server was not able to handle desired load of parallel writes (while same server was easily handling same load before the upgrade and after reverting the changes back).
- Number of threads running (that used to be around 7-10 normally) jumped to 100 and sometimes reached 250, number of threads connected that is normally within 60-100 jumped to 300-450.
- Rate of select queries remained the same (~120 qps), but rate of insert/update queries dropped from 100 to 20 qps.
- Number of active queries in the processlist changed from 7-10 (with longest query time of 40 seconds) to 30-50 with longest query running about 900 seconds. Reason for the latest one was that queries timed out (increasing lock wait timeouts and session timeouts from 20 to 45 gave no positive effect), and rollback took ages due to being unable to gain a lock.
- Normally, mariadb has more than one table cache instance of 120K tables open. But after upgrade even one table cache instance was not utilized completely (reached 110K open tables only).
- Normally, mariadb 10.4 was able to perform frequent or even parallel inserts even into same table (tables have size of up to 100GB). MariaDB 10.5 was not able to perform frequent (every 3 seconds) writes into small table (17MB) with no parallel writes (queries stuck, timed out and one was in trx_state : ROLLING BACK for 860 seconds!)
- SHOW ENGINE INNODB STATUS output is full of messages like following one:
however, this seems to have no relation to the issue.
Studying innodb-system-variables list for new or not configured parameters indicates that there are lots of deprecations, but no adjustment for handling high parallelism anymore.
The question is - are there any (other) replacement settings to overcome the issue caused by fixed single pool instance?