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