vlambda博客
学习文章列表

最近遇到一个MySQL数据导入时候遇到问题，先来看下问题产生的具体报错信息如下所示：

# mysql -u dba_admin -p'xxxxxx' -h 127.0.0.1 -P4306 TEST_RT < test2.sql mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1114 (HY000) at line 5: The table '/data/mysql/tmp/#sql_13c53_2' is full

从报错信息看，应该是创建的临时表空间不够用了，最直接的感觉，是不是磁盘空间满了，接下来查看一下系统的磁盘空间:

# df -ThFilesystem Type Size Used Avail Use% Mounted on/dev/sda2 ext4 25G 7.6G 16G 33% /tmpfs tmpfs 7.8G 12K 7.8G 1% /dev/shm/dev/sda1 ext4 190M 78M 103M 44% /boot/dev/data ext4 291G 173G 103G 63% /data

发现磁盘空间还有100多G，应该不是磁盘空间满了导致的问题

接下来，应该就是临时表空间ibtmp1满了，查看临时表空间文件的大小

# ll ibtmp1-rw-r----- 1 mysql mysql 10737418240 Feb 22 19:39 ibtmp1

发现临时表空间已经达到了10G，我们检查一下临时表空间大小的最大限制，临时表空间大小设置由参数innodb_temp_data_file_path空间，关于该参数相关说明，会在下面的文章内容中解释说明；

mysql>show variables like '%innodb_temp_data_file_path%';+----------------------------+------------------------------+| Variable_name | Value |+----------------------------+------------------------------+| innodb_temp_data_file_path | ibtmp1:1G:autoextend:max:10G |+----------------------------+------------------------------+1 row in set (0.00 sec) 同时查看一下元信息中关于临时表空间的记录：mysql>SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES -> AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES -> WHERE TABLESPACE_NAME = 'innodb_temporary'\G*************************** 1. row *************************** FILE_NAME: /data/mysql/data/ibtmp1TABLESPACE_NAME: innodb_temporary ENGINE: InnoDB INITIAL_SIZE: 1073741824TotalSizeBytes: 10737418240 DATA_FREE: 9668919296 MAXIMUM_SIZE: 107374182401 row in set (0.00 sec) 说明：The TotalSizeBytes value reports the current size of the temporary tablespace data file. For information about other field valuesTotalSizeBytes值是指临时表空间数据文件的当前大小。

从上述结果看，临时表空间 ibtmp1最大设置的是10G，那么在导入过程中占用的临时表空间大小已经超过最大的限制，所以出现了文章开头的错误提示；

问题分析到这里，基本就比较清楚了，那具体怎么解决呢？先不着急，我们来简单说说MySQL中临时表空间相关的一些知识；

下面是官方文档中关于临时表空间的重要内容说明：

By default, the temporary tablespace data file is autoextending and increases in size as necessary to accommodate on-disk temporary tables. For example, if an operation creates a temporary table that is 20MB in size, the temporary tablespace data file, which is 12MB in size by default when created, extends in size to accommodate it. When temporary tables are dropped, freed space can be reused for new temporary tables, but the data file remains at the extended size.

An autoextending temporary tablespace data file can become large in environments that use large temporary tables or that use temporary tables extensively. A large data file can also result from long running queries that use temporary tables.

To reclaim disk space occupied by a temporary tablespace data file, restart the MySQL server. Restarting the server removes and recreates the temporary tablespace data file according to the attributes defined by innodb_temp_data_file_path.

To prevent the temporary data file from becoming too large, you can configure the innodb_temp_data_file_path variable to specify a maximum file size.

When the data file reaches the maximum size, queries fail with an error indicating that the table is full. Configuring innodb_temp_data_file_path requires restarting the server.

Alternatively, configure the default_tmp_storage_engine and internal_tmp_disk_storage_engine variables, which define the storage engine to use for user-created and on-disk internal temporary tables, respectively. Both variables are set to InnoDB by default. The MyISAM storage engine uses an individual file for each temporary table, which is removed when the temporary table is dropped.

默认情况下，临时表空间数据文件是自动扩展的，并根据需要增加大小，以容纳磁盘上的临时表。例如，如果一个操作创建了一个大小为20MB的临时表，那么临时表空间数据文件（创建时默认大小为12MB）会扩展大小以容纳它。删除临时表时，释放的空间可以重新用于新的临时表，但数据文件仍保持扩展大小。

在使用大型临时表或广泛使用临时表的环境中，自动扩展临时表空间数据文件可能会变大。使用临时表的长时间运行的查询也可能产生大数据文件。有时执行SQL请求时会产生临时表，极端情况下，可能导致临时表空间文件暴涨，之前遇到过最高涨到直接把磁盘占满的情况，导致业务数据无法写入的问题，可见临时表空间设置最大大小限制的重要性；为了防止临时数据文件变得太大，可以配置innodb_temp_data_file_path变量以指定最大文件大小；当数据文件达到最大限制时，查询将失败，并出现一个错误，指示表已满（具体的报错就是文章开头的错误）。

[mysqld]

innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

要回收临时表空间数据文件占用的磁盘空间，需要重新启动MySQL服务器。重新启动服务器会根据innodb_temp_data_file_path定义的属性删除并重新创建临时表空间数据文件，和 ibdata1 不同，ibtmp1 重启时会被重新初始化而 ibdata1 则不可以。

下面是关于临时表中几个比较重要的参数：

（1）default_tmp_storage_engine 和 internal_tmp_disk_storage_engine

这两个变量分别定义了用于用户创建和磁盘上内部临时表的存储引擎。默认情况下，这两个变量都设置为InnoDB。MyISAM存储引擎为每个临时表使用一个单独的文件，当临时表被删除时，该文件将被删除。

（2）innodb_temp_data_file_path

The temporary tablespace data file cannot have the same name as another InnoDB data file. Any inability or error creating a temporary tablespace data file is treated as fatal and server startup is refused. The temporary tablespace has a dynamically generated space ID, which can change on each server restart.

File sizes are specified KB, MB or GB (1024MB) by appending K, M or G to the size value. The sum of the sizes of the files must be slightly larger than 12MB.

The size limit of individual files is determined by your operating system. You can set the file size to more than 4GB on operating systems that support large files. Use of raw disk partitions for temporary tablespace data files is not supported.

If you specify the autoextend option, InnoDB extends the data file if it runs out of free space. The autoextend increment is 64MB by default. To modify the increment, change the innodb_autoextend_increment system variable.

The full directory path for temporary tablespace data files is formed by concatenating the paths defined by innodb_data_home_dir and innodb_temp_data_file_path.

The temporary tablespace is shared by all non-compressed InnoDB temporary tables. Compressed temporary tables reside in file-per-table tablespace files created in the temporary file directory, which is defined by the tmpdir configuration option.

临时表空间数据文件不能与另一个InnoDB数据文件同名。临时表空间使用动态的表空间ID，因此每次重启时都会变化（每次重启时，都会重新初始化临时表空间文件）。

临时表空间文件大小通过在大小值后面附加K、M或G来指定KB、MB或GB（1024MB）。文件大小之和必须略大于12MB。

单个文件的大小限制由操作系统决定。在支持大文件的操作系统上，可以将文件大小设置的更大。不支持裸设备（raw device）用于临时表空间数据文件。

如果指定autoextend选项，InnoDB将在数据文件的可用空间不足时扩展该文件。默认情况下，自动扩展增量为64MB。通过参数innodb_autoextend_increment 变量控制每次扩展增量的大小。

临时表空间数据文件的完整目录路径是通过连接innodb_data_home_dir和innodb_temp_data_file_path定义的路径形成的。

临时表空间由所有未压缩的InnoDB临时表共享。压缩的临时表位于临时文件目录中创建的每个表的文件表空间文件中，临时文件目录由tmpdir配置选项定义。

（3）tmp_table_size

The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.

The actual limit is the smaller of tmp_table_size and max_heap_table_size. When an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table. The internal_tmp_disk_storage_engine option defines the storage engine used for on-disk temporary tables.

Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing Created_tmp_disk_tables and Created_tmp_tables values.

内存中临时表的最大大小。此变量不适用于用户创建的内存表。

实际限制是tmp_table_size和max_heap_table_size中的较小者。当内存中的临时表超过限制时，MySQL会自动将其转换为磁盘上的临时表。internal_tmp_disk_storage_engine选项定义了用于磁盘上临时表的存储引擎。

如果执行了许多高级分组查询，并且拥有大量内存，需要考虑增加tmp_table_size的值（如果需要，还可以增加max_heap_table_size）

关于临时表空间相关的内容就先介绍到这里；那么针对文章开头报错的问题，我们该如何解决呢？

1、调整临时表空间的最大大小限制，但是该方法修改参数需要重启实例才会生效，并且这种方法依赖于磁盘大小限制，并且无法从根本上解决问题，显然这种方法在生产上是不可行；

2、检查导入的sql文件的SQL语句，发现只有一个SQL：insert into xxxx select xxxx;

本身该语法没问题，问题就出在了select xxxx，select查询是一个多表关联的的大查询，并且查询的数据量是最近两年的时间，并且查询还要做group by,order by等排序聚合操作，除了表的数据量大之外，表都属于大宽表，所以在导入执行一段时间就出现了ERROR 1114 (HY000) at line 5: The table ‘/data/mysql/tmp/#sql_13c53_2’ is full的报错

解决方法：

将select查询的根据时间查询进行条件拆分。拆分成不同查询时间范围的SQL语句，重新导入即可；

临时表空间使用小结：

1、设置 innodb_temp_data_file_path 选项，最好设定文件最大上限，超过上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可借此机会进行优化）。

2、检查 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO，找到最大的临时表对应的线程，kill之即可释放，但 ibtmp1 文件则不能释放，除非重启。

3、重启实例，释放 ibtmp1 文件，和 ibdata1 不同，ibtmp1 重启时会被重新初始化而 ibdata1 则不可以

4、定期检查运行时长超过N秒的SQL，考虑干掉，避免垃圾SQL长时间运行影响业务。