Packing needed for MySQL?

[Sonnich]

Hi all!

I am still playing around with databases. So here it goes:

Is packing needed for MySQL (5.0)?

I have a new DB, which already is 178 MB. Will that continue to grow,
when I add and delete data? Does it reclaim freed space and deleted
rows?

If not, is there a (automated) way to pack tables using SQL or
something simple, which I can call from my app?

Sonnich

[Gordon Burditt]

>I am still playing around with databases. So here it goes:

Is packing needed for MySQL (5.0)?
No, for various definitions of "needed".
I have a new DB, which already is 178 MB. Will that continue to grow,
when I add and delete data? Does it reclaim freed space and deleted
rows?
MySQL will reclaim freed space but it will NOT necessarily give
it back to the OS. It will re-use it.
If not, is there a (automated) way to pack tables using SQL or
something simple, which I can call from my app?

There are a number of commands which will copy a table to itself and
in the process take all the unused space out of MyISAM tables (hope
you have enough disk space for *two* copies of the table, temporarily):

ALTER TABLE
OPTIMIZE TABLE
REPAIR TABLE

I think these free up partially-unused blocks in InnoDB tables as well.

Gordon L. Burditt

[Sonnich]

I have a new DB, which already is 178 MB. Will that continue to grow,
when I add and delete data? Does it reclaim freed space and deleted
rows?

MySQL will reclaim freed space but it will NOT necessarily give
it back to the OS. It will re-use it.

If not, is there a (automated) way to pack tables using SQL or
something simple, which I can call from my app?

There are a number of commands which will copy a table to itself and
in the process take all the unused space out of MyISAM tables (hope
you have enough disk space for *two* copies of the table, temporarily):
ALTER TABLE
OPTIMIZE TABLE
REPAIR TABLE
I think these free up partially-unused blocks in InnoDB tables as well.

Thanks, here is how I found it works:

When simply optimizing, the file grows to 294 MB. Seems like the copies
takes up the space.

When deleting rows, then it only grows to 258 MB (it is adding data
too). After that optimizing is much faster and does not require more
space.

Thanks
Sonnich