Franky, since MySQL performs
ALTER TABLE ... TYPE = InnoDB; by totally rebuilding the table, it is very normal that the space usage temporarily doubles in ibdata files. But if it doubles also after an immediate SECOND rebuild, then that must be a bug. If you can reproduce that phenomenon, please send me the following before and after each step: 1) SHOW TABLE STATUS; 2) SHOW INNODB STATUS; 3) ls -l in the datadir, 4) and what the following prints to the .err log: CREATE TABLE innodb_table_monitor(a INT) TYPE = InnoDB; <wait some 70 sec here> DROP TABLE innodb_table_monitor; Note that if you use multiple tablespaces in 4.1.1, then InnoDB will delete the old .ibd file after the rebuild, and the disk space is freed to the OS. Thus, multiple tablespaces help in your problem. Note also that the big transaction which builds the new table will also use some 20 bytes per row in the undo logs in the system tablespace, that is, the ibdata files. And the ibdata files will not shrink in 4.1.1, either. The space in undo logs is freed within the ibdata files, and can be used for undo logs of other transactions, but the space is not freed to the OS. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html ....................... List:MySQL General Discussion« Previous MessageNext Message » From:Franky Van LiedekerkeDate:December 30 2003 3:00pm Subject:innodb defragmentation question since it has been the holidays, I can understand this one failed to draw attention of the people able to answer, so I'm sending it again: ==================================================================== Hi all, if I try to defrag an InnoDB table (using "alter table ... type=innodb;") I see the size of ibdata1 growing to almost double its size, and again it doubles if I try it again ... there's only 1 innodb table in my setup, so no other table can be causing this. Is this intentional/normal or is there something I should configure? I'm running mysql-4.0.16 on solaris 2.8. Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
