Re: Worsening performance with 7.4 on flash-based system - Mailing list pgsql-performance

Well, since I got no response at all to this message, I can only assume that
I've asked the question in an insufficient way, or else that no one has
anything to offer on our problem.

This was my first post to the list, so if there's a better way I should be
asking this, or different data I should provide, hopefully someone will let
me know...

Thanks,
Greg

"Greg Stumph" <gregstumph@comcast.net> wrote in message
news:e2b80f$245o$1@news.hub.org...
> 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...
>