PostgreSQL Performance issue

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

Responses

Browse pgsql-general by date

  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