By default, when a file-per-table or general tablespace requires additional space, the tablespace is extended incrementally according to the following rules:

For information about extent size, see Section 15.11.2, “File Space Management”.

From MySQL 8.0.23, the amount by which a file-per-table or general tablespace is extended is configurable by specifying the AUTOEXTEND_SIZE option. Configuring a larger extension size can help avoid fragmentation and facilitate ingestion of large amounts of data.

To configure the extension size for a file-per-table tablespace, specify the AUTOEXTEND_SIZE size in a CREATE TABLE or ALTER TABLE statement:

CREATE TABLE t1 (c1 INT) AUTOEXTEND_SIZE = 4M;

ALTER TABLE t1 AUTOEXTEND_SIZE = 8M;

To configure the extension size for a general tablespace, specify the AUTOEXTEND_SIZE size in a CREATE TABLESPACE or ALTER TABLESPACE statement:

CREATE TABLESPACE ts1 AUTOEXTEND_SIZE = 4M;

ALTER TABLESPACE ts1 AUTOEXTEND_SIZE = 8M;

The AUTOEXTEND_SIZE setting must be a multiple of 4M. Specifying an AUTOEXTEND_SIZE setting that is not a multiple of 4M returns an error.

The AUTOEXTEND_SIZE default setting is 0, which causes the tablespace to be extended according to the default behavior described above.

The maximum AUTOEXTEND_SIZE setting is 64M in MySQL 8.0.23. From MySQL 8.0.24, the maximum setting is 4GB.

The minimum AUTOEXTEND_SIZE setting depends on the InnoDB page size, as shown in the following table:

The default InnoDB page size is 16K (16384 bytes). To determine the InnoDB page size for your MySQL instance, query the innodb_page_size setting:

mysql> SELECT @@GLOBAL.innodb_page_size;
+---------------------------+
| @@GLOBAL.innodb_page_size |
+---------------------------+
|                     16384 |
+---------------------------+

When the AUTOEXTEND_SIZE setting for a tablespace is altered, the first extension that occurs afterward increases the tablespace size to a multiple of the AUTOEXTEND_SIZE setting. Subsequent extensions are of the configured size.

When a file-per-table or general tablespace is created with a non-zero AUTOEXTEND_SIZE setting, the tablespace is initialized at the specified AUTOEXTEND_SIZE size.

ALTER TABLESPACE cannot be used to configure the AUTOEXTEND_SIZE of a file-per-table tablespace. ALTER TABLE must be used.

For tables created in file-per-table tablespaces, SHOW CREATE TABLE shows the AUTOEXTEND_SIZE option only when it is configured to a non-zero value.

To determine the AUTOEXTEND_SIZE for any InnoDB tablespace, query the INFORMATION_SCHEMA.INNODB_TABLESPACES table. For example:

mysql> SELECT NAME, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
WHERE NAME LIKE 'test/t1';
+---------+-----------------+
| NAME    | AUTOEXTEND_SIZE |
+---------+-----------------+
| test/t1 |         4194304 |
+---------+-----------------+

mysql> SELECT NAME, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
WHERE NAME LIKE 'ts1';
+------+-----------------+
| NAME | AUTOEXTEND_SIZE |
+------+-----------------+
| ts1  |         4194304 |
+------+-----------------+