MySQL InnoDB transactions with lot of delete instructions

Question

We have a process that runs frequently and does the following steps on a mysql database of type innodb.

SET autocommit = 0;
DELETE FROM table WHERE category='something';
-- every 300 rows or whatever is left in case of last round
INSERT IGNORE INTO table (fields) VALUES (row1),(row2),..,(row300);
    -- check affected rows == number of rows given if not, ROLLBACK;
-- at end of all rows
COMMIT;

This process for some reason seems to expand the ibd file into way more than the actual size. Previously we had one huge ibdata1 that went to about 60gb size, but then we dropped everything, changed the my.cnf to store data into separate files for each table, and still the table on which these transactions are happening just blew up again.

Is there some way to see what the ibd file consists of? Or if we could name the transaction to say transaction_2014_06_30 and then at the end of the erase all information related to this transaction from the idb file?

Also, will using a temporary table help in this process, because I've read that temporary tables use a different ibtmp1 file to store data and are actually lot faster in the recent versions of mysql.

Answer 1

this file ibdata1 also known as system tablespace, which consistent of undo log even if you have seperated tables into different files;

from your process I found it is a really huge transaction, which will produce drampatic quantitiy of undo logs, till MySQL 5.5, undo log space can not be shrink or adjust automatically or manually.

so there is a simple way to prevent you from the huge undo log:

commit your transaction every 300 delete statements.

each next transaction will recycle the undo log space.