Re: Need to tune for Heavy Write

On 05/08/11 05:40, Samuel Gendler wrote:
>
>
> On Wed, Aug 3, 2011 at 9:56 PM, Adarsh Sharma
> <adarsh(dot)sharma(at)orkash(dot)com <mailto:adarsh(dot)sharma(at)orkash(dot)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