Mysql InnoDB shared tablespaces and stand-alone table spaces

It is always customary to compare with Oracle database when learning MySQL. When learning the storage structure of MySQL InnoDB, it is also unavoidable to compare with Oracle. Oracle's data storage is table space, segments, blocks, chunks, data files; MySQL InnoDB storage management is similar, but MySQL adds a concept of shared tablespace and stand-alone table space;

First, the concept

Shared tablespace: All of InnoDB's data is stored in a single tablespace, and the tablespace can consist of many files, a table can exist across multiple files, so its size limit is no longer a file size limit, but its own limitations. As you can see from InnoDB's official documentation, the maximum table space limit is 64TB, that is, InnoDB's single-table limit is basically around 64TB, and of course this size is all other relevant data including all the indexes of this table.

Stand-alone table space:

Second, view the table space of the database

Mysql> Show variables like ' innodb_data% ';

L table space has four files: ibdata1, Ibdata2, Ibdata3, IBDATA4, each file size is 10M, when each file is full, IBDATA4 will automatically expand;

When the current storage space is full, you can add data files to other disks as follows: The syntax is as follows:

Pathtodatafile:sizespecification;pathtodatafile:sizespec;.; Pathtodatafile:sizespec[:autoextend[:max:sizespecification]]

If the last data file is described with the Autoextend option, the last data file is automatically expanded when InnoDB runs out of all table free space, with 8 MB increments per increment. Example:

Both the shared tablespace and the stand-alone table space have innodb_data_file files, because these files not only hold data, but also act as some roles like the Undo tablespace for Oracle.

Iii. advantages and disadvantages of shared table spaces

Since InnoDB has two types of shared tablespace and stand-alone tablespace, it is reasonable that both of these table spaces exist, sometimes in their own application scenarios. Here are some excerpts from the official MySQL:

3.1 Advantages of shared table spaces

Table spaces can be partitioned into multiple files, so tables can be split into multiple files on disk, and the size of the table is not limited by the size of the disk (many documents describe a problem).

Data and documents are easily managed together.

3.2 Disadvantages of shared table spaces

All the data and indexes are stored in a file, although a large file can be divided into small files, but multiple tables and indexes in the table space mixed storage, when the amount of data is very large, the table after a large number of delete operations in the table space will have a large number of gaps, especially for statistical analysis, This type of application is most inappropriate for shared table spaces for frequently deleted operations.

Cannot retract after a shared tablespace allocation: When a temporary index is created or a temporary table's table space is expanded, it is not possible to delete the related table or to shrink that part of the space (it can be understood as Oracle Tablespace 10G, but only 10M is used. However, the operating system shows that the table space of MySQL is 10G), and the cold standby of the database is very slow;

Iv. advantages and disadvantages of independent table spaces

4.1 Advantages of a standalone table space

Each table has its own independent tablespace, and each table's data and indexes exist in its own table space, enabling a single table to move in different databases.

Space can be recycled (except for the drop table operation, the meter is not able to recycle)

The drop table operation automatically reclaims the tablespace, and if you delete large amounts of data for statistical analysis or a daily value table, you can pass: ALTER TABLE TableName ENGINE=INNODB;

The use of Turncate table for Innodb-plugin InnoDB 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.

4.2 Disadvantages of the stand-alone table space

Single table increase is too large, when the single table occupied space is too large, storage space is not enough, only from the operating system level to think about the solution;

V. Conversion between shared tablespace and stand-alone table space

5.1 View the table space management type for the current database

Script: Show variables like "innodb_file_per_table";

Mysql> Show variables like "innodb_file_per_table";

On stands for independent table space management, off for shared table space management, (view table space management for single table, need to see if there is a separate data file for each table)

5.2 Modifying the table space management of a database

Modify the parameter values of the innodb_file_per_table, but the modifications do not affect the shared table space and the stand-alone table spaces that have been used before;

Innodb_file_per_table=1 to use exclusive tablespace

Innodb_file_per_table=0 to use shared table spaces

5.3 Ways to convert shared tablespace to stand-alone table space (parameter innodb_file_per_table=1 required)
Single table conversion operation, script: ALTER TABLE table_name ENGINE=INNODB;

When there are a large number of tables need to operate, first export the database, and then delete the data before the import operation, the operation can be operated with mysqldump (HTTP://BLOG.ITPUB.NET/12679300/VIEWSPACE-1259451/)

Summary: After the above operation to complete the database storage space conversion, to understand the technology is to better use technology, when the amount of data is very small, we recommend the use of shared table space management method. When the amount of data is large, it is recommended to use a stand-alone tablespace management method.

Reprinted from: http://blog.itpub.net/12679300/viewspace-1379902/

Mysql InnoDB shared tablespaces and stand-alone table spaces