I have a 700GB InnoDB table which I'm not writing any more data to (only reading). I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete part is pretty easy, because I have an auto-inc primary index so I can just iterate in chunks using it, and delete the rows, but that won't bring me back the space. I assume OPTIMIZE TABLE will but that might take forever on a 700GB table, so is there another option I'm overlooking?

Edit by RolandoMySQLDBA

Assuming your table is mydb.mytable, please run the following query and post it here so you can determine diskspace needed for the table's shrinkage:

SELECT
    FORMAT(dat/POWER(1024,3),2) datsize,
    FORMAT(ndx/POWER(1024,3),2) ndxsize,
    FORMAT((dat+ndx)/POWER(1024,3),2) tblsize
FROM (SELECT data_length dat,index_length ndx
FROM information_schema.tables WHERE
table_schema='mydb' AND table_name='mytable') A;

We also need to see the table structure, if allowed.

Edit by Noam

This is the output of the query:

datsize ndxsize tblsize
682.51 47.57 730.08

This is the table structure (SHOW CREATE TABLE)

`CREATE TABLE `mybigtable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,  
  `created_at` datetime NOT NULL,  
  `tid` bigint(20) NOT NULL,  
  `text` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
  `ft` tinyint(1) NOT NULL,  
  `irtsd` bigint(20) NOT NULL,  
  `irtuid` int(11) NOT NULL,  
  `rc` int(11) NOT NULL,  
  `r` tinyint(1) NOT NULL,  
  `e` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,  `timezone` varchar(5) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `uid_tid` (`uid`,`tid`)) ENGINE=InnoDB AUTO_INCREMENT=2006963844 DEFAULT CHARSET=utf8`
  • Do you have another disk volume to catch the data only ??? – RolandoMySQLDBA Apr 29 '14 at 21:13
  • @RolandoMySQLDBA I have an external hard-drive I can mount. Does that count? – Noam Apr 30 '14 at 6:47
  • @RolandoMySQLDBA but would of-course like an option to delete some space without needing to have another 700GB – Noam Apr 30 '14 at 7:35
  • @RolandoMySQLDBA does the extra disk size cause any performance issues? – Aris Aug 13 at 15:01
  • @Aris it might depending on the disk and its seek time. These days, most disks perform better now, but what good is wasting cycles (even going really fast) if you have large sparse pockets of diskspace in your table ???. This is especially true for InnoDB which is normally fixed at 16K blocks. With internal fragmentation of 16K blocks, you may want to defragment the table using ALTER TABLE ... ENGINE=InnoDB; (if you got the room to do it). Most are just satisfied with their very fast SSDs and would not longer worry. – RolandoMySQLDBA Aug 13 at 22:04

This is a good question. You have several solutions but your table is quite big so none will be without pain :)

You have three solutions to "shrink" InnoDB tables:

1. OPTIMIZE TABLE

You can use OPTIMIZE TABLE as you mentionned it but you should care about the innodb_file_per_table variable :

mysql> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

Let me explain:

The OPTIMIZE TABLE whith InnoDB tables, locks the table, copy the data in a new clean table (that why the result is shrinked), drop the original table and rename the new table with the original name. That why you should care to have the double of the volumetry of your table available in your disk (During operation you'll need 2x700GB).

When you are in the innodb_file_per_table = ON. All tables has it proper data file. So the OPTIMIZE statement will create a new data file (~700GB) when the operation is finish, MySQL will drop the original one and rename the new one (so at the end the 700GB -- probably less because it will be shrinked -- of data generated during the operation will be released)

When you are in the innodb_file_per_table = OFF. All data goes to one data file : ibdata. This file has a sad particularity, it can't be shrinked. So during the OPTIMIZE process, your new table will be created (near 700GB), but even after the drop and renaming operation (and the end of OPTIMIZE phase) your ibdata will not released the ~700GB, so you wanted free some data but you have 700GB more, cool isn't it?

2. ALTER TABLE

You can also use an ALTER TABLE statement, the ALTER TABLE will work in the same way as OPTIMIZE TABLE. You can just use:

ALTER TABLE myTable EGINE=InnoDB;

3. ALTER TABLE (ONLINE)

The problem of OPTIMIZE and ALTER TABLE that it locks table during operation. You can use the Percona tool : pt-online-schema-change (from Percona Toolkit : link ). pt-online-schema... will construct a mecanism with triggers and temp table that you permit original table to be available for read and writes during the operation. I use this tool in production for the big ALTER it's pretty cool.

Note that you should have FOREIGN KEY referenced to your table, FK and triggers risk to produce a mess. To check this prereqs, query:

mysql> SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE REFERENCED_TABLE_NAME = "myTable";
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.04 sec)

Here is how I use pt-online-schema-change:

pt-online-schema-change --alter "ENGINE=InnoDB" D=myBase,t=myTable --user --ask-pass

Note that my note on innodb_file_per_table is true also for this solution.

4. mysqldump

The last solution is to recreate all databases from a dump. Terribly long, but terribly efficient. Note that it's the only solution to "shrink" the ibdata file.

Max.

  • Also in the percona tool online alter table option I will need 700GB of free disk space? – Noam Apr 29 '14 at 12:54
  • Yes, pt-online just use some mecanism to do the ALTER online but it makes an ALTER anyway. – Maxime Fouilleul Apr 29 '14 at 13:05
  • @MaximeFouilleul does the extra disk size cause any performance issues? – Aris Aug 13 at 13:50

If you are short on disk size i would suggest you do just like Max suggested with pt-online-schema-change (ONLINE). I have been in the same situation with a much smaller table (200GB) and chose do do some compression at the same time. Something along the lines of this should work :

pt-online-schema-change --alter="ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4" D=myBase,t=myTable --user --ask-pass

This will only work if you are in barracuda file format and in COMPACT format of the table. Also you must have innodb_file_per_table enabled. This can do marvels on the size of your table especially if there is a lot of text and if you use smaller KEY_BLOCK_SIZE such as 8K or even 4K (the default is 16K). You can also check out how much space you can gain from multiple benchmarks regarding this issue on other blogs but MySQL documentation advertises 25% to 50% (it was almost 90% for me).

Note that this can also affect performance when doing SELECTs (from MySQL documentation) :

Thus, at any given time, the buffer pool might contain both the compressed and uncompressed forms of the page, or only the compressed form of the page, or neither.

MySQL also has to uncompress the data when not in the buffer pool. So be warned.

This has really worked fine in my case. I had a long text. 200GB became 26GB. Performances were not altered.

For more in depth info check these links :

https://dev.mysql.com/doc/refman/5.5/en/innodb-compression-usage.html

https://dev.mysql.com/doc/refman/5.5/en/innodb-compression-internals.html

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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