Message-ID: <4E3B1E02.1060704@catalyst.net.nz>
Date: Fri, 05 Aug 2011 10:32:34 +1200
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Need to tune for Heavy Write
References: <4E3A268C.9090409@orkash.com>
	<CAEV0TzD7==4mmNXA2a_pM3o2aw1BmD2pNg+OFkM356webohPQw@mail.gmail.com>
In-Reply-To: 
 <CAEV0TzD7==4mmNXA2a_pM3o2aw1BmD2pNg+OFkM356webohPQw@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------090001010707030301060309"

This is a multi-part message in MIME format.
--------------090001010707030301060309
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 05/08/11 05:40, Samuel Gendler wrote:
>
>
> On Wed, Aug 3, 2011 at 9:56 PM, Adarsh Sharma 
> <adarsh.sharma@orkash.com <mailto:adarsh.sharma@orkash.com>> wrote:
>
>     Dear all,
>
>     From the last few days, I researched a lot on Postgresql
>     Performance Tuning due to slow speed of my server.
>     My application selects data from mysql database about 100000 rows
>     , process it & insert into postgres 2 tables by making about 45
>     connections.
>
>
> It's already been mentioned, but is worth reinforcing, that if you are 
> inserting 100,000 rows in 100,000 transactions, you'll see a huge 
> performance improvement by doing many more inserts per transaction. 
>  Try doing at least 500 inserts in each transaction (though you can 
> possibly go quite a bit higher than that without any issues, depending 
> upon what other traffic the database is handling in parallel).  You 
> almost certainly don't need 45 connections in order to insert only 
> 100,000 rows.  I've got a crappy VM with 2GB of RAM in which inserting 
> 100,000 relatively narrow rows requires less than 10 seconds if I do 
> it in a single transaction on a single connection.  Probably much less 
> than 10 seconds, but the code I just tested with does other work while 
> doing the inserts, so I don't have a pure test at hand.

Also worth mentioning is doing those 500 inserts in *fewer* than 500 
INSERT operations is likely to be a huge improvement, e.g:

INSERT INTO table VALUES (....),(....);

instead of

INSERT INTO table VALUES (....);
INSERT INTO table VALUES (....);

I'd be tempted to do all 500 row insertions in one INSERT statement as 
above. You might find that 1 connection doing this is fast enough (it is 
only doing 200 actual INSERT calls in that case to put in 100000 rows).

regards

Mark


--------------090001010707030301060309
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
    <title></title>
  </head>
  <body bgcolor="#ffffff" text="#000000">
    On 05/08/11 05:40, Samuel Gendler wrote:
    <blockquote
cite="mid:CAEV0TzD7==4mmNXA2a_pM3o2aw1BmD2pNg+OFkM356webohPQw@mail.gmail.com"
      type="cite">
      <meta http-equiv="Context-Type" content="text/html;
        charset=ISO-8859-1">
      <br>
      <br>
      <div>On Wed, Aug 3, 2011 at 9:56 PM, Adarsh Sharma <span>&lt;<a
            moz-do-not-send="true"
            href="mailto:adarsh.sharma@orkash.com">adarsh.sharma@orkash.com</a>&gt;</span>
        wrote:<br>
        <blockquote>
          Dear all,<br>
          <br>
          From the last few days, I researched a lot on Postgresql
          Performance Tuning due to slow speed of my server.<br>
          My application selects data from mysql database about 100000
          rows , process it &amp; insert into postgres 2 tables by
          making about 45 connections.</blockquote>
        <div><br>
        </div>
        <div>It's already been mentioned, but is worth reinforcing, that
          if you are inserting 100,000 rows in 100,000 transactions,
          you'll see a huge performance improvement by doing many more
          inserts per transaction. &nbsp;Try doing at least 500 inserts in
          each transaction (though you can possibly go quite a bit
          higher than that without any issues, depending upon what other
          traffic the database is handling in parallel). &nbsp;You almost
          certainly don't need 45 connections in order to insert only
          100,000 rows. &nbsp;I've got a crappy VM with 2GB of RAM in which
          inserting 100,000 relatively narrow rows requires less than 10
          seconds if I do it in a single transaction on a single
          connection. &nbsp;Probably much less than 10 seconds, but the code
          I just tested with does other work while doing the inserts, so
          I don't have a pure test at hand.</div>
      </div>
    </blockquote>
    <br>
    Also worth mentioning is doing those 500 inserts in *fewer* than 500
    INSERT operations is likely to be a huge improvement, e.g:<br>
    <br>
    INSERT INTO table VALUES (....),(....);<br>
    <br>
    instead of<br>
    <br>
    INSERT INTO table VALUES (....);<br>
    INSERT INTO table VALUES (....);<br>
    <br>
    I'd be tempted to do all 500 row insertions in one INSERT statement
    as above. You might find that 1 connection doing this is fast enough
    (it is only doing 200 actual INSERT calls in that case to put in
    100000 rows).<br>
    <br>
    regards<br>
    <br>
    Mark<br>
    <br>
    <br>
    <br>
    <br>
  </body>
</html>

--------------090001010707030301060309--