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@xxxxxxxxxxxx]
Sent: Tuesday, April 27, 2010 2:19 PM
To: Bhattacharya, A
Cc: pgsql-general
Subject: Re: PostgreSQL Performance issue
On Tue, Apr 27, 2010 at 10:11, <A.Bhattacharya@xxxxxxxxxxx>
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/
