PostgreSQL High CPU load

[BrianC]

Hello,

I'm seeing some odd behavior on our Zabbix server. The postmaster process will consume 100% of the cpu for about 20 minutes, then will be fine for an hour before maxing the cpu again. The interval is one hour between when the process drops and when it starts again, not between starts. The only thing using the database right now is Zabbix, and we only have a couple hundred counters. Version of Zabbix is 1.1.6 and Postgres is 7.4.13-2.RHEL4.1, but this was happening in 1.1.4 as well.

Any help fixing or troubleshooting is appreciated. Thanks.

-B

[Alexei]

I believe this is due hourly (by default) execution of housekeeper. You may change the frequency or disable it. In this case do not forget to configure autovacuum on PostgreSQL side.

[BrianC]

That was it. Changing the frequency of the housekeeping changed how often the server was getting hit. I didn't realize that the housekeeping process was so resource intensive.

Thanks.

[Alexei]

This is because it does vacuuming of some tables as well, it is PostgreSQL specific stuff.

[Gilmar]

PostgreSQL High CPU load

In my case the postmaster process consume 100% of CPU constantly.
My Postgres has vacuuming enabled.
In zabbix I disabled housekeeping and set StartPollers=1.

Suggestions?

Thanks!

[newbish]

Missing Index on the trigger table

There is a missing index on the trigger table which is causing the high CPU load on my system but there could be others.

CREATE INDEX triggers_3
ON triggers
USING btree
(triggerid);

[Alexei]

There is a missing index on the trigger table which is causing the high CPU load on my system but there could be others.

CREATE INDEX triggers_3
ON triggers
USING btree
(triggerid);

Come on! The triggerid is a primary key for table 'triggers'! DO NOT CREATE THIS INDEX.

[newbish]

Come on! The triggerid is a primary key for table 'triggers'! DO NOT CREATE THIS INDEX.

Good point didn't realize that PostgreSQL auto created a unique index on primary keys but that does make sense.

In any case there is still an index missing on the trigger table. Since the query that is run orders by h.host and t.description the query would benefit from having an index on the description column.

[Mike78]

Postgres 8.1, Zabbix 1.6.4, heavy cpu load

I am experiencing the same behaviour.

1. I set the StartPollers=1 which helped a little now CPU usage is around 30%
2. I set DebugLevel=4 to check what is going on and I found zabbix_server checks the database very frequently for the next 'check task' and this causes the heavy load

Is there any config parameter which could make zabbix_server to do this checking for the next task not so often?

[Mike78]

I modified the source of /include/common.h changed

#define POLLER_DELAY 5
to
#define POLLER_DELAY 30

and recompiled Zabbix.

Now the periodic check for "next check task" is not so frequent and the load has significally decreased on the DB server.

Alexei, could this cause problem in zabbix operation?

[torrieri]

Hi guys!

In my case I just did a "VACUUM FULL VERBOSE;" on database.

The performance come back to the normal!

Best regards!