pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Disabling transaction/outdated-tuple behaviour
3+ messages / 3 participants
[nested] [flat]

* Disabling transaction/outdated-tuple behaviour
@ 2004-08-26 18:02 Neil Cooper <Neil.Cooper@scigames.com>
  2004-08-26 18:18 ` Re: Disabling transaction/outdated-tuple behaviour Matt Clark <matt@ymogen.net>
  2004-08-26 18:20 ` Re: Disabling transaction/outdated-tuple behaviour Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Neil Cooper @ 2004-08-26 18:02 UTC (permalink / raw)
  To: pgsql-performance

I am using a simple PostgreSQL 7.3 database in a soft-realtime
application.

I have a problem where an update on a record within a (fully indexed)
table containing less than ten records needs to occur as fast as
possible.

Immediately after performing a vaccum, updates take upto 50 milliseconds
to occur, however the update performance degrades over time, such that
after a few hours of continuous updates, each update takes about half a
second. Regular vacuuming improves the performance temporarily, but
during the vacuum operation (which takes upto 2 minutes), performance of
concurrent updates falls below an acceptable level (sometimes > 2
seconds per update).

According to the documentation, PostgreSQL keeps the old versions of the
tuples in case of use by other transactions (i.e. each update is
actually extending the table). I believe this behaviour is what is
causing my performance problem.

Is there a way to disable this behaviour such that an update operation
would overwrite the current record and does not generate an outdated
tuple each time? (My application does not need transactional support).
 
I believe this would give me the performance gain I need, and would
eliminate the need for regular vacuuming too.
 
Thanks in advance,

Neil Cooper.

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Disabling transaction/outdated-tuple behaviour
  2004-08-26 18:02 Disabling transaction/outdated-tuple behaviour Neil Cooper <Neil.Cooper@scigames.com>
@ 2004-08-26 18:18 ` Matt Clark <matt@ymogen.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Matt Clark @ 2004-08-26 18:18 UTC (permalink / raw)
  To: 'Neil Cooper' <Neil.Cooper@scigames.com>; pgsql-performance

> Immediately after performing a vaccum, updates take upto 50 
> milliseconds to occur, however the update performance 
> degrades over time, such that after a few hours of continuous 
> updates, each update takes about half a second. Regular 
> vacuuming improves the performance temporarily, but during 
> the vacuum operation (which takes upto 2 minutes), 
> performance of concurrent updates falls below an acceptable 
> level (sometimes > 2 seconds per update).

You must be doing an enormous number of updates!  You can vacuum as often as
you like, and should usually do so at least as often as the time it takes
for 'all' tuples to be updated.  So, in your case, every 10 updates.  OK,
that seems unnecessary, how about every 100 updates? 

> According to the documentation, PostgreSQL keeps the old 
> versions of the tuples in case of use by other transactions 
> (i.e. each update is actually extending the table). I believe 
> this behaviour is what is causing my performance problem.

Yes, it probably is.

> Is there a way to disable this behaviour such that an update 
> operation would overwrite the current record and does not 
> generate an outdated tuple each time? (My application does 
> not need transactional support).

No, I don't believe there is.  If you really don't need transaction support
then you might want to reconsider whether postgres is really the right tool.

M




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Disabling transaction/outdated-tuple behaviour
  2004-08-26 18:02 Disabling transaction/outdated-tuple behaviour Neil Cooper <Neil.Cooper@scigames.com>
@ 2004-08-26 18:20 ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2004-08-26 18:20 UTC (permalink / raw)
  To: Neil Cooper <Neil.Cooper@scigames.com>; pgsql-performance

Neil,

> I am using a simple PostgreSQL 7.3 database in a soft-realtime
> application.

Then you're not going to like the answer I have for you, see below.

> I have a problem where an update on a record within a (fully indexed)
> table containing less than ten records needs to occur as fast as
> possible.

Have you considered dropping the indexes?  On such a small table, they won't 
be used, and they are detracting significantly from your update speed.

> Immediately after performing a vaccum, updates take upto 50 milliseconds
> to occur, however the update performance degrades over time, such that
> after a few hours of continuous updates, each update takes about half a
> second. Regular vacuuming improves the performance temporarily, but
> during the vacuum operation (which takes upto 2 minutes), performance of
> concurrent updates falls below an acceptable level (sometimes > 2
> seconds per update).

This is "normal" depending on your platform and concurrent activity.   More 
frequent vacuums would take less time each.   What is your max_fsm_pages set 
to?   Increasing this may decrease the necessity of vacuums as well as 
speeding them up.  Also, are you vacuuming the whole DB or just that table?  
2 mintues seems like a long time; I can vacuum a 100GB database in less than 
4.

> Is there a way to disable this behaviour such that an update operation
> would overwrite the current record and does not generate an outdated
> tuple each time? (My application does not need transactional support).

No.  Our ACID Transaction compliance depends on "that behaviour" (MVCC).  We 
don't offer PostgreSQL in a "non-ACID mode".   If your application truly does 
not need transactional support, you may want to consider an embedded database 
instead, such as BerkeleyDB or SQLite.    PostgreSQL has a *lot* of "baggage" 
associated with having 99.99% incorruptable transactions.

Alternately, you may also want to take a look at TelegraphCG, a derivative of 
PostgreSQL designed to handle "streaming data".  They may have already 
conquered some of your difficulties for you.  
http://telegraph.cs.berkeley.edu/

Were I you, I would start with tuning the database first through 
PostgreSQL.conf and a careful look at my hardware usage and DB maintenance.   
Then I would consider testing 8.0, which has some specific improvements 
designed to address some of the problems you are having.   Particularly, 
Jan's Background Writer and Lazy Vacuum.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-08-26 18:20 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-26 18:02 Disabling transaction/outdated-tuple behaviour Neil Cooper <Neil.Cooper@scigames.com>
2004-08-26 18:18 ` Matt Clark <matt@ymogen.net>
2004-08-26 18:20 ` Josh Berkus <josh@agliodbs.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