Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 44.6Mb
PDF (A4) - 44.7Mb
PDF (RPM) - 40.5Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 205.6Kb
Man Pages (Zip) - 308.9Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

15.6.3.1 The System Tablespace

The InnoDB system tablespace is the storage area for the doublewrite buffer and the change buffer. The system tablespace also contains table and index data for user-created tables created in the system tablespace. In previous releases, the system tablespace contained the InnoDB data dictionary. In MySQL 8.0, InnoDB stores metadata in the MySQL data dictionary. See Chapter 14, MySQL Data Dictionary.

The system tablespace can have one or more data files. By default, one system tablespace data file, named ibdata1, is created in the data directory. The size and number of system tablespace data files is controlled by the innodb_data_file_path startup option. For related information, see System Tablespace Data File Configuration.

Resizing the System Tablespace

This section describes how to increase or decrease the size of the InnoDB system tablespace.

Increasing the Size of the InnoDB System Tablespace

The easiest way to increase the size of the InnoDB system tablespace is to configure it from the beginning to be auto-extending. Specify the autoextend attribute for the last data file in the tablespace definition. Then InnoDB increases the size of that file automatically in 64MB increments when it runs out of space. The increment size can be changed by setting the value of the innodb_autoextend_increment system variable, which is measured in megabytes.

You can expand the system tablespace by a defined amount by adding another data file:

  1. Shut down the MySQL server.

  2. If the previous last data file is defined with the keyword autoextend, change its definition to use a fixed size, based on how large it has actually grown. Check the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify this rounded size explicitly in innodb_data_file_path.

  3. Add a new data file to the end of innodb_data_file_path, optionally making that file auto-extending. Only the last data file in the innodb_data_file_path can be specified as auto-extending.

  4. Start the MySQL server again.

For example, this tablespace has just one auto-extending data file ibdata1:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

Suppose that this data file, over time, has grown to 988MB. Here is the configuration line after modifying the original data file to use a fixed size and adding a new auto-extending data file:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

When you add a new data file to the system tablespace configuration, make sure that the filename does not refer to an existing file. InnoDB creates and initializes the file when you restart the server.

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 your InnoDB tables, including InnoDB tables located in the MySQL database.

    mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
    +---------------------------+
    | TABLE_NAME                |
    +---------------------------+
    | columns_priv              |
    | component                 |
    | db                        |
    | default_roles             |
    | engine_cost               |
    | func                      |
    | global_grants             |
    | gtid_executed             |
    | help_category             |
    | help_keyword              |
    | help_relation             |
    | help_topic                |
    | innodb_dynamic_metadata   |
    | innodb_index_stats        |
    | innodb_table_stats        |
    | plugin                    |
    | procs_priv                |
    | proxies_priv              |
    | role_edges                |
    | server_cost               |
    | servers                   |
    | slave_master_info         |
    | slave_relay_log_info      |
    | slave_worker_info         |
    | tables_priv               |
    | time_zone                 |
    | time_zone_leap_second     |
    | time_zone_name            |
    | time_zone_transition      |
    | time_zone_transition_type |
    | user                      |
    +---------------------------+
  2. Stop the server.

  3. Remove all the existing tablespace files (*.ibd), including the ibdata and ib_log files. Do not forget to remove *.ibd files for tables located in the MySQL database.

  4. Configure a new tablespace.

  5. Restart the server.

  6. Import the dump files.

Note

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

Using Raw Disk Partitions for the System Tablespace

You can use raw disk partitions as data files in the InnoDB system tablespace. This technique enables nonbuffered I/O on Windows and on some Linux and Unix systems without file system overhead. Perform tests with and without raw partitions to verify whether this change actually improves performance on your system.

When you use 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 you run the server as the mysql user, the partition must be readable and writeable by mysql. If you run 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.7, “Using Option Files”.

Allocating a Raw Disk Partition on Linux and Unix Systems
  1. When you create a new data file, specify the keyword newraw immediately after the data file size for the innodb_data_file_path option. 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.

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
  2. Restart the server. InnoDB notices the newraw keyword and initializes the new partition. However, do not create or change any InnoDB tables yet. Otherwise, when you next restart the server, InnoDB reinitializes the partition and your changes are lost. (As a safety measure InnoDB prevents users from modifying data when any partition with newraw is specified.)

  3. After InnoDB has initialized the new partition, stop the server, change newraw in the data file specification to raw:

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw
  4. 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.

  1. When you create a new data file, specify the keyword newraw immediately after the data file size for the innodb_data_file_path option:

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=//./D::10Gnewraw

    The //./ corresponds to the Windows syntax of \\.\ for accessing physical drives. In the example above, D: is the drive letter of the partition.

  2. Restart the server. InnoDB notices the newraw keyword and initializes the new partition.

  3. After InnoDB has initialized the new partition, stop the server, change newraw in the data file specification to raw:

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=//./D::10Graw
  4. Restart the server. InnoDB now permits changes to be made.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.