ibdata file growing huge despite using innodb file per table

[noxis]

Hi There,

Zabbix version 1.4.6
Mysql version 5.0.51a-log

I am no noob when it comes to mysql administration (have in fact been on the mysql DBA training course) but this is stumping me. I recently moved to a new server for Zabbix as the old server ran out of space (had 50gb for mysql but we are trying to keep history data for a long time).

When I moved to the new server it started life using the innodb_file_per_table option (as the old one did as well) in fact the entire config was identical.

On the old server the ibdata1 file was 936mb in size, the other tables were growing as expected.

New server, the idb files are growing as expected using a total of around 48Gb for the Zabbix database (only database on there other than the usual mysql ones). The ibdata1 file is 105Gb in size

Anyone able to shed some light? I know you can't reduce the size of the ibdata file without redumping the entire thing, and there is no point in me doing this until I figure out what is causing it.

Thanks

[xs-]

you mean you have one_file_per_table enabled and you still have a ibdata file (in the mysql datadir root) of 105G and growing?
If thats the case then i can only asume a configuration problem. If you created all databases after you enabled the one_file_per_table option, ibdata should not grow much (a little for temp tables etc, but thats just megs, not gigs).

If you mean your zabbix database data files have a total of 105G and growing, well yes, you can shrink these because you are using one_file_per_table. mysqlcheck has an option to do this.

[noxis]

you mean you have one_file_per_table enabled and you still have a ibdata file (in the mysql datadir root) of 105G and growing?
If thats the case then i can only asume a configuration problem. If you created all databases after you enabled the one_file_per_table option, ibdata should not grow much (a little for temp tables etc, but thats just megs, not gigs).

If you mean your zabbix database data files have a total of 105G and growing, well yes, you can shrink these because you are using one_file_per_table. mysqlcheck has an option to do this.

You first paragraph is correct. I have innodb_file_per_table enabled, the Zabbix *.ibd files are growing as expected. Its almost as if the ibdata1 file is not flushing out the temporary tables.

[xs-]

Hmm, thats weird.
Well because its the global ibdata, added space used for temptables and alike doesnt 'shrink' because of the nature of the file (but this is all normal behaviour).
The weird thing is, why is it 105G. Although your zabbix database is around 48G, doesnt clarify the 105G ibdata.

Oh well, i guess the first thing you could do is dump all databases, delete the entire mysql data tree and start over with a fresh import of the dump.
I'd also check beforehand what the counters say on the temp table usage. (and run mysqltuner, http://wiki.mysqltuner.com/MySQLTuner, easier than manually searching for counters).

[rrr]

I have the same problem, I think it results in having both options (innodb_data_file_path & one_file_per_table) activated.
At this moment I dump the zabbix-database, uncomment innodb_data_file_path, and re-import the database.

We will see...

[noxis]

I have the same problem, I think it results in having both options (innodb_data_file_path & one_file_per_table) activated.
At this moment I dump the zabbix-database, uncomment innodb_data_file_path, and re-import the database.

We will see...

I'd be interested in your results. I am only going to redump when I am confident it is not going to happen again, are system is very much live and relied upon.

[rrr]

For me it helped. Now the ibdata1-File is very small. No errors occured.
I have 12 GiB of data and it took approximately 3 hours (filebackup, dumping, re-importing)