0

I imported a 800MB SQL file to Google Cloud MySQL. I have one table that has 200 fields which is amounting to 500MB. I deleted all except 240 necessary rows in that table. phpMyAdmin still shows its having 450MB when I know it should be less than an MB. How is this being calculated - is there some place where the deleted rows exists which is still being counted as 450MB ?

Erik Darling
  • 40,781
  • 14
  • 130
  • 456
anjanesh
  • 279
  • 1
  • 3
  • 10

2 Answers2

1

How is this being calculated - is there some place where the deleted rows exists which is still being counted as 450MB ?

Acquiring space from the disk is a resource intensive task. Most database systems, MySQL included, won't release previously claimed space from the disk back to the disk, even after deleting data from the database. Instead it'll stay marked as claimed but unused.

When more data is added to the database, instead of taking more space from the disk, it'll first use space from this pile of already claimed but unused space. So your database size won't actually grow further, even though more data is added to it - until it runs out of that claimed unused space. Then the database will need to reach back out to the disk to claim more disk space.

More specifically, in MySQL, if the set of unused space isn't big enough to account for a specific data change, then a database growth operation occurs to consume more free space from the disk. Over time, this can lead to multiple areas of fragmentation in the tablespace that add up, as mentioned by Bill.

For more information on how this works, how to defragment the tablespace, and ways to shrink the database, please see this similar DBA.StackExchange question.

J.D.
  • 37,483
  • 8
  • 54
  • 121
  • 2
    In practice it isn't this simple. MySQL does grow the tablespace if the "holes" left by deletions aren't large enough to reuse for subsequent new data. Over time, the tablespace may grow fragmented with lots of little gaps that aren't big enough to be reused. But it depends on the application. Some applications delete in large contiguous blocks, some applications delete in dribs and drabs. – Bill Karwin Oct 23 '23 at 16:05
  • @BillKarwin Fair point, thanks! I am not as versed with MySQL, but I've updated my answer to mention that. Feel free to make any other additions or corrections, if you think it needs anything. – J.D. Oct 23 '23 at 19:36
  • 3
    Another point that surprises many users of MySQL is that you can't query MySQL to find out how much free space there is in a tablespace. You can query data_free from a table's status, but that only counts large groups of 64 contiguous pages called extents. All the smaller bits of fragmentation are not reported by any command or status value. So you can run OPTIMIZE TABLE to rebuild the table, and you'll find out after it runs whether it saves any significant space or not. :-( – Bill Karwin Oct 23 '23 at 19:52
  • SHOW TABLE STATUS LIKE 'tablename'; is the command to get "Data_free" (and other stuff). But, as Bill points out, that metric is incomplete. – Rick James Nov 07 '23 at 22:03
1

MySQL fails to shrink disk usage when deleting rows. Instead, it leaves the free space in the file for future INSERTs.

This SQL may shrink the table for you: OPTIMIZE TABLE tablename. (I say "may" because there is at least one exception.)

A much faster way to delete most of the rows of a table is to copy the rows you need ot keep to a new table, then RENAME TABLE to swap it into place. The details of that, plus other techniques, are discussed in Big Deletes

Rick James
  • 78,038
  • 5
  • 47
  • 113