Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> 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
[EMAIL PROTECTED] | (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;
}
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
