help needed - trimming InnoDB database size

[Beyonder]

I'm using InnoDB with Mysql and I've used delete to delete over 10
million records, but the InnoDB database is still the same size on the
disk (over 100 gigs) is there a way to get Mysql/InnoDB to shrink or
trim this size?

and also, can I set a maximum size and make it so itll rotate itself,
as in purge out old entries as new ones come in when it hits the
maximum?

I'm not used to InnoDBs... so I could use some help with this...

the database is using about 95% of my drive, so its a real problem...

[Bill Karwin]

Beyonder wrote:

I'm using InnoDB with Mysql and I've used delete to delete over 10
million records, but the InnoDB database is still the same size on the
disk (over 100 gigs) is there a way to get Mysql/InnoDB to shrink or
trim this size?
I don't know specificaly about InnoDB, but I've used other RDBMS that do
not shrink the database when you delete, they just re-use the available
space the next time you add more records.

The reason is that it's hard to guarantee that the records you deleted
would be contiguous, and if it tried to shrink the file following a
delete, it'd have to move the remaining records around to try to
"defrag" the database file. This can be a time-consuming operation, and
it would make the DELETE statement take many minutes to complete, if it
operated on a very large file like yours.

Anyway, one solution I have seen is often to back up and restore the
database. But this is hard to do if you have limited disk space.

You can also do this for just one table. For example, you could do this
(assuming the table you just reduced is called "MyTable"):

ALTER TABLE MyTable RENAME TO MyTable_purgeme
CREATE TABLE MyTable (
...column defs identical to original table...
) AS SELECT * FROM MyTable_purgeme ;
...do some tests to verify that it copied correctly...
DROP TABLE MyTable_purgeme ;

I'm assuming the remaining rows you want to keep after the DELETE are
more than zero, but few enough to fit an additional copy in the
remaining space on your hard drive. If you have deleted _all_ rows from
the table, I suggest just dropping the table and re-creating it.
and also, can I set a maximum size and make it so itll rotate itself,
as in purge out old entries as new ones come in when it hits the
maximum?

I don't know of any such feature for data tables, only logs.

But I imagine one problem with such a feature would be: how does one
define "old entries"? They could be those with lower-value primary
keys, or records with earlier values in a DATE field, or some other
criterion. It's best to purge old entries based on your application
logic.

If you did this operation on a regular schedule, it might delete a more
modest portion of the table, and then further database activity would
reclaim the empty space, eliminating the need for the data-swap I
described above.

Regards,
Bill K.