Обсуждение: Postgres 7.3.2 -> 8.1.2 upgrade performance issue
Hi, Recently tried an upgrade from 7.3.2 to 8.1.2. The actual upgrade went pretty well. However my application is now getting >10 times slower INSERT times than it was under 7.3.2. As far as possible I mirrored the settings in postgresql.conf (obviously I couldn't just drop in the old config). Any thoughts as to where to look, or what to do to fix this? Thanks, David.
David Brain <dbrain@bandwidth.com> writes:
> Recently tried an upgrade from 7.3.2 to 8.1.2. The actual upgrade went
> pretty well. However my application is now getting >10 times slower
> INSERT times than it was under 7.3.2. As far as possible I mirrored the
> settings in postgresql.conf (obviously I couldn't just drop in the old
> config).
> Any thoughts as to where to look, or what to do to fix this?
Did you remember to VACUUM ANALYZE after loading the data? It sounds
to me like bad choices of plans ...
regards, tom lane
Did you analyze after you imported the dump?
Woody
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Brain
Sent: Friday, February 03, 2006 10:03 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Postgres 7.3.2 -> 8.1.2 upgrade performance issue
Hi,
Recently tried an upgrade from 7.3.2 to 8.1.2. The actual upgrade went
pretty well. However my application is now getting >10 times slower INSERT
times than it was under 7.3.2. As far as possible I mirrored the settings
in postgresql.conf (obviously I couldn't just drop in the old config).
Any thoughts as to where to look, or what to do to fix this?
Thanks,
David.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Tom Lane wrote: > > Did you remember to VACUUM ANALYZE after loading the data? It sounds > to me like bad choices of plans ... > Yes - although I have autovacuum off, partly to make an apples to apples comparison with 7.3 and partly due to the nature of the app. On the application side I'm connecting via npgsql from .Net - the application is basically pumping large numbers of rows into a heavily indexed db (used for analysis later). Performance went from 100-200 rows per-second (which was being capped by cpu usage on the client side) to more like 10 rows per second. I'm going to investigate what effect upgrading npgsql has too - as it appears there is a new version available. I will report back with what I find. I just enabled autovacuum - with no apparent speed increase. Odd. David. -- David Brain - bandwidth.com dbrain@bandwidth.com 919.297.1078
OK - spent some more time profiling what was going on on both the client and server machine. It began ti look more like a connector problem (I was seeing low CPU usage on both client & server and could see very slow BIND/INSERT/COMMIT commands being sent to the db). Upgraded to Npgsql 1.0beta2 - from http://pgfoundry.org/projects/npgsql and things have improved significantly. Thanks for the help, David. -- David Brain - bandwidth.com dbrain@bandwidth.com 919.297.1078