How to compress or restrict MySQL ibdata1 data file

Question

Our Application is having huge data and full of inserts and deletes. So in 3 weeks, the ibdata1 file reached 30 GB. So I thought we can restrict the size of ibdata1 file and changed the variable innodb_data_file_path to "ibdata1:10M:autoextend:max:32G".

Once the file reached to 32 GB, it is continuously throwing errors like table is full for every insert statement.

Please help me out of this either to compress or limit the data file size.

As we need to run our application for years.

Answer 1

I recommend storing InnoDB tables in separate files, one file per table. It makes it easier to manage them and to see which tables take the most storage. Put the following in /etc/mysql/my.cnf (or wherever it is on your system) [mysqld] section:

innodb_file_per_table

After that it is easy to identify the tables which are using too much space. Then you need to re-create the problematic table(s) periodically (once a day, once a month, whatever) by doing the following:

ALTER TABLE tablename ENGINE=InnoDB;

That will take quite some time to run on big tables. It will recreate the table files from scratch and that will get rid of fragmentation and holes which add up to the file sizes. While ALTER TABLE is running, it uses double the needed disk capacity. I am not sure if you can do this unless you are running in "file per table" mode.

UPDATE

I wanted to add that to get rid of the inflated existing "single file for all tables" InnoDB ibdata1 file, you need to do the following:

1. export all InnoDB tables with mysqldump
2. shutdown mysqld
3. put the "innodb_file_per_table" line in your my.cnf
4. rm ibdata* ib_logfile* in your mysql data directory
5. start mysqld
6. import your data which you backed up at the step 1

Answer 2

To enable compression on specific tables you must:

• set innodb_file_per_table to ON
• set innodb_file_format to Barracuda
• restart MySQL and recreate tablespace.
• change table definition using ALTER TABLE name ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

Barracuda row format in supported by MySQL 5.5 or MySQL 5.1 with InnoDB plugin.

Answer 3

There is a specific reason why compressing ibdata1 is very important.

What goes into ibdata1 ? Four things:

• Table Data
• Table Indexes
• Table Metadata
• MVCC (Multiversioning Concurrency Control) Data

You are going to need to do two things:

• CleanUp the ibdata1 by separating data and indexes from it (I answered this same type of question back in Oct 29, 2010 and Feb 4, 2011). It is similar to @snap's answer, but since @snap mentioned this method first in this question, he should get kudos (acceptance of his answer) for it.
• Remove that 32G cap from innodb_data_file_path. It should read 'ibdata1:10M:autoextend' (which is the default value anyway)

CAVEAT

The reason why innodb_data_file_path should be the default goes back to what gets stored in ibdata1. Even if you remove all data and indexes as specified by @snap, the table metadata and MVCC Data still gets written in ibdata1. Table metadata can still make ibdata1 grow if there is a very high volume of DDL commands (CREATE TABLEs, DROP TABLEs, ALTER TABLEs, etc.). MVCC Data can still make ibdata1 grow if there are high volume of transactions that make snapshots of large amounts of data per transactions. Since that is the case with ibdata1, it may as well be as lean and mean as possible. Therefore, let innodb_data_file_path=ibdata1:10M:autoextend in /etc/my.cnf

BTW : +1 from me for @snap !!!

Answer 4

What nobody seems to mention is the impact innodb_undo_log_truncate setting can have.

Take a look at my answer at How to shrink/purge ibdata1 file in MySQL in StackOverflow.

Answer 5

This is an excellent guide posted here for thorough explanation & resolution:

https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261