Greetings,

First: thanks for this great monitoring system. We use it with PostgreSQL on several production servers, and will be able to test and give feedback / patches for PostgreSQL issues / improvements we may find for Zabbix.

I got that query an slow one (with more than 80 seconds query duration):

delete from history where itemid=22330 and oid in (select oid from history where itemid=22330 limit 500;

After taking a look at the code, it is from housekeeper.c , around line 93 (on 1.8.5 source).

Anyway, I just added that index:

create index idx_oid on history (oid);

Please note the index is not unique. And now the query takes only 1.5 seconds.

This is a Major issue because it was actually slowing-down the whole server (there are other apps using this DB server).

I think you should include that index into the DB schema.

On other issue (related): that delete query looks a little scary to me, because it doesn't precisely identify which rows it have to delete.... I'd feel more safe if you were to use a more specific query, something like:

delete from history where itemid=22330 and oid in (select oid from history where itemid=22330 order by clock asc limit 500);

That will sort using clock, and delete 500 oldest items, this query currently takes around 2 seconds to run.

I hope this helps to improve this already good monitoring system. I'll take a closer look at the DB usage, and report other findings I may come up.

Sincerely,

Ildefonso Camargo