In some of MySQL DB's, we have tables that grow to 200G in size and frequently we have to prune the data to keep the query retrieval times manageable. However, when we use DELETE statements on tables, we see that table sizes doesn't shrink till we run OPTIMIZE on the tables. For e.g:-
Before:-
+--------------------------+------------+
| Tables | Size in MB |
+--------------------------+------------+
| LOG_DETAILS | 126286.48 |
| LOG_MESSAGES | 0.02 |
+--------------------------+------------+
After running OPTIMIZE:-
+--------------------------+------------+
| Tables | Size in MB |
+--------------------------+------------+
| LOG_DETAILS | 62565.48 |
| LOG_MESSAGES | 0.02 |
+--------------------------+------------+
AWS Support also confirmed our observation:-
"MySQL by default uses InnoDB as a database engine. InnoDB is a multi-version system.
This means that each time you update or delete a row, the previous version of the row gets copied to a special place called a rollback segment. The original record is rewritten with the new data (in case of an UPDATE) or marked as deleted (in case of a DELETE).
If you do a transaction rollback or execute a concurrent SELECT statement (which has to see the previous version since the new one is not yet committed), the data is retrieved from the rollback segment.
The problem with this is that InnoDB tablespace never shrinks. If the tablespace is full, new space is allocated. The space occupied by the rollback information from the committed transactions may be reused too, but if the tablespace is full it will be extended and this is permanent.
InnoDB generates undo to support features such as transaction rollback and Multi Versioning Concurrency Control (MVCC) . Since undo is stored in the system tablespace and since the system tablespace never shrinks in size, large data load transactions can cause the system tablespace to become quite large, consuming disk space that cannot be reclaimed without recreating the database from scratch.
There is no supported way to shrink the tablespace files except backing up the RDS instance and restoring from backup. This is a limitation of the MySQL engine.
Note, though, that space occupied by the tables and space occupied by the tablespace are the different things. Tables can be shrunk by running OPTIMIZE and this will make them more compact (within the tablespace), but the tablespace itself cannot be shrunk:
* http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-storage-layout.htm."
There are also other links indicating the same -
Before:-
+--------------------------+------------+
| Tables | Size in MB |
+--------------------------+------------+
| LOG_DETAILS | 126286.48 |
| LOG_MESSAGES | 0.02 |
+--------------------------+------------+
After running OPTIMIZE:-
+--------------------------+------------+
| Tables | Size in MB |
+--------------------------+------------+
| LOG_DETAILS | 62565.48 |
| LOG_MESSAGES | 0.02 |
+--------------------------+------------+
AWS Support also confirmed our observation:-
"MySQL by default uses InnoDB as a database engine. InnoDB is a multi-version system.
This means that each time you update or delete a row, the previous version of the row gets copied to a special place called a rollback segment. The original record is rewritten with the new data (in case of an UPDATE) or marked as deleted (in case of a DELETE).
If you do a transaction rollback or execute a concurrent SELECT statement (which has to see the previous version since the new one is not yet committed), the data is retrieved from the rollback segment.
The problem with this is that InnoDB tablespace never shrinks. If the tablespace is full, new space is allocated. The space occupied by the rollback information from the committed transactions may be reused too, but if the tablespace is full it will be extended and this is permanent.
InnoDB generates undo to support features such as transaction rollback and Multi Versioning Concurrency Control (MVCC) . Since undo is stored in the system tablespace and since the system tablespace never shrinks in size, large data load transactions can cause the system tablespace to become quite large, consuming disk space that cannot be reclaimed without recreating the database from scratch.
There is no supported way to shrink the tablespace files except backing up the RDS instance and restoring from backup. This is a limitation of the MySQL engine.
Note, though, that space occupied by the tables and space occupied by the tablespace are the different things. Tables can be shrunk by running OPTIMIZE and this will make them more compact (within the tablespace), but the tablespace itself cannot be shrunk:
* http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-storage-layout.htm."
There are also other links indicating the same -
- http://dba.stackexchange.com/questions/21615/deleting-entries-from-table-not-reducing-my-db-file
- http://www.percona.com/blog/2014/07/28/what-i-learned-while-migrating-a-customer-mysql-installation-to-amazon-rds
No comments:
Post a Comment