From: Greg Stumph <gregstumph@comcast.net>
To: pgsql-performance@postgresql.org
Subject: Worsening performance with 7.4 on flash-based system
Date: Fri, 21 Apr 2006 11:24:15 -0700
Message-ID: <e2b80f$245o$1@news.hub.org> (raw)
We are experiencing gradually worsening performance in PostgreSQL 7.4.7, on
a system with the following specs:
Linux OS (Fedora Core 1, 2.4 kernal)
Flash file system (2 Gig, about 80% full)
256 Meg RAM
566 MHz Celeron CPU
We use Orbit 2.9.8 to access PostGres. The database contains 62 tables.
When the system is running with a fresh copy of the database, performance is
fine. At its worst, we are seeing fairly simple SELECT queries taking up to
1 second to execute. When these queries are run in a loop, the loop can take
up to 30 seconds to execute, instead of the 2 seconds or so that we would
expect.
VACUUM FULL ANALYZE helps, but doesn't seem to eliminate the problem.
The following table show average execution time in "bad" performance mode in
the first column, execution time after VACUUM ANALYZE in the second column,
and % improvement (or degradation?) in the third. The fourth column show the
query that was executed.
741.831|582.038|-21.5| ^IDECLARE table_cursor
170.065|73.032|-57.1| FETCH ALL in table_cursor
41.953|45.513|8.5| CLOSE table_cursor
61.504|47.374|-23.0| SELECT last_value FROM pm_id_seq
39.651|46.454|17.2| select id from la_looprunner
1202.170|265.316|-77.9| select id from rt_tran
700.669|660.746|-5.7| ^IDECLARE my_tran_load_cursor
1192.182|47.258|-96.0| FETCH ALL in my_tran_load_cursor
181.934|89.752|-50.7| CLOSE my_tran_load_cursor
487.285|873.474|79.3| ^IDECLARE my_get_router_cursor
51.543|69.950|35.7| FETCH ALL in my_get_router_cursor
48.312|74.061|53.3| CLOSE my_get_router_cursor
814.051|1016.219|24.8| SELECT $1 = 'INSERT'
57.452|78.863|37.3| select id from op_sched
48.010|117.409|144.6| select short_name, long_name from la_loopapp
54.425|58.352|7.2| select id from cd_range
45.289|52.330|15.5| SELECT last_value FROM rt_tran_id_seq
39.658|82.949|109.2| SELECT last_value FROM op_sched_id_seq
42.158|68.189|61.7| select card_id,router_id from rt_valid
Has anyone else seen gradual performance degradation like this? Would
upgrading to Postgres 8 help? Any other thoughts on directions for
troubleshooting this?
Thanks...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gregstumph@comcast.net
Subject: Re: Worsening performance with 7.4 on flash-based system
In-Reply-To: <e2b80f$245o$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox