Documentation Home
MySQL 9.3 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 261.1Kb
Man Pages (Zip) - 368.3Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


17.6.3.1 The System Tablespace

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.

The system tablespace can have one or more data files. By default, a single system tablespace data file, namedibdata1, is created in the data directory. The size and number of system tablespace data files is defined by theinnodb_data_file_path startup option. For configuration information, seeSystem Tablespace Data File Configuration.

Additional information about the system tablespace is provided under the following topics in the section:

Resizing the System Tablespace

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 theautoextend attribute for the last data file in theinnodb_data_file_path setting, and restart the server. For example:

innodb_data_file_path=ibdata1:10M:autoextend

When theautoextend attribute is specified, the data file automatically increases in size by 8MB increments as space is required. Theinnodb_autoextend_increment variable controls the increment size.

You can also increase system tablespace size by adding another data file. To do so:

  1. Stop the MySQL server.

  2. If the last data file in theinnodb_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.

  3. Append a new data file to theinnodb_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.

  4. 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 theinnodb_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.

Note

You cannot increase the size of an existing system tablespace data file by changing its size attribute. For example, changing theinnodb_data_file_path setting fromibdata1:10M:autoextend toibdata1:12M:autoextend produces the following error when starting the server:

[ERROR] [MY-012263] [InnoDB] The Auto-extending innodb_systemdata file './ibdata1' is of a different size 640 pages (rounded down to MB) thanspecified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!

The error indicates that the existing data file size (expressed inInnoDB pages) is different from the data file size specified in the configuration file. If you encounter this error, restore the previousinnodb_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, seeSection 17.18.1, “InnoDB Backup”.

For information about configuring data files for a new system tablespace. SeeSystem 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 anInnoDB 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, seeSection 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. SeeSection 17.6.3.3, “General Tablespaces”.

Using Raw Disk Partitions for the System Tablespace

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 themysql user, the partition must be readable and writeable bymysql. If running the server with the--memlock option, the server must be run asroot, so the partition must be readable and writeable byroot.

The procedures described below involve option file modification. For additional information, seeSection 6.2.2.2, “Using Option Files”.

Allocating a Raw Disk Partition on Linux and Unix Systems

  1. To use a raw device for a new server instance, first prepare the configuration file by settinginnodb_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 inInnoDB is 1024 × 1024 bytes, whereas 1MB in disk specifications usually means 1,000,000 bytes.

  2. 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.

  3. 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 theinnodb_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.