pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alex Stapleton <alexs@advfn.com>
To: Steve Bergman <steve@rueb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizing for writes. Data integrity not critical
Date: Sat, 21 May 2005 18:27:18 +0100
Message-ID: <9040B971-3565-40F8-9CEC-62995B70052C@advfn.com> (raw)
In-Reply-To: <428D1153.1020109@rueb.com>
References: <428D1153.1020109@rueb.com>

I am interested in optimising write performance as well, the machine  
I am testing on is maxing out around 450 UPDATEs a second which is  
quite quick I suppose. I haven't tried turning fsync off yet. The  
table has...a lot of indices as well. They are mostly pretty simple  
partial indexes though.

I would usually just shuv stuff into memcached, but I need to store  
and sort (in realtime) 10's of thousands of rows. (I am experimenting  
with replacing some in house toplist generating stuff with a PG  
database.) The partial indexes are basically the only thing which  
makes the table usable btw.

The read performance is pretty damn good, but for some reason I chose  
to wrote the benchmark script in PHP, which can totally destroy the  
accuracy of your results if you decide to call pg_fetch_*(), even  
pg_affected_rows() can skew things significantly.

So any ideas how to improve the number of writes I can do a second?  
The existing system sorts everything by the desired column when a  
request is made, and the data it sorts is updated in realtime (whilst  
it isn't being sorted.) And it can sustain the read/write load (to  
memory) just fine. If I PG had heap tables this would probably not be  
a problem at all, but it does, so it is. Running it in a ramdisk  
would be acceptable, it's just annoying to create the db everytime  
the machine goes down. And having to run the entire PG instance off  
of the ramdisk isn't great either.

On 19 May 2005, at 23:21, Steve Bergman wrote:

> Hi,
>
> I am using postgresql in small (almost trivial) application in  
> which I pull some data out of a Cobol C/ISAM file and write it into  
> a pgsl table.  My users can then use the data however they want by  
> interfacing to the data from OpenOffice.org.
>
> The amount of data written is about 60MB and takes a few minutes on  
> a 1200Mhz Athlon with a single 60MB IDE drive running Fedora Core 3  
> with pgsql 7.4.7.  I'd like to speed up the DB writes a bit if  
> possible.  Data integrity is not at all critical as the database  
> gets dropped, created, and populated immediately before each use.   
> Filesystem is ext3, data=ordered and I need to keep it that way as  
> there is other data in the filesystem that I do care about.  I have  
> not done any tuning in the config file yet, and was wondering what  
> things would likely speed up writes in this situation.
>
> I'm doing the writes individually.  Is there a better way?   
> Combining them all into a transaction or something?
>
> Thanks,
> Steve Bergman
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>




view thread (6+ messages)

Message-ID: <9040B971-3565-40F8-9CEC-62995B70052C@advfn.com>
Permalink:  ../9040B971-3565-40F8-9CEC-62995B70052C@advfn.com/
Also on:    postgresql.org/message-id/9040B971-3565-40F8-9CEC-62995B70052C@advfn.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: alexs@advfn.com, steve@rueb.com
  Subject: Re: Optimizing for writes. Data integrity not critical
  In-Reply-To: <9040B971-3565-40F8-9CEC-62995B70052C@advfn.com>
  References: <428D1153.1020109@rueb.com> <9040B971-3565-40F8-9CEC-62995B70052C@advfn.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