From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: pgsql-performance@postgresql.org
Subject: Re: Need to tune for Heavy Write
Date: Fri, 05 Aug 2011 10:32:34 +1200
Message-ID: <4E3B1E02.1060704@catalyst.net.nz> (raw)
In-Reply-To: <CAEV0TzD7==4mmNXA2a_pM3o2aw1BmD2pNg+OFkM356webohPQw@mail.gmail.com>
References: <4E3A268C.9090409@orkash.com>
<CAEV0TzD7==4mmNXA2a_pM3o2aw1BmD2pNg+OFkM356webohPQw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz
Subject: Re: Need to tune for Heavy Write
In-Reply-To: <4E3B1E02.1060704@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox