2

Since a couple of days my mariadb database randomly becomes unresponsive after random intervals (which have been between 4 and 20 hours).

The database comes back to life when rebooting the ct.
And after some time these queries start to fail with MySqlException (0x80004005): The Command Timeout expired before the operation completed.
I have also noticed that sometimes restarting the mariadb-service also gets stuck.

Below is all information I have on the issue:

System Info

Ubuntu: Ubuntu 22.04 LTS
Mariadb: 10.6.7
Proxmox: 7.2-3

Fixing attemps

  • Fresh install (vm) importing old data (only copying the database fivem) using mysqldump

  • mysqlcheck --all-databases --auto-repair (while mysql is working. When it is in the unresponsive state, there's no output.

Proxmox

This is a screenshot of all information on the proxmox CT: https://i.imgur.com/nR6n3nt.png

Data and structure

It happens in a database called fivem which has multiple tables. Notably only the bigger ones are stuck (meaning that select * from TABLE_NAME will not finish for a long time).

Phpmyadmin table (content): https://i.imgur.com/cUdhvs1.png
Phpmyadmin table (size and "is hung"): https://i.imgur.com/C6gxn1N.png

MariaDB Config

I have done these modification to the default config:

skip-name-resolve
max_allowed_packet      = 1G
max_connections         = 100000
table_cache             = 640
thread_handling         = pool-of-threads
innodb_buffer_pool_size = 48103633715

INNODB

SHOW ENGINE INNODB STATUS; was also unresponsive.

Processlist

Shows multiple queries on mostly the states commit and sending data (some commits).

Hastebin-Link to full processlist: https://www.toptal.com/developers/hastebin/sapajapohi.sql

Syslog

Shows a lot of Got an error reading communication packets and (This connection closed normally without authentication.
Note that these happen while mysql is operational too.

Some examples: https://www.toptal.com/developers/hastebin/rewozofana.yaml

Mariadb service

Output while starting: https://www.toptal.com/developers/hastebin/uracoqutab.yaml

Restarting the mariadb service also did not work. The last warning it shows are: https://www.toptal.com/developers/hastebin/usewerugut.less

Conclusion

If anyone has ideas of what the issue might be or just ideas on how to further debug this I would really appreciate it.

Edit / Updates

  • max_connections has been lowered to 250 but will need to be increased
  • innodb_buffer has also been lowered
  • It has become apparent that shortly after (or right before) the unresponsivness a lot of Disk IO is observed (20x the usual amount)
  • mysql status shows some warnings the quickly increase in size (https://i.imgur.com/TxMxC35.png). I am not sure if these could be a cause and how to fix them for now
  • I noticed that restarting the service (mariadb) occasionally got it stuck at Waiting to flush the buffer pool with the last messages being
May 19 08:11:06 database systemd[1]: Stopping MariaDB 10.6.7 database server...
May 19 08:11:06 database mariadbd[659]: 2022-05-19  8:11:06 0 [Note] InnoDB: FTS optimize thread exiting.
May 19 08:11:06 database mariadbd[659]: 2022-05-19  8:11:06 0 [Note] InnoDB: Starting shutdown...
May 19 08:11:06 database mariadbd[659]: 2022-05-19  8:11:06 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
May 19 08:11:06 database mariadbd[659]: 2022-05-19  8:11:06 0 [Note] InnoDB: Buffer pool(s) dump completed at 220519  8:11:06
8
  • 1
    Do you expect 100K concurrent connections? If so, you should do something about it. If you don't, then why did you set max_connections = 100000?
    – mustaccio
    May 17 at 17:49
  • @mustaccio That is a fair point. I do not expect 100000 connections. I had some problems where the default of 100 was not enough so I changed it to the maximum. Could this be a possible cause for the problems?
    – Keyinator
    May 17 at 18:27
  • Update on @mustaccio 's suggestion. I tried it with a limit of 250 and sadly the database just became unresponsive again.
    – Keyinator
    May 17 at 22:19
  • My best guess is that it is somehow running out of space. Since only the bigger tables are affected. However I don't know what space it could be since innodb_buffer_pool_size is set high enough to my understanding.
    – Keyinator
    May 17 at 23:04
  • How much RAM do you actually have? Where have you set your default configuration? The startup shows that its still reading 128M for the innodb buffer pool from the configuration file rather than you 48G setting (which looking at your table sizes is wastefully large).
    – danblack
    May 17 at 23:38

2 Answers 2

1

I have probably found the cause and it's a weird edge case.

I have the cts (from proxmox) running on lvm instead of lvm-thin. And had a backup job using snapshot for the database every 4 hours. My guess is that since snapshot was unavailable it froze the ct for the backup and somehow mariadb did not like that.

I have now changed that to a backup once a day with the shutdown option.

1

Turns out it is a known issue on proxmox+mariadb

https://forum.proxmox.com/threads/snapshot-backup-not-working-guest-agent-fs-freeze-gets-timeout.99887/page-2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.