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

Usually when simple queries take a long time to run, it's the system
tables (pg_*) that have become bloated and need vacuuming. But that's
just random guess on my part w/o my detailed info.


Greg Stumph wrote:
> 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...
>>
>
>