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.

Disk Space Usage doesn't include MySQL databases size

Discussion in 'General Discussion' started by sehh, Aug 27, 2007.

  1. sehh

    sehh Well-Known Member

    Joined:
    Feb 11, 2006
    Messages:
    579
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Europe
    The "Disk Space Usage" bar on the left side of the cPanel, doesn't include the size of the SQL databases.

    As a result, my users complain why they hit their over quota limit when the displayed usage is only showing 30% or so (user has 1gb quota with a 700mb database).

    Is there a way to calculate and include the size of the database in the counter?
     
  2. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
  3. sehh

    sehh Well-Known Member

    Joined:
    Feb 11, 2006
    Messages:
    579
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Europe
    Indeed, its currently very hard to get proper sizes from MySQL.

    After lots of research, i found the following reliable method:

    Code:
    SELECT s.schema_name, CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),"Mb") total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used, CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used, COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name = "mydatabasename" GROUP BY s.schema_name ORDER BY pct_used DESC;
    
    Just change the "mydatabasename" to the name of the database you want to get data for, keep the double quotes around the name.

    The above should print this:

    Code:
    +-------------+------------+-----------+-----------+----------+--------------+
    | schema_name | total_size | data_used | data_free | pct_used | total_tables |
    +-------------+------------+-----------+-----------+----------+--------------+
    | mydb        | 100.87Mb   | 100.86Mb  | 0.00Mb    |   100.00 |           49 | 
    +-------------+------------+-----------+-----------+----------+--------------+
    
    the "size" is the REQUESTED size (but not used by data)

    the "used" is the ACTUAL data size used in the db

    unfortunately, it doesn't work on MySQL 4.1 that i tried, it only works on my systems with MySQL 5.x.

    maybe not the solution cPanel developers are looking for, but worth taking a look.
     
  4. sehh

    sehh Well-Known Member

    Joined:
    Feb 11, 2006
    Messages:
    579
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Europe
    Another idea, is to store the SQL database in the /home/user directory, so whatever happens it would be easy to just count the size of the home dir. Of course that may not give real-time numbers for InnoDB tables running on the live database, but its a close estimate that should make everyone happy with quotas and disk usage.
     
  5. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,461
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    Was the above done on an InnoDB table type? Also, did you run it over a period of time as the data stored changed to see how accurate the produced values are?

    InnoDB tables can be split into separate files, similar to how MyISAM tables are, but MySQL doesn't do it by default. Hence on an existing system, those tables would need to go through some kind of /dump/drop/restore process for MySQL to create them in the proper location. Placing them in /home/user/mysql (for example), introduces other issues pertaining to permissions and quotas. Surmountable issues, but annoying.
     
  6. sehh

    sehh Well-Known Member

    Joined:
    Feb 11, 2006
    Messages:
    579
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Europe
    The database contains tables of both InnoDB and MyIsam types. No i haven't tested the above over a prolonged period of time, just over a few hours and it seems "ok" to me.

    Our problem is with cPanel 11. Since the upgrade from cPanel 10, many of our users have started complaining about disk usage quota limits.

    For example, one client of ours has 500MB of disk usage and 800MB of SQL data (1.3GB total in 1.5GB quota).

    The client before the upgrade would see about 80% disk usage and after the upgrade to cPanel 11 the user sees 30% disk usage.

    He then tried to upload loads of stuff thinking that he had enough free space.

    Now i've got over quota users who think they are not over quota and cPanel allows them to store unlimited SQL data.

    Please help.
     
    #6 sehh, Aug 28, 2007
    Last edited: Aug 28, 2007
  7. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,461
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    /scripts/updatemysqlquota should resolve that.
     
  8. sehh

    sehh Well-Known Member

    Joined:
    Feb 11, 2006
    Messages:
    579
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Europe
    I run the script you mentioned and it worked, now space usage is back to normal.

    Do i have to run that manually via a cron job or something? or is this a one-time thing that was only required to run after the upgrade?
     
  9. Danny_T

    Danny_T Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    181
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Netherlands
    I am reffered to here bu cpaneldavid.

    user quouta 1Gb
    user home 500mb
    user innodb database 700mb
    cpanel says: MySQL Disk Space 72.71/482.59 MB
    mysql version 4.1.22

    I need some sollution before it gets out of hand and other customers going to migrate too to innodb to avoid quota counts.

    Is it possible to set somewhere in a config to not allow innodb?
    Is there a script that can convert all found innodb back to myisam ?
     
  10. sehh

    sehh Well-Known Member

    Joined:
    Feb 11, 2006
    Messages:
    579
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Europe
    We are having two kinds of problems:

    1) cPanel/WHM does not accurately calculate the disk space used by user databases

    2) We have to manually execute the "/scripts/updatemysqlquota" script via a cron job because cPanel/WHM fails to maintain disk usage on its own.

    So far the developers seen to ignore the existance of Innodb databases which is a problem for us.


    Danny_T, you can disable Innodb databases from the /etc/my.cnf but i wouldn't recommend that because you'll break many hosted sites. You can't convert a database from InnoDB to MyISAM because that would break the actual application that depends on the InnoDB features.
     
  11. Danny_T

    Danny_T Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    181
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Netherlands
    Thanks for the info.

    At this point i wished that mysql had not invented InnoDB. And i hope cPanel developers are giving us a sollution in a new release or whatever.
    Its a big problem that can fill up diskspaces and you dont know why.
     
  12. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,461
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    InnoDB started life as a third party project (meaning, not developed by MySQL). In fact, the InnoDB technology itself is now owned by Oracle.

    While you can disable the InnoDB table, it is not recommended. However, it is recommended that you configure that table type to create the tables similar to how MyISAM does, by creating a separate file in the database directory, rather than merging it into one central file named ibdata1. To do this, add the following to /etc/my.cnf:

    Code:
    [mysqld]
    innodb_file_per_table
    

    While it seems we are ignoring the overall database space issues, we are not.
     
  13. sehh

    sehh Well-Known Member

    Joined:
    Feb 11, 2006
    Messages:
    579
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Europe
    What happens if we add "innodb_file_per_table" in an existing database? do the databases/tables get converted?

    EDIT:

    to answer my own question, existing databases are not affected, only newly created databases will go into separate files, which is a problem unless we have a way to convert existing databases.
     
    #13 sehh, Sep 13, 2007
    Last edited: Sep 13, 2007
  14. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,461
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
  15. NNNils

    NNNils Well-Known Member

    Joined:
    Sep 17, 2002
    Messages:
    580
    Likes Received:
    0
    Trophy Points:
    16
    Since some time also these big files appear:

    #sql_7dd2_0.MYD
    #sql_7dd2_0.MYI
    #sql_7dd2_1.MYD
    #sql_7dd2_1.MYI

    What are those for?
     
  16. astopy

    astopy Well-Known Member

    Joined:
    Apr 3, 2003
    Messages:
    165
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    The following advice from the MySQL web site seems to work:

    and to generate all the alter table commands you'll need:

    http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html
     
  17. ahlul_b4n9_s

    ahlul_b4n9_s Member

    Joined:
    Nov 28, 2006
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Just Use This

    /scripts/updatemysqlquota

    Its worked for me.
     
  18. Kent Brockman

    Kent Brockman Well-Known Member

    Joined:
    Jan 20, 2008
    Messages:
    1,130
    Likes Received:
    3
    Trophy Points:
    38
    Location:
    Buenos Aires, Argentina
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hi, posibly setting a weekly cron job (or even daily if the hosted sites have a hard work) is the solution?


    Let us know when having news about the issue, so we can abandon this cron job
     
  19. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,461
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    I'm subscribed to this thread. Once I have further information to convey I will pass it along.
     
  20. ffeingol

    ffeingol Well-Known Member
    PartnerNOC

    Joined:
    Nov 9, 2001
    Messages:
    215
    Likes Received:
    1
    Trophy Points:
    18
    cPanel Access Level:
    DataCenter Provider
    I have a question about the "/scripts/updatemysqlquota" script. In looking at the code it basically chown's the database files to be owned by the account user-id and group mysql. From memory chowning the db files to anything other than mysql caused a lot of problems. The basic issue (again from memory) was that when the cpanel account went over quota mysql could no longer write to the database. mysql then 'hung' trying to perform the database write. This then totally hung mysql until you either restarted mysql or gave the account more quota.

    Has this been 'fixed' with the latest version of mysql or is this still a nightmare lurking out there?
     
Loading...

Share This Page