MySQL 5.7版本的6种Tablespace.docx

5.7拥有的6种Tablespaces1. system tablespaceThe InnoDB system tablespace contains the InnoDB data dictionary (metadata for InnoDB-relatedobjects) and is the storage area for the doublewrite buffer, the change buffer, and undo logs. Thesystem tablespace also contains table and index data for any user-created tables that are created in the system tablespace. The system tablespace is considered a shared tablespace since it is shared by multiple tables. The system tablespace is represented by one or more data files. By default, one system data file,named ibdata1, is created in the MySQL data directory. The size and number of system data files is controlled by the innodb_data_file_path startup option.增大系统表空间The easiest way to increase the size of the InnoDB system tablespace is to configure it fromthe beginning to be auto-extending. Specify the autoextend attribute for the last data file inthe tablespace definition. Then InnoDB increases the size of that file automatically in 64MBincrements when it runs out of space. The increment size can be changed by setting the value of the innodb_autoextend_increment system variable, which is measured in megabytesYou can expand the system tablespace by a defined amount by adding another data file:1. Shut down the MySQL server.2. If the previous last data file is defined with the keyword autoextend, change its definition to use a fixed size, based on how large it has actually grown. Check the size of the data file, round it down to the closest multiple of 1024 1024 bytes (= 1MB), and specify this rounded size explicitly in innodb_data_file_path.3. Add a new data file to the end of innodb_data_file_path, optionally making that file autoextending.Only the last data file in the innodb_data_file_path can be specified as autoextending.4. Start the MySQL server again.For example, this tablespace has just one auto-extending data file ibdata1:innodb_data_home_dir =innodb_data_file_path = /ibdata/ibdata1:10M:autoextendSuppose that this data file, over time, has grown to 988MB. Here is the configuration line aftermodifying the original data file to use a fixed size and adding a new auto-extending data file:innodb_data_home_dir =innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextendWhen you add a new data file to the system tablespace configuration, make sure that the filename does not refer to an existing file. InnoDB creates and initializes the file when you restart the server.缩小系统表空间逻辑备份，导出导入。2. file-per-table tablespacesHistorically, all InnoDB tables and indexes were stored in the system tablespace. This monolithicapproach was targeted at machines dedicated entirely to database processing, with carefully planned data growth, where any disk storage allocated to MySQL would never be needed for other purposes.InnoDBs file-per-table tablespace feature provides a more flexible alternative, where each InnoDB table and its indexes are stored in a separate .ibd data file. Each such .ibd data file represents an individual tablespace. This feature is controlled by the innodb_file_per_table configuration option, which is enabled by default in MySQL 5.6.6 and higher.开启独立表空间的优势： You can reclaim disk space when truncating or dropping a table stored in a file-per-table tablepace.Truncating or dropping tables stored in the shared system tablespace creates free space internally in the system tablespace data files (ibdata files) which can only be used for new InnoDB data.Similarly, a table-copying ALTER TABLE operation on table that resides in a shared tablespace can increase the amount of space used by the tablespace. Such operations may require as much additional space as the data in the table plus indexes. The additional space required for the tablecopying ALTER TABLE operation is not released back to the operating system as it is for file-pertable tablespaces. The TRUNCATE TABLE operation is faster when run on tables stored in file-per-table tablepaces. You can store specific tables on separate storage devices, for I/O optimization, space management,or backup purposes. In previous releases, you had to move entire database directories to other drives and create symbolic links in the MySQL data directory. In MySQL 5.6.6 and higher, you can specify the location of each table using the syntax CREATE TABLE . DATA DIRECTORY = absolute_path_to_directory. You can run OPTIMIZE TABLE to compact or recreate a file-per-table tablespace. When you run an OPTIMIZE TABLE, InnoDB creates a new .ibd file with a temporary name, using only the spacerequired to store actual data. When the optimization is complete, InnoDB removes the old .ibd file and replaces it with the new one. If the previous .ibd file grew significantly but the actual data only accounted for a portion of its size, running OPTIMIZE TABLE can reclaim the unused space. You can move individual InnoDB tables rather than entire databases. You can copy individual InnoDB tables from one MySQL instance to another (known as thetransportable tablespace feature). Tables created in file-per-table tablespaces use the Barracuda file format. The Barracuda file format enables features such as compressed and dynamic row formats. You can enable more efficient storage for tables with large BLOB or TEXT columns using the dynamic row format. File-per-table tablespaces may improve chances for a successful recovery and save time whena corruption occurs, when a server cannot be restarted, or when backup and binary logs areunavailable. File-per-table tablespaces are convenient for per-table status reporting when copying or backing up tables. You can monitor table size at a file system level, without accessing MySQL. Common Linux file systems do not permit concurrent writes to a single file when innodb_flush_method is set to O_DIRECT. As a result, there are possible performance improvements when using file-per-table tablespaces in conjunction with innodb_flush_method. The system tablespace stores the data dictionary and undo logs, and is limited in size by InnoDB tablespace size limits. With file-per-table tablespaces, each table has its own tablespace, which provides room for growth.开启独立表空间的劣势： With file-per-table tablespaces, each table may have unused space, which can only be utilized by rows of the same table. This could lead to wasted space if not properly managed. fsync operations must run on each open table rather than on a single file. Because there is aseparate fsync operation for each file, write operations on multiple tables cannot be combinedinto a single I/O operation. This may require InnoDB to perform a higher total number of fsync operations. mysqld must keep one open file handle per table, which may impact performance if you havenumerous tables in file-per-table tablespaces. More file descriptors are used. innodb_file_per_table is enabled by default in MySQL 5.6.6 and higher. You may consider disabling it if backward compatibility with MySQL 5.5 or 5.1 is a concern. Disabling innodb_file_per_table prevents ALTER TABLE from moving an InnoDB table from the system tablespace to an individual .ibd file in cases where ALTER TABLE recreates the table (ALGORITHM=COPY).For example, when restructuring the clustered index for an InnoDB table, the table is re-created using the current setting for innodb_file_per_table. This behavior does not apply when adding or dropping InnoDB secondary indexes. When a secondary index is created without rebuilding the table, the index is stored in the same file as the table data, regardless of the current innodb_file_per_table setting. This behavior also does not apply to tables added to the system tablespace using CREATE TABLE . TABLESPACE or ALTER TABLE . TABLESPACE syntax.These tables are not affected by the innodb_file_per_table setting. If many tables are growing there is potential for more fragmentation which can impede DROP TABLE and table scan performance. However, when fragmentation is managed, having files in their own tablespace can improve performance. The buffer pool is scanned when dropping a file-per-table tablespace, which can take severalseconds for buffer pools that are tens of gigabytes in size. The scan is performed with a broad internal lock, which may delay other operations. Tables in the system tablespace are not affected. The innodb_autoextend_increment variable, which defines increment size (in MB) for extending the size of an auto-extending shared tablespace file when it becomes full, does not apply to file-per-table tablespace files, which are auto-extending regardless of the innodb_autoextend_increment setting. The initial extensions are by small amounts, after whichextensions occur in increments of 4MB.3. InnoDB temporary tablespace:ibtmp1Temporary table undo logs are used for temporary tables and related objects. This type of undo log is not a redo log, as temporary tables are not recovered during crash recovery and do not require redo logs. Temporary table undo logs are, however, used for rollback while the server is running. This special type of non-redo undo log benefits performance by avoiding redo logging I/O for temporary tables and related objects. Temporary table undo logs reside in the temporary tablespace. The default temporary tablespace file, ibtmp1, is located in the data directory by default and is always recreated on server startup. A user defined location for the temporary tablespace file can be specified by setting innodb_temp_data_file_path.32 rollback segments are reserved for temporary table undo logs for transactions that modify temporary tables and related objects, which means that the maximum number of rollback segments available for data-modifying transactions that generate undo records is 96. With 96 available rollback segments, the limit on concurrent data-modifying transactions is 96K.4. undo tablespaces5.7 新增了可以对undo日志做truncate的内容，启用的参数是innodb_undo_log_truncate，可以对undo tablespaces中的undo logs做truncate，涉及到的variables如下：innodb_undo_directory定义undo logs存放的位置，默认在datadirinnodb_undo_logs定义undo日志的回滚段的数量（每个回滚段能支持最多1024个事务），代替之前的innodb_rollback_segments参数，该参数范围是1-128，默认设置为128，其中一个回滚段分配给system tablespace,32个分配给temporary tablespace文件ibtmp1，之后再额外的分配给undo logs的回滚段。该值必须设置为大于33才能有额外的回滚段分配给一般的回滚事务记录，最大为128（如果等于32的话，事务的回滚段依然是放在其中的system tablespace的回滚段）。innodb_undo_tablespaces额外为undo transaction分配的undo segment,这个值的范围是0-95，加上之前共33个的undo segment，正好是128个segmentinnodb_undo_log_truncate是否开启undo logs的truncate功能，这个参数要求innodb_undo_tablespaces大于等于2，即同时要求innodb_undo_logs大于等于35。当开启时：1. Undo tablespace文件大小超过innodb_max_undo_log_size设置的大小就会被标记需要被truncate。2. 标记需要被truncate的这个undo tablespace不会再次将回滚段分配出去，直到该undo tablespace中所有正在用rollback segment的事务都完成，进而执行truncate操作。3. 清理回滚段purge syetem的可以不再需要。4. 清理完某个undo tablespace后，该回滚段的文件大小初始化为10M。innodb_purge_rseg_truncate_frequency所有的回滚段是空闲时，该undo tablespace 才能被truncate。当purge线程被调用一次时，purge system将清理128个segment共需要执行128次，加大该参数值innodb_purge_rseg_truncate_frequency，可以提高undo tablespace被truncate的频率，进而可以降低purge system清理undo tablespace的频率而提高整个undo 清理的效率。innodb_max_undo_log_size当开启了 innodb_undo_log_truncate, Undo tablespace文件大小超过innodb_max_undo_log_size设置的大小就会被标记需要被truncate，由一个内部线程确认该文件所有的回滚段都可以被清理然后完成truncate操作清理完后，该file变成初始化的10M大小。status：Innodb_available_undo_logs查看innodb 可用的undo 回滚段推荐设置：innodb_undo_logs=128innodb_undo_tablespaces=3 （看情况增加，最大为95）innodb_undo_log_truncate=oninnodb_purge_rseg_truncate_frequency=128innodb_max_undo_log_size=2G （太大容易造成truncate的时间很长，配合undo tablespace的个数一起调整）5. general tablespacesA general tablespace is a new type of InnoDB tablespace, introduced in MySQL 5.7. The general tablespace feature provides the following capabilities: Similar to the system tablespace, general tablespaces are shared tablespaces that can store data for multiple tables. General tablespaces have a potential memory advantage over file-per-table tablespaces. The server keeps tablespace metadata in memory for the lifetime of a tablespace. Multiple tables in fewer general tablespaces consume less memory for tablespace metadata than the same number of tables in separate file-per-table tablespaces. General tablespace data files may be placed in a directory relative to or independent of the MySQL data directory, which provides you with many of the data file and storage management capabilities of file-per-table tablespaces. As with file-per-table tablespaces, the ability to place data files outside of the MySQL data directory allows you to manage performance of critical tables separately, setup RAID or DRBD for specific tables, or bind tables to particular disks, for example. General tablespaces support both Antelope and Barracuda file formats, and therefore support all table row formats and associated features. With support for both file formats, general tablespaces have no dependence on innodb_file_format or innodb_file_per_table settings, nor do these variables have any effect on general tablespaces. The TABLESPACE option can be used with CREATE TABLE to create tables in a generaltablespaces, file-per-table tablespace, or in the system tablespace. The T