Hi looking to the quotas for each site active, I can see for a specific database, via ISPConfing Interface, i.e. "databasename" is the name of database: databasename myserver client-username 34.33 MB 200 MB instead of if I use the du -sh command, I get this quota du -sh /var/lib/mysql/databasename/ 71M databasename/ this happens with: Debian 11 Mysql 5 ISPConfig 3 latest version what's wrong?!? ISPConfig misconfiguration or bug? thank you very much for your help
You can not get the correct size of a database like this. You can look up the size of the database in e.g. phpmyadmin. And also take in account what @Th0m posted about quota check interval.
OK I now I'm going to upload the screenshot; nothing happens last days so that the quotas are different. Forgive me, but If I do "du -sh" why this is not correct? I'm afraid to ask my customers less money than expected. Moreover (I have not inspected your PHP code) tell me eventually how is calculated the db disk quota, so I can learn and verify what is the method. Thank you very much
I explained to you how to get the database size correctly, open phpmyadmin, it shows the size of the database. MySQL and mariaDB have builtin functions to get the real used size of databases. You just looked at some files in a folder using du command, but MySQL does not store all data in that folder, so your method is wrong as it does not show the right full size.
Forgive me till I'm not a great system manager but browsing around Internet I found this link, regarding cpanel support: support.cpanel.net/hc/en-us/articles/4408316841751-Difference-in-database-size-in-cPanel-and-phpMyAdmin ok it seems that there is a setting to calculate the space used in similiar way of the "du -sh" command. I do not know if you want modify (eventually) ISPConfig to configure or support this calculation method thank you for all
We use the officially reported value from MariaDB and Mysql. If you think that MySQL and MariaDB report wrong database sizes with their database size calculation functions, then you should contact Oracle and MariaDB and ask them to change their database size calculation. Why should we implement an old and outdated way to calculate database size that is known to be wrong in any recent MySQL and MariaDB version? CPanel is a really old control panel, which means it has some outdated code in its code base like quota calculation via du command and they provide a switch to enable such an old style calc mode for old databases, as this works only reliably for MyISAM databases (the format used about 10 years ago) but not for InnoDB. InnoDB does not store all data inside the database directory anymore, by default it stores only the schema there but not the data. This means quota calculations with du command are flawed and not used anymore today. Especially as MySQL and MariaDB both provide database size information in SQL already, which is used by ISPconfig to determine the correct database size.
have a look here: dba.stackexchange.com/questions/11567/substantial-difference-between-database-size-under-mysql-and-actual-size-on-disk the difference in the article above is related to the INNODB configuration. It seems that there is not a way to reduce the space, so that I prefer calculate myself the webspace using PHP.
ISPConfig is OpenSource software, so feel free to change your copy in any way you like. I'm aware of the innodb_file_per_table option, but it is not used as a default either on Debian, Ubuntu, or CentOS. As the article further claims, you must force MySQL to shrink files with innodb_file_per_table. While shrinking might not be possible with default settings, this actually does not matter or is even a benefit as on typical systems, databases grow over time, so with a shared storage file for all databases, the space it not lost when you delete a database or delete data in a database, it is reclaimed by other databases that grow. So in the end, the approach we currently use is the more practical and reliable way, especially as it works on all systems while your approach just works on non-standard systems. But as mentioned above, feel free to implement your approach in your copy of ISPConfig.
