Bug#985821: Mariadb crashes on long semaphore list related to "InnoDB persistent stats analyze forces full scan forcing lock crash"
vasilis g
Version: 1:10.3.27-0+deb10u1
Mariadb aborts an long semaphore list relating to analyze table commands
for large databases. After I ran analyze on a big table on a server with
slow storage, I aborted the command and restarted the server. Since then
it seems that mariadb crashes every day at some time with a long
semaphore list. The last crash was monitored, and I stopped the workload
on the db. The only queries still running where 12 insert and updates
that just did never commit. These queries are usual application queries
that hit the db hundred times per minute.
After searching it seems that this is fixed upstream with
https://jira.mariadb.org/browse/MDEV-24275 and has to do with "InnoDB
persistent stats analyze forces full scan forcing lock crash".
We are running Debian buster 10.8 and I think this should be fixed as
this is the only mariadb version available for stable.
Mariadb logs
[ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We
intentionally crash the server because it appears to be hung.
Kind regards
Faustin Lammler
Control: -1 tags + fixed-upstream
Hi Vasilis!
Thank you for your report.
I have asked Otto when 10.3.28 (or later) would make it into Debian 10,
see
https://salsa.debian.org/mariadb-team/mariadb-10.3/-/merge_requests/30#note_234016.
In the mean time, if using the MariaDB Foundation deb packages is an
option for you, take a look at
https://mariadb.org/download/#mariadb-repositories.
Regards,
--
Faustin Lammler
GPG: F652 BCD1 1AA8 8975 F010 48A5 390A 2F27 832A 5C79
vasilis g
Thank you for your reply.
I'm glad that you will include it. Pitty that we didn't catch up
this relese.
In the meantime, I am just adding some evidence (and a workaround) that I tried in our production write intensive mariadb (10.3.27).
Indeed after changing
# mysql
> set global innodb_stats_auto_recalc=0;
the server continues for the 6th day without crashes.
Before that we experienced daily crashes.
Also another clue that leads to this bug is that indeed I
have run ANALYZE on a big table and then aborted (and even
restarted mariadb) because it was affecting traffic. Possibly
this triggered the background statistics collection and hit the
bug the days to come.
Regards