MySQL & InnoDB

[rvgogh]

Hi,

good to see that forums are back up... Some1 made a booboo yesterday?

Anyway, probably not the right place to ask but hopefully someone can send me in the right direction.

My innodb files are growing exceptionally with all the data that Zabbix puts into the database. And by exceptionally I mean that, my logdata files had grown until no space was left on my zabbix system which caused MySQL to crash.. So... I had to start all over again.

Is there anyway to prevent such things of happening?

Thnx.

[just2blue4u]

how are your items set up?
To lower the value of "keep history" and "keep trends" should cause zabbix to clean up the DB from time to time...

[rvgogh]

I have set both history and trends to 7 days....

I tried to re-configure mysql by specificing maximum size for innodb's logfiles, but that causes it to produce errors like invalid *.frm file...

[pdwalker]

What was taking up space?

The primary innodb table space?

The binary logs (do you have binary logs enabled?)

How big were the files?

- Paul

[bbrendon]

Sounds like you aren't rotation your logs fast enough. Yes, zabbix is a very taxing application for relative to other applications that use mysql.

make sure you're rotationg your logfiles. I rotate mine often. I have about 40 hosts and rotating logfiles frees up about 2 gb of space. my innodb file is 6 gb

my my.cnf:
expire-logs-days = 2

[rvgogh]

What was taking up space?

The primary innodb table space?

The binary logs (do you have binary logs enabled?)

How big were the files?

- Paul

The following files filled up my disk:
ib_data1 (currently 1,1 GB again!)
ib_logfile# (currently 49 files of 5mb each)

[bbrendon]

Thats only like 2 or 3 gigs of stuff and it filled your disk? Sounds like you have bigger problems...

[rvgogh]

Well, no.
This is my current situation :-)

I started over again on monday, cause the files got bigger in the weekend, filled up the disk and made mysql crash...

[pdwalker]

Dude, is that all?

My zabbix database is currently about 5GB for monitoring 15 servers with a number of different services each.

If you are concerned about the amount of data being stored, you have two ways you can reduce the amount:

1/ monitor your services less often. If you monitor something every 30 seconds, change it to 60 seconds, or 120 seconds.

2/ reduce the amount of history you keep. Is your history set to 90 days? Reduce it to 7, or 1 day.

The second thing is your bin logs. You can have the mysql server automatically delete them for you by setting this option in your my.cnf file.

expire_logs_days = 1 (or 2, or 7 or whatever you like)

The binary logs may not be terribly useful for your particular instance. On my zabbix server, I have them disabled like so:

# binary logging disabled since I am not replicating the data to a slave server
# log-bin

Some other db expert may give you a good reason why you should leave the bin logs on that I do not know. Choice is yours.

Anyway, given the small amount of data you have, you may wish to consider getting a larger hard disk. (on one of my db servers, the bin-logs from one day of changes alone is over 20GB)

- Paul

[alj]

Hi,

good to see that forums are back up... Some1 made a booboo yesterday?

Anyway, probably not the right place to ask but hopefully someone can send me in the right direction.

My innodb files are growing exceptionally with all the data that Zabbix puts into the database. And by exceptionally I mean that, my logdata files had grown until no space was left on my zabbix system which caused MySQL to crash.. So... I had to start all over again.

Is there anyway to prevent such things of happening?

Thnx.

Please read this about mysql tuning (as well as how to change size of log file and not get invalid frm errors).

http://www.zabbix.com/forum/showthread.php?t=4435