MySQL InnoDB shared tablespace and independent tablespace

MySQL InnoDB shared tablespace and independent tablespace

Shared tablespace

All the table data of a database is stored in one file, and the file path of the shared tablespace is under the data directory by default. The default file name is ibdata1 and the initialization is 10 MB.

Because it is the default method, it is understood as the officially recommended Mysql method for the moment. All data is relatively stored in one (or several) file, which is easier to manage. You only need to open this (or several) file during operations, relatively low cost. However, the problem is that when the data reaches G, the advantages and disadvantages are reversed. A large file is not conducive to management, and for such a huge file, reading and writing it requires a huge amount of resources. Even more puzzling, the INNODB Engine stores indexes and data in the same file, and there is still resource contention between indexes and data, which is not conducive to performance improvement. Of course, you can plan multiple tablespace files through the configuration of innodb_data_file_path. However, the MySQL logic is "Add after use", which is only a file split and cannot fundamentally separate data and indexes.

Exclusive tablespace

Each table is stored in an independent file. Each table has a. frm table description file and A. ibd file. This file includes the data content and index content of a separate table.

Advantages and disadvantages of shared and independent tablespaces Advantages and Disadvantages of shared tablespaces

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 files that are not synchronized ). Put data and files together for convenient management.

Disadvantages

Storing all the data and indexes in one file means that there will be a 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 daily value system do not use the most shared tablespace.

In shared tablespace management, the tablespace cannot be reduced after it is allocated. When a temporary index is created or a temporary table is created, that is, you cannot shrink the space of a table after deleting it. During disk monitoring, alarms may continue, but MySQL can still run well. In addition, when the disk occupies a large amount of space, the performance is not very good. In this case, you can only create a new Slave to Dump it from the master database and then Dump it to the Slave database. For InnoDB Hot Backup operations (or direct cold Backup), each copy is large. If there are currently GB tablespaces, but the actual data is only 50 GB, we will face the need to copy GB of data each time.

Advantages and disadvantages of independent tablespace

Each table has its own independent tablespace. Each table's data and indexes are stored in its own tablespace.
A single table can be moved across different databases. Space can be recycled (except for the drop table operation, the tablespace cannot be recycled by itself ).
The Drop table operation automatically recycles tablespaces. For statistical analysis or daily value 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.

Disadvantages

The increase in a single table is too large, for example, more than 100 GB.

Note: When an independent tablespace is created, the ibdata1 File Stores dictionary information and undo pages. Therefore, you still need to back up the ibdata1 file during cold backup. If the ibdata1 file is lost, the innodb table does not exist, but its data file and table structure exist.

Innodb shared tablespace is converted into independent tablespace

1. view the current tablespace:

mysql> show variables like '%per_table';

Variable_name	Value
innodb_file_per_table	OFF

1 row in set (0.00 sec)

It indicates that the table space is currently shared.

You can also observe the files that contain innodb tables:

[root@localhost Idx]# pwd/home/mysql/data/Idx[root@localhost Idx]# lsalbum.frm artist.frm db.opt Track.frm Track.MYD Track.MYI

There are two ways to convert a shared tablespace to an independent tablespace:

Perform logical backup first, and then modify the parameter innodb_file_per_table in the configuration file my. cnf to 1. Restart the service and import the logical backup.

Modify the parameter innodb_file_per_table in the configuration file my. cnf to 1. After the service is restarted, execute all innodb tables to be modified: alter table table_name engine = innodb;

After the second method is used, the data in the table in the original database will be stored in ibdata1, and the new table will use the independent tablespace.

View the current data file: [root @ localhost Idx] # lsalbum. frm album. ibd artist. frm artist. ibd db. opt Track. frm Track. MYD Track. in this way, MYI has converted the shared tablespace into an independent tablespace.