Thread: When did we get to be so fast?
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, Pennsylvania19073
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 ***********************************************************************
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
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;
}
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?
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 934one INSERT per transaction, fsync false 1818INSERTs all in onetransaction, 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, Pennsylvania19073
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? :)
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? ByeShridhar -- philosophy: The ability to bear with calmness the misfortunes of our friends.