How InnoDB fill factor affect MySQL performance?
InnoDB fill factor is a configuration parameter that controls how much space is reserved in the leaf pages of a B-tree index when it is created or rebuilt. The fill factor is specified as a percentage of the leaf page size, and it controls the amount of free space that will be left in each leaf page when the index is created or rebuilt. The fill factor can affect MySQL performance in the following ways:
- Disk Space Usage: A higher fill factor will result in more free space on the leaf pages, which will reduce the number of pages needed to store the index. This can result in lower disk space usage and less I/O for index-related operations.
- Insert Performance: A lower fill factor will result in more free space on the leaf pages, which will reduce the number of page splits needed when new records are inserted. This can result in better insert performance, as fewer page splits mean less I/O and less contention for the index.
- Read Performance: A higher fill factor will result in fewer pages needed to store the index, which can result in better read performance, as fewer pages need to be read to access the same amount of data.
- Update Performance: A lower fill factor will result in more free space on the leaf pages, which can help to reduce the number of page splits needed when records are updated. This can result in better update performance, as fewer page splits mean less I/O and less contention for the index.