Mysql storage space is it in user quota ?

Hello,

I think I have already the answer to this, as the quota is not defined in /var/lib/mysql/

But is the allocated space storage to a client take in account the database used storage space ?

Do you plan to change this ? indeed after a quick check we have some clients with huge database and their allocated storage space is lower than the really used if we’d count the database storage use + the home storage use

Pascal

I just checked and all my files in /var/lib/mysql are owned by mySQL so I dont think they are counting towards the user quota

NodeWorx only shows the storage used by the user (based off of group ownership) on whatever partition the /home directory is on. If you had one large partition, which contained both /home and /var/lib/mysql directories, then you databases would get counted. If /var/lib/mysql is on a different partition than the /home directory, then quota only reports the storage used on /home. This is a limitation of the quota command.

Socheat

Well, yes and no. Yes because the aquota.group and aquota.user will count the /var/lib/mysql files but as all databases in /var/lib/mysql are owned by mysql, quota will give the used space to the mysql user :-p

Ok Socheat, but do you plan to try to find a way for Nodeworx to count all the space used per siteworx accounts ?

As we’ve seen, even with an only one partition the pbm remain.

I think it’s a needed functionality. Exactly as for the Bandwidth usage (mail, ftp, http)

I’ve just check and see that some users have some databases higher than 800MB :\ but they only have a 250MB space storage available.

I think it is the control-panel’s job to check this and alert the user/admin

Don’t you agree with this ?
would you imagine to have some counts to have to be done by the server admin even if he has a very good, what I say … the best control panel ?

I know it is difficult to have a soft that take in account all the configs, but it is frequent to have more than one partition. And for the future what will happen if the database server is not on the local host ? (anyway even with one partition there is still the pbm of the database owner)

The solution could be to move all the /var/lib.mysql in the /home directory and create some symlinks, but I don’t want perform this, because I want my panel supported by you :slight_smile:

So to conclude I think it’s the panel that should take this pbm in account

Hope in a next version, all bandwidth and space used by a siteworx account will be take in account

Pascal

[QUOTE=pascal;9809]Well, yes and no. Yes because the aquota.group and aquota.user will count the /var/lib/mysql files but as all databases in /var/lib/mysql are owned by mysql, quota will give the used space to the mysql user :-p[/QUOTE]That is what I was trying to say. Even if you have just one big partition it should count, but all the files are mysql mysql. I guess I could manually go in and chown them to mysql:siteworxname and that would fix it hopefully without breaking anything.

Agree with you, but I really think it’s the panel to count this. It should not be so difficult to create a script which does something like
du -hs /var/lib/mysql evry n minutes and count the space used per database. And as the siteworx username is in the database name, it should be possible to apply this used space to this siteworx account and write it somewhere in the iworx database

If the panel allow to restrict the storage space and the bandwidth, then it should also count everything.

I’m pretty sure it’d be a real plus.

Pascal

Sorry, should’ve been clearer, but you guys already knew anyway. :slight_smile: Correct, quota will only be counted on the partition that /home is mounted on and only for files grouped to the user.

However, it can be a slippery slope. Sure, we can add up the database sizes, but du can suck up some resources, especially if the databases are pretty big, and especially if we’re going to be running it every 5, or even 15 minutes. And what about the site logs in /home/user/var/domain/logs/ (the transfer and error logs). They’re not owned/grouped by the user either, and thus don’t get counted toward the quota. Should those get counted as well? Maybe yes, maybe no.

What if you had /tmp mounted to another partition, and users were writing files in there as well? Should we be totalling the quotas across all partitions? This wouldn’t be too bad because while repquota/quota may not be as accurate-to-the-second as du, it’s pretty quick. But, in our case, we use rsnapshot on all our boxes, and rsync the data to a centralized server. If you were to browse through our /backups directory, you’d see files owned by some weird users (because the username on the source server is different than the uid on the storage server). If we did total up quotas over all partitions, our /backup partition would definitely be throwing off the count. In our case, we don’t use our storage servers for anything else, so it’s not really a problem in our case, but we’ve seen clients who do.

We’ve also been bitten by this same problem on some of our own boxes, where our clients have very few site files but their databases are huge. So, we’re definitely aware of the problem, and we’re more than just interested in solving it too. :slight_smile: Nobody likes free-loading clients. :wink:

How about doing something like this?


<?php
$db = mysql_connect("localhost","root","my-super-password");
mysql_select_db("my-wonderful-db",$db);
$sql = "SHOW TABLE STATUS";
$result = mysql_query($sql);
$total = 0;
while($row = mysql_fetch_array($result))
{
$total += $row['Data_length'] + $row['Index_length'];
}
echo $total;
?>

$total then gives you the, erm, total size of that database! :stuck_out_tongue: :smiley:

Obviously you would want to do a for each loop and enhance this a bit, but the basics seem to work - I compared the total from this script and the one phpMyAdmin gave me and they match up :slight_smile:

[QUOTE=Justec;9821]I guess I could manually go in and chown them to mysql:siteworxname and that would fix it hopefully without breaking anything.[/QUOTE]Would this work? Leave the owner as MySQL but change the group to the correct group?

Another Very Very simple way is to create a symlink in every /home/account/var/domain/ dir that point to /var/lib/mysql and then change the group of all databases to their real owner.

This way the quota should take in account the database of every user. Ok in /home/account/var/domain/ it will have ALL databases, but as var/ is secure and the owner/group of the databases is set (at least on mysql) the user will never see this and will never use it. It is just here to allow the quota to count the space used, that’s all

So in this case, interworx-cp would have to :

  • set the good group owner to the database when a siteworx user createds a new one
  • Create the suymlink when a new user is created.

And what about the site logs in /home/user/var/domain/logs/ (the transfer and error logs). They’re not owned/grouped by the user either, and thus don’t get counted toward the quota. Should those get counted as well? Maybe yes, maybe no.

For me it should not (yet). What should be take in account is ALL storage space used and created by the user (email/files owned by the user in his dir/database). I would not count the log and the stats because users don’t have the ability to delete these files. If the users could delete the logs and delete old stats, then ok it should be take in account. It’ll then up to the user to delete or not the big files.

Pascal

As long as they are on a different physical disk partition from /chroot/home then no.

My example is assuming thats it is part of the /

But wasn’t sure if changing the group names in /var/lib/mysql would break anything or if it would fix the problem.

[quote=Justec;9842]My example is assuming thats it is part of the /

But wasn’t sure if changing the group names in /var/lib/mysql would break anything or if it would fix the problem.[/quote]

Changing perms won’t “fix” the problem and if it’s in the same physical disk partion there should be no need to “fix” anything :wink:

Tim it’s need even in the same physical disk partition. The databases are owned by mysql.mysql so quota won’t count and assigned the space used to the siteworx account but to mysql user.

Do not hesitate if I miss smething witht this :slight_smile:

Pascal

Okay, for claifirication to anyone who is as confused by this thread as I’m starting to be :wink:

If you want your databases to count toward your quotas two things MUST be in place

  1. The databases MUST be on the same physical disk partition as /chroot/home

The easiest way to do this is just make sure that you use one large partition (excluding /boot /swap and possibly /tmp) when you provision the box.

OR

If that isn’t possible, move /var/lib/mysql inside /chroot/home and create a symlink between the two. (Will not work with clustering.)

AND

  1. Since quota uses the group ownership to count files toward the quotas the database in question must be owned by mysql and the group must be the siteworx user (unique Linux user NOT user iworx).

If BOTH of these are not in place then your databases WILL NOT count toward the quotas.

If #1 is in place and #2 is not then Justin’s suggestion will work, just be carefull. We are investigating the possibility of a bug somewhere (possibly in the importer) because I found a box yesterday where some files were owned my mysql / mysql and some were owned by mysql / siteworxuser Is that the case with you guys?

All that being said we are aware that this is not the best solution and are discussing alternatives internally BUT please also be aware that when the basic SiteWorx was designed way back when that Chris, Paul and others discussed these issues at length and tried a lot of things and this was the base solutuion at the time.

We are investigating the possibility of a bug somewhere (possibly in the importer) because I found a box yesterday where some files were owned my mysql / mysql and some were owned by mysql / siteworxuser Is that the case with you guys?

All my boxes are owned by Mysql.Mysql

And I’ve tested my solution ( http://www.interworx.com/forums/showpost.php?p=9838&postcount=10 ) for those like me whom have not the same partition for /home and mysql and it works (**seems to work *** even if it is not a beautiful sol).

Pascal

Hmm, interesting. Well as long as it works. Either way we’re looking into things and hopefully we can come up with something better.