Trigger-Configurationscreen extremely slow

[ploeger]

Hi everybody!

We're running 1.6.2 on Debian using PostgreSQL and switching to Configuration/Triggers is extremely slow (takes about 2 minutes to go there)

My data:

Systems: 160 (47 monitored)
Elements: 7147 (3783 monitored)
Triggers: 529 (510 monitored)
New values per sec: 438

My server:

CPU: 2xDualCore Opteron 2220, 2.8 GHz
Mem: 8 GB
Mountpoint for Postgresql: LVM with 110G free space running XFS file system

I've already tuned my postgresql installation.

I suppose, that this query slows the whole thing down:

SELECT DISTINCT g.groupid,g.name FROM groups g, hosts_groups hg, hosts h WHERE (g.groupid IN (8,7,11,12,2,10,9,5,1,3,4)) AND hg.groupid=g.groupid AND h.hostid=hg.hostid AND EXISTS(SELECT DISTINCT i.itemid FROM items i WHERE i.hostid=h.hostid ) ORDER BY g.name

What can I do?

Kind regards,

Dennis

[Aly]

For now, I guess nothing.. May try the latest frontend (from zabbix pre1.6.3) there was some changes, may be it will help...

[Alexei]

I believe the problem still exists in pre-1.6.3. Actually it is due to EXTREMELY inefficient processing of certain SQL requests on PostgreSQL side. We will try to implement a workaround on Zabbix side soon.

[ploeger]

Okay. I'll wait. I guess, large environments are not suitable for PostgreSQL right now.

I hope, you fix this anytime soon, because I wasn't really happy with MySQL/InnoDB, too (stability problems, crashing of tables due to lack of free system space, etc.)

So I switched to the more mature database...

Kind regards,
Dennis

[newbish]

Missing Index on the trigger table

This is probably due to a missing index on the trigger table.

CREATE INDEX triggers_3
  ON triggers
  USING btree
  (triggerid);

In my experience PostgreSQL has never been slow if indexed properly.