Shulou(Shulou.com)06/01 Report--

0 Preface:

After MySQL 5.6 referenced the stand-alone undo tablespace, MySQL 5.7 made improvements on temporary tablespace.

It has been implemented to separate temporary tablespace from the ibdata (system tablespace file).

And you can restart the reset size to avoid problems like ibdata that are difficult to release.

But all of the following discussions focus only on InnoDB and specify innodb_file_per_table, using version 5.7.x of MySQL

0. New features share temporary tablespaces (shared temporary tablespace):

Shared temporary tablespaces appear in MySQL 5.7.1 to separate temporary tablespaces from system tablespace (system tablespace) files. The shared temporary table space is used to store data such as uncompressed InnoDB temporary table (non-compressed InnoDB temporary tables), relational object (related objects), rollback segment (rollback segment), etc. For more information, please see [MySQL 5.7 Reference Manual 8.4.4 Internal Temporary Table Use in MySQL]

Because of the particularity of the stored data, it will not participate in crash recovery, so there is no need to record redo log.

The default size of the shared temporary tablespace is 12MB. After the instance is closed, it will be deleted. It is created when the instance is started.

By default, the shared temporary tablespace is stored in ibtmp1 in innodb_data_home_dir, while innodb_data_home_dir defaults to datadir.

So the ibtmp1 is generally stored under the datadir, and obviously, its path is the same as the path of the shared tablespace, depending on the innodb_data_home_dir.

A new parameter innodb_temp_data_file_path is added, and the file name and extension size of the shared temporary table space can be modified by changing its value.

For example, add innodb_temp_data_file_path = temp_tablespace:64M:autoextend to the configuration file

Then after starting the instance, a temp_tablespace file with the size of 64MB will be generated

-rw-r- 1 root root 67108864 Jun 20 17:29 temp_tablespace

This parameter appears by default in 5.7.1, static, and the default value is ibtmp1:12M:autoextend.

Optimization of statistical information of InnoDB temporary table with new features

Because of the nature of temporary tables, it is impossible to query its metadata information in SHOW TABLES; and through information_schema.TABLES.

Older versions may only be viewed in some of the more troublesome ways:

Such as SHOW CREATE TABLE tmp_a\ G

After version 5. 7, a table has been added to iTunes to count the metadata information INNODB_TEMP_TABLE_INFO of the table.

You can view the definition of the table through Iclims:

SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO

+-+ +

| | TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED | |

+-+ +

| | 68 | # sql2b79_35_0 | 4 | 37 | FALSE | FALSE | |

+-+ +

1 row in set (0.00 sec)

Field description:

TABLE_ID: table id

NAME: table name. The table structure corresponding to this name is $NAME.frm. If the table is a compressed temporary table, the corresponding data file is $NAME.ibd, and vice versa.

N_COLS: the number of columns, one column that I showed created, and the other three hidden columns of InnoDB (DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR)

SPACE: the tablespace id of the temporary table, which is always non-zero and dynamically changes with the restart of the instance

PER_TABLE_TABLESPACE: if TRUE, the temporary table has its own temporary table space (with its own ibd file), and if FALSE, the temporary table uses a shared table space.

IS_COMPRESSED: if TRUE, the table is compressed, otherwise it is not compressed.

0 what's new: innodb_tmpdir

Versions that appear after 5.7.11 are used to store temporary data when doing some Online DDL.

The value of innodb_tmpdir overrides tmpdir, which is valid only for Online DDL.

0 shared temporary tablespace compared with tmpdir:

Through CREATE TEMPORARY TABLE... Created table, the table definition will be placed under tmpdir, default is / tmp

Tmpdir is not a new parameter and generally does not need to be specified. The default value is / tmp, which is mentioned here and compared with shared temporary tablespaces.

The tmpdir parameter is used to specify the directory where temporary files (temporary files) and temporary tables (temporary tables) are stored.

Can be set to a set merge for polling scheduling (use: split), if you want to use, it is recommended to specify multiple disk directories to improve performance.

In addition, for explicitly created temporary tables (create temporary table):

Unlike shared temporary tablespaces, tmpdir stores temporary independent tablespaces for compressed InnoDB temporary tables.

Here's a test to verify it:

Parameter check:

SELECT @ innodb_temp_data_file_path, @ @ innodb_file_per_table, @ @ tmpdir, @ @ innodb_data_home_dir

+-+

| | @ @ innodb_temp_data_file_path | @ @ innodb_file_per_table | @ @ tmpdir | @ @ innodb_data_home_dir |

+-+

| | ibtmp1:12M:autoextend | 1 | / tmp | NULL | |

+-+

1 row in set (0.00 sec)

First, create two temporary tables, both of which are InnoDB by default. The first one specifies the row format as COMRESSED, and the second one is not compressed:

Root@localhost [test] > CREATE TEMPORARY TABLE compress_table (id int, name char) ROW_FORMAT=COMPRESSED;Query OK, 0 rows affected (0.02 sec)

Root@localhost [test] > CREATE TEMPORARY TABLE uncompress_table (id int, name char)

Query OK, 0 rows affected (0.00 sec)

Root@localhost [test] > SHOW CREATE TABLE compress_table\ G

* * 1. Row *

Table: compress_table

Create Table: CREATE TEMPORARY TABLE `compress_ table` (

`id`int (11) DEFAULT NULL

`name` char (255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED

1 row in set (0.00 sec)

Root@localhost [test] > SHOW CREATE TABLE uncompress_table\ G

* * 1. Row *

Table: uncompress_table

Create Table: CREATE TEMPORARY TABLE `uncompress_ table` (

`id`int (11) DEFAULT NULL

`name` char (255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

Check the table definitions of the two temporary tables:

Root@localhost [test] > SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO

+-+ +

| | TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED | |

+-+ +

| | 73 | # sqlb48_3_1 | 5 | 58 | FALSE | FALSE |

| | 72 | # sqlb48_3_0 | 5 | 59 | TRUE | TRUE | +-+-+ |

2 rows in set (0.00 sec)

Based on TABLE_ID and IS_COMPRESSED and PER_TABLE_TABLESPACE parameters

As you can see, # sqlb48_3_0 is compress_table,#sqlb48_3_1 and uncompress_table

Once created, check the / tmp directory, which is tmpdir.

# ll / tmp/

Total 88

-rw-r- 1 root root 8586 Jun 20 16:38 # sqlb48_3_0.frm

-rw-r- 1 root root 65536 Jun 20 16:38 # sqlb48_3_0.ibd

-rw-r- 1 root root 8586 Jun 20 16:39 # sqlb48_3_1.frm

-rw-. 1 root root 0 Jan 3 2014 yum.log

You can see that the table definition files for both explicitly created temporary tables are placed under tmpdir.

In addition, # sqlb48_3_0 is the compressed table whose IS_COMPRESSED is TRUE, and the ibd file is also placed in the tmpdir file.

So in theory, the data from the uncompressed table # sqlb48_3_1 is put into ibtmp1, that is, in a shared temporary tablespace.

Simply verify that the idea of verification is to insert a large amount of data into two tables.

And check the size changes of the ibtmp1 file and # sqlb48_3_0.ibd file respectively:

For the compress_ table:

Root@localhost [test] > INSERT INTO compress_table SELECT id, name FROM a limit 50000

Query OK, 50000 rows affected (1.2 sec)

Records: 50000 Duplicates: 0 Warnings: 0

(table an is a table for test data)

Root@localhost [test] >\! Ls-l / tmp-rw-r- 1 root root 11534336 Jun 20 16:54 # sqlb48_3_0.ibd

Root@localhost [test] > INSERT INTO compress_table SELECT id, name FROM a limit 20000bot query OK, 20000 rows affected (.53 sec) Records: 20000 Duplicates: 0 Warnings: 0 (Table an is a table for test data)

Root@localhost [test] >\! Ls-l / tmp/*.ibd-rw-r- 1 root root 14680064 Jun 20 16:55 # sqlb48_3_0.ibd

It can be found that for the compressed InnoDB temporary table, the data is placed in the ibd file under tmpdir

Let's briefly test the uncompressed InnoDB temporary table:

Root@localhost [test] >\! Ls-l / data/mysql-data/mysql57-3357/datadir/ibtmp1

-rw-r- 1 root root 12582912 Jun 20 16:57 / data/mysql-data/mysql57-3357/datadir/ibtmp1

Root@localhost [test] > INSERT INTO uncompress_table SELECT id, name FROM a limit 50000

Query OK, 50000 rows affected (.53 sec)

Records: 50000 Duplicates: 0 Warnings: 0

Root@localhost [test] >\! Ls-l / data/mysql-data/mysql57-3357/datadir/ibtmp1

-rw-r- 1 root root 79691776 Jun 20 17:02 / data/mysql-data/mysql57-3357/datadir/ibtmp1

Obviously, uncompressed InnoDB temporary tables store data in a shared temporary tablespace.

The biggest common feature between things under tmpdir and shared temporary tablespaces is that after the instance is closed, it will be deleted.

0 slave_load_tmpdir

This parameter is not new to 5.7. the default value depends on the parameter of tmpdir.

Used to store special temporary files generated on the slave:

LOAD DATA INFILE appears on master. When it is recorded to binlog and sent to slave, when SQL thread extracts data from relaylog, writes it to the specified directory, then executes LOAD DATA LOCAL INFILE..., and then deletes the file.

This parameter is added for replication reliability and data consistency.

If you put it under tmpdir by default, if you restart at this time and the file is lost, it will cause the replication to fail.

If master uses such a statement, it is recommended that you specify the directory on a reliable storage device.

0 problems that may be encountered:

After MySQL 5.7.6, page size for 32KB and 64KB is supported. If you change page size to 32 or 64KB, you cannot use ROW_FORMAT=COMPRESSED. The maximum page size supported by this format is 16KB.

For ROW_FORMAT=COMPRESSED to take effect, innodb_file_format must be set to Barracuda.

0 reference documentation:

MySQL 5.7 Reference Manual 5.1.3 Server Option and Variable Reference

MySQL 5.7 Reference Manual 14.4.12 Temporary Tablespace

MySQL 5.7 Reference Manual 14.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Information Table

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.