Re: Postgres tuning?

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   |
+------------------------------+---------------------------------------------+