From: | Rafael Martinez Guerrero <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Same SQL, 104296ms of difference between 7.4.12 and |
Date: | 2006-04-07 14:00:44 |
Message-ID: | 1144418444.24060.269.camel@bbking.uio.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Fri, 2006-04-07 at 15:31, Richard Huxton wrote:
> Rafael Martinez Guerrero wrote:
> > Hello
> >
> > I have a sql statement that takes 108489.780 ms with 8.0.7 in a
> > RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz /
> > 8GB RAM and only 4193.588 ms with 7.4.12 in a RHEL3/386linux server with
> > 2xIntel(R) Xeon(TM) CPU 2.40GHz / 4GB RAM.
> >
> > Some information:
> >
> > - There is no IO when I am running the sql, but it uses 99% of the cpu.
> > - I run VACUUM VERBOSE ANALYZE in both databases before the test.
> > - The databases are identical.
> > - No other jobs running when testing.
> > - Some different parameters between 7.4.12 and 8.0.7 :
> >
> > 7.4.12:
> > -------
> > shared_buffers = 114966 #(15% of ram)
> > sort_mem = 16384
> > vacuum_mem = 524288
> > wal_buffers = 64
> > checkpoint_segments = 16
> > effective_cache_size = 383220 #(50% ram)
> > random_page_cost = 3
> > default_statistics_target = 100
> >
> > 8.0.7:
> > ------
> > shared_buffers = 250160 #(25% ram)
> > work_mem = 8192
> > maintenance_work_mem = 131072
> > wal_buffers = 128
> > checkpoint_segments = 64
> > effective_cache_size = 500321 #(50% ram)
> > random_page_cost = 3
> > default_statistics_target = 100
> >
> > Any ideas of what I can test/configurate to find out why this happens?
> > Thanks in advance.
>
> I haven't looked in detail at the plans, but what stands out to me is
> that you've got a sort with a lot of columns and you've halved sort_mem
> (work_mem). Try increasing it (perhaps to 32000 even).
> set work_mem = 32000;
>
> Give that a quick go and see what happens. If it doesn't work, we'll
> look at the plans in more detail.
I know that this SQL could be done in a much better way, but I can not
change it at the moment.
work_mem = 16384:
-----------------
After restarting the database and running the explain two times:
107911.229 ms
work_mem = 32768:
-----------------
After restarting the database and running the explain two times:
103988.337 ms
--
Rafael Martinez, <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-04-07 14:34:53 | Re: Query planner is using wrong index. |
Previous Message | Richard Huxton | 2006-04-07 13:31:17 | Re: Same SQL, 104296ms of difference between 7.4.12 and |