From: padusuma <venkata.adusumalli@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance of INSERT into temporary tables using psqlODBC driver
Date: Thu, 13 Sep 2018 05:57:39 -0700 (MST)
Message-ID: <1536843459130-0.post@n3.nabble.com> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: venkata.adusumalli@gmail.com
Subject: Re: Performance of INSERT into temporary tables using psqlODBC driver
In-Reply-To: <1536843459130-0.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox