X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 143BDD1B537 for ; Wed, 10 Sep 2003 03:25:54 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 57919-04 for ; Wed, 10 Sep 2003 00:25:43 -0300 (ADT) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by svr1.postgresql.org (Postfix) with ESMTP id 2E261D1B50A for ; Wed, 10 Sep 2003 00:25:42 -0300 (ADT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id h8A3PNU07656; Tue, 9 Sep 2003 23:25:23 -0400 (EDT) From: Bruce Momjian Message-Id: <200309100325.h8A3PNU07656@candle.pha.pa.us> Subject: Re: Hardware recommendations to scale to silly load In-Reply-To: To: Matt Clark Date: Tue, 9 Sep 2003 23:25:23 -0400 (EDT) Cc: Ron Johnson , PgSQL Performance ML X-Mailer: ELM [version 2.4ME+ PL106 (25)] MIME-Version: 1.0 Content-Type: multipart/mixed; boundary=ELM1063164323-4470-1_ Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/164 X-Sequence-Number: 3530 --ELM1063164323-4470-1_ Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII 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 --ELM1063164323-4470-1_ Content-Transfer-Encoding: 7bit Content-Type: text/plain Content-Disposition: inline; filename="/wrk/tmp/speedtest.pgc" /* * Thread test program * by Philip Yarra */ #include 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"); } --ELM1063164323-4470-1_--