6

In mydatabase, there is a table table1 with almost 8M row, after deleting around 7M rows from the table, the size of the DB and the table itself remains the same.

Table size:

+---------------------------------------------+-----------+
| Table                                       | Size (MB) |
+---------------------------------------------+-----------+
| Table1                                      |     96000 |

Delete query:

DELETE FROM table1 WHERE foo < 'boo'; 
Query OK, 7809000 rows affected (43.25 sec)

Size after deleting the data:

+---------------------------------------------+-----------+
| Table                                       | Size (MB) |
+---------------------------------------------+-----------+
| Table1                                      |     96000 |

Any idea why deleting the data from this table didn't decrease the database size?

Server version: 5.7.19-17-log Percona Server (GPL)

2
  • BTW, What version of MySQL is this ??? Feb 2, 2021 at 19:15
  • Server version: 5.7.19-17-log Percona Server (GPL), Release 17
    – Eng7
    Feb 2, 2021 at 19:39

2 Answers 2

6

There is no auto cleanup of fragmentation on deleting table rows from an InnoDB table..

You have to defragment the table manually.

METHOD #1

You can run the following

OPTIMIZE TABLE table1;

which will execute the following fotr you

ALTER TABLE table1 ENGINE=InnoDB;
ANALYZE TABLE table1;

This might lock the table if you still have a lot of rows (this requires downtime). Once done, the table will be smaller. Make sure you have enough diskspace for the table rebuild.

METHOD #2

If you cannot afford the downtime, you will have to use pt-online-schema-change to defrag the table. I just mentioned in a recent post : Can I disable binlogs to save space temporarily

2
  • For method1, this won't cause any loss in the remaining data, isn't it?
    – Eng7
    Feb 3, 2021 at 7:02
  • @Eng7 As I mentioned in my answer, ‘ This might lock the table if you still have a lot of rows (this requires downtime).’ Any data loss would be due to nor being able to read from or write to the table. Method 1 requires downtime after business hours and all nightly logging:reporting I’d done. Feb 3, 2021 at 11:13
7

There are several ways to delete 'most' of a table. Perhaps the best is

SET @@innodb_file_per_table = ON; -- if this is not already on  (optional)

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real
        WHERE ... -- the rows you want to _keep_;
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;

More techiques: http://mysql.rjweb.org/doc.php/deletebig

If this will be a recurring task, see the partitioning suggestion in that link.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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