Re: strange performance regression between 7.4 and 8.1

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:

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

Thanks,

Alex

>
> --
> Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 650-780-7908 FAX: 650-649-1954
>