2

Have multiple tables which had more than 1 billion rows. Archived and deleted many of them, now want to recover the disk space.

Have option to either optimize the table, or dump the table and restore it.

For restoration I will be splitting the dumped sql file into multiple files and will run parallel restore upto the point the CPU is not blocking much on DiskIO, to restore at the best possible rate.

Now, from mysql's point of view alone, which would be faster, and why?

I understand that DiskIO will be an important factor, but I want the technical points from mysql's perspective.

NOTE: The disk on which mysql is, its a high performance SSD disk.

Improve this question
8
  • I don't no specifically about MySQL, but I would expect unload/reload always being slower than reorganizing.
    – dnoeth
    Commented Feb 6, 2016 at 13:20
  • @dnoeth But in optimization, MySql creates a new table with remaining records, so may be it is not exactly re-organizing. No?
    – Gautam Somani
    Commented Feb 6, 2016 at 13:21
  • @GautamSomani InnoDB table? MyISAM does optimization in-place. Why do you even want to optimize the table? It does not usually give any benefits in InnoDB. If you have to, the optimize might be faster because it can create secondary indexes after reinserting all rows whereas mysqldump usually creates them with the table (but if the optimization is used depends on the mysql version and you can emulate it by modifying the dump manually.
    – jkavalik
    Commented Feb 6, 2016 at 17:05
  • 1
    @jkavalik InnoDB Table. I want to recover disk space. Database size was hitting 960G, had only 20Gb left. Apart from secondary indexes, anything else? Also, how do I emulate the same speed factor?
    – Gautam Somani
    Commented Feb 6, 2016 at 17:20
  • @GautamSomani is there really enough empty space (previously deleted rows) in the table to be worth it? Does the table have its own *.ibd file? If no, then that would be a problem, because the shared ibdata* files cannot be shrinked.
    – jkavalik
    Commented Feb 6, 2016 at 17:33

1 Answer 1

Reset to default
3

OPTIMIZE is probably better because what it does is

  1. CREATE TABLE ...
  2. Copy all the existing rows over
  3. RENAME ...

(Some time during or after step 2, the indexes are rebuilt.)

Dump and reload:

  1. Read the entire table, write to disk
  2. DROP TABLE or TRUNCATE TABLE and CREATE TABLE ...
  3. Read the dump, inserting into the table

This is slower because of the extra I/O for the dump.

If disk space is tight, execute the dump (mysqldum/xtrabackup/etc) and restore (mysql) from a remote machine and have the dump file on that remote machine. This may be the only way to do the cleanup without running out of disk space.

Even better...

Next time you need to do a big DELETE...

  1. CREATE TABLE new LIKE real
  2. Copy the rows you do not want to delete into new
  3. RENAME TABLE real TO old, new TO real
  4. DROP TABLE old

This 'eliminates' the effort to do the DELETEs.

Deleting big chunks is a popular topic; see my blog on the topic.

Still even better...

If this is a time series, and you are dropping "old" data, PARTITION on the date. Then DROP PARTITION to jettison the old data 'instantly'. Details.

Caveat

If you have only 20GB of spare disk, and the remaining data + index in the InnoDB table is more than 20GB, OPTIMIZE will run out of disk space and fail (without doing 'anything'). So will dump+reload unless you can use a different machine for the dump. (The 'better' solutions need setup; can't do either this time.)

If the table is in ibdata1 (not innodb_file_per_table), no space will be given back to the OS. It will be available for future growth in InnoDB tables.

Addenda

Their may be a difference is size or speed between the restore and the OPTIMIZE. The rebuilding of the indexes may be different.

If you are doing any of this to recoup space on disk, start with the smallest table first and work your way up. Keep in mind that InnoDB makes some attempt at cleaning up holes in its BTrees. In theory, a well-kept BTree will average 31% empty space, even after lots of churn. So, that's the most you can expect to recoup from OPTIMIZE (or equivalent). However, a huge delete leaves free space in addition to the 31%.

Improve this answer
3
  • Dump takes time, no doubt. I don't want to know methods of doing this, there are many, I just want to know that between restoration process and optimize why is optimize better? We never take dumps to recover space, unless there are use cases. Problem with creating new table is that there should be enough space left on the disk. Partitions are automatically archived and dropped after a certain number of days. Each table has its own IBD file.
    – Gautam Somani
    Commented Feb 6, 2016 at 20:44
  • The better way which I do is that I start optimizing with the smallest table is the free disk space is 5 Gb more than the size of the smallest table, and then I go next with the next bigger table, this ensures that as space keeps recovering, So when the remaining disk space is size of smallest table + 5 to 10 Gb extra, that's my trigger point to start this activity.
    – Gautam Somani
    Commented Feb 6, 2016 at 20:44
  • (I added some more.)
    – Rick James
    Commented Feb 6, 2016 at 21:44

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.