Mysql/innodb optimizations

[attilla]

Morning all,

I'm working on a pretty large, but uncommon Zabbix setup. In contrary to most here, I won't have a large number of hosts with a normal amount of items to deal with, but a small amount of hosts with an incredible amount of items (~4k - 5k items per host). I should end up with around 125k items, which should result in enough vps.

Now I'm not a db admin and for putting this in production I need some advice on how to configure the Mysql/innodb setup. I've been reading around the forum here and in general it doesn't seem to matter which db you use, the load remains the same (unless anyone can tell me that Postgres is definitely better for my setup than Mysql, since we already use that for a few systems).

I'd like to have some pointers on how to configure Mysql (or Postgres) to perform better in a large scale setup. E.g what I should change in the Zabbix config (I noticed some disabled db housekeeping), what is preferred in the Mysql config, what should I change in the linux host configuration (Debian).

Thanks!

[kewan]

Hi,
I'm not a dbadmin myself, but until others, more experienced in db optimization, will chime in, you could try to:
1) use one file per table in the innodb setup
2) disable housekeeping
3) enable table partitioning for the history* tables
4) if you have enough RAM, put at least part of your /tmp in RAM, so temporary tables are not written on disk

point 2) and 3) should really go together.

Cheers

Stefano

[Zaniwoop]

what is the log term impact of dissabling housekeeping?

[attilla]

Hi,
3) enable table partitioning for the history* tables

Can you point me in to the right direction for this, specifically with the zabbix database? As far as I read now, table partitioning isn't a trivial thing to do, without a full understanding of the db structure.

[thtux]

Hi Attilla

If you don't mind some not well tested Zabbix-patches, try these:

http://www.zabbix.com/forum/showpost.php?p=84071&postcount=3

I got the mysql cpu-usage down from about 400% to about 20% and it works flawlessly. It allows the database to make better use of the indexes, nothing more (or less).

[xsbr]

Hi,
3) enable table partitioning for the history* tables

Finally my post about partitioning is finished:

http://zabbixzone.com/zabbix/partitioning-tables/

[Jason]

What is the performance hit/gain on zabbix using the partitioning?

As mysql is handling smaller tables then does it's memory utilisation improve?

[xsbr]

What is the performance hit/gain on zabbix using the partitioning?

I could only reach this environment using partitioning.

Without partitioning on MySQL, I think that is impossible works with large environments.

[LenR]

We are several "generations" into a POC for network monitoring. The partitioning info is GREAT. One question, I didn't see a stored procedure to create/expire the monthly partitions. Did I miss it?

It's been a very long time since I did database stuff, I think I could build one using what is there for an example, but if someone has already done it, I'll use it!

We are finding monitoring switches, routers and AP's to be somewhat different from monitoring servers. We will have lots of SNMP items, but most can have long intervals. Our POC is at 2000 hosts, 350,000 items and 900 nv/sec now. Zabbix server and web are on one 2950, mysql db on a 2nd 2950, partitioned, no housekeeping. As of now, our only problems are some of the large screens failing with sql errors, but it could be from database corruption before we got things tuned.