Re: PostgreSQL Performance issue

Most likely you are inserting one per transaction. Set autocommit to
false and commit only after all the inserts are done.

-n.

On 27-04-2010 13:41, 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.
>
> 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
>