Zabbix web interface extremely slow :(

[Aly]

We decided to go with EXISTS statement way.

[Emir Imamagic]

The problem with different results might be in the left join, altough EXISTS should squash out all groups without hosts. Could you give me more details in which case you get different results.

I put all patches here:

http://kosjenka.srce.hr/~eimamagi/zabbix/

Most of them are corrections of two same queries, except for charts.php.patch.

[jsosic]

Anyway, with these queries Emir was able to speed up page loading from ~5.5 seconds for one host Latest data, to around ~1.5 seconds. Similar speedups were obtained for other pages too.

You should really implement these patches and look around optimizing queries even more, because this is apsurd!

And about PostgreSQL vs MySQL... PostgreSQL offers a way way better performance. I am a PostgreSQL zealot, and this is not the first time I've seen this performance margin, but I must admit I did not expect it to be so big! So, yes, PostgreSQL is a way faster, and we didn't even optimize it thoroughly!

[stephen.soltesz]

Hello,

I wanted to add my experience to this thread, because I too was experiencing extremely slow performance with zabbix & postgresql. It turned out that I was using the default postgresql.conf file, which includes some very conservative settings for memory usage.

In particular, I increased:

shared_buffers=50000
work_mem = 4096
max_fsm_pages = 40000
checkpoint_segments = 10
effective_cache_size = 4000

The result is much faster response time for almost all pages. I do not know if these settings are advisable in general, as I am not a db admin, so use them at your own discretion.

Stephen.

[stephen.soltesz]

Also regularly running 'vacuumdb' is necessary...

[exkg]

Hi Emir,

The problem with different results might be in the left join, altough EXISTS should squash out all groups without hosts. Could you give me more details in which case you get different results.

I put all patches here:

http://kosjenka.srce.hr/~eimamagi/zabbix/

Most of them are corrections of two same queries, except for charts.php.patch.

What exactly version are made this patches ?

[Emir Imamagic]

What exactly version are made this patches ?

Zabbix 1.6 from SVN (build 6204). However, I believe these changes are incorporated in Zabbix 1.6.1.

cheers,
emir

[exkg]

Hmmmm,

Apparently not.

In ZBX 1.6.1:

=====
$sql = 'SELECT DISTINCT g.groupid,g.name '.
' FROM groups g, hosts_groups hg, hosts h '.
' WHERE '.DBcondition('g.groupid',$available_groups).
' AND hg.groupid=g.groupid '.
' AND h.hostid=hg.hostid '.
' AND h.status='.HOST_STATUS_MONITORED.
' AND EXISTS(SELECT i.itemid FROM items i WHERE i.status='.ITEM_STATUS_ACTIVE.' AND i.hostid=h.hostid ) '.
' ORDER BY g.name';
=====

In PATCH:

=====
$sql = 'SELECT DISTINCT g.groupid,g.name '.
' FROM groups g INNER JOIN hosts_groups hg ON (hg.groupid=g.groupid)'.
' LEFT JOIN hosts h ON (h.hostid = hg.hostid)'.
' WHERE EXISTS (SELECT i.hostid FROM items i WHERE i.status='.ITEM_STATUS_ACTIVE.' AND i.hostid=h.hostid)'.
' AND '.DBcondition('g.groupid',$available_groups).
' AND h.status='.HOST_STATUS_MONITORED.
' ORDER BY g.name';
=====

When we have a big quantity items managed by ZBX interface (screens, graphs, maps, slides, user groups, users perms, hosts groups, etc, etc) things start to get slow .... I'm trying to find a solution or workaround because a slow interface is bad. Users didnt feel if a poller gets more time to colect data ... but they feel (very fast) if its in a screen.


[]s,
Luciano


[]s,
Luciano

[Aly]

In addition I would recommend to create an index of "itemid" column in "graphs_items" table.

[exkg]

Aly,

I think we need pay atention to Emir patches, we migrate to 1.6.1 and performance in interface is poor yet

What you think ?

[]s,
Luciano

[Aly]

Sql's in 1.6.1 and in the patch are slightly different, but processing logic is the same.