MySQL InnoDB engine shared tablespace and standalone table space (reprinted)

Ps:innodb This engine is very different from the MyISAM engine. In particular, its data storage format. For the data structure of INNODB, there are two conceptual issues to be solved first: shared tablespace and exclusive table space.

1. What is a shared table space and exclusive tablespace

Shared tablespace and exclusive tablespace are all data stores for InnoDB tables, ibdata1 data files for InnoDB engines, IB_LOGFILE0 and ib_logfile1 log files for InnoDB engines
Shared tablespace: The InnoDB table (data, index) of all databases in the MySQL server is placed in a single file, the file path of the shared tablespace is in the data directory by default. The default file name is: Ibdata1 is initialized to 10M.
Exclusive tablespace: Each table will be generated in a separate file way for storage, each table has a. frm table description file, and an. ibd file. This file includes the data content of a single table and the index content, which, by default, is stored in the table's location.

2. Advantages and disadvantages between the two

2. shared tablespace:

   Pros:
   InnoDB in shared tablespace mode, is multi-file supported, configurable with the Innodb_data_file_path option:
   Innodb_data_file_ Path =/disk1/ibdata1:2g;/disk2/ibdata2:2g:autoextend
   This configuration will spread the data files in the Disk1 and Disk2 two paths, the first file fixed 2G size, the second file initialized 2G, Can self-grow. (data file size is not limited by the size of the table, such as a table can be distributed across different data files). Data and files are easily managed together.
   Cons:
   1. Storing all the data and indexes in a file means that there will be a very large file, although a large file can be divided into small files, but the InnoDB tables and indexes in all the libraries on the server are stored in the table space. In this way, there will be a large number of voids in the table space after a large number of deletions for a table, especially for statistical analysis, such applications are most unsuitable for shared table spaces.
   2. Shared tablespace does not shrink! Ibdata1 will only kept the growth. For a server above n libraries and there is a deletion of the library of the situation is depressed!

4. Stand-alone table space:

   Set in configuration file (my.cnf): innodb_file_per_table

   Advantages:
   1. Each table has a self-contained table space.
   2. The data and indexes for each table will exist in the table space themselves.
   3. You can implement a single table to move through different databases.
   4. Space can be recycled (except for the drop table operation, the meter is not able to recycle)
   A) The drop table operation automatically reclaims the tablespace, if for statistical analysis or a daily value table, delete a large amount of data can pass: ALTER TABLE TableName ENGINE=INNODB;
   b) The use of Turncate table for Innodb-plugin InnoDB also shrinks the space.
   c) For tables that use stand-alone table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too much and there is a chance to process it.
   Disadvantages:
   Single table increased too large, such as more than 100 g.
   For single-table growth problems, if using shared tablespace can separate files, but there is also a problem, if the scope of access is too large to access multiple files, it will be slower. There is also a workaround for stand-alone table spaces: using partitioned tables, You can also move that large table space to a different space and make a connection. In fact, from the performance, when a table more than 100 g may respond is also slower, for the independent table space is also easy to find problems early processing.

In contrast, the efficiency and performance of using exclusive tablespaces is a bit higher, and the reason for using a stand-alone table space is:

1. Compare shared table spaces and stand-alone tablespaces from performance:

   Shared tablespace has few advantages over insert operations. There are no other independent table spaces to perform well. Here's a tips. When you enable a stand-alone table space, make a reasonable adjustment: Innodb_open_files.

2. Starting from Linux system processing:

   File system Fsync A large update of the data, the impact on the system IO. If separated into multiple small data fsync, can reduce the impact on reading. At the same time from the MySQL code, found that MySQL guaranteed two times between fsync at least 20ms sleep, so that if the fsync into multiple small data operations, you should be able to reduce the ratio of slow queries. So the system for a large number of update operations is less suitable for shared tablespace

Turn from (http://wangying.sinaapp.com/archives/1488)

MySQL InnoDB engine shared tablespace and standalone table space (reprinted)