X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A1225D1B430 for ; Wed, 26 Nov 2003 16:52:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 70459-08 for ; Wed, 26 Nov 2003 12:51:36 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id EF51BD1B44C for ; Wed, 26 Nov 2003 12:51:30 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id hAQGpZNu086709 for ; Wed, 26 Nov 2003 16:51:35 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id hAQGivrO085544 for pgsql-performance@postgresql.org; Wed, 26 Nov 2003 16:44:57 GMT From: William Yu X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Maximum Possible Insert Performance? Date: Wed, 26 Nov 2003 08:44:59 -0800 Organization: Hub.Org Networking Services Lines: 42 Message-ID: References: <3FC19C2C.4050704@myrealbox.com> <200311240925.58070.josh@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en In-Reply-To: <200311240925.58070.josh@agliodbs.com> To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/266 X-Sequence-Number: 4826 Josh Berkus wrote: > William, > > >>When my current job batch is done, I'll save a copy of the dir and give >>the WAL on ramdrive a test. And perhaps even buy a Sandisk at the local >>store and run that through the hooper. > > > We'll be interested in the results. The Sandisk won't be much of a > performance test; last I checked, their access speed was about 1/2 that of a > fast SCSI drive. But it could be a feasability test for the more expensive > RAMdrive approach. Some initial numbers. I simulated a CPU increase by underclocking the processors. Most of the time, performance does not scale linearly with clock speed but since I also underclocked the FSB and memory bandwidth with the CPU, it's nearly an exact match. 1.15GHz 6.14 1.53GHz 6.97 +33% CPU = +13.5% performance I then simulated adding a heapload of extra memory by running my job a second time. Unfortunately, to keep my 25GB DB mostly cached in memory, the word heapload is too accurate. Run 1 6.97 Run 2 7.99 +14% I popped in an extra IDE hard drive to store the WAL files and that boosted the numbers by a little. From looking at iostat, the ratio looked like 300K/s WAL for 1MB/s data. WAL+Data on same disk 6.97 WAL+Data separated 7.26 +4% I then tried to put the WAL directory onto a ramdisk. I turned off swapping, created a tmpfs mount point and copied the pg_xlog directory over. Everything looked fine as far as I could tell but Postgres just panic'd with a "file permissions" error. Anybody have thoughts to why tmpfs would not work?