GreatSQL茶话会——分享一下你的五一假期

GreatSQL社区»论坛问答 › GreatSQL › GreatSQL

[已解决] delete后空间无法释放

554 4 2022-8-30 15:34

本帖最后由 fuhao009 于 2022-8-30 15:40 编辑

Greatsql 8.0.25-16
# 删除数据后,文件大小没有缩减

(Tue Aug 30 15:25:32 2022)[hsyq@GreatSQL][c_station]>delete from test01;
Query OK, 59251 rows affected (2.70 sec)
# 查看数据已经没有了

(Tue Aug 30 15:26:57 2022)[hsyq@GreatSQL][c_station]>select count(1) from test01
-> ;
+----------+
| count(1) |
+----------+
|       0 |
+----------+

# 查看表文件仍然是有数据的状态

[root@mgr02 c_station]# ll -h test01.ibd
-rw-r----- 1 mysql mysql 72M Aug 30 15:27 test01.ibd

# 事务积压状态

(Tue Aug 30 15:36:20 2022)[hsyq@GreatSQL][c_station]>SELECT MEMBER_ID AS id, COUNT_TRANSACTIONS_IN_QUEUE AS trx_tobe_verified, COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE AS trx_tobe_applied, COUNT_TRANSACTIONS_CHECKED AS trx_chkd, COUNT_TRANSACTIONS_REMOTE_APPLIED AS trx_done, COUNT_TRANSACTIONS_LOCAL_PROPOSED AS proposed FROM performance_schema.replication_group_member_stats;
+--------------------------------------+-------------------+------------------+----------+----------+----------+
| id                                  | trx_tobe_verified | trx_tobe_applied | trx_chkd | trx_done | proposed |
+--------------------------------------+-------------------+------------------+----------+----------+----------+
| 8eeedfb2-f5b5-11ec-bffb-fa160a219e3a |                0 |             0 |  4685988 |  4609338 | 76651 |
| a34484fb-f5b6-11ec-80a9-fa160a219e09 |                0 |             0 | 76553 | 76553 |       0 |
| c7acd552-f5b2-11ec-ba1e-fa160a219e07 |                0 |             0 |  4686352 |  4686355 |       0 |
+--------------------------------------+-------------------+------------------+----------+----------+----------+
3 rows in set (0.00 sec)
# 组状态

(Tue Aug 30 15:33:51 2022)[hsyq@GreatSQL][c_station]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME             | MEMBER_ID                         | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8eeedfb2-f5b5-11ec-bffb-fa160a219e3a | 10.33.158.58 |       3306 | ONLINE    | PRIMARY    | 8.0.25       |
| group_replication_applier | a34484fb-f5b6-11ec-80a9-fa160a219e09 | 10.33.158.9  |       3306 | ONLINE    | SECONDARY | 8.0.25       |
| group_replication_applier | c7acd552-f5b2-11ec-ba1e-fa160a219e07 | 10.33.158.7  |       3306 | ONLINE    | SECONDARY | 8.0.25       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

全部回复(4)

yejr 2022-8-30 16:01:53

delete from table这种方式删除数据后，不会自动回收磁盘空间，需要再手动回收。
如果是想删除所有数据，最好是直接执行truncate table。

回复采纳

fuhao009 2022-8-30 16:04:55

yejr 发表于 2022-8-30 16:01
delete from table这种方式删除数据后，不会自动回收磁盘空间，需要再手动回收。
如果是想删除所有数据，最 ...

如果只是删除部分数据，想回收又不能重启mysql,有没有什么好的办法

回复采纳

fuhao009 2022-8-30 16:38:06

# 碎片整理
ALTER TABLE test01 ENGINE = InnoDB;
# 分析表
analyze table test01;
解决

回复采纳

yejr 2022-8-30 16:48:32

fuhao009 发表于 2022-8-30 16:38
# 碎片整理
ALTER TABLE test01 ENGINE = InnoDB;
# 分析表

执行 ALTER TABLE 就够了，analyze table不是必须的

回复已采纳

fuhao009

25 主题	0 博客	93 贡献

Rank: 2

积分: 157

互动与交流: 社区活动; 论坛问答; 加入社区; 贡献者指南

学习与应用: 文章博客; 文档资料; 视频资料; 常见问题

发现社区: 社区准则; 建议反馈; 社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号

社区小助手

QQ群

友情链接: 万里数据库开源中国墨天轮开放原子开源基金会 OpenEuler

©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )

GMT+8, 2024-5-19 10:52 , Processed in 0.016659 second(s), 12 queries , Redis On.

快速回复 返回顶部 返回列表