Is it possible to compact an Innodb Table?

acivita · #1 (**permalink**) 12-09-03, 07:05

hi
i have a mysql 4.0.16 db with innodb tables...
the database tables are frequently updated by deleting all data and putting in new data every 15 minutes..
in this situation the database size is 13 GB instead of 3 Gb as should be? is there a way to compact the Innodb tables?
i've fund a tool only for myisam such as myisamchk..
Thank you

omiossec · #2 (**permalink**) 12-09-03, 09:36

There is no tool like this cause INNODB use a table space instead of multiple files.

But you can use this simple tech to defrag your tables and speed up you indices

Dump your data to a text file
drop the table
recreate it and import you data

You can also use

ALTER TABLE yourtablename TYPE=InnoDB

acivita · #3 (**permalink**) 12-09-03, 09:56

well thx..
i've noticed that on /var/lib/mysql there are many file in this format:
machinename-bin.001 to 00n...
each file has many MB size..but what are these files?
i've 23 files of that type..and i've deleted the first 21 ..and restarted mysql and all seems to work fine? is it possible to avoid the creation of such files? or is there a way to tell mysql to cancel them automatically?
thx

omiossec · #4 (**permalink**) 12-09-03, 14:13

thas was your transaction logs or your data

You can configure how many file can be used by INNODB
in MySql Start up option

http://www.lasso-developpeur.net/us/...o?id=22&tab=db