Re: 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/