Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
PDF (RPM) - 40.1Mb
HTML Download (TGZ) - 10.1Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.7Mb
Man Pages (TGZ) - 244.6Kb
Man Pages (Zip) - 349.8Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Optimizing Tablespace Space Allocation on Linux

15.6.3.8 Optimizing Tablespace Space Allocation on Linux

As of MySQL 8.0.22, you can optimize how InnoDB allocates space to file-per-table and general tablespaces on Linux. By default, when additional space is required, InnoDB allocates pages to the tablespace and physically writes NULLs to those pages. This behavior can affect performance if new pages are allocated frequently. As of MySQL 8.0.22, you can disable innodb_extend_and_initialize on Linux systems to avoid physically writing NULLs to newly allocated tablespace pages. When innodb_extend_and_initialize is disabled, space is allocated to tablespace files using posix_fallocate() calls, which reserve space without physically writing NULLs.

A posix_fallocate() operation is not atomic, which makes it possible for a failure to occur between allocating space to a tablespace file and updating the file metadata. Such a failure can leave newly allocated pages in an uninitialized state, resulting in a failure when InnoDB attempts to access those pages. To prevent this scenario, InnoDB writes a redo log record before allocating a new tablespace page. If a page allocation operation is interrupted, the operation is replayed from the redo log record during recovery. (A page allocation operation replayed from a redo log record physically writes NULLs to the newly allocated page.) A redo log record is written before allocating a page regardless of the innodb_extend_and_initialize setting.

On non-Linux systems and Windows, InnoDB allocates new pages to the tablespace and physically writes NULLs to those pages, which is the default behavior. Attempting to disable innodb_extend_and_initialize on those systems returns the following error:

Changing innodb_extend_and_initialize not supported on this platform. Falling back to the default.

The AUTOEXTEND_SIZE option, introduced in MySQL 8.0.23, defines the amount of space allocated by posix_fallocate() calls. Allocating space in larger amounts helps avoid fragmentation and facilitates ingestion of large amounts of data. For more information, see Section 15.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”.