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.
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
