Movatterモバイル変換

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, seeSection 17.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 theAUTOEXTEND_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 theAUTOEXTEND_SIZE size in aCREATE TABLE orALTER 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 theAUTOEXTEND_SIZE size in aCREATE TABLESPACE orALTER TABLESPACE statement:

CREATE TABLESPACE ts1 AUTOEXTEND_SIZE = 4M;

ALTER TABLESPACE ts1 AUTOEXTEND_SIZE = 8M;

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

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

The maximum allowedAUTOEXTEND_SIZE is 4GB. The maximum tablespace size is described atSection 17.22, “InnoDB Limits”.

The minimumAUTOEXTEND_SIZE setting depends on theInnoDB page size, as shown in the following table:

The defaultInnoDB page size is 16K (16384 bytes). To determine theInnoDB page size for your MySQL instance, query theinnodb_page_size setting:

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

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

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

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

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

To determine theAUTOEXTEND_SIZE for anyInnoDB tablespace, query the Information SchemaINNODB_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 |+------+-----------------+