I have a simple innodb Mariadb database used for logging (only inserts with
insert ignore into a single large table).
I back up the database nightly with
mysqldump --no-create-info --hex-blob --disable-keys --skip-extended-insert --single-transaction --quick while inserts are running (usually under 5-10 inserts per second, on a Hetzner VPS instance with SSD).
Randomly (once in cca 7-14 days) the inserts made during the
mysqldump get stuck in
Updating state (so that
show full processlist; shows them with Time like 12000 s = time since the mysqldump). This is a problem as the app has a limited connection pool, and as all connections are used by stuck inserts, the app loses the ability to use the database.
More weird symptoms: When the db server gets into this state,
service mariadb restart never finishes, is just stuck for minutes, and I cannot reboot the server with
sudo reboot which works normally - the only working way I found is to run
sudo systemctl --force --force reboot.
It seems to me that some database files get stuck during the backup (filesystem problem?) and so the database flush never finishes, not for inserts nor for database restart.
Any ideas about how to go debug this?
Status, variable,s innodb status, queries, table definition: https://gist.github.com/tkafka/e43fbb7a4440eca7d7d4dd6e8b490630