Rechercher dans le manuel MySQL

15.6.3.1 The System Tablespace

Inhaltsverzeichnis

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.

Inhaltsverzeichnis 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:

  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.

Inhaltsverzeichnis 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:

  1. 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. +---------------------------+

  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.

Inhaltsverzeichnis 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”.

Inhaltsverzeichnis Haut

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.

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

  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.


Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-innodb-system-tablespace.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut