MySQL Load - Ready to Give Up on Zabbix

[dlindquist]

Well, I'm about ready to give up on zabbix...

So far, it's an absolutely great product, except that the data storage for the values seems to be horribly inefficient.

When I started out, my box was being kept at about 0.40 load average all the time by zabbix_server and mysqld (mostly mysqld). While that seemed a little excessive, I could live with that.

However, it's now been running for nearly a week, and the load average just keeps going up.

As I write this, I've had to shut down zabbix_server because my box now has a 24-hour average load average of greater than 1.00. At this rate, I'll need a Cray by next month!

To give a comparison, I'd previously been using cacti, which does basically the same thing (except the interface is crappy), but uses rrdtool databases. It used zero resources on the box.

(My server is an AMD 1.2GHz machine -- nothing fancy, but far more than adequate for monitoring all of 2 servers).

Can nothing be done about this? Perhaps mysql is really not the appropriate place to store the values?

I'd love to use this product, but unfortunately this type of performance is a deal-breaker (and from other posts, I don't think I'm alone in seeing problems).

[StanZoid]

I think you need to tune your MySQL database. I am currently monitoring 65 hosts with over 2800 items and 1600 triggers, both agent and SNMP items, and my server runs at an average 20% utilization and 0.7 CPU load. This server runs both zabbix and the zabbix database.

For reference, my server is an Intel dual core 2.8GHz machine with 1GB RAM and dual drive RAID.

MySQL out of the box is tuned for a small low-traffic instance. You should make sure that the configuration is adequate for the traffic you are generating.

StanZoid

[dlindquist]

Hmm, well that is good to know ;-)

Are there any good guides on "tuning" mysql for this type of thing?

[StanZoid]

Yes, there are a few, including some suggestions in the forum I have seen that work well. Here are three I know of:

http://www.day32.com/MySQL/ has a script that analyzes your database and makes suggestions

http://www.profitpapers.com/papers/p...l-for-load.php has some tips for load tuning

http://hackmysql.com/mysqlreport has another script that analyzes server performance

StanZoid

[dlindquist]

Great, thanks!

You've obviously been through this -- was there anything you did specifically for zabbix? Or just these types of things, in general?

Thanks in advance!

[dlindquist]

Just a quick followup...

I used the "tuning-primer" posted above, and following some of the suggestions there did bring down the load significantly. I'm continuing to tweak things to try to get better performance.

However, I still think that the *requirement* of performance-tweaking mysql to monitor all of 2 hosts is a little extreme.

[StanZoid]

I 100% agree about out-of-the-box performance. The issue is not really Zabbix, but the decision by MySQL to configure the *default* shipping database to support small usage patterns. I find most database vendors do that, so I don't even consider using a default configuration any more, regardless of the vendor.

You asked about Zabbix-specific tuning. I can't honestly say that I have made changes specific to Zabbix, although I did concentrate on changes that would allow many small transactions to be made quickly, which is how monitoring system operate. DB tuning is still a black art in some ways, and knowing the characteristics of the system you are implementing can steer you in making these changes.

In general, configure your database with as much memory as possible, make one change at a time, and check common performance measures like cache utilization after every change.

I did move to InnoDB with the implementation of 1.4, but I was trying to remedy long waits for table locks in the old 1.1.7 MyISAM database. It seems to have worked, but I now have a much larger database to deal with, so I'm still working on the overall configuration with backups and disk space. Guess I needed something to do after all...


Success!
StanZoid