0

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
Nitin Pund
  • 1,082
  • 2
  • 9
  • 23
  • So you did more or less tryed these [Deleting & Reclaiming space from InnoDB table](https://dba.stackexchange.com/questions/64134/deleting-reclaiming-space-from-innodb-table) or [MySQL InnoDB not releasing disk space after deleting data rows from table](https://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table) things already ? – Raymond Nijland Oct 01 '19 at 14:13
  • @RaymondNijland I have already tried all the steps mentioned in the solution except persona tools but no success – Nitin Pund Oct 01 '19 at 14:14
  • Consider contacting Amazon they might have changed the MySQL source code and removed that feature and recompiled.. – Raymond Nijland Oct 01 '19 at 14:15
  • @RaymondNijland Yes, planning to do the same – Nitin Pund Oct 01 '19 at 14:17
  • did you happen to delete the transaction logs? https://stackoverflow.com/questions/3394132/where-can-i-find-the-mysql-transaction-logLooks like that might be culprit as well. – Sudhendu Oct 01 '19 at 17:48
  • @Sudhendu where I will get that log on AWS RDS – Nitin Pund Oct 01 '19 at 18:09
  • Do you have a `COMMIT` after each batch? And/or did you have `autocommit=ON`? – Rick James Jan 03 '20 at 23:11

1 Answers1

0

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

Pierre
  • 716
  • 4
  • 10