MySQL Forums :: InnoDB :: Growth of .ibd and .ibdata files

Advanced Search

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

Subject Views Written By Posted
Growth of .ibd and .ibdata files 531 Tiago 504 10/06/2016 08:38AM
Re: Growth of .ibd and .ibdata files 352 Rick James 10/08/2016 12:00PM
Re: Growth of .ibd and .ibdata files 297 Peter Brawley 10/08/2016 06:18PM
Re: Growth of .ibd and .ibdata files 388 Tiago 504 10/11/2016 10:36AM
Re: Growth of .ibd and .ibdata files 251 Rick James 10/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.