-1

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 |
+------------+---------------------------+----------------------------+-------------------------+----------------+
4
  • Does this answer your question? Deleting & Reclaiming space from InnoDB table
    – bbaird
    Nov 1, 2021 at 15:16
  • Please see my edit on how percona works. I'm aware of the copy/rebuild but I do not wish to use it as it lock the table.
    – Nir
    Nov 1, 2021 at 16:39
  • Your pt-online-schema-change command doesn't have the --execute option; is that a copy/paste error?
    – mustaccio
    Nov 1, 2021 at 21:32
  • sorry, I re-edit the question
    – Nir
    Nov 2, 2021 at 12:59

1 Answer 1

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.)

6
  • 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 1, 2021 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 1, 2021 at 16:53
  • percona.com/blog/2015/09/09/… see this link
    – Nir
    Nov 1, 2021 at 18:11
  • Also, I do have a PK. on (id,timestamp)
    – Nir
    Nov 1, 2021 at 18:11
  • 1
    @Nir - Without pt-osc, "=copy" blocks writes while copying over the table and reindexing its indexes.
    – Rick James
    Nov 8, 2021 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.