Re: CPU load

Thanks’ for the instructions for detecting the problem.
It helped a lot.

First I have increased shared_buffers from 2000 to 8000. Since the
postgresql is on Debian I had to increase SHMMAX kernel value.
Everything is working much faster now.
There is still heavy load of postmaster process (up to 100%) for a simple
query

EXPLAIN ANALYSE SELECT * FROM system_alarm WHERE id_camera='3' AND
confirmed='false' AND dismissed='false' ORDER BY date DESC, time DESC
LIMIT 1;

(the table is indexed by id_camera, has around 1 milion rows, and this
query returns around 700000 rows and is executed (EXPLAIN ANALYSE) in
around 4800 ms, and this table is queried a lot although not so often
queried modified)

but I don't think that is strange behavior of the postgresql.
And it is exhibited all the time; the postgresql reset does not influence
it at all.
Once again thanks a lot, I learned a lot.

Regards,
Maja
> It would be useful to confirm that this is a backend process.
> With top, hit the 'c' key to show the full path / description of the
> process.
> Backend postgres processes should then have more useful descriptions of
> what
> they are doing and identifying themselves.
> You can also confirm what query is causing that by lining up the process
> id
> from top with the one returned by:
>
> select current_query, procpid from pg_stat_activity where current_query
> not
> like '<IDLE%';
>
> Or by simply using the process id for the where clause (where procpid = ).
>
> How often is the table being queried modified? Between the startup when
> the
> query is fast, and when it slows down, is there a lot of modification to
> its
> rows?
>
>
> On Fri, Sep 26, 2008 at 5:52 AM, Albe Laurenz
> <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:
>
>> kiki wrote:
>> > The number of rows returned by the query varies, right now is:
>> >
>> > 49 row(s)
>> > Total runtime: 3,965.718 ms
>> > The table currently has 971582 rows.
>> >
>> > But the problem is that when database server is restarted everything
>> works
>> > fine and fast. No heavy loads of the processor and as time passes
>> > situation with the processor is worsen.
>>
>> It would be interesting to know the result of EXPLAIN ANALYZE for the
>> query, both when it performs well and when it doesn't.
>>
>> One thing I see right away when I look at your postgresql.conf is that
>> you have set shared_buffers to an awfully small value of 2000, when you
>> have
>> enough memory on the machine (vmstat reports 2GB free memory, right?).
>>
>> Does the situation improve if you set it to a higher value?
>>
>> Yours,
>> Laurenz Albe
>>
>> --
>> Sent via pgsql-performance mailing list
>> (pgsql-performance(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>