MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:John B. Ivski Date:November 9 2004 7:41pm
Subject:InnoDB data files keep growing with innodb_file_per_table
View as plain text  
Hello,

I have a DB of about 100 tables, and MySQL is configured to keep them in separate files.
Total size 
of the files is ~2GB. Most of the tables are of similar format and consist of many short 
fixed-length rows (~50 bytes/row). The database size is increased for several MB a day.

Initial configuration had 200MB for InnoDB data files. Initial database size was ~1GB.
Current 
database size is, as I said, ~2GB. Current size of idbdata files is 1.8GB.

As you can see, idbdata files are growing faster than the database itself.

There are no lengthy transactions.

I tried playing with the new 'innodb_max_purge_lag' setting, but it only made things worse
(the data 
files kept growing and I started to get many slow queries).

So, why do the data files keep growing???

Using MySQL 4.1.7 on Windows XP (3.2GHz CPU, 1GB RAM). Relevant settings from my.cnf
below:

max_connections=50
query_cache_size=32M
table_cache=768
tmp_table_size=52M
log-bin=binlog
max_binlog_size=256M
max_allowed_packet=32M

innodb_data_file_path=ibdata1:100M;ibdata2:100M:autoextend
innodb_additional_mem_pool_size=8M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_log_file_size=64M
innodb_thread_concurrency=8
innodb_file_per_table
innodb_open_files=2048

Good luck,
John
Thread
InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski9 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri9 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski10 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableSasha Pachev11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri21 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski23 Nov