Yet another SQL Growth Thread

[jrandolph]

Details:
Zabbix version 1.62
58 hosts
3280 Items

It is a VMWare server (I have no choice about this...) with 2 CPUs, and 4 gig of ram. Running Centos 5.2 x64

The entire install is maybe 4 months old, including fresh DB

The mysql db is at 17G and growing... HALP!

8.0K ./test
17G ./zabbix
976K ./mysql
17G .

I have already exported and re-imported the DB to turn on file_per_table after trimming history (Config > General > Housekeeper) down to 7 days for actions, and 14 days for events.

Housekeeper IS running. I had it set to hourly, but changed this to daily (today).

From a background perspective I'm a network guy, so I'm really not the greatest at setting up mysql at times, but this service is in my lap, and I need to get the DB size under control and shrinking.

What can I do and/or try to contain this? Mostly I'm using modified versions of the default Zabbix Linux template based on server needs.

At this point graphing is slow, datapoints are being missed during housekeeping. It's kind of really not good.

Please let me know what additional steps/commands/whatever you need me to provide in order to give you a better idea of what is causing this conundrum. I've tried searching, but either I'm missing something or I'm just being dense.

Thanks

[globifrosch]

Details:
Zabbix version 1.62
58 hosts
3280 Items

It is a VMWare server (I have no choice about this...) with 2 CPUs, and 4 gig of ram. Running Centos 5.2 x64

The entire install is maybe 4 months old, including fresh DB

The mysql db is at 17G and growing... HALP!

8.0K ./test
17G ./zabbix
976K ./mysql
17G .

I have already exported and re-imported the DB to turn on file_per_table after trimming history (Config > General > Housekeeper) down to 7 days for actions, and 14 days for events.

Thanks

actions and events won't take a lot of space (normally).

what consumes space is normally items.

* increase items Update interval (in sec)"
-> if you have lots of "30 second" checks I would think about it

* reduce items "Keep history (in days)" in favour of trends
-> if you got lots of "90" - do you really need 90 days? most of the time trend data (max/min/avg of 1h) is sufficient

[MrKen]

Wow, 58 hosts, 4 months old, and db is 17G.

Well, you've done the right thing by adding file_per_table to your my.cnf. And so after dumping the db and re-importing it, you should have noticed some reduction in the size of the db.

4 months old, and 17G! Sounds like you need to review the Update Interval for some/many of your items. Items updated every 5 or 10 seconds make really nice graphs, but will cause the db to grow quickly. Flapping triggers, excessive alerts, and many more things need to be tweaked.

The best advice regarding data management I found was in this thread (post #2). Surely, you checked this thread already anyway, it will probably have the 'tips' that you are looking for. http://www.zabbix.com/forum/showthread.php?t=9925 [Mysql Database Grow | How Optimize parameters]

Quote you: "At this point graphing is slow, datapoints are being missed" This can sometimes be due to insufficient trappers/pollers in your zabbix_server.conf. Check in Administration --> Queue to see which type of data is being queued.

Back to the database. You probably need to Optimize your tables. This will also reduce the size of the tables.

mysqlcheck -aor zabbix (Will Analyze, Optimize, Repair the zabbix database)

The History and Trends tables may take hours to optimize, but you should see a significant reduction in size. You could also optimize individual tables every day or so until you have it under control again:

mysqlcheck -aor zabbix history

Reference: http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html

And finally, try using my.cnf tuning scripts like
http://blog.mysqltuner.com/ to tune your conf.

Hope this is some help

MrKen

[jrandolph]

Wow, 58 hosts, 4 months old, and db is 17G.

Back to the database. You probably need to Optimize your tables. This will also reduce the size of the tables.

mysqlcheck -aor zabbix (Will Analyze, Optimize, Repair the zabbix database)

MYSQL comes back w/ the database isn't set up to be repaired?

Running just the ao flags I received the following on the History table.

zabbix.history
error : The table '#sql-2566_24d7' is full
status : Operation failed

[MrKen]

Time to learn mysql. Unfortunately, I'm probably not sql-savvy enough to help!

Sql errors usually have an error code which you can google. Also, it's often useful to cut and paste errors to the forum so that someone might say 'hey I had that problem before'.

I think you will find that '#sql-2566_24d7' is a temporary table in /tmp, so you might need to check on disk space.

Here are some leads that might be useful:

http://scrutator.lo2k.net/uri40376-table-tables-mysql-size-using.html

http://dev.mysql.com/doc/refman/5.0/...ize-table.html

HTH

[jrandolph]

Time to learn mysql. Unfortunately, I'm probably not sql-savvy enough to help!

Sql errors usually have an error code which you can google. Also, it's often useful to cut and paste errors to the forum so that someone might say 'hey I had that problem before'.

I think you will find that '#sql-2566_24d7' is a temporary table in /tmp, so you might need to check on disk space.

Here are some leads that might be useful:

http://scrutator.lo2k.net/uri40376-table-tables-mysql-size-using.html

http://dev.mysql.com/doc/refman/5.0/...ize-table.html

HTH

Well I changed the monitoring intervals on quite a few items, so that should help w/ the growth issue...

However I can't find reference of that error while using file_per_table... =|

But, it appears that I have bigger issues as the DB's authentication tables appear to be hosed, and as such I cannot do any sort of remote mysqldump...

Now I'm just wondering if there's a way I can manually prune the history DB so that I don't have to completely rebuild the zabbix monitoring from scratch... =|

[tchjts1]

Curious - what version of MySql are you using?

[richlv]

you can force mysql to skip auth information. that should at least allow to dump the zabbix database and redo the db setup

[jrandolph]

you can force mysql to skip auth information. that should at least allow to dump the zabbix database and redo the db setup

Except that it won't permit external connections, and I don't have the disk space on that VM to dump to that host anymore now that it is at 18 gig...

And to answer the other gentleman's question: 5.0.45

[jrandolph]

Oh boy, it's at 19 gig today...

Er... Nervermind. Must not check disk space and panic when running mysqlcheck against a database... (repeat)

It is at 17G now, and housekeeper cleaned some 22.6 million records in the past 18 hours...

However since the history table seems hosed I'm wondering if the history table will refuse to shrink/optimize...

[jrandolph]

New line of thinking...

Could it be that I cannot optimize the table because there isn't enough free drive space to do create a large (15 gig?) undo table copy of the zabbix.history table?

[jrandolph]

Yep, that was the problem. When running the optimize command it tries to replicate the changes in case in needs to back-out and the drive was filling to 100% on the history table.

I mounted a temp share, moved everything over, ran mysqlcheck and we're down to 14 gig. I'll give it a few more days to let housekeeping prune more records over the weekend and re-run mysqlcheck -ao on Monday.

After that I can start looking to tackle the db issues.

[simonc]

Back to the database. You probably need to Optimize your tables. This will also reduce the size of the tables.

mysqlcheck -aor zabbix (Will Analyze, Optimize, Repair the zabbix database)

Should we close the zabbix server to do that ?

Thanks
Simon

[MrKen]

Should we close the zabbix server to do that ?

Thanks
Simon

No, you can leave zabbix server running, and leave mysql running. However, when you are optimizing large tables like 'history' or 'trends' you will have gaps in the graphs due to table locking.

MrKen