15.6.3.1 The System Tablespace

Contents

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.

Contents Haut

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:

Shut down the MySQL server.
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.
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.
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.

Contents Haut

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:

Use mysqldump to dump all your InnoDB tables, including InnoDB tables located in the MySQL database.
1. mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
2. +---------------------------+
3. | TABLE_NAME |
4. +---------------------------+
5. | columns_priv |
6. | component |
7. | db |
8. | default_roles |
9. | engine_cost |
10. | func |
11. | global_grants |
12. | gtid_executed |
13. | help_category |
14. | help_keyword |
15. | help_relation |
16. | help_topic |
17. | innodb_dynamic_metadata |
18. | innodb_index_stats |
19. | innodb_table_stats |
20. | plugin |
21. | procs_priv |
22. | proxies_priv |
23. | role_edges |
24. | server_cost |
25. | servers |
26. | slave_master_info |
27. | slave_relay_log_info |
28. | slave_worker_info |
29. | tables_priv |
30. | time_zone |
31. | time_zone_leap_second |
32. | time_zone_name |
33. | time_zone_transition |
34. | time_zone_transition_type |
35. | user |
36. +---------------------------+
Stop the server.
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.
Configure a new tablespace.
Restart the server.
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.

Contents Haut

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.2.2, “Using Option Files”.

Contents Haut

Allocating a Raw Disk Partition on Linux and Unix Systems

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
```
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.)
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
```
Restart the server. InnoDB now permits changes to be made.

Contents Haut

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.

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.
Restart the server. InnoDB notices the newraw keyword and initializes the new partition.
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
```
Restart the server. InnoDB now permits changes to be made.

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-innodb-system-tablespace.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut