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:
-
shut down the mysql server.
-
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 ininnodb_data_file_path
. -
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 theinnodb_data_file_path
can be specified as auto-extending. -
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
-
when you create a new data file, specify the keyword
newraw
immediately after the data file size for theinnodb_data_file_path
option. the partition must be at least as large as the size that you specify. note that 1mb ininnodb
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
-
restart the server.
innodb
notices thenewraw
keyword and initializes the new partition. however, do not create or change anyinnodb
tables yet. otherwise, when you next restart the server,innodb
reinitializes the partition and your changes are lost. (as a safety measureinnodb
prevents users from modifying data when any partition withnewraw
is specified.) -
after
innodb
has initialized the new partition, stop the server, changenewraw
in the data file specification toraw
:[mysqld] innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:3graw;/dev/hdd2:2graw
-
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.
-
when you create a new data file, specify the keyword
newraw
immediately after the data file size for theinnodb_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. -
restart the server.
innodb
notices thenewraw
keyword and initializes the new partition. -
after
innodb
has initialized the new partition, stop the server, changenewraw
in the data file specification toraw
:[mysqld] innodb_data_home_dir= innodb_data_file_path=//./d::10graw
-
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-copyingalter 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 anoptimize 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, runningoptimize 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
ortext
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 too_direct
. as a result, there are possible performance improvements when using file-per-table tablespaces in conjunction withinnodb_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 separatefsync
operation for each file, write operations on multiple tables cannot be combined into a single i/o operation. this may requireinnodb
to perform a higher total number offsync
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 theinnodb_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
file. unlike the tbl_name
.ibdmyisam
storage engine, with its separate
andtbl_name
.myd
files for indexes and data, tbl_name
.myiinnodb
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_table
setting. 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
orinnodb_file_per_table
settings, nor do these variables have any effect on general tablespaces. -
the
tablespace
option can be used withcreate table
to create tables in a general tablespaces, file-per-table tablespace, or in the system tablespace. -
the
tablespace
option can be used withalter 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
or tbl_name
... tablespace [=] tablespace_name
alter table
to add tables to the tablespace, as shown in the following examples:tbl_name
tablespace [=]tablespace_name
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 tablespace
.tablespace_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;
注意:
is a case-sensitive identifier in mysql.tablespace_name
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-copyingalter table
operation is not released back to the operating system as it is for file-per-table tablespaces. -
alter table ... discard tablespace
andalter 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 tabalespace
syntax.
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 tablespace
statement.tablespace_name
set active
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
statement. attempting to drop an undo tablespace that is not empty returns an error.tablespace_name
set active
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
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.tablespace_name
set inactive
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_frequency
variable, 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 directory
clause 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 partitionedinnodb
tables, andalter 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 whenforeign_key_checks
is set to1
. before discarding a tablespace for parent-child tables, setforeign_key_checks=0
. partitionedinnodb
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. partitionedinnodb
tables do not support foreign keys. -
alter table ... import tablespace
andalter 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 aselect max(ai_col) from
statement to initialize the in-memory auto-increment counter that is used in assigning values for to antable_name
for updateauto_increment
column. otherwise, the current maximum auto-increment counter value is read from the.cfg
metadata 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
andalter 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 toon
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 yourmy.cnf
ormy.ini
file before creating databases, tablespaces, or tables:[mysqld] lower_case_table_names=1
注意:it is prohibited to start the server with alower_case_table_names
setting that is different from the setting used when the server was initialized. -
alter table ... discard tablespace
andalter table ...import tablespace
are not supported with tables that belong to aninnodb
general tablespace. for more information, seecreate tablespace
. -
the default row format for
innodb
tables is configurable using theinnodb_default_row_format
configuration option. attempting to import a table that does not explicitly define a row format (row_format
), or that usesrow_format=default
, could result in a schema mismatch error if theinnodb_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 thealter 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, runoptimize table
to rebuild thefulltext
indexes. alternatively, dropfulltext
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.
-
stop the server.
-
move the tablespace files or directories.
-
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
, anddatadir
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 thesakila
schema, then theactor.ibd
data file can only be moved to a directory namedsakila
. -
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
, anddatadir
settings, as necessary.
-
-
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 movinginnodb
tablespace files. moving files that belong to a storage engine other thaninnodb
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 directory
andcreate 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 the
innodb_directories
argument value. otherwise,innodb
is not able to locate these files during recovery.create table ... data directory
andcreate 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 theinformation_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 toinnodb
. 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.
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.
转载、节选于
上一篇: vue路由传参的三种方式
下一篇: python3基础之“小练习(2)”
推荐阅读
-
InnoDB On-Disk Structures(三)--Tablespaces (转载)
-
InnoDB On-Disk Structures(四)--Doublewrite Buffer (转载)
-
InnoDB On-Disk Structures(五)-- Redo Log & Undo Logs (转载)
-
InnoDB Architecture (InnoDB In-Memory Structures 转载)
-
InnoDB On-Disk Structures--Tables (转载)
-
InnoDB On-Disk Structures(三)--Tablespaces (转载)
-
InnoDB On-Disk Structures(四)--Doublewrite Buffer (转载)
-
InnoDB On-Disk Structures(五)-- Redo Log & Undo Logs (转载)
-
InnoDB Architecture (InnoDB In-Memory Structures 转载)
-
InnoDB On-Disk Structures--Tables (转载)