innodb_file_per_table

[EtherTrogg]

Does anyone have any experience with the MySQL option innodb_file_per_table and Zabbix? Specifically, I'm interested in the following:

• Does Zabbix perform better or worse with this option enabled?
• Does Zabbix scale better with this option enabled?
• Will this option reduce or increase the CPU load on the MySQL server?
• What risks (if any) would I face if I use this option?
• Does this have any impact on the amount of wood that a woodchuck chucks if a woodchuck would chuck wood?

Thanks!

[Alexei]

Does Zabbix perform better or worse with this option enabled?

I do not think it really matters provided all your data is kept on the same disk array. If disk IO is a bottleneck of your ZABBIX setup, then keeping of historical tables on an independent disk storage may help, theoretically.

[Tenzer]

I changed this setting a while ago for the MySQL server I am using, but I don't see any difference in performance of the server. It does however make it easier to manage the raw files, if needed

[just2blue4u]

I am using that MySQL feature for about 2 months now, and it works fine. I can't say it's better then all-in-one-file, but as said: "it makes it easier to manage the raw files!"

Maybe you are interested in this thread where this community got me to optimize my MySQL-Settings: http://www.zabbix.com/forum/showthread.php?t=8641

[EtherTrogg]

Thanks for all the replies, everyone. This community's eagerness to help is one of many reasons I've been so pleased with Zabbix.

I switched to innodb_file_per_table shortly after starting this thread, and so far, I have seen no downside to it. It had no impact on Zabbix's performance, but as Tenzer said, it does make it easier to manage the raw files.

Additionally, the amount of drive space used by the Zabbix database is slightly smaller. When I was running in "all-in-one-file" mode, my DB was around 23 GB; with innodb_file_per_table enabled, my DB dropped to 20 GB. Not a significant space savings, but it's an interesting result.

Otherwise, I've seen nothing to indicate that this option has any real plusses or minuses with regards to how Zabbix functions. It works well in either mode.

Thanks again for the advice.

-- Trogg

[Alexei]

I have similar very positive experience with the option enabled on our test systems, now the option is mandatory for all our servers.

First af all, it does save disk space. Also the option simplifies management of databases in case of "disk full" situations very much!