Re: strange performance regression between 7.4 and 8.1
От | Jeff Frost |
---|---|
Тема | Re: strange performance regression between 7.4 and 8.1 |
Дата | |
Msg-id | Pine.LNX.4.64.0703011331460.3892@discord.home.frostconsultingllc.com обсуждение исходный текст |
Ответ на | Re: strange performance regression between 7.4 and 8.1 ("Alex Deucher" <alexdeucher@gmail.com>) |
Список | 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
В списке pgsql-performance по дате отправления:
Предыдущее
От: "Alex Deucher"Дата:
Сообщение: Re: strange performance regression between 7.4 and 8.1