Re: Slow PG after upgrade to 8.2.9!!

On 8/28/08, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> 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 DB 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 db 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