pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWorsening performance with 7.4 on flash-based system
5+ messages / 4 participants
[nested] [flat]
* Worsening performance with 7.4 on flash-based system
@ 2006-04-21 18:24 Greg Stumph <gregstumph@comcast.net>
2006-04-28 18:36 ` Re: Worsening performance with 7.4 on flash-based system Greg Stumph <gregstumph@comcast.net>
0 siblings, 1 reply; 5+ messages in thread
From: Greg Stumph @ 2006-04-21 18:24 UTC (permalink / raw)
To: pgsql-performance
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...
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Worsening performance with 7.4 on flash-based system
2006-04-21 18:24 Worsening performance with 7.4 on flash-based system Greg Stumph <gregstumph@comcast.net>
@ 2006-04-28 18:36 ` Greg Stumph <gregstumph@comcast.net>
2006-04-29 20:59 ` Re: Worsening performance with 7.4 on flash-based system William Yu <wyu@talisys.com>
2006-04-29 23:51 ` Re: Worsening performance with 7.4 on flash-based system chris smith <dmagick@gmail.com>
2006-05-01 15:55 ` Re: Worsening performance with 7.4 on flash-based system Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 3 replies; 5+ messages in thread
From: Greg Stumph @ 2006-04-28 18:36 UTC (permalink / raw)
To: 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...
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Worsening performance with 7.4 on flash-based system
2006-04-21 18:24 Worsening performance with 7.4 on flash-based system Greg Stumph <gregstumph@comcast.net>
2006-04-28 18:36 ` Re: Worsening performance with 7.4 on flash-based system Greg Stumph <gregstumph@comcast.net>
@ 2006-04-29 20:59 ` William Yu <wyu@talisys.com>
2 siblings, 0 replies; 5+ messages in thread
From: William Yu @ 2006-04-29 20:59 UTC (permalink / raw)
To: 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...
>>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Worsening performance with 7.4 on flash-based system
2006-04-21 18:24 Worsening performance with 7.4 on flash-based system Greg Stumph <gregstumph@comcast.net>
2006-04-28 18:36 ` Re: Worsening performance with 7.4 on flash-based system Greg Stumph <gregstumph@comcast.net>
@ 2006-04-29 23:51 ` chris smith <dmagick@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: chris smith @ 2006-04-29 23:51 UTC (permalink / raw)
To: Greg Stumph <gregstumph@comcast.net>; +Cc: pgsql-performance
On 4/29/06, Greg Stumph <gregstumph@comcast.net> 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.
If you're inserting/updating/deleting a table or tables heavily, then
you'll need to vacuum it a lot more often than a reasonably static
table. Are you running contrib/autovacuum at all? PG 8.0 and above
have autovacuum built in but 7.4.x needs to run the contrib version.
PS - the latest 7.4 version is .12 - see
http://www.postgresql.org/docs/7.4/interactive/release.html for what
has changed (won't be much in performance terms but may fix data-loss
bugs).
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Worsening performance with 7.4 on flash-based system
2006-04-21 18:24 Worsening performance with 7.4 on flash-based system Greg Stumph <gregstumph@comcast.net>
2006-04-28 18:36 ` Re: Worsening performance with 7.4 on flash-based system Greg Stumph <gregstumph@comcast.net>
@ 2006-05-01 15:55 ` Scott Marlowe <smarlowe@g2switchworks.com>
2 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2006-05-01 15:55 UTC (permalink / raw)
To: Greg Stumph <gregstumph@comcast.net>; +Cc: pgsql-performance
On Fri, 2006-04-28 at 13:36, 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...
I'd pick one particular case and do explain analyze on it both right
after a reload, after running for a while, and after a vacuum analyze.
Also, do a vacuum verbose on the database and post the output of that
when the system's slowed down.
Do you make a lot of temp tables? Run a lot of DDL? I don't think we
have enough information to make a real informed decision, but I'm not
sure what questions to ask to find out where the bottleneck is...
Also, this could be the flash controller / card combo causing problems.
Do you start with a freshly formatted card at the beginning? I know
that flash controllers randomize the part of the card that gets written
to so that you don't kill one part of it early due to writing on just on
part. Could be that as the controller maps the card behind the scenes,
the access gets slower on the lower level, and there's nothing
PostgreSQL can do about it.
Can you tell us what your usage patterns are in a bit more detail?
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-05-01 15:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-21 18:24 Worsening performance with 7.4 on flash-based system Greg Stumph <gregstumph@comcast.net>
2006-04-28 18:36 ` Greg Stumph <gregstumph@comcast.net>
2006-04-29 20:59 ` William Yu <wyu@talisys.com>
2006-04-29 23:51 ` chris smith <dmagick@gmail.com>
2006-05-01 15:55 ` Scott Marlowe <smarlowe@g2switchworks.com>
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