pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Neil Cooper <Neil.Cooper@scigames.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Disabling transaction/outdated-tuple behaviour
Date: Thu, 26 Aug 2004 11:20:20 -0700
Message-ID: <200408261120.20890.josh@agliodbs.com> (raw)
In-Reply-To: <1093543374.16123.38.camel@NCooper>
References: <1093543374.16123.38.camel@NCooper>

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



view thread (3+ messages)

Message-ID: <200408261120.20890.josh@agliodbs.com>
Permalink:  ../200408261120.20890.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200408261120.20890.josh@agliodbs.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com, Neil.Cooper@scigames.com
  Subject: Re: Disabling transaction/outdated-tuple behaviour
  In-Reply-To: <200408261120.20890.josh@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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