InnoDB On-Disk Structures（三）--Tablespaces (转载）

转载、节选于 

 

this section covers topics related to innodb tablespaces.

1.the system tablespace

the innodb system tablespace is the storage area for the doublewrite buffer and the change buffer. the system tablespace also contains table and index data for user-created tables created in the system tablespace. in previous releases, the system tablespace contained the innodb data dictionary. in mysql 8.0, innodb stores metadata in the mysql data dictionary.

the system tablespace can have one or more data files. by default, one system tablespace data file, named ibdata1, is created in the data directory. the size and number of system tablespace data files is controlled by the innodb_data_file_path startup option.

resizing the system tablespace

this section describes how to increase or decrease the size of the innodb system tablespace.

increasing the size of the innodb system tablespace

the easiest way to increase the size of the innodb system tablespace is to configure it from the beginning to be auto-extending. specify the autoextend attribute for the last data file in the tablespace definition. then innodb increases the size of that file automatically in 64mb increments 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 megabytes.

you 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 auto-extending. only the last data file in the innodb_data_file_path can be specified as auto-extending.

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:autoextend

suppose that this data file, over time, has grown to 988mb. here is the configuration line after modifying 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:autoextend

when 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.

decreasing the size of the innodb system tablespace

you cannot remove a data file from the system tablespace. to decrease the system tablespace size, use this procedure:

1.use mysqldump to dump all your innodb tables, including innodb tables located in the mysql database.

mysql> select table_name from information_schema.tables where table_schema='mysql' and engine='innodb';
+---------------------------+
| table_name                |
+---------------------------+
| columns_priv              |
| component                 |
| db                        |
| default_roles             |
| engine_cost               |
| func                      |
| global_grants             |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_dynamic_metadata   |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| procs_priv                |
| proxies_priv              |
| role_edges                |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

2.stop the server.

3.remove all the existing tablespace files (*.ibd), including the ibdata and ib_log files. do not forget to remove *.ibd files for tables located in the mysql database.

4.configure a new tablespace.

5.restart the server.

6.import the dump files.

注意：if your databases only use the innodb engine, it may be simpler to dump all databases, stop the server, remove all databases and innodb log files, restart the server, and import the dump files.

using raw disk partitions for the system tablespace

you can use raw disk partitions as data files in the innodb system tablespace. this technique enables nonbuffered i/o on windows and on some linux and unix systems without file system overhead. perform tests with and without raw partitions to verify whether this change actually improves performance on your system.

when you use a raw disk partition, ensure that the user id that runs the mysql server has read and write privileges for that partition. for example, if you run the server as the mysql user, the partition must be readable and writeable by mysql. if you run the server with the --memlock option, the server must be run as root, so the partition must be readable and writeable by root.

the procedures described below involve option file modification.

allocating a raw disk partition on linux and unix systems

1. when you create a new data file, specify the keyword newraw immediately after the data file size for the innodb_data_file_path option. the partition must be at least as large as the size that you specify. note that 1mb in innodb is 1024 × 1024 bytes, whereas 1mb in disk specifications usually means 1,000,000 bytes.

   [mysqld]
   innodb_data_home_dir=
   innodb_data_file_path=/dev/hdd1:3gnewraw;/dev/hdd2:2gnewraw

2. restart the server. innodb notices the newraw keyword and initializes the new partition. however, do not create or change any innodb tables yet. otherwise, when you next restart the server, innodb reinitializes the partition and your changes are lost. (as a safety measure innodb prevents users from modifying data when any partition with newraw is specified.)

3. after innodb has initialized the new partition, stop the server, change newraw in the data file specification to raw:

   [mysqld]
   innodb_data_home_dir=
   innodb_data_file_path=/dev/hdd1:3graw;/dev/hdd2:2graw

4. restart the server. innodb now permits changes to be made.

allocating a raw disk partition on windows

on windows systems, the same steps and accompanying guidelines described for linux and unix systems apply except that the innodb_data_file_path setting differs slightly on windows.

1. when you create a new data file, specify the keyword newraw immediately after the data file size for the innodb_data_file_path option:

   [mysqld]
   innodb_data_home_dir=
   innodb_data_file_path=//./d::10gnewraw

   the //./ corresponds to the windows syntax of \\.\ for accessing physical drives. in the example above, d: is the drive letter of the partition.

2. restart the server. innodb notices the newraw keyword and initializes the new partition.

3. after innodb has initialized the new partition, stop the server, change newraw in the data file specification to raw:

   [mysqld]
   innodb_data_home_dir=
   innodb_data_file_path=//./d::10graw

4. restart the server. innodb now permits changes to be made.

2.file-per-table tablespaces

historically, innodb tables were stored in the system tablespace. this monolithic approach was targeted at machines dedicated to database processing, with carefully planned data growth, where any disk storage allocated to mysql would never be needed for other purposes. the file-per-table tablespace feature provides a more flexible alternative, where each innodb table is stored in its own tablespace data file (.ibd file). this feature is controlled by the innodb_file_per_table configuration option, which is enabled by default.

advantages

• you can reclaim disk space when truncating or dropping a table stored in a file-per-table tablespace. 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 table-copying alter table operation is not released back to the operating system as it is for file-per-table tablespaces.

• the truncate table operation is faster when run on tables stored in file-per-table tablespaces.

• you can store specific tables on separate storage devices, for i/o optimization, space management, or backup purposes by specifying 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 space required 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 the transportable tablespace feature).

• tables created in file-per-table tablespaces support features associated with 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 when a corruption occurs, when a server cannot be restarted, or when backup and binary logs are unavailable.

• you can back up or restore individual tables quickly using the mysql enterprise backup product, without interrupting the use of other innodb tables. this is beneficial if you have tables that require backup less frequently or on a different backup schedule. see making a partial backup for details.

• 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.

potential disadvantages

• 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 a separate fsync operation for each file, write operations on multiple tables cannot be combined into 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 have numerous tables in file-per-table tablespaces.

• more file descriptors are used.

• innodb_file_per_table is enabled by default in mysql 5.6 and higher. you may consider disabling it if backward compatibility with earlier versions of mysql is a concern.

• 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 several seconds 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 which extensions occur in increments of 4mb.

enabling file-per-table tablespaces

the innodb_file_per_table option is enabled by default.

you can also set innodb_file_per_table dynamically, while the server is running:

mysql> set global innodb_file_per_table=1;

with innodb_file_per_table enabled, you can store innodb tables in a tbl_name.ibd file. unlike the myisam storage engine, with its separate tbl_name.myd andtbl_name.myi files for indexes and data, innodb stores the data and the indexes together in a single .ibd file.

if you disable innodb_file_per_table in your startup options and restart the server, or disable it with the set global command, innodb creates new tables inside the system tablespace unless you have explicitly placed the table in file-per-table tablespace or general tablespace using the create table ... tablespace option.

you can always read and write any innodb tables, regardless of the file-per-table setting.

to move a table from the system tablespace to its own tablespace, change the innodb_file_per_table setting and rebuild the table:

mysql> set global innodb_file_per_table=1;
mysql> alter table table_name engine=innodb;

tables added to the system tablespace using create table ... tablespace or alter table ... tablespace syntax are not affected by the innodb_file_per_tablesetting. to move these tables from the system tablespace to a file-per-table tablespace, they must be moved explicitly using alter table ... tablespace syntax.

注意：

innodb always needs the system tablespace because it puts its internal data dictionary and undo logs there. the .ibd files are not sufficient forinnodb to operate.

when a table is moved out of the system tablespace into its own .ibd file, the data files that make up the system tablespace remain the same size. the space formerly occupied by the table can be reused for new innodb data, but is not reclaimed for use by the operating system. when moving largeinnodb tables out of the system tablespace, where disk space is limited, you may prefer to enable innodb_file_per_table and recreate the entire instance using the mysqldump command. as mentioned above, tables added to the system tablespace using create table ... tablespace oralter table ... tablespace syntax are not affected by the innodb_file_per_table setting. these tables must be moved individually.

3.general tablespaces

a general tablespace is a shared innodb tablespace that is created using create tablespace syntax. general tablespace capabilities and features are described under the following topics in this section.

general tablespace capabilities

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 general tablespaces, file-per-table tablespace, or in the system tablespace.

• the tablespace option can be used with alter table to move tables between general tablespaces, file-per-table tablespaces, and the system tablespace. previously, it was not possible to move a table from a file-per-table tablespace to the system tablespace. with the general tablespace feature, you can now do so.

creating a general tablespace

general tablespaces are created using create tablespace syntax.

create tablespace tablespace_name
    [add datafile 'file_name']
    [file_block_size = value]
        [engine [=] engine_name]

a general tablespace can be created in the data directory or outside of it. to avoid conflicts with implicitly created file-per-table tablespaces, creating a general tablespace in a subdirectory under the data directory is not supported. when creating a general tablespace outside of the data directory, the directory must exist and must be known toinnodb prior to creating the tablespace. to make an unknown directory known to innodb, add the directory to the innodb_directories argument value. innodb_directories is a read-only startup option. configuring it requires restarting the server.

examples:

creating a general tablespace in the data directory:

mysql> create tablespace `ts1` add datafile 'ts1.ibd' engine=innodb;

or

mysql> create tablespace `ts1` engine=innodb;

the add datafile clause is optional as of mysql 8.0.14 and required before that. if the add datafile clause is not specified when creating a tablespace, a tablespace data file with a unique file name is created implicitly. the unique file name is a 128 bit uuid formatted into five groups of hexadecimal numbers separated by dashes (aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee). general tablespace data files include an .ibd file extension. in a replication environment, the data file name created on the master is not the same as the data file name created on the slave.

creating a general tablespace in a directory outside of the data directory:

mysql> create tablespace `ts1` add datafile '/my/tablespace/directory/ts1.ibd' engine=innodb;

you can specify a path that is relative to the data directory as long as the tablespace directory is not under the data directory. in this example, the my_tablespace directory is at the same level as the data directory:

mysql> create tablespace `ts1` add datafile '../my_tablespace/ts1.ibd' engine=innodb;

注意：the engine = innodb clause must be defined as part of the create tablespace statement, or innodb must be defined as the default storage engine (default_storage_engine=innodb).

adding tables to a general tablespace

after creating an innodb general tablespace, you can use create table tbl_name ... tablespace [=] tablespace_name or alter table tbl_name tablespace [=]tablespace_name to add tables to the tablespace, as shown in the following examples:

create table:

mysql> create table t1 (c1 int primary key) tablespace ts1;

alter table:

mysql> alter table t2 tablespace ts1;

注意：support for adding table partitions to shared tablespaces was deprecated in mysql 5.7.24 and removed in mysql 8.0.13. shared tablespaces include the innodb system tablespace and general tablespaces.

general tablespace row format support

general tablespaces support all table row formats (redundant, compact, dynamic, compressed) with the caveat that compressed and uncompressed tables cannot coexist in the same general tablespace due to different physical page sizes.

for a general tablespace to contain compressed tables (row_format=compressed), file_block_size must be specified, and the file_block_size value must be a valid compressed page size in relation to the innodb_page_size value. also, the physical page size of the compressed table (key_block_size) must be equal tofile_block_size/1024. for example, if innodb_page_size=16kb and file_block_size=8k, the key_block_size of the table must be 8.

the following table shows permitted innodb_page_size, file_block_size, and key_block_size combinations. file_block_size values may also be specified in bytes. to determine a valid key_block_size value for a given file_block_size, divide the file_block_size value by 1024. table compression is not support for 32k and 64kinnodb page sizes. 

permitted page size, file_block_size, and key_block_size combinations for compressed tables

innodb page size (innodb_page_size)	permitted file_block_size value	permitted key_block_size value
64kb	64k (65536)	compression is not supported
32kb	32k (32768)	compression is not supported
16kb	16k (16384)	n/a: if innodb_page_size is equal to file_block_size, the tablespace cannot contain a compressed table.
16kb	8k (8192)	8
16kb	4k (4096)	4
16kb	2k (2048)	2
16kb	1k (1024)	1
8kb	8k (8192)	n/a: if innodb_page_size is equal to file_block_size, the tablespace cannot contain a compressed table.
8kb	4k (4096)	4
8kb	2k (2048)	2
8kb	1k (1024)	1
4kb	4k (4096)	n/a: if innodb_page_size is equal to file_block_size, the tablespace cannot contain a compressed table.
4kb	2k (2048)	2
4kb	1k (1024)	1

 

 

this example demonstrates creating a general tablespace and adding a compressed table. the example assumes a default innodb_page_size of 16kb. thefile_block_size of 8192 requires that the compressed table have a key_block_size of 8.

mysql> create tablespace `ts2` add datafile 'ts2.ibd' file_block_size = 8192 engine=innodb;

mysql> create table t4 (c1 int primary key) tablespace ts2 row_format=compressed key_block_size=8;

if you do not specify file_block_size when creating a general tablespace, file_block_size defaults to innodb_page_size. when file_block_size is equal toinnodb_page_size, the tablespace may only contain tables with an uncompressed row format (compact, redundant, and dynamic row formats).

moving tables between tablespaces using alter table

you can use alter table with the tablespace option to move a table to an existing general tablespace, to a new file-per-table tablespace, or to the system tablespace.

注意：support for placing table partitions in shared tablespaces was deprecated in mysql 5.7.24 and removed mysql 8.0.13. shared tablespaces include the innodb system tablespace and general tablespace.

to move a table from a file-per-table tablespace or from the system tablespace to a general tablespace, specify the name of the general tablespace. the general tablespace must exist. 

alter table tbl_name tablespace [=] tablespace_name;

to move a table from a general tablespace or file-per-table tablespace to the system tablespace, specify innodb_system as the tablespace name.

alter table tbl_name tablespace [=] innodb_system;

to move a table from the system tablespace or a general tablespace to a file-per-table tablespace, specify innodb_file_per_table as the tablespace name.

alter table tbl_name tablespace [=] innodb_file_per_table;

alter table ... tablespace operations always cause a full table rebuild, even if the tablespace attribute has not changed from its previous value.

alter table ... tablespace syntax does not support moving a table from a temporary tablespace to a persistent tablespace.

the data directory clause is permitted with create table ... tablespace=innodb_file_per_table but is otherwise not supported for use in combination with thetablespace option.

restrictions apply when moving tables from encrypted tablespaces.

dropping a general tablespace

the drop tablespace statement is used to drop an innodb general tablespace.

all tables must be dropped from the tablespace prior to a drop tablespace operation. if the tablespace is not empty, drop tablespace returns an error.

use a query similar to the following to identify tables in a general tablespace.

mysql> select a.name as space_name, b.name as table_name from information_schema.innodb_tablespaces a, 
       information_schema.innodb_tables b where a.space=b.space and a.name like 'ts1';
+------------+------------+
| space_name | table_name |
+------------+------------+
| ts1        | test/t1    |
| ts1        | test/t2    |
| ts1        | test/t3    |
+------------+------------+

a general innodb tablespace is not deleted automatically when the last table in the tablespace is dropped. the tablespace must be dropped explicitly using drop tablespace tablespace_name.

a general tablespace does not belong to any particular database. a drop database operation can drop tables that belong to a general tablespace but it cannot drop the tablespace, even if the drop database operation drops all tables that belong to the tablespace. a general tablespace must be dropped explicitly using drop tablespacetablespace_name.

similar to the system tablespace, truncating or dropping tables stored in a general tablespace creates free space internally in the general tablespace .ibd data file which can only be used for new innodb data. space is not released back to the operating system as it is when a file-per-table tablespace is deleted during a drop table operation.

this example demonstrates how to drop an innodb general tablespace. the general tablespace ts1 is created with a single table. the table must be dropped before dropping the tablespace.

mysql> create tablespace `ts1` add datafile 'ts1.ibd' engine=innodb;

mysql> create table t1 (c1 int primary key) tablespace ts10 engine=innodb;

mysql> drop table t1;

mysql> drop tablespace ts1;

注意：tablespace_name is a case-sensitive identifier in mysql.

general tablespace limitations

• a generated or existing tablespace cannot be changed to a general tablespace.

• creation of temporary general tablespaces is not supported.

• general tablespaces do not support temporary tables.

• similar to the system tablespace, truncating or dropping tables stored in a general tablespace creates free space internally in the general tablespace .ibd data file which can only be used for new innodb data. space is not released back to the operating system as it is for file-per-table tablespaces.

  additionally, a table-copying alter table operation on table that resides in a shared tablespace (a general tablespace or the system tablespace) can increase the amount of space used by the tablespace. such operations require as much additional space as the data in the table plus indexes. the additional space required for the table-copying alter table operation is not released back to the operating system as it is for file-per-table tablespaces.

• alter table ... discard tablespace and alter table ...import tablespace are not supported for tables that belong to a general tablespace.

• support for placing table partitions in general tablespaces was deprecated in mysql 5.7.24 and removed in mysql 8.0.13.

4.undo tablespaces

undo tablespaces contain undo logs, which are collections of undo log records that contain information about how to undo the latest change by a transaction to a clustered index record. undo logs exist within undo log segments, which are contained within rollback segments. the innodb_rollback_segments variable defines the number of rollback segments allocated to each undo tablespace.

two default undo tablespaces are created when the mysql instance is initialized. default undo tablespaces are created at initialization time to provide a location for rollback segments that must exist before sql statements can be accepted. a minimum of two undo tablespaces is required to support automated truncation of undo tablespaces. 

default undo tablespaces are created in the location defined by the innodb_undo_directory variable. if the innodb_undo_directory variable is undefined, default undo tablespaces are created in the data directory. default undo tablespace data files are named undo_001 and undo_002. the corresponding undo tablespace names defined in the data dictionary are innodb_undo_001 and innodb_undo_002.

as of mysql 8.0.14, additional undo tablespaces can be created at runtime using sql.

the initial size of an undo tablespace data file depends on the innodb_page_size value. for the default 16kb page size, the initial undo tablespace file size is 10mib. for 4kb, 8kb, 32kb, and 64kb page sizes, the initial undo tablespace files sizes are 7mib, 8mib, 20mib, and 40mib, respectively.

adding undo tablespaces

because undo logs can become large during long-running transactions, creating additional undo tablespaces can help prevent individual undo tablespaces from becoming too large. as of mysql 8.0.14, additional undo tablespaces can be created at runtime using create undo tablespace syntax.

create undo tablespace tablespace_name add datafile 'file_name.ibu'

the undo tablespace file name must have an .ibu extension. it is not permitted to specify a relative path when defining the undo tablespace file name. a fully qualified path is permitted, but the path must be known to innodb. known paths are those defined by the innodb_directories variable. unique undo tablespace file names are recommended to avoid potential file name conflicts when moving or cloning data.

at startup, directories defined by the innodb_directories variable are scanned for undo tablespace files. (the scan also traverses subdirectories.) directories defined by the innodb_data_home_dir, innodb_undo_directory, and datadir variables are automatically appended to the innodb_directories value, regardless of whether theinnodb_directories variable is defined explicitly. an undo tablespace can therefore reside in paths defined by any of those variables.

if the undo tablespace file name does not include a path, the undo tablespace is created in the directory defined by the innodb_undo_directory variable. if that variable is undefined, the undo tablespace is created in the data directory.

注意：the innodb recovery process requires that undo tablespace files reside in known directories. undo tablespace files must be discovered and opened before redo recovery and before other data files are opened to permit uncommitted transactions and data dictionary changes to be rolled back. an undo tablespace not found before recovery cannot be used, which can cause database inconsistencies. an error message is reported at startup if an undo tablespace known to the data dictionary is not found. the known directory requirement also supports undo tablespace portability.

to create undo tablespaces in a path relative to the data directory, set the innodb_undo_directory variable to the relative path, and specify the file name only when creating an undo tablespace.

to view undo tablespace names and paths, query information_schema.files:

select tablespace_name, file_name from information_schema.files 
  where file_type like 'undo log';

a mysql instance supports up to 127 undo tablespaces including the two default undo tablespaces created when the mysql instance is initialized.

注意：

prior to mysql 8.0.14, additional undo tablespaces are created by configuring the innodb_undo_tablespaces startup variable. this variable is deprecated and no longer configurable as of mysql 8.0.14.

prior to mysql 8.0.14, increasing the innodb_undo_tablespaces setting creates the specified number of undo tablespaces and adds them to the list of active undo tablespaces. decreasing the innodb_undo_tablespaces setting removes undo tablespaces from the list of active undo tablespaces. undo tablespaces that are removed from the active list remain active until they are no longer used by existing transactions. theinnodb_undo_tablespaces variable can be configured at runtime using a set statement or defined in a configuration file.

prior to mysql 8.0.14, deactivated undo tablespaces cannot be removed. manual removal of undo tablespace files is possible after a slow shutdown but is not recommended, as deactivated undo tablespaces may contain active undo logs for some time after the server is restarted if open transactions were present when shutting down the server. as of mysql 8.0.14, undo tablespaces can be dropped using drop undo tabalespacesyntax. 

dropping undo tablespaces

as of mysql 8.0.14, undo tablespaces created using create undo tablespace syntax can be dropped at runtime using drop undo tabalespace syntax.

an undo tablespace must be empty before it can be dropped. to empty an undo tablespace, the undo tablespace must first be marked as inactive using alter undo tablespace syntax so that the tablespace is no longer used for assigning rollback segments to new transactions.

alter undo tablespace tablespace_name set inactive;

after an undo tablespace is marked as inactive, transactions currently using rollback segments in the undo tablespace are permitted to finish, as are any transactions started before those transactions are completed. after transactions are completed, the purge system frees the rollback segments in the undo tablespace, and the undo tablespace is truncated to its initial size. (the same process is used when truncating undo tablespaces. ) when the undo tablespace is empty, it can be dropped.

drop undo tablespace tablespace_name;

注意：alternatively, the undo tablespace can be left in an empty state and reactivated later, when needed, by issuing an alter undo tablespacetablespace_name set active statement.

the state of an undo tablespace can be monitored by querying the information_schema.innodb_tablespaces table.

select name, state from information_schema.innodb_tablespaces 
  where name like tablespace_name;

an inactive state indicates that rollback segments in an undo tablespace are no longer used by new transactions. an empty state indicates that an undo tablespace is empty and ready to be dropped, or made active again using an alter undo tablespace tablespace_name set active statement. attempting to drop an undo tablespace that is not empty returns an error.

the default undo tablespaces (innodb_undo_001 and innodb_undo_002) created when the mysql instance is initialized cannot be dropped. they can, however, be made inactive using an alter undo tablespace tablespace_name set inactive statement. before a default undo tablespace can be made inactive, there must be an undo tablespace to take its place. a minimum of two active undo tablespaces are required at all times to support automated truncation of undo tablespaces.

configuring the number of rollback segments

the innodb_rollback_segments variable defines the number of rollback segments allocated to each undo tablespace and to the global temporary tablespace. theinnodb_rollback_segments variable can be configured at startup or while the server is running.

the default setting for innodb_rollback_segments is 128, which is also the maximum value. 

truncating undo tablespaces

there are two methods of truncating undo tablespaces, which can be used individually or in combination to manage undo tablespace size. one method is automated, enabled using configuration variables. the other method is manual, performed using sql statements.

the automated method does not require monitoring undo tablespace size and, once enabled, it performs deactivation, truncation, and reactivation of undo tablespaces without manual intervention. the manual truncation method may be preferable if you want to control when undo tablespaces are taken offline for truncation. for example, you may want to avoid truncating undo tablespaces during peak workload times.

the purge thread is responsible for emptying and truncating undo tablespaces. by default, the purge thread looks for undo tablespaces to truncate once every 128 times that purge is invoked. the frequency with which the purge thread looks for undo tablespaces to truncate is controlled by the innodb_purge_rseg_truncate_frequencyvariable, which has a default setting of 128.

mysql> select @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                    128 |
+----------------------------------------+

to increase that frequency, decrease the innodb_purge_rseg_truncate_frequency setting. for example, to have the purge thread look for undo tabespaces once every 32 timees that purge is invoked, set innodb_purge_rseg_truncate_frequency to 32.

mysql> set global innodb_purge_rseg_truncate_frequency=32;

when the purge thread finds an undo tablespace that requires truncation, the purge thread returns with increased frequency to quickly empty and truncate the undo tablespace.

5 temporary tablespaces

innodb uses session temporary tablespaces and a global temporary tablespace.

session temporary tablespaces

session temporary tablespaces store user-created temporary tables and internal temporary tables created by the optimizer when innodb is configured as the storage engine for on-disk internal temporary tables. beginning with mysql 8.0.16, the storage engine used for on-disk internal temporary tables is always innodb. (previously, the storage engine was determined by the value of internal_tmp_disk_storage_engine.)

session temporary tablespaces are allocated to a session from a pool of temporary tablespaces on the first request to create an on-disk temporary table. a maximum of two tablespaces is allocated to a session, one for user-created temporary tables and the other for internal temporary tables created by the optimizer. the temporary tablespaces allocated to a session are used for all on-disk temporary tables created by the session. when a session disconnects, its temporary tablespaces are truncated and released back to the pool. a pool of 10 temporary tablespaces is created when the server is started. the size of the pool never shrinks and tablespaces are added to the pool automatically as necessary. the pool of temporary tablespaces is removed on normal shutdown or on an aborted initialization. session temporary tablespace files are five pages in size when created and have an .ibt file name extension.

a range of 400 thousand space ids is reserved for session temporary tablespaces. because the pool of session temporary tablespaces is recreated each time the server is started, space ids for session temporary tablespaces are not persisted when the server is shut down and may be reused.

the innodb_temp_tablespaces_dir variable defines the location where session temporary tablespaces are created. the default location is the #innodb_temp directory in the data directory. startup is refused if the pool of temporary tablespaces cannot be created.

shell> cd basedir/data/#innodb_temp
shell> ls
temp_10.ibt  temp_2.ibt  temp_4.ibt  temp_6.ibt  temp_8.ibt
temp_1.ibt   temp_3.ibt  temp_5.ibt  temp_7.ibt  temp_9.ibt

the innodb_session_temp_tablespaces table provides metadata about session temporary tablespaces.

the information_schema.innodb_temp_table_info table provides metadata about user-created temporary tables that are active in an innodb instance.

global temporary tablespace

the global temporary tablespace (ibtmp1) stores rollback segments for changes made to user-created temporary tables.

the innodb_temp_data_file_path variable defines the relative path, name, size, and attributes for global temporary tablespace data files. if no value is specified forinnodb_temp_data_file_path, the default behavior is to create a single auto-extending data file named ibtmp1 in the innodb_data_home_dir directory. the initial file size is slightly larger than 12mb.

the global temporary tablespace is removed on normal shutdown or on an aborted initialization, and recreated each time the server is started. the global temporary tablespace receives a dynamically generated space id when it is created. startup is refused if the global temporary tablespace cannot be created. the global temporary tablespace is not removed if the server halts unexpectedly. in this case, a database administrator can remove the global temporary tablespace manually or restart the mysql server. restarting the mysql server removes and recreates the global temporary tablespace automatically.

the global temporary tablespace cannot reside on a raw device.

information_schema.files provides metadata about the global temporary tablespace. issue a query similar to this one to view global temporary tablespace metadata:

 select * from information_schema.files where tablespace_name='innodb_temporary'\g

by default, the global temporary tablespace data file is autoextending and increases in size as necessary.

to determine if a global temporary tablespace data file is autoextending, check the innodb_temp_data_file_path setting:

mysql> select @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12m:autoextend        |
+------------------------------+

to check the size of global temporary tablespace data files, query the information_schema.files table using a query similar to this one:

mysql> select file_name, tablespace_name, engine, initial_size, total_extents*extent_size 
       as totalsizebytes, data_free, maximum_size from information_schema.files 
       where tablespace_name = 'innodb_temporary'\g
*************************** 1. row ***************************
      file_name: ./ibtmp1
tablespace_name: innodb_temporary
         engine: innodb
   initial_size: 12582912
 totalsizebytes: 12582912
      data_free: 6291456
   maximum_size: null

totalsizebytes shows the current size of the global temporary tablespace data file.

alternatively, check the global temporary tablespace data file size on your operating system. the global temporary tablespace data file is located in the directory defined by the innodb_temp_data_file_path variable.

to reclaim disk space occupied by a global temporary tablespace data file, restart the mysql server. restarting the server removes and recreates the global temporary tablespace data file according to the attributes defined by innodb_temp_data_file_path.

to limit the size of the global temporary tablespace data file, configure innodb_temp_data_file_path to specify a maximum file size. for example:

[mysqld]
innodb_temp_data_file_path=ibtmp1:12m:autoextend:max:500m

configuring innodb_temp_data_file_path requires restarting the server.

6 creating a tablespace outside of the data directory

the create table ... data directory clause permits creating a file-per-table tablespace outside of the data directory. for example, you can use the data directoryclause to create a tablespace on a separate storage device with particular performance or capacity characteristics, such as a fast ssd or a high-capacity hdd.

be sure of the location that you choose. the data directory clause cannot be used with alter table to change the location later.

the tablespace data file is created in the specified directory, within in a subdirectory named for the schema to which the table belongs.

the following example demonstrates creating a file-per-table tablespace outside of the data directory. it is assumed that the innodb_file_per_table variable is enabled.

mysql> use test;
database changed

mysql> create table t1 (c1 int primary key) data directory = '/remote/directory';

# mysql creates the tablespace file in a subdirectory that is named
# for the schema to which the table belongs

shell> cd /remote/directory/test
shell> ls
t1.ibd

when creating a tablespace outside of the data directory, ensure that the directory is known to innodb. otherwise, if the server halts unexpectedly before tablespace data file pages are fully flushed, startup fails when the tablespace is not found during the pre-recovery discovery phase that searches known directories for tablespace data files. to make a directory known, add it to the innodb_directories argument value. innodb_directories is a read-only startup option that defines directories to scan at startup for tablespace data files. configuring it requires restarting the server.

create table ... tablespace syntax can also be used in combination with the data directory clause to create a file-per-table tablespace outside of the data directory. to do so, specify innodb_file_per_table as the tablespace name.

mysql> create table t2 (c1 int primary key) tablespace = innodb_file_per_table
       data directory = '/remote/directory';

the innodb_file_per_table variable does not need to be enabled when using this method.

usage notes:

• mysql initially holds the tablespace data file open, preventing you from dismounting the device, but might eventually close the table if the server is busy. be careful not to accidentally dismount an external device while mysql is running, or start mysql while the device is disconnected. attempting to access a table when the associated tablespace data file is missing causes a serious error that requires a server restart.

  a server restart issues errors and warnings if the tablespace data file is not at the expected path. in this case, you can restore the tablespace data file from a backup or drop the table to remove the information about it from the data dictionary.

• before placing a tablespace on an nfs-mounted volume, review potential issues outlined in using nfs with mysql.

• if using an lvm snapshot, file copy, or other file-based mechanism to back up the tablespace data file, always use the flush tables ... for export statement first to ensure that all changes buffered in memory are flushed to disk before the backup occurs.

• using the data directory clause is an alternative to using symbolic links, which is not supported.

7 copying tablespaces to another instance

this section describes how to copy a file-per-table tablespaces from one mysql instance to another, otherwise known as the transportable tablespaces feature. this feature also supports partitioned innodb tables and individual innodb table partitions and subpartitions.

there are many reasons why you might copy an innodb file-per-table tablespace to a different instance:

• to run reports without putting extra load on a production server.

• to set up identical data for a table on a new slave server.

• to restore a backed-up version of a table or partition after a problem or mistake.

• as a faster way of moving data around than importing the results of a mysqldump command. the data is available immediately, rather than having to be re-inserted and the indexes rebuilt.

• to move a file-per-table tablespace to a server with storage medium that better suits system requirements. for example, you may want to have busy tables on an ssddevice, or large tables on a high-capacity hdd device.

limitations and usage notes

• the tablespace copy procedure is only possible when innodb_file_per_table is enabled, which is the default setting. tables residing in the shared system tablespace cannot be quiesced.

• when a table is quiesced, only read-only transactions are allowed on the affected table.

• when importing a tablespace, the page size must match the page size of the importing instance.

• alter table ... discard tablespace is supported for partitioned innodb tables, and alter table ... discard partition ... tablespace is supported forinnodb table partitions.

• discard tablespace is not supported for tablespaces with a parent-child (primary key-foreign key) relationship when foreign_key_checks is set to 1. before discarding a tablespace for parent-child tables, set foreign_key_checks=0. partitioned innodb tables do not support foreign keys.

• alter table ... import tablespace does not enforce foreign key constraints on imported data. if there are foreign key constraints between tables, all tables should be exported at the same (logical) point in time. partitioned innodb tables do not support foreign keys.

• alter table ... import tablespace and alter table ... import partition ... tablespace do not require a .cfg metadata file to import a tablespace. however, metadata checks are not performed when importing without a .cfg file, and a warning similar to the following is issued:

  message: innodb: io read error: (2, no such file or directory) error opening '.\
  test\t.cfg', will attempt to import without schema verification
  1 row in set (0.00 sec)

  the ability to import without a .cfg file may be more convenient when no schema mismatches are expected. additionally, the ability to import without a .cfg file could be useful in crash recovery scenarios in which metadata cannot be collected from an .ibd file.

  if no .cfg file is used, innodb uses the equivalent of a select max(ai_col) from table_name for update statement to initialize the in-memory auto-increment counter that is used in assigning values for to an auto_increment column. otherwise, the current maximum auto-increment counter value is read from the .cfgmetadata file. for related information, see innodb auto_increment counter initialization.

• due to a .cfg metadata file limitation, schema mismatches are not reported for partition type or partition definition differences when importing tablespace files for partitioned tables. column differences are reported.

• when running alter table ... discard partition ... tablespace and alter table ... import partition ... tablespace on subpartitioned tables, both partition and subpartition table names are allowed. when a partition name is specified, subpartitions of that partition are included in the operation.

• importing a tablespace file from another mysql server instance works if both instances have ga (general availability) status and the server instance into which the file is imported is at the same or higher release level within the same release series. importing a tablespace file into a server instance running an earlier release of mysql is not supported.

• in replication scenarios, innodb_file_per_table must be set to on on both the master and slave.

• on windows, innodb stores database, tablespace, and table names internally in lowercase. to avoid import problems on case-sensitive operating systems such as linux and unix, create all databases, tablespaces, and tables using lowercase names. a convenient way to accomplish this is to add the following line to the [mysqld]section of your my.cnf or my.ini file before creating databases, tablespaces, or tables:

  [mysqld]
  lower_case_table_names=1

  注意：it is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized.

• alter table ... discard tablespace and alter table ...import tablespace are not supported with tables that belong to an innodb general tablespace. for more information, see create tablespace.

• the default row format for innodb tables is configurable using the innodb_default_row_format configuration option. attempting to import a table that does not explicitly define a row format (row_format), or that uses row_format=default, could result in a schema mismatch error if the innodb_default_row_format setting on the source instance differs from the setting on the destination instance. for related information, see defining the row format of a table.

• when exporting an encrypted tablespace, innodb generates a .cfp file in addition to a .cfg metadata file. the .cfp file must be copied to the destination instance together with the .cfg file and tablespace file before performing the alter table ... import tablespace operation on the destination instance. the .cfp file contains a transfer key and an encrypted tablespace key. on import, innodb uses the transfer key to decrypt the tablespace key. for related information, seesection 15.6.3.9, “innodb data-at-rest encryption”.

• flush tables ... for export is not supported on tables that have a fulltext index. full-text search auxiliary tables are not flushed. after importing a table with afulltext index, run optimize table to rebuild the fulltext indexes. alternatively, drop fulltext indexes before the export operation and recreate them after importing the table on the destination instance.

8 moving tablespace files while the server is offline

the innodb_directories option, which defines directories to scan at startup for tablespace files, supports moving or restoring tablespace files to a new location while the server is offline. during startup, discovered tablespace files are used instead those referenced in the data dictionary, and the data dictionary is updated to reference the relocated files. if duplicate tablespace files are discovered by the scan, startup fails with an error indicating that multiple files were found for the same tablespace id.

the directories defined by the innodb_data_home_dir, innodb_undo_directory, and datadir configuration options are automatically appended to theinnodb_directories argument value. these directories are scanned at startup regardless of whether the innodb_directories option is specified explicitly. the implicit addition of these directories permits moving system tablespace files, the data directory, or undo tablespace files without configuring the innodb_directories setting. however, settings must be updated when directories change. for example, after relocating the data directory, you must update the --datadir setting before restarting the server.

the innodb_directories option may be specified in a startup command or mysql option file. quotes are used around the argument value because otherwise a semicolon (;) is interpreted as a special character by some command interpreters.

mysql option file:

[mysqld]
innodb_directories="directory_path_1;directory_path_2"

the following procedure is applicable to moving individual file-per-table and general tablespace files, system tablespace files, undo tablespace files, or the data directory. before moving files or directories, review the usage notes that follow.

1. stop the server.

2. move the tablespace files or directories.

3. make the new directory known to innodb.

   • if moving individual file-per-table or general tablespace files, add unknown directories to the innodb_directories value.

     • the directories defined by the innodb_data_home_dir, innodb_undo_directory, and datadir configuration options are automatically appended to theinnodb_directories argument value, so you need not specify these.

     • a file-per-table tablespace file can only be moved to a directory with same name as the schema. for example, if the actor table belongs to the sakilaschema, then the actor.ibd data file can only be moved to a directory named sakila.

     • general tablespace files cannot be moved to the data directory or a subdirectory of the data directory.

   • if moving system tablespace files, undo tablespaces, or the data directory, update the innodb_data_home_dir, innodb_undo_directory, and datadir settings, as necessary.

4. restart the server.

usage notes

• wildcard expressions cannot be used in the innodb_directories argument value.

• the innodb_directories scan also traverses subdirectories of specified directories. duplicate directories and subdirectories are discarded from the list of directories to be scanned.

• the innodb_directories option only supports moving innodb tablespace files. moving files that belong to a storage engine other than innodb is not supported. this restriction also applies when moving the entire data directory.

• the innodb_directories option supports renaming of tablespace files when moving files to a scanned directory. it also supports moving tablespaces files to other supported operating systems.

• when moving tablespace files to a different operating system, ensure that tablespace file names do not include prohibited characters or characters with a special meaning on the destination system.

• when moving a data directory from a windows operating system to a linux operating system, modify the binary log file paths in the binary log index file to use backward slashes instead of forward slashes. by default, the binary log index file has the same base name as the binary log file, with the extension '.index'. the location of the binary log index file is defined by --log-bin. the default location is the data directory.

• if moving tablespace files to a different operating system introduces cross-platform replication, it is the responsibility of the database administrator to ensure proper replication of ddl statements that contain platform-specific directories. statements that permit specifying directories include create table ... data directoryand create tablespace.

• the directory of file-per-table and general tablespace files created with an absolute path or in a location outside of the data directory should be added to theinnodb_directories argument value. otherwise, innodb is not able to locate these files during recovery. create table ... data directory and create tablespace permit creation of tablespace files with absolute paths. create tablespace also permits tablespace file directories that are relative to the data directory. to view tablespace file locations, query the information_schema.files table:

  mysql> select tablespace_name, file_name from information_schema.files \g

• create tablespace requires that the target directory exists and is known to innodb. known directories include those implicitly and explicitly defined by theinnodb_directories option.

9 innodb data-at-rest encryption

innodb supports data-at-rest encryption for file-per-table tablespaces, general tablespaces, the mysql system tablespace, redo logs, and undo logs.

as of mysql 8.0.16, setting an encryption default for schemas and general tablespaces is also supported, which permits dbas to control whether tables created in those schemas and tablespaces are encrypted.

innodb uses a two tier encryption key architecture, consisting of a master encryption key and tablespace keys. when a tablespace is encrypted, a tablespace key is encrypted and stored in the tablespace header. when an application or authenticated user wants to access encrypted tablespace data, innodb uses a master encryption key to decrypt the tablespace key. the decrypted version of a tablespace key never changes, but the master encryption key can be changed as required. this action is referred to as master key rotation.

the data-at-rest encryption feature relies on a keyring plugin for master encryption key management.

all mysql editions provide a keyring_file plugin, which stores keyring data in a file local to the server host.

mysql enterprise edition offers additional keyring plugins:

• the keyring_encrypted_file plugin, which stores keyring data in an encrypted file local to the server host.

• the keyring_okv plugin, which includes a kmip client (kmip 1.1) that uses a kmip-compatible product as a back end for keyring storage. supported kmip-compatible products include centralized key management solutions such as oracle key vault, gemalto keysecure, thales vormetric key management server, and fornetix key orchestration.

• the keyring_aws plugin, which communicates with the amazon web services key management service (aws kms) as a back end for key generation and uses a local file for key storage.

注意：the keyring_file and keyring_encrypted file plugins are not intended as regulatory compliance solutions. security standards such as pci, fips, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (hsms).

a secure and robust encryption key management solution is critical for security and for compliance with various security standards. when the data-at-rest encryption feature uses a centralized key management solution, the feature is referred to as “mysql enterprise transparent data encryption (tde)”.

the data-at-rest encryption feature supports the advanced encryption standard (aes) block-based encryption algorithm. it uses electronic codebook (ecb) block encryption mode for tablespace key encryption and cipher block chaining (cbc) block encryption mode for data encryption.

 

转载、节选于