In a MySQL database shared by many clients, how do you calculate the storage consumed by a single client and its data

Question

So basically the same table is shared by many client with a foreign key that identifies which data belongs to whom

Thanks

Answer 1

As far as I know MySQL does not have any way of showing internal resource usage by user, database, or table let alone usage by a certain key in a table. When you say "storage" do you mean on-disk storage or in-memory resource usage? The former will be much easier to get than the latter especially since it item in question is one table and its resources will likely be shared among "clients".

A few ideas you can consider in roughly increasing order of difficulty:

1. Manually count the records by client (i.e., SELECT COUNT(*) FROM table WHERE id=XXX). This gets harder if the record has variable sized fields but it would give you a rough estimate on the total size used by each client. Note that this just gives you the database size on disk and not what is actually currently in use (a 1G table might have use near zero resources if it is never used).
2. Enable bin-logging and then parse the queries in the log to get the usage by client. This will tell you that client X is using 10k queries/day while client Y is only using 100 queries/day. This again doesn't tell you the exact resource usage but will give you a rough approximation.
3. Split the table/database up into multiple MySQL instances on the same or different physical/virtual servers. This isn't practical if you have a lot of clients but it also may give you more accurate resource usages.
4. Modify the MySQL source code to log/extract the resource information you need.

I would start by looking at exactly why you need to know resource usage on a per client basis. Do you actually want to know the resources per client or just know how many queries they are running? If you just want to know the storage required per client then a simple/complex SELECT query to count records and field sizes would suffice.

Answer 2

Once you separate the clients into separate databases, you can use the INFORMATION_SCHEMA to tell you how much data is used by database and storage engine. Here are three queries:

1) Data storage by Grouped Storage Engine in MB

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

2) Data storage Grouped by Database in MB

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,index_length XSize,data_length+index_length TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA GROUP BY DB WITH ROLLUP) AA,(SELECT 2 pw) BB ORDER BY (SDSize+SXSize);

3) Data storage Grouped by Database and Storage Engine in MB

SELECT IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT table_schema,engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Each query has a subquery that looks like this: (SELECT 2 pw)

If you use (SELECT 0 pw), the Report comes out in Bytes
If you use (SELECT 1 pw), the Report comes out in KiloBytes
If you use (SELECT 2 pw), the Report comes out in MegaBytes
If you use (SELECT 3 pw), the Report comes out in GigaBytes
If you use (SELECT 4 pw), the Report comes out in TeraBytes
If you use (SELECT 5 pw), the Report comes out in PetaBytes (Email Me If You Reach This Much)

I have used these queries for years, and they work!