MySQL Forums
Forum List  »  InnoDB

Re: Growth of .ibd and .ibdata files
Posted by: Rick James
Date: October 08, 2016 12:00PM

Data and indexes are subject to the whims of BTrees. A block split of a block that is not cached may involve multiple 16KB reads and/or writes. So, a .ibd will usually grow by 16KB, the block size. For any non-small tables, space is pre-allocated in megabyte chunks. In your small test, probably the table was cached in the innodb_buffer_pool_size, and you saw none of the BTree I/O.

The undo/rollback logs are mostly aimed at sequential I/O, writing only a small amount of data. The structure is linear (actually circular in iblog*), not BTree. There, I would expect device-sided blocks (512 bytes?) to be written sequentially. In particular, at least one such write occurs on every COMMIT. (This is subject to the fsync setting of innodb_flush_log_at_trx_commit.)

Index changes are 'delayed' in the "Change buffer" that will eventually be flushed. This involves 16KB blocks. So, in your little test, you probably did not see such.

Bottom Line: InnoDB's I/O is much more complex that what you are seeing. And InnoDB is highly tuned to minimize I/O for scaling.

Options: ReplyQuote

Written By
October 06, 2016 08:38AM
Re: Growth of .ibd and .ibdata files
October 08, 2016 12:00PM
October 11, 2016 10:36AM
October 16, 2016 03:29PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.