pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marinos J. Yannikos <mjy@geizhals.at>
To: pgsql-performance@postgresql.org
Subject: optimization ideas for frequent, large(ish) updates in frequently accessed DB?
Date: Fri, 13 Feb 2004 01:58:34 +0100
Message-ID: <402C213A.4030105@geizhals.at> (raw)

Hi,

one of our tables has to be updated frequently, but concurrently running 
SELECT-queries must also have low latency times (it's being accessed 
through a web interface).

I'm looking for ideas that might improve the interactive performance of 
the system, without slowing down the updates too much. Here are the 
characteristics of the table and its use:

- approx. 2 million rows

- approx. 4-5 million rows per day are replaced in short bursts of 
1-200k rows (average ~3000 rows per update)

- the table needs 6 indexes (not all indexes are used all the time, but 
keeping them all the time slows the system down less than re-creating 
some of them just before they're needed and dropping them afterwards)

- an "update" means that 1-200k rows with a common value in a particular 
field are replaced with an arbitrary number of new rows (with the same 
value in that field), i.e.:

begin transaction;
   delete from t where id=5;
   insert into t (id,...) values (5,...);
   ... [1-200k rows]
end;

The problem is, that a large update of this kind can delay SELECT 
queries running in parallel for several seconds, so the web interface 
used by several people will be unusable for a short while.

Currently, I'm using temporary tables:

create table xyz as select * from t limit 0;
insert into xyz ...
...
begin transaction;
delete from t where id=5;
insert into t select * from xyz;
end;
drop table xyz;

This is slightly faster than inserting directly into t (and probably 
faster than using COPY, even though using that might reduce the overall 
load on the database).

What other possibilities are there, other than splitting up the 15 
columns of that table into several smaller tables, which is something 
I'd like to avoid? Would replication help? (I doubt it, but haven't 
tried it yet...) Writing to one table (without indexes) and creating 
indexes and renaming it to the "read table" periodically in a double 
buffering-like fashion wouldn't work either(?), since the views and 
triggers would have to be re-created every time as well and other 
problems might arise.

The postgresql.conf options are already reasonably tweaked for 
performance(IMHO), but perhaps some settings are particularly critical:

shared_buffers=100000
(I tried many values, this seems to work well for us - 12GB RAM)
wal_buffers=500
sort_mem=800000
checkpoint_segments=16
effective_cache_size=1000000
etc.

Any help/suggestions would be greatly appreciated... Even if it's 
something like "you need a faster db box, there's no other way" ;-)

Regards,
  Marinos



view thread (10+ messages)  latest in thread

Message-ID: <402C213A.4030105@geizhals.at>
Permalink:  ../402C213A.4030105@geizhals.at/
Also on:    postgresql.org/message-id/402C213A.4030105@geizhals.at

 · 

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: mjy@geizhals.at
  Subject: Re: optimization ideas for frequent, large(ish) updates in frequently accessed DB?
  In-Reply-To: <402C213A.4030105@geizhals.at>

* 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