Mysql innodb engine's shared and independent tablespaces, mysqlinnodb

Source: Internet
Author: User

Mysql innodb engine's shared and independent tablespaces, mysqlinnodb

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.



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.