Skip site navigation (1) Skip section navigation (2)

Re: Postgres tuning?

From: Paul Thomas <paul(at)tmsl(dot)demon(dot)co(dot)uk>
To: Simon Windsor <simon(dot)windsor(at)cornfield(dot)org(dot)uk>
Cc: Postgres List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Postgres tuning?
Date: 2004-06-29 23:01:47
Message-ID: 20040630000147.A7323@bacon (view raw or flat)
Thread:
Lists: pgsql-general
On 29/06/2004 22:30 Simon Windsor wrote:
> Hi
> 
> I am in the process of converting a small multi-user application from
> MySQL, and most queries are performing better. The only noticeable
> exception is a batch load, which is half the speed of MySQL version.
> 
> What are the basic parameters I should be focusing on for best
> performance ?
> 
> - sort_mem
> - shared_buffers

How does your batch load work? Is it a series of inserts? If it is then 
you should try wrap a bunch of your inserts within a single transaction 
and then commit. If you issue an insert without explicitely using a 
transaction, PG will effectively wrap the insert withing a BEGIN...COMMIT 
block and you will actually incur the transaction overhead on each insert. 
You'll find that inserting rows in batches of 100 or more within a 
transaction really speeds thing up.

HTH


-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for 
Business             |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

In response to

pgsql-general by date

Next:From: wespvpDate: 2004-06-29 23:01:51
Subject: Re: Postgres tuning?
Previous:From: Scott MarloweDate: 2004-06-29 22:33:48
Subject: Re: Postgres tuning?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group