MariaDB 10.2.35 - Major performance issues

[jameskirsop]

Our Zabbix Server updated to mariadb 10.2.35 from .34 last night and we've had enough problems that I've rolled back to .34.

zabbix_server.log shows that inserts were timing out, and `show processlist` was clogging up with a bunch of zabbix-server issued queries. We've not had any similar issues under 10.2.34. The net result was that Zabbix thought that nearly all our proxies were offline, and data was very patchy and not really useful for anything. Our NVPS is a little under 1000.

I'd be interested to know if anyone else has similar problems.

[kernbug]

Before any upgrade no matter the software is used - it's highly recommended to read release notes, for example:

https://mariadb.com/kb/en/mariadb-10235-release-notes/

Any RDBMS is a complex solution and performance is related to the configuration of the database, hardware and operating system used.

So depending on all factors possible reasons may very, but you can check them one by one, most suitable from the release notes:

• Bug fixes related to adaptive hash index (MDEV-23452, MDEV-23370)
• Fixed a race condition in MVCC reads (MDEV-22924)

Also please check: https://jira.mariadb.org/browse/MDEV-14698, collation and encoding should be utf8_bin and utf8 accordingly.

Also MariaDB has poor performance through 10.2 to 10.5 in comparison to Percona and MySQL 8.0.X series.
In addition where is no support of new tools like MySQL Shell and still old InnoDB 5.7 engine is used.

https://www.percona.com/blog/2020/07/29/checkpointing-in-mysql-and-mariadb/

https://www.percona.com/blog/2020/07/31/how-mysql-8-0-21-and-mariadb-10-5-4-perform-in-io-bound-scenarios-on-sata-ssd-and-nvme-storage/

https://www.percona.com/blog/2020/08/14/evaluating-performance-improvements-in-mariadb-10-5-5/

http://dimitrik.free.fr/blog/posts/mysql-performance-80-iobound-oltprw-vs-percona57.html

For example, "crazy" decision from MariaDB dev team in 10.5: https://jira.mariadb.org/browse/MDEV-15058.

In 3-5 year perspective it's recommended to move from MariaDB to MySQL CE or Percona Server.

Some helpful info to dive in with new options and features:

https://mysqlserverteam.com

[jasonmeta13]

Can confirm.

We run a magento shop and some of the queries went from instantaneous to taking over a minute.

Had to roll back to 10.2.34 and performance is restored.

[vso]

I am sorry to hear that it has become slow after upgrade, could you please be so kind and share which query is being slow ?

[jameskirsop]

I am sorry to hear that it has become slow after upgrade, could you please be so kind and share which query is being slow ?

I'm seeing a bunch of queries reported in the dump of active transactions that are just before the crash report written to mariadb.log. There's a mix of queries performed by the Zabbix Server user, and the user we have for the web front end.

Here's a few of them:

INSERT INTO sessions (sessionid,userid,lastaccess,status) VALUES ('7dda6cd9a0977947358343f1c10686f2','5','1609719601','0')
INSERT INTO `api_flip` (`owner_id`, `created`, `processed`, `old_status`, `new_status`) VALUES (3, '2021-01-04 00:16:15.062829', NULL, 'up', 'down')
update hosts set lastaccess=1609718915 where hostid=10321

We've had some issues with inserts to the `sessions` table since we moved to 5.0 (LTS) last year, but I've mostly ignored them because they didn't cause a huge issue. Some of our logins were rejected because the session info couldn't be written and we just waited a few moments and the issue went away (returns periodically). I've got no historical data to compare against, but some of these inserts now are reporting to take >800 seconds - for example:

---TRANSACTION 8278393571, ACTIVE 917 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 408787, OS thread handle 140191627343616, query id 41354182 localhost zabbix_web updating
UPDATE sessions SET lastaccess='1609718842' WHERE sessionid='98a67d77292e9e51df91d5f97d9c037a'

From the log, it seems like a lot of our current issues are related to locks on the sessions table.

The crash we're seeing is:

2021-01-04 11:22:58 140192159364864 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds.

Before November, we had not seen this crash since July 2020. We're now seeing it about every 2-3 days and mariadb takes about 15 minutes to recover.

I've made a change to the mariadb config but it's not resulted in any perceivable improvement.