Date: Thu, 13 Sep 2018 05:57:39 -0700 (MST)
From: padusuma <venkata.adusumalli@gmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1536843459130-0.post@n3.nabble.com>
In-Reply-To: <1536554876104-0.post@n3.nabble.com>
References: <1536339842382-0.post@n3.nabble.com> <8736uk6eh5.fsf@gmail.com>
 <1536385261054-0.post@n3.nabble.com> <87zhwq48ap.fsf@gmail.com>
 <1536554876104-0.post@n3.nabble.com>
Subject: Re: Performance of INSERT into temporary tables using psqlODBC
 driver
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hello Tim,

I have tried the suggestions provided to the best of my knowledge, but I did
not see any improvement in the INSERT performance for temporary tables. The
Linux host on which PostgreSQL database is installed has 32 GB RAM.
Following are current settings I have in postgresql.conf file:
shared_buffers = 8GB
temp_buffers = 256MB
work_mem = 256MB
maintenance_work_mem = 256MB
wal_buffers = 256MB

checkpoint_timeout = 30min
checkpoint_completion_target = 0.75
max_wal_size = 1GB

effective_cache_size = 16GB

>>- Increase work_mem to reduce use of temp files. Need it to be 2 to 3 
>>  times largest temp file (but use common sense) 

>I have already increased the work_mem and maintenance_work_mem to 256MB. I 
>will check on the temp file sizes and adjust the work_mem parameter as you 
>suggested. 

>- Tweak wal checkpoint parameters to prevent wal checkpoints occurring 
>  too frequently. Note that there is a play off here between frequency 
>  of checkpoints and boot time after a crash. Fewer wal checkpoints will 
>  usually improve performance, but recovery time is longer. 

>How effectively you can increase insert times will depend on what the 
>memory and cpu profile of the system is. More memory, less use of temp 
>files, faster system, so spend a bit of time to make sure your system is 
>configured to squeeze as much out of that RAM as you can! 

Please let me know if there are any other suggestions that I can try.


--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html