0

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 _<table_name>_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 |
+------------+---------------------------+----------------------------+-------------------------+----------------+
Nir
  • 523
  • 1
  • 9
  • 23

1 Answers1

1

For a number of reasons, MySQL does not release free space back to the OS in a number of situations. (The common thread: MySQL picks speed over space.)

In this particular case, there were two choices (though you probably did not realize these details).

  • The default ALTER ... DROP COLUMN ... runs fast by simply changing the table definition and not worrying about the space consumed by the column.

  • ALTER ... ALGORITHM=COPY ... DROP COLUMN ... copies the table over and rebulds the indexes. This is slow, but does recoup the free space. But it is much slower, especially for a big table. OPTIMIZE TABLE was effectively done as part of the "copy".

You are stuck with the classic computer choice -- speed vs. space.

Partitioning is rarely useful; have you determined that it provides any benefit?

There is a "bug" in OPTIMIZE PARTITION -- it rebuilds every partition, not just the one you specify. (To rebuild a single partition, use REORGANIZE.)

Bill Karwin
  • 14,693
  • 2
  • 30
  • 42
Rick James
  • 78,038
  • 5
  • 47
  • 113
  • Thanks but I think you are missing my point. I wish to know why percona doesn't work as expected (see question edit). I wish to use it as it change the table without lock. which in this case is very important. – Nir Nov 01 '21 at 16:41
  • @Nir - pt-osc does (indirectly) use locks, but they tend to be very brief. Please describe the symptoms that you see. And, I wonder if the tool actually works with a Partitioned table, or perhaps not as the paragraph implies. Another issue: Does the table have an explicit PRIMARY KEY? – Rick James Nov 01 '21 at 16:53
  • https://www.percona.com/blog/2015/09/09/testing-mysql-partitioning-pt-online-schema-change/ see this link – Nir Nov 01 '21 at 18:11
  • Also, I do have a PK. on (id,timestamp) – Nir Nov 01 '21 at 18:11
  • so using alter table force algorithm=inplace freed the space without lock. does algorithm=copy have the same effect without locking? – Nir Nov 08 '21 at 10:02
  • 1
    @Nir - Without pt-osc, "=copy" blocks writes while copying over the table and reindexing its indexes. – Rick James Nov 08 '21 at 18:44