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.albe@xxxxxxxxxx>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@xxxxxxxxxxxxxx) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> >
