Innodb shared tablespace vs independent tablespace

When using the innodb engine, Innodb shared tablespaces vs independent tablespaces face the management and selection of two tablespaces. Innodb has two ways to manage tablespaces: 1. shared tablespace (you can also split it into multiple small tablespaces) 2. each table has an independent tablespace. I personally recommend using independent tablespaces. In terms of performance and O & M, independent tablespaces have many advantages over shared tablespaces. The following describes the features of two types of table space management. Shared tablespace: You can divide a tablespace into multiple files and store them on each disk (the tablespace file size is not limited by the table size, for example, a table can be distributed on non-synchronized files ). Put data and files together for convenient management. Disadvantages: all the data and indexes of www.2cto.com are stored in one file to store 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 daily value system do not use the most shared tablespace. We know that shared tablespace management will cause the problem that the tablespace cannot be reduced after it is allocated. When a temporary index is created or a temporary table is created to expand the tablespace, that is, you cannot shrink the space of a table after deleting it. When we have disk monitoring, alarms may continue, but MySQL can still run well. In addition, when the disk occupies a large amount of space, the performance is not very good. In this case, you can only create a new Slave to Dump it from the master database and then Dump it to the Slave database. For InnoDB Hot Backup operations (or direct cold Backup), CP files are required each time. If there are currently GB tablespaces, but the actual data is only 50 GB, we will face the need to copy GB of data each time. This method may be a good way to handle mysqldump. Independent tablespace: Set innodb_file_per_table 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 in different databases. 4. space can be recycled (except for the drop table operation, the table cannot be recycled by itself) a) the Drop table operation automatically recycles the table space. For a statistical analysis or daily value table, after deleting a large amount of data, you can use: alter table TableName engine = innodb; Reduce Unnecessary space. B) using turncate table for innodb-plugin will also shrink the space. C) For tables that use independent tablespaces, no matter how they are deleted, the tablespace fragments will not seriously affect performance, and there is a chance to process them. Disadvantage: the increase in a single table at www.2cto.com is too large, for example, over 100 GB. If a single table grows too much, you can separate the files using the shared tablespace, but there is also a problem. If the access range is too large, multiple files will be accessed, it will be slow. There is also a solution for standalone tablespaces: to use a partition table, you can also move that large tablespace to another space and then make a connection. In fact, from the perspective of performance, when a table contains more than 100 GB, the response may be slow, and it is easy to detect problems in the independent tablespace. Backup: InnoDB Hot Backup (cold Backup) tablespace cp won't face a lot of useless copies. In addition, innodb hot backup and tablespace management commands can be used to achieve single-current movement. Monitoring: www.2cto.com can better monitor the data size from the system, the size of each table. In addition, we recommend that you use independent tablespaces because of Performance Comparison between shared tablespaces and independent tablespaces: Shared tablespaces have few advantages in Insert operations. Other independent tablespaces do a good job. Here is also a TIPS. When you enable the independent tablespace, please adjust it properly: innodb_open_files. Starting from the Linux system processing: fsync, a large volume of updated data, has a great impact on system io. If multiple small data fsyncs are separated, the impact on reading can be reduced. At the same time, from the mysql code, it is found that mysql ensures a sleep of at least 20 ms between two fsync operations. In this way, if one fsync is converted into multiple small data operations, the proportion of slow queries should be reduced. Therefore, shared tablespace is not suitable for systems with a large number of update operations. Source http://www.mysqlsupport.cn/