15.6.3.1 The System Tablespace

Table des matières

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:

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.

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.

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

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.

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.

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-innodb-system-tablespace.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

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

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut