Re: PostgreSQL Performance issue

Thanks a lot for your help. However I am new to Postgres database
therefore it would be nice if you can let me know how to set autocommit
off.

I know from psql client issuing "\set Autocommit Off" would set it off
but unfortunately it doesn't set it off.

Many thanks for your help.

From: Magnus Hagander [mailto:magnus(at)hagander(dot)net]
Sent: Tuesday, April 27, 2010 2:19 PM
To: Bhattacharya, A
Cc: pgsql-general
Subject: Re: [GENERAL] PostgreSQL Performance issue

On Tue, Apr 27, 2010 at 10:11, <A(dot)Bhattacharya(at)sungard(dot)com> wrote:

Dear All Experts,

I am using in PostgreSQL 8.3.5 database on windows 64 bit OS.

You really need to upgrade. At least to 8.3.10. It has many important
bugfixes.

However, I have a batch program written in Java which processes
the data and populates them into tables in Postgres database.

I have 622,000 number of records but it is taking almost 4 and
half hours to load these data into the tables.

I have a simple function in db which is being called from Java
batch program to populate the records into tables from flat files.

I have the below system configuration for my database server.

Database Server

PostgreSQL v8.3.5

Operating System

Windows 2003 Server 64 bit, Service Pack 2

CPU

2 * Quad Core AMD Opteron Processor 2376 @ 2.29 GHz

Memory

16 GB RAM

Disk Space

total 2.5 TB [C drive - 454 GB & D drive 1.99 TB]

The interesting point is not how much disk you have, but what
configuration you have it in. Eitehr way, 622k records in 4 1/2 hours is
obviously crappy even for a single disk though.

and I have set my postgresql.conf parameters as below.

shared_buffers = 1GB

You might want to try to lower that one drastically, say 128Mb. In some
cases, this has been known to give better performance on Windows. not in
all case though, so you have to try it out.

temp_buffers = 256MB

max_prepared_transactions = 100

Are you really using 2-phase commit on the system? If not, turn this
off. This is prepared transactions, not prepared statements.

========================================================================

Please advise me the best or optimum way setting these
parameters to achieve better performance.

Also note that, when I am setting my shared_buffer = 2GB or high
, Postgres is throwing an error "shared_buffer size cannot be more than
size_t"

That is normal since your binary is 32-bit. In fact, having
shared_buffers at 1Gb may give you some trouble with your fairly high
work_mem as well, as the *total* amount of memory in the process is
limited. That's another reason to try a lower shared_buffers.

(other than that, read the comments from Thom)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/