Thread: PostgreSQL Performance issue

Are these all being sent in 1 transaction?  Can't you use COPY to bulk insert into the database?  If not, can you insert in batches (like 1,000 at a time) Have you got any triggers/constraints/complicated domains on the table you're inserting into?

Thom

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.Bhattacharya@sungard.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

On Tue, Apr 27, 2010 at 10:11, <A.Bhattacharya@sungard.com> wrote:
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

A.Bhattacharya@sungard.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]*
>
...

with one thread doing inserts, the other 7 cores will be idle. but
you're almost certainly disk IO bound.

OTOH, if you're calling a function (is that pl-pgsql, pl-java, pl-perl,
or what?) for each insert, you could be compute bound on that single
core. really depends on what that function is doing.

On 27 Apr 2010, at 10:11, <A.Bhattacharya@sungard.com> <A.Bhattacharya@sungard.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.

Wow, that's averaging almost 40s per insert! I don't think those inserts not being in a single transaction explains
thatmuch of a delay. Not that changing that doesn't help, but there's more going on here. 

Maybe you got some foreign key constraints involved that don't have indices on the foreign keys? In that case I expect
thatyou either have a lot of foreign references, only a few but from rather large tables (several million rows at
least)or something in between? 

Any other constraints that could be relevant?

> I have a simple function in db which is being called from Java batch program to populate the records into tables from
flatfiles. 

Did you verify that most of the time is spent waiting on the database?

I'm not entirely sure what you mean by the above. Do you have a batch program that starts the java interpreter for each
flatfile? 

Are they running synchronously (one after the other) or parallel? In the latter case you may be waiting on locks.

Is the data that your program needs to insert in one line in the flat file or does it need to collect data from
multiplelines throughout the file? 

How much memory does your java program use, could it be that it causes postgres to be swapped out?

Did you do any benchmarking on your "simple function in db" or on the queries it performs (assuming it does perform
any)?

> 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]

A system like that should be able to insert that small a number of records in no time.

> Also note that, when I am setting my shared_buffer = 2GB or high , Postgres is throwing an error “shared_buffer size
cannotbe more than size_t” 

That's odd... Is this a 32-bit Postgres build or is a 64-bit Windows incapable of assigning more than a 32-bit number
forthe amount of shared memory? Are you running in some kind of 32-bit compatibility mode maybe (PAE comes to mind)? 

That said, I haven't used Windows for anything more serious than gaming since last century - I'm not exactly an expert
onits behaviour. 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4bd6abc310411173714063!

On 27 Apr 2010, at 11:15, <A.Bhattacharya@sungard.com> <A.Bhattacharya@sungard.com> wrote:

> Thanks a lot for your help. However I am new to Postgres database therefore it would be nice if you can let me know
howto set autocommit off. 
> I know from psql client issuing “\set Autocommit Off” would set it off but unfortunately it doesn’t set it off.

It's a client-side setting, not a server-side one. Hence you need to turn it off in your java program, probably in the
databaseconnection settings. 

P.S. Your message was so mangled that I couldn't find which part of the original message you were referring to, so I
deletedthe remainder. Top-posting is considered bad form in mailing-lists. 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bd6e4f110411684215286!

On Tue, Apr 27, 2010 at 5:17 AM, Alban Hertroys
<dalroi@solfertje.student.utwente.nl> wrote:
> On 27 Apr 2010, at 10:11, <A.Bhattacharya@sungard.com> <A.Bhattacharya@sungard.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.
>
> Wow, that's averaging almost 40s per insert! I don't think those inserts not being in a single transaction explains
thatmuch of a delay. Not that changing that doesn't help, but there's more going on here. 

You got that backwards: it's 40 inserts/sec.  This is about par for
the course on windows style fsync on slow hardware iirc.  Solution is
to use transactions, or play with fsync.

merlin

> 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.

It's a client-side setting, not a server-side one. Hence you need to turn it
off in your java program, probably in the database connection settings.

P.S. Your message was so mangled that I couldn't find which part of the
original message you were referring to, so I deleted the remainder.
Top-posting is considered bad form in mailing-lists.

Alban Hertroys

--------------------------------------------------------------------------------------------------------

Actually, top posting is considered bad form in THIS mailing list. All the
others I participate in top post regularly. That said though, it is
considered good form to post in the manner expected by the list that one is
posting to. ;-)

Bayless

A.Bhattacharya@sungard.com wrote:
> 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.
>

Four likely possibilities here, in order of how easy they are to test
for and therefore resolve:

1) Your hardware doesn't handle commits very well.  You can turn off the
synchronous_commit command while doing the data loading to see if that
helps.  See http://www.postgresql.org/docs/8.3/static/wal-async-commit.html

2) You're doing a commit after every single transaction.  See
http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html
for an intro to disabling this.  Common practice is to commit every 100
to 1000 transactions instead.

3) The simple function in your database is executing very slowly.

4) The design of the database involves a lot of overhead, such as
difficult to process foreign keys or an excess of indexes on some tables.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

2010/4/27 Greg Smith <greg@2ndquadrant.com>:
> A.Bhattacharya@sungard.com wrote:
>>
>> I have *622,000 number of records *but it is taking almost *4 and half
>> hours* to load these data into the tables.

Without the schema and the queries, all you can get is guessing.

--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

1. Try using COPY Command, you will see significant decrease in the loading time.