mysql unavailable (for all accounts) if one single account exceeds quota

General Interworx Control Panel Info General Inquries
1

I did noticed a problem on my iworx server today morning, mysql was refusing any new connections, I did looked and found that the cause of the problem was an user account that exceeded quota, there was a big number of httpd processes spawn (same amount as mysqld max conections setting I guess…), and on mysqld.log I had this:

111027  6:26:35 [ERROR] /usr/libexec/mysqld: Disk is full writing '/var/lib/mysql/xxxxxxxx/yyyyyyyyy.MYI' (Errcode: 122). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
111027  6:26:35 [ERROR] /usr/libexec/mysqld: Retry in 60 secs. Message reprinted in 600 secs
111027  6:36:35 [ERROR] /usr/libexec/mysqld: Retry in 60 secs. Message reprinted in 600 secs
111027  6:46:35 [ERROR] /usr/libexec/mysqld: Retry in 60 secs. Message reprinted in 600 secs
111027  6:56:35 [ERROR] /usr/libexec/mysqld: Retry in 60 secs. Message reprinted in 600 secs
111027  7:06:35 [ERROR] /usr/libexec/mysqld: Retry in 60 secs. Message reprinted in 600 secs
111027  7:16:35 [ERROR] /usr/libexec/mysqld: Retry in 60 secs. Message reprinted in 600 secs
111027  7:26:35 [ERROR] /usr/libexec/mysqld: Retry in 60 secs. Message reprinted in 600 secs
111027  7:36:35 [ERROR] /usr/libexec/mysqld: Retry in 60 secs. Message reprinted in 600 secs
111027  7:46:35 [ERROR] /usr/libexec/mysqld: Retry in 60 secs. Message reprinted in 600 secs
111027  7:56:35 [ERROR] /usr/libexec/mysqld: Retry in 60 secs. Message reprinted in 600 secs
111027  8:06:35 [ERROR] /usr/libexec/mysqld: Retry in 60 secs. Message reprinted in 600 secs

As soon as I did increased that specific account quota problem got fixed.

Any ideas on how to fix/workaround this? Obviously it’s not a very good idea to “take all server down” because one single site account did exceed quota… temporarily I had to just disable quotas to prevent the problem :frowning:

2

Hi Nars,

This is currently an unfortunate reality with MySQL and linux quotas. Your immediate options are

  1. mount your /var partition (where mysql database are stored) on a separate partition, which will then not be limited to the main root partition quotas. (in which case mysql databases wouldn’t count towards the users’ quotas.
  2. Don’t set storage quotas.

Neither of these options are very good. We need to put some serious thought into how we can prevent this, as the current system of mysql just failing is not acceptable.

3

Interesting. Can I assume that spinning mysql off onto it’s own server has the same implications as option #1? Or are there still issues with the few databases left behind when that’s done? Would using xfs and “accounting” rather than “enforcing” help at all here?

4

Parallels Ensim/PPCP is preventing this kind of problems by disabling database access for the user when a website is reaching a critical amount of free space. This will also prevent that user databases will get corrupt.