Re: Hardware recommendations to scale to silly load

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