Sample Header Ad - 728x90

Drop column with percona does not shrink table size

0 votes
1 answer
420 views
I have a table in Aurora MySQL 5.7. table has few partitions with 800m rows and weights 2tb. Recently I dropped few column using percona. Surprisingly the table size did not change (looking in information_schema.tables. The way percona doing a change is using new table __new with triggers on the original table. it creates an empty new table with same DDL, execute the changes we wish, and copies everything to the new table with the triggers to keep it up to date. once the data is synced - percona renames the tables and drop the old one. So the table is been build from scratch (without locking). However, after running alter table optimize partition I saw the size shrunk to 250gb. Anyone have explanation or know what I did wrong? pt command: 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 optimize command: 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 | +------------+---------------------------+----------------------------+-------------------------+----------------+
Asked by Nir (529 rep)
Nov 1, 2021, 01:14 PM
Last activity: Nov 2, 2021, 12:57 PM