Re: When did we get to be so fast?

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I was just testing the threaded ecpg, and ran some performance tests.
> > Without using threads, I am seeing 100,000 inserts of a single word into
> > a simple table take 12 seconds:
> >     CREATE TABLE test_thread(message TEXT);
> > giving me 8333 inserts per second.  That seems very high.
>
> Single transaction, or one transaction per INSERT?

This is ecpg, and I didn't have AUTOCOMMIT on, so it was a single
transaction.  I had forgotten that.

Also, I was wrong in my computations.  It is 4166 inserts per second,
not 8333.  Sorry.

I am now seeing more reasonable numbers:

    one INSERT per transaction, fsync true     934
    one INSERT per transaction, fsync false    1818
    one INSERT per transaction, fsync true    4166

> With the present WAL design, it's not possible for one backend to commit
> more than one transaction per disk rotation --- unless fsync is off, or
> your disk drive lies about write-complete.  Given that you recently
> updated your hardware, I'm betting on the last item ...

Yep.

--
  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);
void        ins2(void);

EXEC SQL BEGIN DECLARE SECTION;
char       *dbname;
int        iterations;
EXEC SQL END DECLARE SECTION;

int
main(int argc, char **argv)
{
    if (argc != 3)
    {
        fprintf(stderr, "Usage: %s dbname iterations\n", argv[0]);
        return 1;
    }
    dbname = argv[1];

    iterations = atoi(argv[2]);

    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 = 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;
}