Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id F253A2E01FA for ; Sun, 2 Dec 2007 18:53:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 01003-02 for ; Sun, 2 Dec 2007 18:53:03 -0400 (AST) Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id E63062E01F3 for ; Sun, 2 Dec 2007 18:53:08 -0400 (AST) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id lB2Mr6NE022541; Sun, 2 Dec 2007 17:53:06 -0500 (EST) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id lB2Mr6if022532; Sun, 2 Dec 2007 17:53:06 -0500 (EST) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Sun, 2 Dec 2007 17:53:06 -0500 (EST) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Beyers Cronje cc: pgsql-performance@postgresql.org Subject: Re: PostgreSQL 8.2.5 slow performance on INSERT on Linux In-Reply-To: <329bff5c0712021334k474483bav278f79e7e27b26ac@mail.gmail.com> Message-ID: References: <329bff5c0712021334k474483bav278f79e7e27b26ac@mail.gmail.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/17 X-Sequence-Number: 27833 On Sun, 2 Dec 2007, Beyers Cronje wrote: > Initially I tested this on my development PC, an old P4 system with 2GB > RAM and 10,000 INSERTs took ~12 secs on average, which I was fairly > satisfied with. I then moved everything over to our test server, a new > Dell 1950 server with quad core Xeon processors, 4GB RAM and SCSI hdd > expecting to see better performance, but instead performance dropped to > ~44 secs for 10,000 INSERTs. Your development system is probably running inexpensive IDE disks that cache writes, while the test server is not caching. If you loop over single inserts, PostgreSQL's default configuration will do a physical commit to disk after every one of them, which limits performance to how fast the disk spins. If your server has 15K RPM drives, a single client can commit at most 250 transactions per second to disk, which means 10,000 inserts done one at a time must take at least 40 seconds no matter how fast the server is. There's a rambling discussion of this topic at http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm that should fill in some background here. If you use COPY instead of INSERT, that bypasses the WAL and you don't see this. Also, if you adjust your loop to do multiple inserts as a single transaction, that will change the behavior here as well. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD