[GENERAL] Slow PG after upgrade to 8.2.9!!

I hope someone can urgently help. I was running 8.2.3 with a lot of
pleasure and no-nonsense. Very fast and delightful database that had
me singing paeans.

But I upgraded to 8.2.9 this morning and have had a major slowdown of
the Database processes. How do I begin to test what is going wrong?

I checked the log files and there are "LOG: duration: 84533.845 ms
statement: " type errors messages for the simplest of queries that
call the indexes into question.

Nothing has ben changed on the Database or in the conf files other than just
the RPM upgrade!

The SELECTs are taking about 85 seconds and the main INSERT to a table
(no binary blob or anything, just usual data!) is taking about 145
seconds sometimes.

How can I begin to diagnose what is wrong? Has there been a major
change to some CONF variables between versions 8.2.3 and 8.2.9 that I
should look at?

Thanks for any pointers or direction!

--
Sent via pgsql-general mailing list (pgsql-general-at-postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 8/28/08, Phoenix Kiula wrote:
> I hope someone can urgently help. I was running 8.2.3 with a lot of
> pleasure and no-nonsense. Very fast and delightful database that had
> me singing paeans.
>
> But I upgraded to 8.2.9 this morning and have had a major slowdown of
> the Database processes. How do I begin to test what is going wrong?
>
> I checked the log files and there are "LOG: duration: 84533.845 ms
> statement: " type errors messages for the simplest of queries that
> call the indexes into question.
>
> Nothing has ben changed on the Database or in the conf files other than just
> the RPM upgrade!
>
> The SELECTs are taking about 85 seconds and the main INSERT to a table
> (no binary blob or anything, just usual data!) is taking about 145
> seconds sometimes.
>
> How can I begin to diagnose what is wrong? Has there been a major
> change to some CONF variables between versions 8.2.3 and 8.2.9 that I
> should look at?
>
> Thanks for any pointers or direction!
>




To add to that, some information.


CentOS 4.4 Linux
Postgresql 8.2.9 (now)
6GB RAM
Dual Core 2 Duo CPU
SATA disks RAID 1


These are my postgres.conf settings:


max_connections = 300
shared_buffers = 330MB
effective_cache_size = 512000
max_fsm_relations = 100
max_fsm_pages = 300000

work_mem = 20MB
temp_buffers = 4096
authentication_timeout = 10s
ssl = off
checkpoint_warning = 3600


random_page_cost = 2
autovacuum = on
autovacuum_vacuum_cost_delay = 20
vacuum_cost_delay = 20
autovacuum_naptime = 10
stats_start_collector = on
stats_row_level = on
autovacuum_vacuum_threshold = 75
autovacuum_analyze_threshold = 25
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_scale_factor = 0.01

--
Sent via pgsql-general mailing list (pgsql-general-at-postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Phoenix Kiula schrieb:

> I hope someone can urgently help. I was running 8.2.3 with a lot of
> pleasure and no-nonsense. Very fast and delightful database that had
> me singing paeans.
>
> But I upgraded to 8.2.9 this morning and have had a major slowdown of
> the Database processes. How do I begin to test what is going wrong?
>
> I checked the log files and there are "LOG: duration: 84533.845 ms
> statement: " type errors messages for the simplest of queries that
> call the indexes into question.

Please show us a EXPLAIN ANALYSE for this query. Btw, why
random_page_cost=2? (your other post)



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

--
Sent via pgsql-general mailing list (pgsql-general-at-postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Aug 28, 2008 at 02:44:08PM +0300, Devrim GÜNDÜZ wrote:
> After restart, OS and PostgreSQL caches are cleaned up -- it might also
> slow down PostgreSQL a bit.

I'll bet this is the right answer -- before, you were mostly getting
things out of cache (memory), and right now everything has to come off
the disk. Does iostat seem to confirm that? Are you swapping, by any
chance?

A


--
Andrew Sullivan
ajs-at-commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

--
Sent via pgsql-general mailing list (pgsql-general-at-postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general