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