Thread: 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
Attachment
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/