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.

Limit space in MySQL databases?

Discussion in 'General Discussion' started by jerrypr, Jul 5, 2004.

  1. jerrypr

    jerrypr Active Member

    Joined:
    Jan 12, 2004
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    Hi all,

    I _know_ this issue must have been addressed somewhere. One of the boxes I'm managing has no limits on the user's db area. Actually, it started with no quotas at all, but I know how that works and got that to work properly with cPanel. Now, I need to do something similar for the user db's. I've been managing the partition pretty well with some of the db's, but I have one user who is still growing their db quite rapidly, and I'm afraid that they'll swallow the whole partition if I can't limit them somehow.

    Any ideas on what's going on? I'm running FreeBSD, with a fairly recent version of cPanel.

    Thanks,
    J.
     
  2. dysk

    dysk Well-Known Member

    Joined:
    Apr 22, 2003
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    6
    Hi-
    As I'm sure you know by now, mysql databases run under a seperate user, so it is difficult to make them work under the quota.
    In this situation, I would create a crontab that emails the administrator with the size of each mysql database, and when one is excessively large, simply call up the client and work out an arrangement to either charge them for it, or they reduce the size.
    Sure this is low tech, but it should work. Let whoever the client is know that they'll be charged substantially if they go over the agreed-upon limit, and they'll probably work to keep it down.


    Regards,
    Erek Dyskant
     
  3. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    If you run alot of MySQL database, this maybe not the best idea, but we use to do this, but it got to be too of pain.

    this will include the MySQL database with each user's disc quota.

    when a new database is created, IE username_dbname it is create in the /var/lib/mysql folder, IE./var/lib/mysql/username_dbname, now go into the the account's /home/username folder and create the same identical folder, IE. /home/username/username_dbname= then go back into the /var/lib/mysql folder, remove the folder, username_dbname and create symbolic link from the /home/username/username_dbname to the /var/lib/mysql, like so:
    Code:
    cd /var/lib/mysql
    ln -s /home/username/username_dbname username_dbname
    chown -R mysql /home/username/username_dbname
    
    Now their database tables./data will be stored in the /home/username folder, but again, like I say, this is a pain if you manage alot of customers with MySQL. But it include their db's with their over-all disc quota.

    But remember, if they create a db in their Cpanel, the db folder is created in the /var/lib/mysql folder, and you need to go through the process again, to create a symlink to the db.

    Hope this may help :)
    Mickalo
     
  4. jerrypr

    jerrypr Active Member

    Joined:
    Jan 12, 2004
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    Hi,

    I guess I was misled by the fact that in a user's cPanel there is an area for "db quota". I figured there was some magic there that I didn't understand, but apparently not :(.

    I'm not opposed to hacks and stuff, but there should be a finer control over this. Effectively, this means that a user with quite limited disk allocation can exhaust space if he has access to a database.

    Ok, now that I know I can't limit usage, is there any way to know which tables in a DB are chewing up space? I know what _users_ are eating space, but not which tables, and you know that is what the user will want to know.

    Any ideas?

    Thanks,
    J.
     
  5. jerrypr

    jerrypr Active Member

    Joined:
    Jan 12, 2004
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    Hi,

    This is an excellent suggestion. I might even hack the create routines to do this automagically. :)

    Does anyone know the consequence to MySQL of having the db's spread out like this? Is there any performance penalty from having them in different dirs?

    Second, anyone have a clue where cPanel actually does the creation? I'll dig through the scripts, but if you have a pointer, that might help.

    Thanks,
    J.
     
  6. jerrypr

    jerrypr Active Member

    Joined:
    Jan 12, 2004
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    Here is what my users are seeing, now:

    SQL Disk usage 1.00 Megabytes

    Any way to just fix _that_? :)

    Thanks,
    J.
     
  7. Brio

    Brio Member

    Joined:
    Jul 9, 2003
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    If you SSH into your box as root and run

    /scripts/updatemysqlquota

    it will change the owner of each database to the user (If I remember correctly it parses out the username from the username_databasename format.)

    Then, when you log into cPanel it will include the users SQL and FTP space in the disk usage figures.

    You would have to run this script as a cron job to make sure any newly created databases also have the right permissions.

    It has the potential to be a little dangerous - if a user runs out of disk while writing to a database it can cause corruption if the database.
     
  8. jerrypr

    jerrypr Active Member

    Joined:
    Jan 12, 2004
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    Hi,

    Great tip. Thanks a lot.

    Yup, I can see that. MySQL doesn't have an issue with the database not being owned by it though? Or is there another tweak I need, like an SUID bit on MySQL? (Maybe cpanel just runs MySQL as root, then again, obviating the need for that)

    I'm not too worried about corrupting databases.... I'm more worried about running out of room GENERALLY on my database permissions. I have one guy who had a db twice as big as a very large db, who just hadn't bothered to "clean up" something. I'd rather one user
    run into some weirdness then have the potential for one user to screw up EVERYONE's db.

    Thanks,
    J.
     
  9. navmonkey

    navmonkey Well-Known Member

    Joined:
    Aug 19, 2003
    Messages:
    47
    Likes Received:
    0
    Trophy Points:
    6
    Wouldn't this cause problem if the script parses out the username from the username_databasename file name format? As all existing programs using database are referring to username_databasename instead of just databasename file naming convention.

    Any comments for those we had done this. Thank you.l
     
  10. jerrypr

    jerrypr Active Member

    Joined:
    Jan 12, 2004
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    Hi,

    I don't think so, since this is the database name, and it has to be consistent.

    J.
     
  11. anup123

    anup123 Well-Known Member

    Joined:
    Mar 29, 2004
    Messages:
    897
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    This Planet
    Something along these lines if possible run through cron:

    SQL Disk Usage + Disk Space Usage > Allocated Space

    Update the privelages to disallow insert/create for the database/user which the Admin resets upon renegotiation of the SQL Disk space with the client.

    Just a idea ...

    Anup
     
  12. trout21

    trout21 Well-Known Member

    Joined:
    Apr 2, 2004
    Messages:
    68
    Likes Received:
    0
    Trophy Points:
    6
    So THIS is the way MySQL is setup?

    A user has only 100Mb space on their account - but their db's can end up being larger than this amount and are not counted as being part of their space?

    You must use Mikalos hack to add them to the space?

    Have I got the gist of this....?? This has shocked me!! A user can therefore be actually using 500Mb in a massive database and only 50Mb of their space?

    Please tell me I am wrong!
     
Loading...

Share This Page