Re: [HACKERS] 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? :) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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 <[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 > > 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] When did we get to be so fast?
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
voidins1(void);
voidins2(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
Re: [HACKERS] When did we get to be so fast?
On Thu, 7 Aug 2003, Bruce Momjian wrote: > 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 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? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] When did we get to be so fast?
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? 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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: [EMAIL PROTECTED]www.sonalysts.com215 Parkway North Work: (860)326-3671 or 442-4355 Waterford CT 06385 *** ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
