mysql database latent size problems

[quartz12h]

Hi,

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_fil e_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?

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-databa...ict216402.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=741880

[steve]

"quartz12h" wrote:

Hi,

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_fil e_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?

Besides the above, why not directly parse the data from the data
directory? With the db like that, I am sure you have root access, and
can set up read access to the mysql data directory.

[quartz12h]

"steve" wrote:

Besides the above, why not directly parse the data from the
data directory? With the db like that, I am sure you have
root access, and can set up read access to the mysql data
directory.

because it is an InnoDB file, a private virtual file system of fixed
size view from the OS filesystem

[Bill Karwin]

quartz12h wrote:

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.

I believe this is as designed.

Deleting records from a database file doesn't actually shrink the file,
instead it marks the space occupied by the deleted records as available.
This is a better way than shrinking the file following a delete.
Because in a large database, this is quicker than compacting the data to
a shorter contiguous file, which might take many minutes. You probably
don't want that extra time to be used when you execute a DELETE.

New records are inserted into the available space within the file, as
long as it fits. The database file is increased in size only when the
available space is exhausted by new data (although there might be some
small gaps left over, for instance if the gap is smaller than any newly
inserted record).

Regards,
Bill K.

[quartz12h]

"steve" wrote:

Besides the above, why not directly parse the data from the
data directory? With the db like that, I am sure you have
root access, and can set up read access to the mysql data
directory.

and even if it was myisam, that would not account for non-optimized
tables that have unused freespaces in those files.

[quartz12h]

"quartz12h" wrote:

Hi,

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_fil e_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?

We were told that there ’deletes’ are performed in background long
after the delete statement is finished.

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’?

mysql bug #10074
http://bugs.mysql.com/bug.php?id=10074