MySQL :: MySQL 5.7 Reference Manual :: 14.6.3.1 The System Tablespace (original) (raw)

14.6.3.1 The System Tablespace

The system tablespace is the storage area for theInnoDB data dictionary, the doublewrite buffer, the change buffer, and undo logs. 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 the innodb_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 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. 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 the autoextend 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 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.

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 from ibdata1:10M:autoextend toibdata1: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 previousinnodb_data_file_path setting, and refer to the system tablespace resizing instructions.

Decreasing the Size of the InnoDB System Tablespace

You cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure:

  1. Use mysqldump to dump all of yourInnoDB tables, includingInnoDB tables located in themysql schema. IdentifyInnoDB tables in themysql schema using the following query:
mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';  
+---------------------------+  
| TABLE_NAME                |  
+---------------------------+  
| engine_cost               |  
| gtid_executed             |  
| help_category             |  
| help_keyword              |  
| help_relation             |  
| help_topic                |  
| innodb_index_stats        |  
| innodb_table_stats        |  
| plugin                    |  
| server_cost               |  
| servers                   |  
| slave_master_info         |  
| slave_relay_log_info      |  
| slave_worker_info         |  
| time_zone                 |  
| time_zone_leap_second     |  
| time_zone_name            |  
| time_zone_transition      |  
| time_zone_transition_type |  
+---------------------------+
  1. Stop the server.
  2. Remove all of the existing tablespace files (*.ibd), including theibdata and ib_log files. Do not forget to remove *.ibd files for tables located in the mysql schema.
  3. Remove any .frm files forInnoDB tables.
  4. Configure the data files for the new system tablespace. SeeSystem Tablespace Data File Configuration.
  5. Restart the server.
  6. Import the dump files.

Note

If your databases only use the InnoDB engine, it may be simpler to dumpall databases, stop the server, remove all databases and InnoDB log files, restart the server, and import the dump files.

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, seeSection 14.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 14.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 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 4.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 the raw 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. 2. Then initialize the server for the first time by using--initialize or--initialize-insecure. InnoDB notices the raw 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.