MySQL InnoDB shared tablespaces and stand-alone table spaces

Shared table Spaces

All the table data of a database, the index file is placed in a file, the default file path for this shared tablespace is in the data directory. The default file name is ibdata1 , initialized to 10M .

Because it is the default way, it is understood as the Mysql official recommended way. Relative to all the data in one (or a few) files, more conducive to management, and in the operation of the time only need open this one (or a few) files, relatively low cost. But the problem is that it reverses when the data is calculated as a G unit. A file that is too large is very bad for management, and for a file of this magnitude, it takes as much resources to read and write it. What's even more puzzling is that the INNODB engine keeps the index and data in the same file, and there is a resource contention between the index and the data, which is not conducive to performance improvement. Of course you can innodb_data_file_path plan multiple tablespace files through the configuration, but MySQL the logic is "add after full", just a split of a file, cannot fundamentally separate data and indexes.

Exclusive table Space

Each table will be stored in a separate file, with each table having a .frm table description file and a .ibd file. This file includes the data content of a single table and the contents of the index.

Advantages and disadvantages of shared tablespace and stand-alone table space share table space pros and cons

• Advantages

You can divide a tablespace into multiple files on separate disks (the tablespace file size is not limited by the size of the table, such as a table that can be distributed on a file that is not in sync). Data and documents are easily managed together.

• Disadvantages

Storing all the data and indexes in a file means that there will be a large file, although a large file can be divided into small files, but multiple tables and indexes are stored in the table space, so there will be a lot of voids in the table space after a large number of deletions for a table, especially for statistical analysis, Such applications as day-value systems are most unsuitable for sharing table spaces.

Shared tablespace management can cause problems that cannot be retracted after a tablespace is allocated, and when a temporary index is created or a temporary table is expanded, it is not possible to delete the related table or to shrink that part of the space. In the case of disk monitoring, the alarm may be constant, but it MySQL can actually work well. Also, performance is not as good when the disk is occupied. This situation can only be done by building a new one out of the Slave Main Library, Dump and then moving in Dump from the library, the action is larger. For InnoDB Hot Backup backup operations (or direct cold), the files that need to be copied are larger each time. If there is a 180G table space now, but the actual data is only more than 50 G , then we will face each need to copy 180G the data.

Advantages and disadvantages of stand-alone table spaces

• Advantages

Each table has a self-contained table space. The data and indexes for each table will exist in the table space themselves.
You can implement a single table to move through different databases. The space can be recycled ( drop table in addition to the operation, the table space cannot be recovered).
Drop tableThe table space is automatically reclaimed by the operation, and if you delete large amounts of data for statistical analysis or a daily value table, you can go through: alter table TableName engine=innodb ;

The innodb-plugin use of the Make Innodb turncate table also shrinks the space.
For tables that use stand-alone table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too severely, and there is a chance to process it.

• Disadvantages

Single table increases too large, such as more than 100 G .

Note: When a stand-alone tablespace is ibdata1 in a file, the dictionary information and pages are stored undo . Therefore, backup files are still required for cold backup ibdata1 . If the ibdata1 file is missing, the innodb table does not exist, but the data file and the table structure are in fact present.

InnoDB shared tablespace to stand-alone table space

1. View the current table space situation:

like‘%per_table‘;

variable_name	Value
Innodb_file_per_table	OFF

1 row in Set (0.00 sec)

Indicates that the table space is currently shared.

You can also learn by observing a file that contains a table of type InnoDB:

[[email protected] Idx]# pwd/home/mysql/data/Idx[[email protected] Idx]# lsalbum.frm  artist.frm   db.opt  Track.frm  Track.MYD  Track.MYI

There are two ways to convert a shared table space into a stand-alone table space:

1. Logical backup first, then modify the my.cnf parameter parameters in the configuration file innodb_file_per_table is 1, restart the service after the logical backup import.

2. Modify my.cnf The parameter parameter in the configuration file innodb_file_per_table to 1, and after restarting the service, all the tables that need to be modified innodb are executed once:alter table table_name engine=innodb;

With the second method modified, the data in the table in the original library will continue to be stored ibdata1 in, and the new table will use a separate table space.

查看当前的数据文件为：[[email protected] Idx]# lsalbum.frm  album.ibd  artist.frm  artist.ibd  db.opt  Track.frm  Track.MYD  Track.MYI这样就已经将共享表空间转化为独立表空间。

MySQL InnoDB shared tablespaces and stand-alone table spaces