Re: strange performance regression between 7.4 and 8.1 - Mailing list pgsql-performance

On Thu, 1 Mar 2007, Alex Deucher wrote:

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