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");
}
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