主页 / dba / 问题 / 301972
Accepted
Nir
Asked: 2021-11-02 05:14:42 +0800 CST

使用 percona 删除列不会缩小表大小

  • 772

我在 Aurora MySQL 5.7 中有一张表。表的分区很少，行数为 800m，权重为 2tb。最近我使用 percona 删除了几列。令人惊讶的是，桌子的大小没有改变（在information_schema.tables.

percona 进行更改的方式是_<table_name>_new在原始表上使用带有触发器的新表。它创建一个具有相同 DDL 的空新表，执行我们希望的更改，并使用触发器将所有内容复制到新表中以使其保持最新。一旦数据同步 - percona 重命名表并删除旧表。所以表是从头开始构建的（没有锁定）。

但是，运行后alter table optimize partition我看到大小缩小到 250gb。任何人都有解释或知道我做错了什么？

pt 命令：

pt-online-schema-change --user $MYSQL_DBA_USER --password $MYSQL_DBA_PASS --host $MYSQL_WRITER D=db,t=table_data --alter "drop column a1, drop column a2"  --execute --max-load Threads_running=18446744073709551606 --critical-load Threads_running=18446744073709551606 --recursion-method=none

优化命令：

MySQL [(db)]> select table_rows,data_length/power(1024,3), index_length/power(1024,3),DATA_FREE/power(1024,3),AVG_ROW_LENGTH  from information_schema.tables where table_name='table_data';
+------------+---------------------------+----------------------------+-------------------------+----------------+
| table_rows | data_length/power(1024,3) | index_length/power(1024,3) | DATA_FREE/power(1024,3) | AVG_ROW_LENGTH |
+------------+---------------------------+----------------------------+-------------------------+----------------+
|  610884663 |        1847.7273712158203 |         202.40484619140625 |            0.0322265625 |           3247 |
+------------+---------------------------+----------------------------+-------------------------+----------------+
1 row in set (0.00 sec)


MySQL [db]> ALTER TABLE table_data OPTIMIZE PARTITION p20210601;
+---------------+----------+----------+---------------------------------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                                                    |
+---------------+----------+----------+---------------------------------------------------------------------------------------------+
| db.table_data | optimize | note     | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. |
| db.table_data | optimize | status   | OK                                                                                          |
+------------------------+----------+----------+---------------------------------------------------------------------------------------------+
2 rows in set (5 hours 39 min 40.95 sec)

MySQL [db]>
MySQL [db]> select table_rows,data_length/power(1024,3), index_length/power(1024,3),DATA_FREE/power(1024,3),AVG_ROW_LENGTH  from information_schema.tables where table_name='table_data';


+------------+---------------------------+----------------------------+-------------------------+----------------+
| table_rows | data_length/power(1024,3) | index_length/power(1024,3) | DATA_FREE/power(1024,3) | AVG_ROW_LENGTH |
+------------+---------------------------+----------------------------+-------------------------+----------------+
|  736965899 |        104.25639343261719 |         155.98052978515625 |            0.0244140625 |            151 |
+------------+---------------------------+----------------------------+-------------------------+----------------+
mysql mysql-5.7

1 个回答

  1. Best Answer

    由于多种原因，MySQL 在多种情况下不会将空闲空间释放回操作系统。（共同点：MySQL 选择速度而不是空间。）

    在这种特殊情况下，有两种选择（尽管您可能没有意识到这些细节）。

    • 默认ALTER ... DROP COLUMN ...运行速度很快，只需更改表定义而不用担心列占用的空间。

    • ALTER ... ALGORITHM=COPY ... DROP COLUMN ...复制表并重建索引。这很慢，但确实收回了可用空间。但它要慢得多，尤其是对于一张大桌子。 OPTIMIZE TABLE作为“副本”的一部分有效地完成了。

    你被经典的计算机选择困住了——速度与空间。

    分区很少有用；你确定它有什么好处吗？

    有一个“错误” OPTIMIZE PARTITION——它会重建每个分区，而不仅仅是您指定的分区。（要重建单个分区，请使用REORGANIZE。）

    • 1

相关问题