2

I deleted thousands of thousands of rows in a table, but the free space in (C:) decreased from 40 Gb to 15 Gb. What happened? Wasn't it supposed to go up?

I've already restarted the MySQL Server and even my computer, but the problem remains. I'm using Windows 10 and MySQL 8.0

Evert
  • 93,428
  • 18
  • 118
  • 189
Matheus Douglas
  • 418
  • 4
  • 10
  • Are you sure deleting the table rows caused the issue? Maybe use a tool like treesize to check what exactly uses up your storage – NoConnection Jul 18 '19 at 12:08
  • 1
    Did you OPTIMIZE the table? – HereGoes Jul 18 '19 at 12:08
  • *"but the free space in (C:) decreased from 40 Gb to 15 Gb. What happened? Wasn't it supposed to go up?"* Sounds like you are using InnoDB table engine, InnoDB engine does not give back diskspace after delets and it can be trouble some to claim it back when MySQl isn't configured to run with `innodb_file_per_table` setting.. When you delete in InnoDB those records on disk will be marked as "deleted" (soft deleted) and will be overridden with other data when inserting and marked as "active" – Raymond Nijland Jul 18 '19 at 12:27
  • @HereGoes I optimized the table and it works! Thanks very much! – Matheus Douglas Jul 18 '19 at 12:31
  • @RaymondNijland Thanks for the explanation and correction. It really helped me to understand it. Also, I found this [link](https://stackoverflow.com/questions/30635603/what-does-table-does-not-support-optimize-doing-recreate-analyze-instead-me/30635926) which links what you said (That was another problem I found). – Matheus Douglas Jul 18 '19 at 12:37

1 Answers1

1

Removing rows is not a guaranteed way to reclaim free disk space. MySQL tends to store data for tables in a single file, or one file per disk. Generally it just marks rows in this file as deleted, so if you insert new rows after, they can take up this space.

To reclaim disk space, MySQL would have to completely rewrite these data files as it's not really possible to (cheaply) delete some data from the middle of a large file.

One reason I can think of for actually having less space, is that you might have the binlog enabled, but it's hard to know for sure what took this extra space without knowing which file sizes increased.

One way to really reclaim this disk space is to rebuild the entire table from scratch.

Evert
  • 93,428
  • 18
  • 118
  • 189
  • 1
    *"One way to really reclaim this disk space is to rebuild the entire table from scratch."* Pretty sure when MySQL is configured with `Innodb_file_per_table = ON` and you use `OPTIMIZE TABLE` that you will reclaim diskspace.. – Raymond Nijland Jul 18 '19 at 12:31
  • 2
    @RaymondNijland OPTIMIZE TABLE probably rebuilds the table then ;) Good hint – Evert Jul 18 '19 at 12:32
  • *"OPTIMIZE TABLE probably rebuilds the table then"* Well it depends on the used storage engine and some datatype contitions see [manual](https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html) as InnoDB can use online ddl which can run in place.. when you read the manual you see *"InnoDB Details"* -> *"OPTIMIZE TABLE rebuilds the table using the table copy method under the following conditions: ...."* But not sure what the manual means with *"intermediate table"* and how it is implemented in the source code.. – Raymond Nijland Jul 18 '19 at 12:40
  • 1
    @RaymondNijland I'm going to sound pedantic, but for OPTIMIZE TABLE to work as a means to reclaim disk space, it would almost have to rewrite the entire table to disk, removing all the gaps that were occupied by the deleted records. – Evert Jul 18 '19 at 12:43
  • 1
    Yes that sounds logical and that is what i also was thinking until i read `OPTIMIZE TABLE` uses InnoDB's online ddl feature which "complicated" things a bit... As i didn't know for sure what *"intermediate table"* meant in that context or how it was implemented.. But now i do know as the manual defined what a *"intermediate table"* is on the [Online DDL Space Requirements](https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-space-requirements.html) manual page – Raymond Nijland Jul 18 '19 at 12:49