MySQL hangs for all users when a single user quota is reached

Submitted by ghomem on Thu, 10/23/2014 - 09:54

Seems that virtualmin has MySQL use one directory per virtual server

/var/lib/mysql/VIRTUAL_SERVER_USER

where all files are owned by

myqsl:VIRTUAL_SERVER_USER

When any VIRTUAL_SERVER_USERs reaches its defined quota MySQL blocks for everyone - all the websites go offline.

Why does this happen? What should we do about this?

I googled for a solution and couldn't find one.

Status: 
Closed (works as designed)

Comments

Submitted by andreychek on Thu, 10/23/2014 - 10:10

Howdy -- hmm, I don't believe I've seen MySQL run into that issue when one user runs out of quota.

I'll talk to Jamie and Joe about that though, as that's certainly undesirable.

One option you could do in the meantime, is that you don't actually have to set those databases files to be part of a group. They can be owned by "mysql:mysql".

If you go into System Settings -> Server Templates -> Default -> MySQL, there is an option "Set group ownership of MySQL database files". Setting that to "No" will make it so that new Virtual Servers don't take ownership of any of the MySQL files.

For existing MySQL files, you would need to manually set those back to "mysql:mysql".

Submitted by ghomem on Fri, 10/24/2014 - 13:48

Well, we have seen that happen and we had dozens of sites offline.

You will easily find reports of MySQL stopping when a single write operation fails.

If quotas were enforceable via filesystem why would people write articles like these ones:

http://datacharmer.blogspot.pt/2011/03/implementing-table-quotas-in-mysq... http://code.openark.org/blog/mysql/limiting-table-disk-quota-in-mysql

?

If this is confirmed then Virtualmin shouldn't have this behaviour by default.

I'll try the suggested workaround.

Submitted by JamieCameron on Fri, 10/24/2014 - 18:00

What table format is MySQL using on your system? I believe that these write hangs are only a problem for MyISAM and not InnoDB.

Quota enforcement is important, otherwise users could consume all the space on the system by creating a huge MySQL database.

Submitted by ghomem on Sat, 10/25/2014 - 04:34

Apparently we are using the default for CentOS:

mysql> show engines; +------------+---------+------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+------------------------------------------------------------+--------------+------+------------+ | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +------------+---------+------------------------------------------------------------+--------------+------+------------+ 5 rows in set (0.00 sec)

mysql>

Should Virtualmin change this default to avoid the problem with quotas?

Submitted by JamieCameron on Sun, 10/26/2014 - 12:42

Changing the default engine isn't a bad idea - although in your case, it will only effect newly created tables from now on.

Submitted by ghomem on Sun, 10/26/2014 - 18:42

Well, if quotas are enforced by default on MySQL then an engine that supports quotas MUST be configured. Otherwise Cloudmin should disable quotas for MySQL by default.

We can dump and re-import all the existing databases but I need you to confirm for sure that Innodb will work well with quotas.

Submitted by JamieCameron on Mon, 10/27/2014 - 22:35

InnoDB will work for sure. However, dumping and restoring all databases may not convert their format..

Submitted by ghomem on Fri, 02/01/2019 - 20:50

Status: Active ยป Closed (works as designed)