Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id EF8C52E004B for ; Sun, 2 Dec 2007 19:32:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 32818-01 for ; Sun, 2 Dec 2007 19:32:23 -0400 (AST) Received: from mu-out-0910.google.com (mu-out-0910.google.com [209.85.134.184]) by postgresql.org (Postfix) with ESMTP id 7CB7C2E003E for ; Sun, 2 Dec 2007 19:32:26 -0400 (AST) Received: by mu-out-0910.google.com with SMTP id i10so203798mue for ; Sun, 02 Dec 2007 15:32:24 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=8inwplVtaQSIVmQQ/qCUkFfcD0LdYQXRtk5ww+pGorU=; b=dqcrbCBTu1xMig+4mbyCHLdSOsS8EfWS8o8kwP3qCSXwuJ3Dv8KbQv4sQYtre5xJ3NafzN2hzr8aFkUPzzjksIM9Ir080lxpVgTThyjfUQVozL9gEzNMQVuPvxymsoit1LHLfiC7W0BriUf7Jm+XBWSyCy5jNrXIfLkTm3vVUsY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Oqn8O7z/Wo71CBHtcU9nxJT8Afx70e3A5Eb04n96JqxfIOuRLVzWrhEAob1p2VjLS0A5JmFhxqGppMWbs5bnGm9kN5h8iFx51SZBH9+WyI1Y/Ya3UkA2J0tyu6+hwgP+3Fq0LsdOP8/bBMi3SG3QTDFiz/ye+9NUoHUK+J2Q6kk= Received: by 10.82.173.19 with SMTP id v19mr9687096bue.1196638341551; Sun, 02 Dec 2007 15:32:21 -0800 (PST) Received: by 10.82.108.7 with HTTP; Sun, 2 Dec 2007 15:32:21 -0800 (PST) Message-ID: <329bff5c0712021532r2f65369dkeb8d09ca45f97002@mail.gmail.com> Date: Mon, 3 Dec 2007 01:32:21 +0200 From: "Beyers Cronje" To: "Greg Smith" Subject: Re: PostgreSQL 8.2.5 slow performance on INSERT on Linux Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6007_13606429.1196638341531" References: <329bff5c0712021334k474483bav278f79e7e27b26ac@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/19 X-Sequence-Number: 27835 ------=_Part_6007_13606429.1196638341531 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline > > 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.htmthat > should fill in some background here. This is exactly what is happening. Thank you for the above link, the article was very informative. 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. I will give COPY a go and see how it performs. For testing we specifically only did one insert per transaction, we will obviously optimize the actual application to do multiple insert per transaction where-ever possible. Kind regards Beyers Cronje PS Thank you for the quick responses Greg and Pavel. It is always encouraging starting off with a new product and seeing there are people passionate about it. ------=_Part_6007_13606429.1196638341531 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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.

This is exactly what is happening. Thank you for the above link, the article was very informative. 

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.

I will give COPY a go and see how it performs. For testing we specifically only did one insert per transaction, we will obviously optimize the actual application to do multiple insert per transaction where-ever possible.

Kind regards

Beyers Cronje

PS Thank you for the quick responses Greg and Pavel. It is always encouraging starting off with a new product and seeing there are people passionate about it.
------=_Part_6007_13606429.1196638341531--