From: | <A(dot)Bhattacharya(at)sungard(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | PostgreSQL Performance issue |
Date: | 2010-04-27 08:11:37 |
Message-ID: | 4AD2336877609F41A2B0D53BAD09FC570107D352@VOO-EXCHANGE07.internal.sungard.corp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dear All Experts,
I am using in PostgreSQL 8.3.5 database on windows 64 bit OS.
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]
and I have set my postgresql.conf parameters as below.
======================================================================
#-----------------------------------------------------------------------
-------
# RESOURCE USAGE (except WAL)
#-----------------------------------------------------------------------
-------
# - Memory -
shared_buffers = 1GB
temp_buffers = 256MB
max_prepared_transactions = 100
work_mem = 512MB
maintenance_work_mem = 512MB
# - Free Space Map -
max_fsm_pages = 1600000
max_fsm_relations = 10000
'
#-----------------------------------------------------------------------
-------
# WRITE AHEAD LOG
#-----------------------------------------------------------------------
-------
wal_buffers = 5MB # min 32kB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
#-----------------------------------------------------------------------
-------
# QUERY TUNING
#-----------------------------------------------------------------------
-------
# - Planner Method Configuration -
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
effective_cache_size = 8GB
========================================================================
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"
It would be very grateful, if anyone can help me on this.
Many thanks
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2010-04-27 08:34:01 | Re: PostgreSQL Performance issue |
Previous Message | Kenichiro Tanaka | 2010-04-27 07:12:30 | Re: gmake check problem |