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.
From: Bug Database [mailto:do-not-reply@stripped]
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)
Version: All versions
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
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
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
James Day, MySQL Senior Principal Support Engineer, Oracle
Earlier comments can be viewed at http://bugs.mysql.com/1341
|• #1341 [Com]: InnoDB ibdata1 never shrinks after data is removed||Daevid Vincent||24 Jul|