The system tablespace is the storage area for the change buffer.
It may also contain table and index data if tables are created in
the system tablespace rather than file-per-table or general
tablespaces. In previous MySQL versions, the system tablespace
contained the InnoDB
data dictionary. In MySQL
8.0, InnoDB
stores metadata in the
MySQL data dictionary. See Chapter 16, MySQL Data Dictionary. In
previous MySQL releases, the system tablespace also contained the
doublewrite buffer storage area. This storage area resides in
separate doublewrite files as of MySQL 8.0.20. See
Section 17.6.4, “Doublewrite Buffer”.
The system tablespace can have one or more data files. By default,
a single system tablespace data file, named
ibdata1
, is created in the data directory.
The size and number of system tablespace data files is defined by
the innodb_data_file_path
startup
option. For configuration information, see
System Tablespace Data File Configuration.
Additional information about the system tablespace is provided under the following topics in the section:
This section describes how to increase or decrease the size of the system tablespace.
Increasing the Size of the System Tablespace
The easiest way to increase the size of the system tablespace is
to configure it to be auto-extending. To do so, specify the
autoextend
attribute for the last data file
in the innodb_data_file_path
setting, and restart the server. For example:
innodb_data_file_path=ibdata1:10M:autoextend
When the autoextend
attribute is specified,
the data file automatically increases in size by 8MB increments
as space is required. The
innodb_autoextend_increment
variable controls the increment size.
You can also increase system tablespace size by adding another data file. To do so:
Stop the MySQL server.
If the last data file in the
innodb_data_file_path
setting is defined with theautoextend
attribute, remove it, and modify the size attribute to reflect the current data file size. To determine the appropriate data file size to specify, check your file system for the file size, and round that value down to the closest MB value, where a MB is equal to 1024 x 1024 bytes.Append a new data file to the
innodb_data_file_path
setting, optionally specifying theautoextend
attribute. Theautoextend
attribute can be specified only for the last data file in theinnodb_data_file_path
setting.Start the MySQL server.
For example, this tablespace has one auto-extending data file:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
Suppose that the data file has grown to 988MB over time. This is
the innodb_data_file_path
setting after modifying the size attribute to reflect the
current data file size, and after specifying a new 50MB
auto-extending data file:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
When adding a new data file, do not specify an existing file
name. InnoDB
creates and initializes the new
data file when you start the server.
You cannot increase the size of an existing system tablespace
data file by changing its size attribute. For example,
changing the
innodb_data_file_path
setting
from ibdata1:10M:autoextend
to
ibdata1:12M:autoextend
produces the
following error when starting the server:
[ERROR] [MY-012263] [InnoDB] The Auto-extending innodb_system
data file './ibdata1' is of a different size 640 pages (rounded down to MB) than
specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!
The error indicates that the existing data file size
(expressed in InnoDB
pages) is different
from the data file size specified in the configuration file.
If you encounter this error, restore the previous
innodb_data_file_path
setting, and refer to the system tablespace resizing
instructions.
Decreasing the Size of the InnoDB System Tablespace
Decreasing the size of an existing system tablespace is not supported. The only option to achieve a smaller system tablespace is to restore your data from a backup to a new MySQL instance created with the desired system tablespace size configuration.
For information about creating backups, see Section 17.18.1, “InnoDB Backup”.
For information about configuring data files for a new system tablespace. See System Tablespace Data File Configuration.
To avoid a large system tablespace, consider using
file-per-table tablespaces or general tablespaces for your data.
File-per-table tablespaces are the default tablespace type and
are used implicitly when creating an InnoDB
table. Unlike the system tablespace, file-per-table tablespaces
return disk space to the operating system when they are
truncated or dropped. For more information, see
Section 17.6.3.2, “File-Per-Table Tablespaces”. General
tablespaces are multi-table tablespaces that can also be used as
an alternative to the system tablespace. See
Section 17.6.3.3, “General Tablespaces”.
Raw disk partitions can be used as system tablespace data files. This technique enables nonbuffered I/O on Windows and some Linux and Unix systems without file system overhead. Perform tests with and without raw partitions to verify whether they improve performance on your system.
When using a raw disk partition, ensure that the user ID that
runs the MySQL server has read and write privileges for that
partition. For example, if running the server as the
mysql
user, the partition must be readable
and writeable by mysql
. If running the server
with the --memlock
option, the
server must be run as root
, so the partition
must be readable and writeable by root
.
The procedures described below involve option file modification. For additional information, see Section 6.2.2.2, “Using Option Files”.
Allocating a Raw Disk Partition on Linux and Unix Systems
To use a raw device for a new server instance, first prepare the configuration file by setting
innodb_data_file_path
with theraw
keyword. For example:[mysqld] innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw
The partition must be at least as large as the size that you specify. Note that 1MB in
InnoDB
is 1024 × 1024 bytes, whereas 1MB in disk specifications usually means 1,000,000 bytes.Then initialize the server for the first time by using
--initialize
or--initialize-insecure
. InnoDB notices theraw
keyword and initializes the new partition, and then it stops the server.Now restart the server.
InnoDB
now permits changes to be made.
Allocating a Raw Disk Partition on Windows
On Windows systems, the same steps and accompanying guidelines
described for Linux and Unix systems apply except that the
innodb_data_file_path
setting
differs slightly on Windows. For example:
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=//./D::10Graw
The //./
corresponds to the Windows syntax
of \\.\
for accessing physical drives. In
the example above, D:
is the drive letter of
the partition.