pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: William Yu <wyu@talisys.com>
To: pgsql-performance@postgresql.org
Subject: Re: Low Performance for big hospital server ..
Date: Wed, 05 Jan 2005 19:31:10 -0800
Message-ID: <cribhc$262i$1@news.hub.org> (raw)
In-Reply-To: <1104939342.41dc094ecb1cf@webmail.moph.go.th>
References: <1104831107.41da6283add0c@webmail.moph.go.th>
	<20050104094657.540232d7.frank@wiles.org>
	<1104939342.41dc094ecb1cf@webmail.moph.go.th>

amrit@health2.moph.go.th wrote:
> Now I turn hyperthreading off and readjust the conf . I found the bulb query
> that was :
> update one flag of the table [8 million records which I think not too much]
> .When I turned this query off everything went fine.
> I don't know whether update the data is much slower than insert [Postgresql
> 7.3.2] and how could we improve the update method?

UPDATE is expensive. Under a MVCC setup, it's roughtly the equivalent of 
DELETE + INSERT new record (ie, old record deprecated, new version of 
record. Updating 8 million records would be very I/O intensive and 
probably flushes your OS cache so all other queries hit disk versus 
superfast memory. And if this operation is run multiple times during the 
day, you may end up with a lot of dead tuples in the table which makes 
querying it deadly slow.

If it's a dead tuples issue, you probably have to increase your 
freespace map and vacuum analyze that specific table more often. If it's 
an I/O hit issue, a lazy updating procedure would help if the operation 
is not time critical (eg. load the record keys that need updating and 
loop through the records with a time delay.)



view thread (38+ messages)  latest in thread

Message-ID: <cribhc$262i$1@news.hub.org>
Permalink:  ../cribhc$262i$1@news.hub.org/
Also on:    postgresql.org/message-id/cribhc$262i$1@news.hub.org

 · 

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: wyu@talisys.com
  Subject: Re: Low Performance for big hospital server ..
  In-Reply-To: <cribhc$262i$1@news.hub.org>

* 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