Two tablespaces in MySQL InnoDB

To describe the tablespace, MySQL's tablespace management is far from perfect. In other words, MySQL has no real tablespace management at all. MySQL's InnoDB contains two tablespace file modes: the default shared tablespace and the independent tablespace separated by each table. You only need to add innodb_file_per_table = 1 in my. CNF to switch from the shared tablespace to the independent tablespace. Of course, for an existing table, run the alter table my_table engine = InnoDB command to migrate data.

Table space sharing

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. An astonishing file size is not conducive to management, and for such a huge file, reading and writing it consumes as much resources. Even more puzzling is that MySQL 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.

Previously, we encountered tablespace files larger than GB. What is even more depressing is that the number of such large files is still increasing by GB every day. Since it cannot be stopped, it may take almost one night to copy a copy. It can only be seen as it continues to grow and there is no conservative and feasible way.

Independent tablespace mode

Each table in the opposite tablespace has multiple independent data files, and the indexes and data are separated. It is convenient to copy and migrate data across databases or even hardware between multiple small files. Relatively flexible.

This also brings about another problem. When the number of tables in the database reaches a certain level, each operation involves too many files. If the default centos ulimit-N = 1024, only 256 tables can be opened simultaneously, this is difficult to meet the requirements in the MySQL data structure that is used to "split databases and tables. Still, the utilization of such data files is not very high. When a large number of "not high" files are centralized, the waste of space is amazing, what's more, the last possible situation is not "a bunch of K-level small files" but "a bunch of G-level large files", which is somewhat counterproductive. You can naturally think of a partition table as a "just splitting Files". Multiple partition files are indispensable.

I have also encountered this problem before. A large number of timeouts occur when the MySQL connection is large, but the host load is still good. I checked it to find out that it was an open files Limitation Problem, when the limit is modified, the load becomes astonishing, but the number of connections does not increase much.

In short, the two methods have their own strengths and complement each other, but they are not the ultimate solution to the problem. It is expected that MySQL will be able to show the concept of space in the preceding table and plan data files more freely.