pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Matt Clark <matt@ymogen.net>
Cc: Ron Johnson <ron.l.johnson@cox.net>
Cc: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: Re: Hardware recommendations to scale to silly load
Date: Tue, 9 Sep 2003 23:25:23 -0400 (EDT)
Message-ID: <200309100325.h8A3PNU07656@candle.pha.pa.us> (raw)
In-Reply-To: <OAEAKHEHCMLBLIDGAFELIEHIDHAA.matt@ymogen.net>

Matt Clark wrote:
> > Just a data point, but on my Dual Xeon 2.4Gig machine with a 10k SCSI
> > drive I can do 4k inserts/second if I turn fsync off.  If you have a
> > battery-backed controller, you should be able to do the same.  (You will
> > not need to turn fsync off --- fsync will just be fast because of the
> > disk drive RAM).
> >
> > Am I missing something?
> 
> I think Ron asked this, but I will too, is that 4k inserts in
> one transaction or 4k transactions each with one insert?
> 
> fsync is very much faster (as are all random writes) with the
> write-back cache, but I'd hazard a guess that it's still not
> nearly as fast as turning fsync off altogether.  I'll do a test
> perhaps...

Sorry to be replying late.  Here is what I found.

fsync on
        Inserts all in one transaction         3700 inserts/second
        Inserts in separate transactions        870 inserts/second

fsync off
        Inserts all in one transaction         3700 inserts/second
        Inserts all in one transaction         2500 inserts/second

ECPG test program attached.

--

  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Attachments:

/wrk/tmp/speedtest.pgc (text/plain, 1.1K) download /* * Thread test program * by Philip Yarra */ #include <stdlib.h> void ins1(void); EXEC SQL BEGIN DECLARE SECTION; char *dbname; int iterations = 10; EXEC SQL END DECLARE SECTION; int main(int argc, char *argv[]) { if (argc < 2 || argc > 3) { fprintf(stderr, "Usage: %s dbname [iterations]\n", argv[0]); return 1; } dbname = argv[1]; if (argc == 3) iterations = atoi(argv[2]); if (iterations % 2 != 0) { fprintf(stderr, "iterations must be an even number\n"); return 1; } EXEC SQL CONNECT TO:dbname AS test0; /* DROP might fail */ EXEC SQL AT test0 DROP TABLE test_thread; EXEC SQL AT test0 COMMIT WORK; EXEC SQL AT test0 CREATE TABLE test_thread(message TEXT); EXEC SQL AT test0 COMMIT WORK; EXEC SQL DISCONNECT test0; ins1(); return 0; } void ins1(void) { int i; EXEC SQL WHENEVER sqlerror sqlprint; EXEC SQL CONNECT TO:dbname AS test1; EXEC SQL AT test1 SET AUTOCOMMIT TO ON; for (i = 0; i < iterations; i++) EXEC SQL AT test1 INSERT INTO test_thread VALUES('thread1'); // EXEC SQL AT test1 COMMIT WORK; EXEC SQL DISCONNECT test1; printf("thread 1 : done!\n"); }

view thread (45+ messages)  latest in thread

Message-ID: <200309100325.h8A3PNU07656@candle.pha.pa.us>
Permalink:  ../200309100325.h8A3PNU07656@candle.pha.pa.us/
Also on:    postgresql.org/message-id/200309100325.h8A3PNU07656@candle.pha.pa.us

 · 

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: pgman@candle.pha.pa.us, matt@ymogen.net, ron.l.johnson@cox.net
  Subject: Re: Hardware recommendations to scale to silly load
  In-Reply-To: <200309100325.h8A3PNU07656@candle.pha.pa.us>
  References: <OAEAKHEHCMLBLIDGAFELIEHIDHAA.matt@ymogen.net> <200309100325.h8A3PNU07656@candle.pha.pa.us>

* 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