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.