From: Richard Huxton <dev@archonet.com>
To: Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no>
Cc: pgsql-performance@postgresql.org
Subject: Re: Same SQL, 104296ms of difference between 7.4.12 and
Date: Fri, 07 Apr 2006 14:31:17 +0100
Message-ID: <443669A5.2000603@archonet.com> (raw)
In-Reply-To: <1144413933.24060.253.camel@bbking.uio.no>
References: <1144413933.24060.253.camel@bbking.uio.no>
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.
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, r.m.guerrero@usit.uio.no
Subject: Re: Same SQL, 104296ms of difference between 7.4.12 and
In-Reply-To: <443669A5.2000603@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox