MySQL Bugs: #45173: ibdata1 keeps grownig even if innodb_file_per

Bug #45173	ibdata1 keeps grownig even if innodb_file_per_table is set
Submitted:	28 May 2009 22:50	Modified:	29 Jun 2009 6:30
Reporter:	Tech Magos	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.30 - all	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	growing, IBData1

Description:
For a innodb database of about 60GB of size and the innodb_file_per_table=1, ibdata1 file keeps growing. It quickly reaches 18GB but it is concerning it could grow even more. 

How to repeat:
For a innodb database of about 60GB of size and the innodb_file_per_table=1, ibdata1 file keeps growing. It quickly reaches 18GB but it is concerning it could grow more. 

This seems to always happen with e.g. a large DELETE on 20% of a database table followed by an OPTIMIZE operation after that to compact data.

We know the file stores NO real data and there is no way to shrink, apart from rebuilding the whole DB from backup; in which case, removing  the ibdata1 and the log files manually and restoring actually does reclaim the space that DELETE+OPTIMIZE were planning to in the first place. I.e. starting the whole db from scratch, which is a VERY lengthy operation for the size of DB we deal with.

The problem is serious because a delete+compaction of a table actually reclaims nearly NO space, which can be onerous for a production setup that is tight on space and needs deletions to get data back.

Would be interested to know if there is a way to work-around this problem and restrict ibdata1 growth.

As a design goal, it would be preferrable when one selects innodb_file_per_table  ibdata1 never grows; could be used as a "sratch" area but all space it took could then be reclaimed (at least on a restart of the db).

The config file:
[mysqld]
slow_query_log
slow_query_log_file=/path/slow.log
skip-safemalloc
skip-name-resolve
skip-locking
default-character-set=latin1
default-storage-engine=INNODB
max_allowed_packet=250M
query_cache_type=1
query_cache_size=800M
query_cache_limit=100M
max_connections=150
max_connect_errors=100
wait_timeout=3600
connect_timeout=16
net_read_timeout=50
net_write_timeout=100
interactive_timeout=300

innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
innodb_buffer_pool_size = 16000MB
innodb_log_files_in_group = 2
innodb_log_file_size = 320M
innodb_log_buffer_size=16M
innodb_data_file_path = ibdata1:100M:autoextend
innodb_additional_mem_pool_size=25M
innodb_file_per_table=1
innodb_open_files = 2048
innodb_max_dirty_pages_pct=95
innodb_checksums=0
innodb_support_xa=0
innodb_doublewrite=0
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout=50

key_buffer_size=256M
sort_buffer_size=8M
myisam_sort_buffer_size=8M
read_rnd_buffer_size=32M
read_buffer_size=3M
join_buffer_size=16M
tmp_table_size=256M
max_heap_table_size=256M
max_tmp_tables=100

thread_cache_size=128
thread_concurrency=16

"We know the file stores NO real data"

That is untrue.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces.html:

"With multiple tablespaces enabled, InnoDB  stores each *newly* created table into its own tbl_name.ibd  file"

"The --innodb_file_per_table  option affects only table creation, not access to existing tables."

"InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate. "

So this is not a bug.

True, the docs do mention what you refer to and yes we had read it.
Yes you are right, this is not bug, it is a feature, albeit it is a *problematic* feature. 

"We know the file stores NO real data" is actually true, depending how you look at it. Imagine you run a prod db which grows on data continuously and as you do not have infinite disk space you need to clear old data every so often. What happens here is you DELETE a large set of records, then run OPTIMIZE and then find that NO space is claimed back. So you just run out of space and you have to clear ibdata1 often by rebuilding the db from scratch, which takes you 20 hours. You know for sure that no real data is stored in in that extra disk (that is data of any entropy). Guessing that it is the undo log that grows when the DELETE is done but then when the deleted data is zapped (OPTIMIZE), disk is not claimed back. To us, this is a real problem and the fact that the docs talk about does not make it any better.

There could be workarounds (e.g. configuration settings) for a scenario like the above that make this problem less exaggerated; pls do let us know if you know of any.

I reopen this oen, as i spotted that this "feature" is actually a lot worse than i thought. For a db of around 60GB of data (which constantly gets extended at one end and deleted on the "front"  every week), ibdata1 file has grown to 29GB. So nearly half the disk data is actually wasted. And i am not sure how much more this file can keep growing.

I request for a clear update of your docs, stating in the appropriate places that for innodb disk space planning one should take into account that ibdata* file can keep growing and will roughly require half the space of one's database's actual data - though no real data is kept in it. In case this file grows more than I have spotted (e.g. it can continue to grow to take all disk space), please state this clearly in the docs too.

Many thanks

Thank you for the feedback.

There is already verified feature request - bug #1341 about this problem.

If the undo log space use is a problem for you here are some possible steps that can be taken to reduce the amount of space involved:

1. Do the work in smaller chunks and commit regularly. Wait for some time between pieces. InnoDB's purge thread can't free space from the undo log until the transaction that created the entries finishes and until the last transaction started before it committed has finished.

2. If using MySQL 5.5 be sure to set innodb_purge_threads=1 so that the purge job runs in its own thread and can't be delayed by flushing activity.

3. Set innodb_io_capacity high enough for the purge thread to do sufficient work to catch up quickly.

4. If necessary, set innodb_max_purge_lag to a suitable value to delay the foreground work so the purge thread can keep up. This is not desirable but it can be better than continuous growth if the server never has a chance to catch up with purge activity.

The undo log is required for making previous versions of pages available to other transactions to handle the various transaction isolation modes.

James Day, MySQL Principal Support Engineer, Oracle UK