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?
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