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
