Re: how to capture transaction log size?

[Marko Mäkelä]

Pradeep,

there are two or three logs:

* MySQL binlog (row-level or statement-level)

* InnoDB redo log (ib_logfile*), for crash recovery

* InnoDB transaction undo log, for rollback

The redo log is written in a circular fashion. The log sequence number (LSN) corresponds roughly to the number of redo log bytes written since InnoDB created the data and log files. You can see the LSN from the output of

SHOW ENGINE INNODB STATUS\G

I don't know an easy way of viewing the undo log size. The undo log is needed until the transaction has finished (commit or rollback), all transactions that could see the old data have finished, and the delete-marked records affected by the transaction have been purged.

In other words, it's best to keep your transactions small and short-lived when doing bulk operations. You might consider using the LIMIT clause to split the bulk operation to multiple transactions.

Also note that the insert buffer speeds up inserts to secondary indexes by making the disk accesses more sequential. This mechanism is unavailable for updates or deletes. An alternative to bulk deletes is to copy the rows to be preserved, to drop the original table, and to rename the new table to the old one.

Best regards,

Marko Mäkelä
Innobase Oy/Oracle Corp.