2

I ran the following query to get the database sizes:

SELECT table_schema                                        "DB Name", 
   Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema;

And I noticed one database taking up 8GB. So I went into that database and started truncating some of the tables. When I re-ran the query above, it says the one database that I was cleaning up went down to 200MB.

I then went into bash and did a df -h, but I noticed that my disk usage went down from only 95GB to 93GB. I was expecting it to go down to 87G since the MySQL command seemed to indicate I got rid of 8GB worth of data. Why the discrepancy?

John
  • 32,403
  • 80
  • 251
  • 422
  • 1
    I don't know for sure, but I think your answer lies in the fact that there is more information in a table than just the data. Indexes, for instance. So you could free up what seems like a LOT of data, and still have much filespace taken up. – durbnpoisn Jan 14 '15 at 14:15
  • 1
    possible duplicate of [How to shrink/purge ibdata1 file in MySQL](http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql) (underlying reason: InnoDB never shrink physical files, you need to recreate the tables to force recreating the files to the right size. This applies to the common table space as well as the table-specific tablespaces). – RandomSeed Jan 14 '15 at 14:31

1 Answers1

1

When a table is created it lives in a tablespace - a preallocated (basically a fixed starting size) file or set of files. Deleting the data in a table does very little to the tablespace, it stays the same size. If the table is going to be used in the future, getting rid of the existing "empty" tablespace files may not be a great idea. Truncating a table deletes all rows in the table.

I have not tried to shrink a tablespace, but have a look at this alter tablespace help: http://dev.mysql.com/doc/refman/5.1/en/alter-tablespace.html

Syntax is:

ALTER TABLESPACE tablespace_name
    {ADD|DROP} DATAFILE 'file_name'
    [INITIAL_SIZE [=] size]
    [WAIT]
    ENGINE [=] engine_name

I'm guessing you want to DROP a datafile.

jim mcnamara
  • 16,005
  • 2
  • 34
  • 51