Re: strange performance regression between 7.4 and 8.1

On Thu, 1 Mar 2007, Alex Deucher wrote:

> On 3/1/07, Jeff Frost <jeff(at)frostconsultingllc(dot)com> 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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954