how cPanel is calculating quotas with mysql

httpwwf

Registered
Aug 28, 2004
4
0
151
i create 400M
AS I KNOW it include website+mysql.
but i see

Disk usage 200 Megabytes
SQL Disk usage 200 Megabytes
Disk space available 200 Megabytes

it is no include mysql.
how can i make it include mysql
 

rjwesten

Registered
Sep 24, 2004
1
0
151
MySQL databases are owned by root (or whatever use the MySQL daemon is running as).

That's why it's not counted in the user quota.
 

I-Web

Well-Known Member
Jul 7, 2003
138
0
166
is there anyway to fix this??

I mean all of the db are named username_dbname so surely it should be possible????
 

cafrale

Member
Aug 20, 2003
12
0
151
I would also like to know if there is a way to include mysql usage on account usage. If there is no way to do it, is cpanel planning on do something like this on a near future ?
 

Creazioni

Well-Known Member
Jan 5, 2003
133
0
166
cafrale said:
I would also like to know if there is a way to include mysql usage on account usage. If there is no way to do it, is cpanel planning on do something like this on a near future ?

very very very impostant find a solution
 

jeremy_reliable

Active Member
PartnerNOC
Mar 8, 2004
26
0
151
Yes this is a problem.. Have a customer that has filled var because his db's are so huge, larger than his entire account is supposed to be.. but since this quota system still suffers this cpanel doesn't do anything about it.
 

MattGetWeb

Well-Known Member
Aug 4, 2005
49
0
156
chirpy said:
They have. They've given you the option of running /scripts/updatemysqlquota
Well, searching the forums was worthwhile. :)

Should we set this up in crontab? If so, how often is reasonable? Daily? Weekly? Just before the billing run? :D
 

chirpy

Well-Known Member
Verifed Vendor
Jun 15, 2002
13,437
31
473
Go on, have a guess
Personally, I run it daily to be sure that any new d/b's are chowned appropriately. If you ran it weekly or monthly users might get a nasty shock if they've rapidly filled a new d/b.
 

fleksi

Well-Known Member
Sep 17, 2003
124
0
166
chirpy said:
They have. They've given you the option of running /scripts/updatemysqlquota
Just run /scripts/updatemysqlquota, but mysql still not count into quota.
How to put mysql space into quota?
 

chirpy

Well-Known Member
Verifed Vendor
Jun 15, 2002
13,437
31
473
Go on, have a guess
A couple of possibilities:

1. Did you wait for the disk cache to expire? (15 minutes, IIRC)

2. Do you have quotas enabled on the partition that contains /var
 
C

cPanelBilly

Guest
There is 1 large issue with having the user owning mysql databases. If the user hits disk quota, then mysql fails for the entire server.

This is why we do not have this enabled by default.
 

spaceman

Well-Known Member
Mar 25, 2002
514
7
318
cPanelBilly said:
There is 1 large issue with having the user owning mysql databases. If the user hits disk quota, then mysql fails for the entire server.

This is why we do not have this enabled by default.
Ouch! I've just run /scripts/updatemysqlquota - but now I've read the above, I think I want to 'undo' this. Can I?

Thanks.
 

spaceman

Well-Known Member
Mar 25, 2002
514
7
318
chirpy said:
Yup, just:

chown -R mysql:mysql /var/lib/mysql
Thanks for that. Done and dusted.

Surely a solution this dilemma isn't that hard? Surely the script that calculates the storage usage of each hosting account could be modified to:

1. Lookup which dbs are associated with which hosting account (not hard, surely?)
2. Calculate the size of every mysql db associated with a hosting account
3. Add this total to the main total.
4. Et voila!

Am I missing something?
 

spaceman

Well-Known Member
Mar 25, 2002
514
7
318
cPanelBilly said:
There is 1 large issue with having the user owning mysql databases. If the user hits disk quota, then mysql fails for the entire server.

This is why we do not have this enabled by default.
Just to clarify - when you say "if the user hits disk quota" you are referring to

A. the situation where "space used" is greater then "quota" as displayed by WHM > List Accounts, or
B. a physical HD or partition limit of some sort?

Assuming the answer is 'A', then is there any way to say "don't suspend accounts that are over their disk quota", and if so, would this fix the problem?
 

chirpy

Well-Known Member
Verifed Vendor
Jun 15, 2002
13,437
31
473
Go on, have a guess
No, that's not the real issue. It would indeed be easy to display a combined quota. What Billy and MySQL is refering to are linux disk quotas. They cannot be breached, as that is the whole point of them, they're a hard and fast quota. If you displayed a combined quota, for example a site has a "quota" of 200MB and they have used 120MB of disk space and 70MB of MySQL data, then you'd show a usage of 190MB/200MB, however this would not prevent the user using up a further 80MB of disk space, because that's what the disk quota would allow. The only way to charge the size of the MySQL database to the real physical disk quota is to chown the MySQL files, and then you're back to the original issue.

The fault here really lies with MySQL who haven't considered the usage of databases sizes from a shared hosting perspective. (Similar, in fact, to the complete lack of thought by PHP developers for the same situation and security).

There's no easy solution. It would most likely have to come from MySQL themselves or a move to a different SQL server.
 

spaceman

Well-Known Member
Mar 25, 2002
514
7
318
Hi chirpy,

Thanks for that excellent (as always!) explanation.

What I didn't realise - and now do - is that the disk quotas set in cPanel/WHM for individual hosting accounts are ABSOLUTELY bound to the ownership of files. I didn't realise that the relationship was so strict/simple. I always imagined it to be more 'fluid', eg. if all files associated with a hosting account are stored in, for example, /home/accname/, then the total size of all files in the dir would be what cPanel shows as 'Space Usage', irrespective of ownership on any of those files. WRONG! :)

At the risk of labouring the point - why couldn't cPanel take 'space used' as being the total size of all files in the home directory (irrespective of file ownership)? Answering my own question: probably because it would be more difficult (or impossible) to enforce.