Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

Thanks

share|improve this question
    
Hi, Can you provide some more details? –  Ajo Augustine Mar 26 '11 at 14:19

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.