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
EXPLAIN ANALYSE SELECT * FROM system_alarm WHERE id_camera='3' AND
confirmed='false' AND dismissed='false' ORDER BY date DESC, time DESC
(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
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.
> 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
> Backend postgres processes should then have more useful descriptions of
> they are doing and identifying themselves.
> You can also confirm what query is causing that by lining up the process
> from top with the one returned by:
> select current_query, procpid from pg_stat_activity where current_query
> 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
> query is fast, and when it slows down, is there a lot of modification to
> On Fri, Sep 26, 2008 at 5:52 AM, Albe Laurenz
>> 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
>> > 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
>> enough memory on the machine (vmstat reports 2GB free memory, right?).
>> Does the situation improve if you set it to a higher value?
>> Laurenz Albe
>> Sent via pgsql-performance mailing list
>> To make changes to your subscription:
In response to
pgsql-performance by date
|Next:||From: Harald Armin Massa||Date: 2008-09-29 07:26:03|
|Subject: Re: CPU load|
|Previous:||From: Scott Marlowe||Date: 2008-09-29 04:12:18|
|Subject: Re: Slow updates, poor IO|