On Thu, 1 Mar 2007, Alex Deucher wrote:
On 3/1/07, Jeff Frost <jeff@xxxxxxxxxxxxxxxxxxxxxx> wrote:
On Thu, 1 Mar 2007, Joshua D. Drake wrote:
> Alex Deucher wrote:
>> Hello,
>>
>> I have noticed a strange performance regression and I'm at a loss as
>> to what's happening. We have a fairly large database (~16 GB). The
>> original postgres 7.4 was running on a sun v880 with 4 CPUs and 8 GB
>> of ram running Solaris on local scsi discs. The new server is a sun
>> Opteron box with 4 cores, 8 GB of ram running postgres 8.1.4 on Linux
>> (AMD64) on a 4 Gbps FC SAN volume. When we created the new database
>> it was created from scratch rather than copying over the old one,
>> however the table structure is almost identical (UTF8 on the new one
>> vs. C on the old). The problem is queries are ~10x slower on the new
>> hardware. I read several places that the SAN might be to blame, but
>> testing with bonnie and dd indicates that the SAN is actually almost
>> twice as fast as the scsi discs in the old sun server. I've tried
>> adjusting just about every option in the postgres config file, but
>> performance remains the same. Any ideas?
>
> Vacuum? Analayze? default_statistics_target? How many shared_buffers?
> effective_cache_size? work_mem?
Also, an explain analyze from both the 7.4 and 8.1 systems with one of the
10x slower queries would probably be handy.
I'll run some and get back to you.
What do you mean by "created from scratch rather than copying over the old
one"? How did you put the data in? Did you run analyze after loading it?
Is autovacuum enabled and if so, what are the thresholds?
Both the databases were originally created from xml files. We just
re-created the new one from the xml rather than copying the old
database over. I didn't manually run analyze on it, but we are
running the autovacuum process:
You should probably manually run analyze and see if that resolves your
problem.
autovacuum = on #off # enable autovacuum subprocess?
autovacuum_naptime = 360 #60 # time between autovacuum runs, in
secs
autovacuum_vacuum_threshold = 10000 #1000 # min # of tuple updates
before
# vacuum
autovacuum_analyze_threshold = 5000 #500 # min # of tuple updates
before
Most people make autovacuum more aggressive and not less aggressive. In fact,
the new defaults in 8.2 are:
#autovacuum_vacuum_threshold = 500 # min # of tuple updates before
# vacuum
#autovacuum_analyze_threshold = 250 # min # of tuple updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
# vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before
I'd recommend trying those, otherwise you might not vacuum enough.
It'll be interesting to see the explain analyze output after you've run
analyze by hand.
--
Jeff Frost, Owner <jeff@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
