Hi people, I don't know if some people have a big experience from InnoDB and MySQL. We just see in mysql documentation innoDB does not reallow the disk space of deleted datas. An example : the disk used by one of our clients, who have only 50 000 notices, is 4GB and is still growing. The explication is innodb don't delete data from disk after a "drop" or a "delete". http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html "Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure: " The solution to decrease the size of data file, is to dump, and restore the database... A really bad solution for production installations. Does someone have experimented the same problem? What are your solution about this problem ? bests, -- Nahuel ANGELINETTI
Hi to all, At 16.42 08/01/2009, Nahuel ANGELINETTI wrote:
I don't know if some people have a big experience from InnoDB and MySQL. We just see in mysql documentation innoDB does not reallow the disk space of deleted datas.
I'm try to find a solution of this problem. I'm still working but I think that those are good hits: 1) a .ibd file for every table http://bugs.mysql.com/bug.php?id=1287
[12 Jul 2004 15:51] Heikki Tuuri
If you use MySQL-4.1.3, define
innodb_file_per_table
in my.cnf, and do OPTIMIZE TABLE, the operating system will get the disk space back. That is because the table is stored in its own .ibd file, and OPTIMIZE will rebuild the whole table to another .ibd file, rename the .ibd file, and delete the old .ibd file.
2) buil a script to drop / rewrite tables. The hack is to rewrite tables in MyIsam type and after transform them into InnoDB http://www.saturn.in/portfolio/mysql.shtml Bye Zeno Tajoli CILEA - Segrate (MI) tajoliAT_SPAM_no_prendiATcilea.it (Indirizzo mascherato anti-spam; sostituisci quanto tra AT con @)
Hi, Le Fri, 16 Jan 2009 12:46:29 +0100, Zeno Tajoli <tajoli@cilea.it> a écrit :
[12 Jul 2004 15:51] Heikki Tuuri
If you use MySQL-4.1.3, define
innodb_file_per_table
in my.cnf, and do OPTIMIZE TABLE, the operating system will get the disk space back. That is because the table is stored in its own .ibd file, and OPTIMIZE will rebuild the whole table to another .ibd file, rename the .ibd file, and delete the old .ibd file.
2) buil a script to drop / rewrite tables. The hack is to rewrite tables in MyIsam type and after transform them into InnoDB http://www.saturn.in/portfolio/mysql.shtml
Build a script should be the solution, BUT do not rewrite tables un MyIsam, it doesn't support the foreign keys, so they will be dropped. The solution I found in mysql website, is to mysqldump all the database, drop/create the database, and reinsert all the datas. bests, -- Nahuel ANGELINETTI
Hi, Le Fri, 16 Jan 2009 12:46:29 +0100, Zeno Tajoli <tajoli@cilea.it> a écrit :
[12 Jul 2004 15:51] Heikki Tuuri
If you use MySQL-4.1.3, define
innodb_file_per_table
in my.cnf, and do OPTIMIZE TABLE, the operating system will get the disk space back. That is because the table is stored in its own .ibd file, and OPTIMIZE will rebuild the whole table to another .ibd file, rename the .ibd file, and delete the old .ibd file.
2) buil a script to drop / rewrite tables. The hack is to rewrite tables in MyIsam type and after transform them into InnoDB http://www.saturn.in/portfolio/mysql.shtml
Build a script should be the solution, BUT do not rewrite tables un MyIsam, it doesn't support the foreign keys, so they will be dropped. The solution I found in mysql website, is to mysqldump all the database, drop/create the database, and reinsert all the datas. bests, -- Nahuel ANGELINETTI
participants (3)
-
Nahuel ANGELINETTI -
Nahuel ANGELINETTI -
Zeno Tajoli