@whattheserver sorry brother, i have not checked cyberpanel db structure properly before i just assume that i could use username but its impossible since cyber panel use unique user. also if one user name match with another it will show wrong result. after your last comment i have analysed cyberpanel db stucture.
Here is a demo flowchart, may be it can help you to get some idea.
// get the website ids as per your plan from user domain and subdomain
SELECT id FROM cyberpanel WHERE domain = ‘maindomain.com’ OR domain = ‘sub/addondomain.com’
//result 1,2 (array)
//then instead of using array we can convert array to string and make one query (it will optimize the performance greatly)
SELECT dbName FROM databases_databases WHERE FIND_IN_SET(website_id,‘1,3’);
//result test1, test3
so we got two database now get size of them
there is two way to calculate
- calculate disk size
- calculate the sum of index and data length
lets check their difference, i have tested a small database
Result:
Disk calculation: 2.6G occupied
Query Calculation: 2.292G
so the difference will be really big if we check 10GB or 50 GB database, we can neglect the difference in case of small db but in case of big db it will be bigger.
from my own point of view disk calculation will be good choice as /var/lib/mysql/databasename folder is belongs to the customer himself so he is responsible for all data inside.
when we develop a system our first priority is to give less pressure on the machine so we try to optimize it. so disk calculation is better if you want real time calculation and give less stress on the db server
i agree with your point “I suppose worst case we could do it x times and sum it up but that seems inefficient.” because if a user has 50 database it will run 50 query then sum up that will give lots of pressure. but what if provider use one server for db another for own site? many of us do this to distribute load and reduce expense. in that case we cant calculate db size from another server disk. but we can use db query to calculate them though have to edit settings and other files and will make it more complex but will open another possibility of new feature.
for db query my suggestion:
we can run cron every 15 or 30 min to grab all database uses from all user, below query is really fast and can show all database uses at a single query
SELECT table_schema “database”, sum( data_length + index_length) / 1024 / 1024
“size in MB” FROM information_schema.TABLES GROUP BY table_schema ;
after getting all db size in an array we can easily sum them say
db1 5mb
db2 5mb
db3 5mb
db4 5mb
db1 and db2 belongs to one user so db1+db2= total db size then save this total value in a cache file (can be text file or another db table. i prefer text file) then we can calculate this db size anytime with the real time directory size of website and email.
or we can grab data based on specific user within single query ( probably you are looking for it )
SELECT table_schema “database”, sum( data_length + index_length) / 1024 / 1024
“size in MB” FROM information_schema.TABLES
WHERE FIND_IN_SET(table_schema,‘db1,db2’) GROUP BY table_schema;
result:
database size in MB
db1 0.40861511
db2 2292.92187500
or
SELECT (sum( data_length + index_length) / 1024 / 1024) as total FROM information_schema.TABLES
WHERE FIND_IN_SET(table_schema,‘db1,db2’) GROUP BY table_schema;
we can calculate directory usage from /var/lib/mysql/db
we can simply get all the db list belongs to user from database then calculate directory usage. it can be real time and less resource intensive