Re: [PERFORM] Slow query after upgrade from 8.2 to 8.4

Kaloyan Iliev Iliev writes: > We recently upgrade our server from PG8.2 to 8.4. > ... > Here I will post explain analyze. If you think it is necessary I will > post the exact query: > http://explain.depesz.com/s/J0O Yeah, you need to show the query. It looks like the performance problem is stem

Re: [PERFORM] pg_upgrade

Tory M Blue wrote: > >From my last report I had success but it was successful due to lots of > manual steps. I figured it may be safer to just create a new rpm, > installing to pgsql9 specific directories and a new data directory. > > This allows pg_upgrade to complete successfully (so it says). H

Re: [PERFORM] Slow query after upgrade from 8.2 to 8.4

On 12/08/2011 11:29 AM, Kaloyan Iliev Iliev wrote: I think the planner didn't choose the best plan. I will try to I rewrite the query and set join_collapse_limit to 1 and see what will happen. Meanwhile any suggestions are welcome. Try rewriting the query, definitely. But make sure your statis

[PERFORM] Slow query after upgrade from 8.2 to 8.4

Hello All. We recently upgrade our server from PG8.2 to 8.4. Our current version is: database=> SELECT version(); version

Re: [PERFORM] Response time increases over time

I have put pg_xlog back to the ext3 partition, but nothing changed. I have also switched off sync_commit, but nothing. This is quite interesting... Here is a graph about the transaction time (sync_commit off, pg_xlog on separate file system): Graph On the

Re: [PERFORM] Response time increases over time

Otto, Separate the pg_xlog directory onto its own filesystem and retry your tests. Bob Lunney From: Havasvölgyi Ottó To: Marti Raudsepp Cc: Aidan Van Dyk ; pgsql-performance@postgresql.org Sent: Thursday, December 8, 2011 9:48 AM Subject: Re: [PERFORM] Resp

Re: [PERFORM] Response time increases over time

I have moved the data directory (xlog, base, global, and everything) to an ext4 file system. The result hasn't changed unfortuately. With the same load test the average response time: 80ms; from 40ms to 120 ms everything occurs. This ext4 has default settings in fstab. Have you got any other idea w

Re: [PERFORM] Partitions and joins lead to index lookups on all partitions

Hi Ondrej, Your solution has occurred to me, and wil even work in some cases. But in more advanced queries, where for example, I would need the group ID again to do some window function magic, this will sadly not work, without again doing a reverse lookup to the ref_table to find it again. This sc

Re: [PERFORM] Response time increases over time

On Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk wrote: > Let me guess, debian squeeze, with data and xlog on both on a single > ext3 filesystem, and the fsync done by your commit (xlog) is flushing > all the dirty data of the entire filesystem (including PG data writes) > out before it can return...

Re: [PERFORM] Response time increases over time

Yes, ext3 is the global file system, and you are right, PG xlog and data are on this one. Is this really what happens Aidan at fsync? What is be the best I can do? Mount xlog directory to a separate file system? If so, which file system fits the best for this purpose? Should I also mount the data s