Sample Header Ad - 728x90

Mariadb semi-freezing while writing gigabytes per second according to iotop

1 vote
1 answer
78 views
Recently, I've had a crash of mariadb that started a nightmare. When I restored dumps I had sometimes the daemon hang during bulk inserts. This happened only on the few "big" innodb tables (I have many small dbs that really don't contain much data and most others have a significant amount only in a few tables). The db usually recovered, eventually (sometimes after hours), but sometimes crashed. I restored the dbs on a different machine without a hiccup in a matter of minutes then moved the data directory on the production machine. The only serious difference between the two is that the successful machine isn't a VM and sports SSD storage. Since then, the problems as described still happen, at random times, always when accessing those large tables. Generally I'll have several queries frozen but only one is still enough sometimes to trigger the behaviour. What I find actually hard to believe is the fact that iotop reports a mariadb thread writing **gigabytes per second**, steady, for the whole time the query hangs. This is physically impossible since the performance of the disks cannot even go near those figures. I also scoured the internet to find what I could about optimizing settings that until a few days ago seemed perfectly fine for my environment, I tweaked a few variables but nothing major, also the defaults on the "import successful" machine weren't much different to begin with. The issue is similar to https://jira.mariadb.org/browse/MDEV-30884 however this has supposedly been fixed since I'm using 10.6.14 (I also tested 10.6.15) on Gentoo. I'm at a loss, what could be causing this? Can the mechanical HDD storage somehow justify what is happening?
Asked by meh (33 rep)
Dec 19, 2023, 10:41 PM
Last activity: Feb 1, 2024, 09:57 AM