How much of a table is in use?

I’ve been working on a web based monitoring tool for a while now and it’s getting close to completion. A thought crossed my mind, and be prepared it’s a wild one.

WHAT IF: my site get’s so popular I hit the upper limits of the index or field size of the primary keys of the tables?

Well clearly – it’d break and then i’ld get am email or something telling me to fix it and I would go and change the field types to allow more… problem solved…. but it’s not very nice! and it means downtime! (ironically not something I want for a tool designed to measure downtime!)

Anyway – I’m building in monthly, weekly and daily email routines to let me know certain bits of information regularly without me having to log in, and I thought why not add something that tells me how much I have used? So I did.

Here’s my solution (but not my code):

get a list of tables,
for each table,
get table name,
get primary key,
get field type and convert to human friendly numbers,
get number of rows in use,
work out usage in %,

Half an hour using google and a bit of common sense I now have a page that shows me the stats of the primary key usage and now I can add them to the email routines.

Bookmark the permalink.

Comments are closed.