AWS RDS Table size increased after deleting the data using procedure

Question

Hello All,

I am currently working on the task which contains the deletion of the data from the mysql database.

Right now I have deleted data from only one table which has approx 80 Millions of records.

Table size before deleting the data was 40 GB among which 20 GB is data size and 20 GB is index size. When I delete the data from the table in batches of 10000 using mysql procedure, table size increases.

Following are the steps that I have executed

• Before performing delete operation table size was 40 GB (20 GB data, 20 GB index)
• After deleting 6.5 million records, in a bunch of 10000 at a time using procedure, size increases by 18 GB (9 GB data, 9 GB index)
• After this, by refering other Stack overflow answers, I performed the following steps
  • Optimized Table
  • defragmented table
  • exported table sql, deleted an existing table and reimported it
  • Exported Database sql and imported it in to new RDS

But, still no success. Table size not getting reduced, rather it increases each time.

• I have mysql version 5.6.34 installed
• mysql_file_per_table enabled
• RDS logs enabled

Answer 1

As nobody answered but very good pointers in the comment;

As of today, deleting records does not necessary free the actual space on RDS/MySQL (I just deleted 1.5TB) and the table space usage remained the same.

As hinted in the comment and other question:

optimize table log;

Does solve the problem. Also "Table does not support optimize, doing recreate + analyze instead". hth