How to keep down the ASM database growth

KB50591

Ultimo aggiornamento: 02 April 2013

ASM database growth

ASM database size strongly depends on the number of archived messages and indexing.

 

To avoid the ASM database size growths too much it’s possible to:

·                     avoid to archive unnecessary messages configuring some not-archiving rules from the ASM administration

·                     decide to keep not indexed old messages

·                     disable messages indexing at all. Indexing is a feature affecting heavily the database size. Please note that, if indexing is disabled, it won't be possible to search messages by words contained into the body

·                     choose to archive messages only for a limited period of time for example exporting on CD/DVD and deleting old messages

·                     shrink periodically the database (view next paragraph)

 

Shrinking the ASM database

 

Database Shrinking is a management job and it may be quite heavy for the machine resources, so ASM doesn’t perform any shrinking operation on its database leaving this activity to the database server manager and the appropriate management tools.

 

SQL Server Databases

 

Following steps are referred to SQL Server 2000.

 

One SQL Server consist of one data file (.mdf file) and one transaction log file (.ldf file).

Database shrinking may be scheduled manually or scheduled for an automatic execution by the SQL Server Enterprise Manager:

·                     Select the ASM database from the SQL Server Enterprise Manager

·                     Click the right button and choose All Tasks –> Shrink…

To avoid that the transaction log rise too much you can follow Microsoft suggestions:

A database backup compacts database an truncates the transaction log.

 

MySQL databases

 

A MySQL table can be optimized by the OPTIMIZE query:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.

You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

This statement requires SELECT and INSERT privileges for the table.

In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it is not likely that you need to do this more than once a week or month and only on certain tables.

OPTIMIZE TABLE works only for MyISAM, InnoDB, and (as of MySQL 5.0.16) ARCHIVE tables. It does not work for tables created using any other storage engine.

For MyISAM tables, OPTIMIZE TABLE works as follows:

    1. If the table has deleted or split rows, repair the table.
    2. If the index pages are not sorted, sort them.
    3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

 

MySQL with InnoDB  engine

 

InnoDB may use a shared tablespace file (lbdata1). The Ibdata1 file remains the same size even when a large table is deleted or truncated. Even if the database is deleted the size of that file is no smaller. This is a limitation of InnoDB's shared tablespace file. It would be a  very expensive operation for the MySQL Server to compact the used space in the  tablespace file and shrink the file. Imagine if you have a 100GB tablespace file,  and you want to perform a rebuild to compact it. Typically, MySQL table rebuilds  require both the old and the new storage to exist simultaneously on disk during  the operation.

 

So InnoDB's behavior is to leave the empty space, and use it for subsequent  insertion of new row versions.

 

See:

Bug #1287 

Bug #1341 

 

One solution to shrinking a shared InnoDB tablespace is:

1. Backup *all* InnoDB tables with mysqldump.

2. Drop all of the InnoDB tables.

3. Physically delete the ibdata1 file at the filesystem interface.

4. Restart MySQL Server, which recreates a new, small tablespace file.

5. Restore all your InnoDB tables, which expands the tablespace file as needed.

 

Another solution is to use the option to store InnoDB tables in a separate file  per table. See this If  you do that, you should be able to use OPTIMIZE TABLE or ALTER TABLE on each  InnoDB table that you want to shrink. This is supposed to rebuild the .ibd file  for the individual table.