Обсуждение: 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 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!

On 8/28/08, Phoenix Kiula <phoenix.kiula@gmail.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

Phoenix Kiula <phoenix.kiula@gmail.com> 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 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.

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°

On 8/28/08, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
>
> Please show us a EXPLAIN ANALYSE for this query. Btw, why
>  random_page_cost=2? (your other post)


The EXPLAIN ANALYZE shows that it's using an INDEX and getting one
row! So I know the SQL is right.

Could it be that the SQL queries become slow because some other
background operation is running, like pg_dumpall or vaccuum? How would
I test this hypothesis if the log does have mentioned of "vaccuum
analyze" being slow?

On Thu, 2008-08-28 at 15:23 +0800, Phoenix Kiula wrote:
> 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?

After restart, OS and PostgreSQL caches are cleaned up -- it might also
slow down PostgreSQL a bit.

Regards,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                   http://www.gunduz.org

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@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/