The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

how cPanel is calculating quotas with mysql

Discussion in 'General Discussion' started by httpwwf, Aug 29, 2004.

  1. httpwwf

    httpwwf Registered

    Joined:
    Aug 28, 2004
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    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
     
  2. httpwwf

    httpwwf Registered

    Joined:
    Aug 28, 2004
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    wrong?
    wrong?
    wrong?
     
  3. rjwesten

    rjwesten Registered

    Joined:
    Sep 24, 2004
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    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.
     
  4. I-Web

    I-Web Well-Known Member

    Joined:
    Jul 7, 2003
    Messages:
    138
    Likes Received:
    0
    Trophy Points:
    16
    is there anyway to fix this??

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

    cafrale Member

    Joined:
    Aug 20, 2003
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    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 ?
     
  6. Creazioni

    Creazioni Well-Known Member

    Joined:
    Jan 5, 2003
    Messages:
    133
    Likes Received:
    0
    Trophy Points:
    16

    very very very impostant find a solution
     
  7. jeremy_reliable

    jeremy_reliable Active Member
    PartnerNOC

    Joined:
    Mar 8, 2004
    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    1
    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.
     
  8. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    Go on, have a guess
    They have. They've given you the option of running /scripts/updatemysqlquota
     
  9. MattGetWeb

    MattGetWeb Well-Known Member

    Joined:
    Aug 4, 2005
    Messages:
    49
    Likes Received:
    0
    Trophy Points:
    6
    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
     
  10. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    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.
     
  11. fleksi

    fleksi Well-Known Member

    Joined:
    Sep 17, 2003
    Messages:
    125
    Likes Received:
    0
    Trophy Points:
    16
    Just run /scripts/updatemysqlquota, but mysql still not count into quota.
    How to put mysql space into quota?
     
  12. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    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
     
  13. cPanelBilly

    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.
     
  14. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    Go on, have a guess
  15. spaceman

    spaceman Well-Known Member

    Joined:
    Mar 25, 2002
    Messages:
    481
    Likes Received:
    0
    Trophy Points:
    16
    Ouch! I've just run /scripts/updatemysqlquota - but now I've read the above, I think I want to 'undo' this. Can I?

    Thanks.
     
  16. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    Go on, have a guess
    Yup, just:

    chown -R mysql:mysql /var/lib/mysql
     
  17. spaceman

    spaceman Well-Known Member

    Joined:
    Mar 25, 2002
    Messages:
    481
    Likes Received:
    0
    Trophy Points:
    16
    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?
     
  18. spaceman

    spaceman Well-Known Member

    Joined:
    Mar 25, 2002
    Messages:
    481
    Likes Received:
    0
    Trophy Points:
    16
    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?
     
  19. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    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.
     
  20. spaceman

    spaceman Well-Known Member

    Joined:
    Mar 25, 2002
    Messages:
    481
    Likes Received:
    0
    Trophy Points:
    16
    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.
     
Loading...

Share This Page