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.

I remember testing on older hardware and seeing around 60 inserts per
second.  Have we improved our performance that much, or is it the
hardware improving over the years.

My hardware list listed on my homepage, shown below.

This is with 7.4, default everything.

-- 
  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

Thu, 7 Aug 2003 12:39:30 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
When did we get to be so fast?
Bruce Momjian wrote:
> 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.
> 
> I remember testing on older hardware and seeing around 60 inserts per
> second.  Have we improved our performance that much, or is it the
> hardware improving over the years.
> 
> My hardware list listed on my homepage, shown below.
> 
> This is with 7.4, default everything.
> 

I ran a suite of pgbench runs (100 different client/transaction
count combinations) 5 times to compare 7.3.4 and 7.4b1.

7.4b1 was (on average) 31% faster than 7.3.4.
-- 
P. J. "Josh" Rovero                                 Sonalysts, Inc.
Email: rovero@sonalysts.com    www.sonalysts.com    215 Parkway North
Work: (860)326-3671 or 442-4355                     Waterford CT 06385
***********************************************************************


Thu, 07 Aug 2003 13:25:06 -0400
"P.J. \"Josh\" Rovero" <rovero@sonalysts.com>
Re: When did we get to be so fast?
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?

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 ...

			regards, tom lane

Thu, 07 Aug 2003 16:13:35 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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;
}
Thu, 7 Aug 2003 19:42:00 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: When did we get to be so fast?
On Thu, 7 Aug 2003, Bruce Momjian wrote:

> 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

Shouldn't 1 and 3 be about the same though?  If both are 'one INSERT per
transaction with fsync true', how come such a massive difference in #s?


Thu, 7 Aug 2003 20:49:40 -0300 (ADT)
The Hermit Hacker <scrappy@postgresql.org>
Re: When did we get to be so fast?
The Hermit Hacker wrote:
> On Thu, 7 Aug 2003, Bruce Momjian wrote:
> 
> > 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
> 
> Shouldn't 1 and 3 be about the same though?  If both are 'one INSERT per
> transaction with fsync true', how come such a massive difference in #s?

Man, I can't do anything right; should be:

	one INSERT per transaction, fsync true	 	 934
	one INSERT per transaction, fsync false		1818
	INSERTs all in one transaction, fsync true	4166

-- 
  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

Thu, 7 Aug 2003 19:54:45 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: When did we get to be so fast?
On Thu, 7 Aug 2003, Bruce Momjian wrote:

> Man, I can't do anything right; should be:
>
> 	one INSERT per transaction, fsync true	 	 934
> 	one INSERT per transaction, fsync false		1818
> 	INSERTs all in one transaction, fsync true	4166

Brain thinking one thing, fingers typing something totallydifferent, eh?
:)


Thu, 7 Aug 2003 21:01:55 -0300 (ADT)
The Hermit Hacker <scrappy@postgresql.org>
Re: When did we get to be so fast?
On 7 Aug 2003 at 19:54, Bruce Momjian wrote:
> Man, I can't do anything right; should be:
> 
> 	one INSERT per transaction, fsync true	 	 934
> 	one INSERT per transaction, fsync false		1818
> 	INSERTs all in one transaction, fsync true	4166

Just curiousity, what will all inserts in one transaction with fsync false 
would yield?

Bye
 Shridhar

--
philosophy:	The ability to bear with calmness the misfortunes of our friends.


Fri, 08 Aug 2003 12:35:19 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: When did we get to be so fast?