首页 > 数据库 >mysql-ibtmp1表空间暴涨

mysql-ibtmp1表空间暴涨

时间:2022-12-21 14:24:38浏览次数:49  
标签:ibtmp1 暴涨 mysql test table NULL tmp1

1、在做****巡检时,发现生产主机xxxx上的临时独立表空间ibtmp1暴涨至200G,借此梳理关于临时表空间相关的点
2、ibtmp1暴涨如何处理? 
2.1 简单说明 
ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径,文件名和大小,默认配置为ibtmp1:12M:autoextend,也就是说在文件系统磁盘足够的情况下,这个文件大小是可以无限增长的。 
2.2 解决方法 
a) 停数据库服务进程 
#  设置innodb_fast_shutdown参数 
SET GLOBAL innodb_fast_shutdown = 0;  # 此步骤可以省略 
#  关闭数据库实例 
/mysql/mysql5731/bin/mysqladmin -uroot -p'xxxx' -S /dbdata/data3306/my3306.sock  shutdown 

注意:在服务进程停止后,ibtmp1文件会自动清理 

b)  修改my.cnf配置文件 
为了避免ibtmp1文件无止境的暴涨导致再次出现此情况,可以修改参数,限制其文件最大尺寸。如果文件大小达到上限时,需要生成临时表的SQL无法被执行(一般这种SQL效率也比较低,可借此机会进行优化) 
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G  # 12M代表文件初始大小,5G代表最大size 

c) 重启mysql服务,重启后查看是否生效 

3、什么情况下会用到临时表?(本地环境模拟) 
当EXPLAIN 查看执行计划结果的 Extra 列中,如果包含 Using Temporary 就表示会用到临时表,例如如下几种常见的情况通常就会用到: 
a)  GROUP BY 无索引字段或GROUP  BY+ ORDER  BY 的子句字段不一样时; 
/**  先看一下表结构 */ 
mysql> show  create table  test_tmp1\G 
*************************** 1. row *************************** 
       Table: test_tmp1 
Create Table: CREATE TABLE `test_tmp1` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `name` varchar(50) DEFAULT NULL, 
  `col2` varchar(25) DEFAULT NULL, 
  PRIMARY KEY (`id`), 
  KEY `name` (`name`) 
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 
1 row in set (0.00 sec) 

/**  group  by无索引字段*/ 
mysql> explain select * from test_tmp1 group by  col2 ; 
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           | 
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 
|  1 | SIMPLE      | test_tmp1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | Using temporary; Using filesort | 
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 


/**  group by 与order by字段不一致时,及时group by和order by字段有索引也会使用 */ 
mysql> explain select name from test_tmp1 group by  name order by id desc; 
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+ 
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                     | 
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+ 
|  1 | SIMPLE      | test_tmp1 | NULL       | range | name          | name | 153     | NULL |    3 |   100.00 | Using index for group-by; Using temporary; Using filesort | 
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+ 
1 row in set, 1 warning (0.02 sec) 

b)  order by 与distinct 共用,其中distinct与order by里的字段不一致(主键字段除外) 
/**  例子中有无索引时会存在,如果2个字段都有索引会如何*/ 
mysql> alter table  test_tmp1 add key col2(col2); 
Query OK, 0 rows affected (1.07 sec) 
Records: 0  Duplicates: 0  Warnings: 0 

/**   结果如下,其实该写法与group by +order by 一样*/ 
mysql> explain select distinct col2  from test_tmp1 order  by  name; 
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+ 
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           | 
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+ 
|  1 | SIMPLE      | test_tmp1 | NULL       | index | col2          | col2 | 78      | NULL |    8 |   100.00 | Using temporary; Using filesort | 
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+ 
1 row in set, 1 warning (0.00 sec) 

c)  UNION查询(MySQL5.7后union all已不使用临时表) 
/**  先测一下union all的情况*/ 

mysql> explain select name from test_tmp1 union all  select name from test_tmp1 where id <10; 
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       | 
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 
|  1 | PRIMARY     | test_tmp1 | NULL       | index | NULL          | name    | 153     | NULL |    8 |   100.00 | Using index | 
|  2 | UNION       | test_tmp1 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    8 |   100.00 | Using where | 
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 
2 rows in set, 1 warning (0.01 sec) 

/**  再看一下union 作为对比,发现出现了使用临时表的情况*/ 
mysql> explain select name from test_tmp1 union   select name from test_tmp1 where id <10; 
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ 
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           | 
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ 
|  1 | PRIMARY      | test_tmp1  | NULL       | index | NULL          | name    | 153     | NULL |    8 |   100.00 | Using index     | 
|  2 | UNION        | test_tmp1  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    8 |   100.00 | Using where     | 
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL        | NULL    | NULL    | NULL | NULL |     NULL | Using temporary | 
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ 
3 rows in set, 1 warning (0.00 sec) 

d)  insert into select ...from ... 

/**  简单看一下本表的数据重复插入的情况 */ 
mysql> explain insert into test_tmp1(name,col2)  select name,col2 from test_tmp1; 
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           | 
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 
|  1 | INSERT      | test_tmp1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL            | 
|  1 | SIMPLE      | test_tmp1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | Using temporary | 
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 
2 rows in set (0.00 sec) 

小结: 上面列举的是最常见的使用临时表的情况,其中基本都是引起慢查询的因素,因此,如果遇到临时表空间文件暴涨是需要查看一下是否有大量的慢查询。 

4、和临时表的独立表空间相关的参数 
各参数之间相互影响,其中直接影响临时表空间的参数有如下几个: 
innodb_temp_data_file_path   #innodb临时表的独立表空间 
tmp_table_size               #内存临时表的大小设置(INNODB存储引擎) 
max_heap_table_size          #内存临时表的大小设置(MEMORY存储引擎) 
default_tmp_storage_engine   #默认内存临时表的存储引擎设置 
internal_tmp_disk_storage_engine  #默认磁盘临时表的存储引擎设置 

5、本地模拟一个ibtmp1文件快速暴涨的例子 
当临时表中需要存储的数据量超过了上限( tmp-table-size 或 max-heap-table-size 中取其大者),这个时候就需要产生基于磁盘的临时表了,也就是放在innodb_temp_data_file_path指定的临时表空间中,即ibtmp1文件中。 

5.1 参数调数并重启服务 
tmp_table_size = 2M 
max_heap_table_size=2M 
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:125M 
5.2 建表数据 
CREATE TABLE `t2` ( 
  `id_` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
  `panel_id_` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '面板id', 
  `cust_width_` int(10) DEFAULT NULL COMMENT '自定义宽', 
  `cust_height_` int(10) DEFAULT NULL COMMENT '自定义高', 
  `sn_` int(10) DEFAULT NULL COMMENT '排序', 
  `user_id_` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户id', 
  `create_time_` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

此时的ibtmp1文件的大小 
[root@db1 data]# du -sh * |grep ibtmp1 
12M     ibtmp1 

5.3 利用insert into ...select * from...的方式插入 
mysql> explain insert into t2 select * from t2; 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           | 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 
|  1 | INSERT      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL            | 
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   50 |   100.00 | Using temporary | 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 
mysql> insert into workflow.t2 select * from workflow.t2; 
查看ibtmp1文件的大小 
[mysql@db1 data]# du -sh * |grep ibtmp1 
125M    ibtmp1 

注意:此时ibtmp1文件已经达到最大值了 

5.4 继续测试,看看会发生什么? 
mysql> insert into workflow.t1 select * from workflow.t1; 
ERROR 1114 (HY000): The table '/mysql/data3307/tmp/#sql_4f98_0' is full  
# 报错原因是因为执行的sql需要的系统个表空间大于了ibtmp1的大小 

查看ibtmp1文件的大小 
[root@db1 data]# du -sh * |grep ibtmp1 
125M    ibtmp1 

5.5 查看max_heap_table_size和tmp_table_size 参数的大小 
mysql> show variables like '%table_size'; 
+---------------------+---------+ 
| Variable_name       | Value   | 
+---------------------+---------+ 
| max_heap_table_size | 2097152 | 
| tmp_table_size      | 2097152 | 
+---------------------+---------+ 

5.6 既然内部临时表(Internal Temporary Table)用于排序,分组,当需要的存储空间超过 tmp-table-size 上限的时候,使用临时表空间。临时表空间是磁盘,速度比不上内存,那可以加大tmp_table_size来优化 
调高tmp_table_size和max_heap_table_size参数值来调高内存临时表的上限 

mysql> set session tmp_table_size=3221225472; 
Query OK, 0 rows affected (0.00 sec) 

mysql> set session  max_heap_table_size=3221225472; 
Query OK, 0 rows affected (0.00 sec) 

mysql> show variables like '%table_size'; 
+---------------------+------------+ 
| Variable_name       | Value      | 
+---------------------+------------+ 
| max_heap_table_size | 3221225472 | 
| tmp_table_size      | 3221225472 | 
+---------------------+------------+ 
2 rows in set (0.00 sec) 


mysql> insert into t2 select * from t2 ; 
Query OK, 1638403 rows affected (43.84 sec) 
Records: 1638403  Duplicates: 0  Warnings: 0 

注意:当内存临时表够用时,会优先使用内存临时表 

总结: 
1、ibtmp1文件空间是可以重复利用的,大小即使达到设置的最大值,也可以继续使用; 
2、ibtmp1文件空间不能在线回收,只能重启服务,自动释放;

标签:ibtmp1,暴涨,mysql,test,table,NULL,tmp1
From: https://www.cnblogs.com/harda/p/16996148.html

相关文章

  • Mysql高可用之MMM架构
    MMM(Master-MasterreplicationmanagerforMySQL)是一套支持双主故障切换和双主日常管理的脚本程序。MMM使用Perl语言开发,主要用来监控和管理MySQLMaster-Master(双主)复制,......
  • mysql系统日志 (binlog, redolog, undolog, errorlog, generallog, relaylog, slowque
    mysql系统日志(binlog,redolog,undolog,errorlog,generallog,relaylog,slowquerylog) 1.错误日志errorlog错误日志记录着mysqld服务在启动,停止,和运行过程中发......
  • mysql-MDL锁
    针对****生产环境上出现过一次关于MDL锁导致DDL等待锁失败而出现连接表失败,在本地进行MDL锁复现。初始化环境,有一个test01.t1表,进行模拟MDL锁等待id14(A)id15(B......
  • mysql-表碎片清理和表空间收缩
    根据****热计费项目生产环境上,ibd文件异常大,借机梳理表碎片清理和表空间收缩的知识点 1、碎片清理的好处 降低访问表时的IO,提高mysql性能,释放表空间降低磁盘空间使用......
  • 基于Springboot+Mybatis+mysql+element-vue高校就业管理系统
    @目录一、系统介绍二、功能展示1.用户登陆注册2.个人信息(学生端)3.查看企业岗位信息(学生端)4.我的应聘(学生端)5.学生信息管理(辅导员)6.三方协议书审核(辅导员)7.查看班级就业......
  • 基于Java springboot+mybatis+mysql实现的校园新闻系统
    @目录一、系统介绍二、功能展示1.主页2.登录以及注册3.普通用户对新闻咨询的编辑、发布和删除4.管理员对新闻的审核发布和撤销取消发布三、代码展示四、获取源码一、系统......
  • MySQL统计某个数据库中有多少张表
    在一些命令行下无法查看某个数据库一共有多少张表的时候,可以采用下面的SQL语句SQL语句SELECTcount(*)TABLES,table_schemaFROMinformation_schema.TA......
  • MySQL 索引的创建、删除
    MySQL中索引的创建有三种方法,索引的删除有两种方法。一、创建索引(1)使用createindex#1.创建普通索引createindex索引名on表名(列名[(限制索引长度)]);#2.创建......
  • mysql 查询重复/删除重复的记录[多字段]
    #####查询重复数据SELECTt.*FROMlike_usert,(SELECTuser_id,COUNT(user_id),dynamc_id,COUNT(dynamc_id)FROMlike_userGROUPBYuser_id,dynamc_idHA......
  • MySQL 删除数据 批量删除(大量)数据
    在删除数据的时候根据不同的场景使用不同的方法,比如说删除表中部分数据、删除表的结构、删除所有记录并重置自增ID、批量删除大量数据等,可以使用delete、truncate、drop等......