Skip site navigation (1) Skip section navigation (2)

Re: Worsening performance with 7.4 on flash-based system

From: William Yu <wyu(at)talisys(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Worsening performance with 7.4 on flash-based system
Date: 2006-04-29 20:59:27
Message-ID: e30k3c$enh$1@news.hub.org (view raw or flat)
Thread:
Lists: 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(at)comcast(dot)net> wrote in message 
> news:e2b80f$245o$1(at)news(dot)hub(dot)org(dot)(dot)(dot)
>> 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...
>>
> 
> 

In response to

pgsql-performance by date

Next:From: Michael ArtzDate: 2006-04-29 21:53:10
Subject: Re: Easy question
Previous:From: Andreas KretschmerDate: 2006-04-29 20:40:53
Subject: Re: Slow restoration question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group