InnoDB "autoextend" function

Question

I'm very confused about use of InnoDB. I've a server with limited storage and I've read that InnoDB tables creates default size table at 10MB autoextend.

I don't understand "autoextend" function. If I exceed 10MB for one table, MySQL automatically increase memory (10Mb + 10MB)?

So, can I change default limit for any table?

For example:

• Table1 = default 10MB;
• Table2 = default 0.5MB;
• etc...

is it possible?

Answer 1

Updated for MySQL 8.0

If you don't enable innodb_file_per_table or specify a tablespace for a table explicitly, InnoDB data will be stored in a set of files called a system tablespace.

Latest versions of MySQL enable innodb_file_per_table by default.

https://dev.mysql.com/doc/refman/8.0/en/innodb-file-per-table-tablespaces.html

InnoDB creates tables in file-per-table tablespaces by default. This behavior is controlled by the innodb_file_per_table variable. Disabling innodb_file_per_table causes InnoDB to create tables in the system tablespace.

When this fileset has no free space left, the size of the last file in the set is increased in chunks specified by the autoextend parameter, which is 8MB by default.

InnoDB will only grow the last file in the set.

If you use innodb_file_per_table then each table uses its own tablespace. It is always stored in one file (residing in the db directory).

This file grows in chunks of configurable size, which by default is 4M.

In latest versions of MySQL, you can also create tablespaces explicitly and assign tables to them. Tablespaces that you create explicitly are called general tablespaces. You can configure the auto-extend option for them as well.

https://dev.mysql.com/doc/refman/8.0/en/innodb-file-per-table-tablespaces.html

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.