万答#7,如何批量删数据和调整系统表空间

ywz888
发布于 2022-8-29 15:37
浏览
0收藏

前情提要:


业务需要删除大量数据,如果直接 delete 会造成如下问题:

 

● 1.会产生大事务,造成主从延迟,影响数据库高可用切换。


● 2.系统表空间会不断膨胀。


● 3.锁定的记录多,更容易可能导致锁等待。


问1:如何优雅的删除大量数据

 

答:

 

● 1.如果表不需要就直接 drop


● 2.如果只保留表结构用 truncate


● 3.如果只保留部分数据可以使用 pt-archive 进行分批删除


● 特别注意,如果表太大的话,直接drop会truncate可能会造成大量IO导致数据库出现短暂响应延迟,可以通过硬链接的方式对表删除处理

 


问2:系统表空间不断膨胀怎么处理

 

答:

 

1.如果是已存在的数据库 无法在线收缩,那就通过mysqldump的方式建立新的从库,然后主从切换

 

2.新实例如何处理

 

● a. 可以设置独立表空间,关键参数

innodb_file_per_table

 

独立表空间也会产生碎片,但是可以通过 OPTIMIZE TABLE 或 ALTER TABLE xxxx ENGINE=INNODB 进行碎片回收,5.7之后该操作属online ddl,具体可以自行测试

 

● b. 设置独立UNDO空间,然后设置自动回收。


5.6 版本就支持独立UNDO空间,但是不支持在线回收,关键参数

innodb_undo_directory
innodb_undo_tablespaces
innodb_undo_logs

 

5.7 版本增加了在线回收的功能,关键参数

innodb_undo_log_truncate
innodb_max_undo_log_size
innodb_purge_rseg_truncate_frequency

 

8.0 版本中undo log的管理更加灵活,主要如下改进

 

● 1.可以动态创建或删除UNDO表空间


● 2.可以动态增加或减少UNDO表空间的数量


● 3.无论是否要进行InnoDB恢复,也可以在启动前更改相关设置


问:ibtmp文件不断增大,怎么处理

 

答:

 

5.7 版本可以设置限制ibtmp大小,但是需要重启实例;同时超过设定的最大值会导致SQL执行失败,关键参数

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

 

8.0 版本临时表空间有区分全局和session级;垃圾SQL生成的临时表空间随着SQL的结束也会跟着自动释放。

 

Enjoy GreatSQL :)

 

文章转载自公众号:GreatSQL社区

分类
标签
已于2022-8-29 15:37:07修改
收藏
回复
举报
回复
    相关推荐