Mysql innodb engine's shared and independent tablespaces
For innodb data storage files, we must first solve two conceptual problems: Shared tablespace and exclusive tablespace. (The innodb engine and MYISAM engine are very different. In particular, its data storage methods .)
1. Introduction to shared and exclusive tablespaces
Shared and exclusive tablespaces are used for data storage.
Shared tablespace: All table data of each database is stored in one file. By default, the file path of the shared tablespace is in the data directory. The default file name is: ibdata1 initialized to 10 M.
Exclusive tablespace: each table is generated and stored in an independent file. Each table has one. frm table description file. ibd file (this file includes the data content and index content of a separate table ).
2. Differences between a shared tablespace and an exclusive tablespace
Shared tablespace:
Advantages:
1) The tablespace can be divided into multiple files and stored on each disk (the tablespace file size is not limited by the table size. For example, a table can be distributed on different files ). Table data and table description are put together for convenient management.
Disadvantages:
1) all the data and indexes are stored in one file, and there will be a very large file. Although a large file can be divided into multiple small files, however, multiple tables and indexes are stored in a table space in a hybrid manner. After a large number of deletion operations are performed on a table, there will be a lot of gaps in the table space, especially for statistical analysis, applications such as the log system do not use the most shared tablespace.
Set innodb_file_per_table = 1 in the configuration file (my. cnf ):
Advantages:
1) Each table has its own independent tablespace.
2) data and indexes of each table are stored in its own tablespace.
3) a single table can be moved across different databases.
4) space can be recycled (except for the drop table operation, the table cannot be recycled if it is empty ):
The Drop table operation automatically recycles tablespaces. For statistical analysis or log tables, you can use alter table TableName engine = innodb to delete a large amount of data and reduce unnecessary space.
The use of turncate table for innodb-plugin will also shrink the space.
No matter how you delete a table that uses an independent tablespace, The tablespace fragments will not seriously affect the performance, and there is a chance to process them.
5) the efficiency and performance of using an exclusive tablespace are higher.
Disadvantages:
1) The increase in a single table is too large, such as more than 100 GB.
3. Conversion between shared and exclusive tablespaces
Modify the exclusive empty tablespace configuration. The following parameters must be added together.
Innodb_data_home_dir = "/usr/local/mysql/var/" directory where database files are stored
Innodb_log_group_home_dir = "/usr/local/mysql/var" log storage directory
Innodb_data_file_path = ibdata1: 10 M: autoextend: configure a single file (shared data file) with an expandable size of 10 MB, named ibdata1. No file location is provided, so the default location is in the MySQL data directory (such as/db/mysql/ibdata1 ).
Innodb_file_per_table = 1 whether to use shared and exclusive tablespaces (1 indicates using exclusive tablespaces, and 0 indicates using shared tablespaces)
Innodb_file_per_table is converted using this parameter. If it is OFF, the table space is shared. [by default, the table space used is shared]
The innodb_file_per_table value can be modified, but it does not affect the previously used shared tablespace unless it is manually modified or
Note:
InnoDB does not create a directory. Therefore, before starting the server, make sure that the "configured path directory" exists.
When porting and backing up data, pay attention to the integrity of data files.