MySQL Bugs: #10074: latent database size mesurement

Bug #10074	latent database size mesurement
Submitted:	21 Apr 2005 21:15	Modified:	22 Apr 2005 0:45
Reporter:	Quartz 12h	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.10	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
We tried 2 techniques to mesure db size and both fails to return realtime size.

a) under innodb, we 'show variables', find the innodb_data_file_path variable and parse the size (works only if not autoextend) and we substract the 'InnoDB free: ??? kB' from the Comment field of 'show table status'.

b) for all monitored tables, we figure:
indexFree = indexLength/dataLength * dataFree
and we sum up:
dataLength-dataFree + indexLength-indexFree

In both case the size is fair, although not perfectly equal (method A reports bigger size), but the growth is comparable.

The problem lies in the accuracy of that info. We do bulky deletes, removing 10 to 40% of rows in many tables.

First bug, the size of the DB is not quickly following the 'delete' statements (it may decay for 20-40 minutes after the statements completed!!).

Second bug, the DB size is not changing for 1-2 hours even though we inserts a lot.

(We are talking about a few 10 millions rows tables in a 40 gigs InnoDB space, getting ~500 inserts/s, ~2000-5000 deletes/s)

How can one mesure the DB size more in real-time, and avoid/account the latent period where growth seams abscent?

How to repeat:
Make a big innodb file.
insert a lot,
bulk-delete every 6h or so (reduce the insert rate by 90% during that time)
observe the db size all the time, by the 2 techniques above.

Suggested fix:
there should be another variable describing the unaccounted size being worked on (in background I guess) by the innodb engine. The fragmentation may be in cause, but that should be reflected somehow, so that a new db size technique could be derived.

Hi!

After you delete, InnoDB purges the deleted rows in the background.

New inserts do not necessarily grow the table, if there is lots of free page on the B-tree pages.

Regards,

Heikki

Hi,
Thanks, but I guess I want this to be turned into a feature.
The problem is the unnaccounted space: that thing in background should report the vacant spots somewhere. We cannot monitor our database actual growth and in case of emergencies (db getting full), we may react too late to spawn the purging routines.

Figuring how full is a DB and how it grows (delta-size/delta-time) is crutial to our app.
Thanks.

Where should we look for mesuring that space about to be freed (deletes in progress)?

What should we look for to detect that the background delete jobs are finished?

Where can we find the space in freed 'B-tree pages'?