For 10 YEARS "we" have been asking for a way to reclaim the ibdata files (or even .MYD files) and finally someone from mysql/oracle replied. It's not great news, but at least they acknowledge and give some explanations.
-----Original Message----- From: Bug Database [mailto:do-not-re...@mysql.com] Sent: Tuesday, July 23, 2013 2:30 PM Subject: #1341 [Com]: InnoDB ibdata1 never shrinks after data is removed View this bug at: http://bugs.mysql.com/1341 Updated by: James Day Reported by: Scott Ellsworth Category: Server: InnoDB Severity: S4 (Feature request) Status: Verified Version: All versions OS: Any Defect Class: D5 (Feature request) [23 Jul 21:29] James Day Ten years is because we know it is desired but it is a hard problem. To free the space takes moving lots of internal pointers and data. That is almost as much work as alter table, but in theory it could be done gradually in the background. But would be very complicated with high chance for bugs and performance problems due to the locking needed. With 5.6 the best combination that is implemented so far is: 1. Innodb_file_per_table, on by default in 5.6. Prevents data from going to shared tablespace. 2. Undo logs in their own tablespace, see http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html Separate Tablespaces for InnoDB Undo Logs . This will prevent undo logs from causing the shared tablespace to grow, but there is still no way to shrink the undo logs. 3. Shared tablespace then contains data dictionary, change buffer and statistics, so is much less likely to grow to big sizes. 4. For some workloads the compressed tables feature will help to reduce data sizes and hence the times. Still some way to go on performance of this for OLTP but it's way better in 5.6 than 5.5, in part due to many improvements suggested by Facebook. Best used in general for tables that don't have the highest change rates but do have big column sizes that compress well. This definitely does not solve all problems: 5. The ibd files can have free space and the only way to release that is slow alter table or truncate. Slow for big tables, not practical on a live server. But is practical on a system that has failover capability. Can take a window of slow failover time and do it during this window with a known risk that there might be slower failover than usual. If you do this, drop all the non-unique secondary indexes then add them back later, the fast index recreate will save you time and rebuild the indexes with less free space inside the pages. 6. The portable tablespaces feature in 5.6 could then be used to load the tablespaces into a new copy of Mysql with small shared tablespace and redo log. This still requires downtime, so still requires a failover solution, but it's far faster for big tables than mysqldump and reload. 7. Dropping tables should be faster in 5.6 and 5.5.20 or later but it's still going to be slower using innodb_file_per_table than having the tables in the shared tablespace. More practical for big tables that aren't dropped often than for small tables that are very regularly dropped. For the big/infrequent combination, most people already use innodb_file_per_table. The work on this in bug http://bugs.mysql.com/bug.php?id=51325 helps a lot but there's still scope for more. So we know it's desirable, some major improvements that help the workarounds, but still no way to make it practical online. For now, failover based workarounds are still the way to go. Not ideal, but at least doable. James Day, MySQL Senior Principal Support Engineer, Oracle ------------------------------------------------------------------------ Earlier comments can be viewed at http://bugs.mysql.com/1341 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
