From: Karthik Krishnakumar <karthikk@zohocorp.com>
To: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Prepared statements generating a lot of temp files.
Date: Thu, 23 Mar 2023 15:51:43 +0530
Message-ID: <1870df88210.d2d0b80f51669.8368902571691259324@zohocorp.com> (raw)
In-Reply-To: <5fc7d12e-44cc-2f1d-a6a3-bd8c8ab045d6@sqlexec.com>
References: <1870db7265d.f259eb2750325.860651782313663792@zohocorp.com>
<5267BF6F-73F8-4B94-94EA-F1A6A84DB5EB@sqlexec.com>
<1870de3f764.b57db35951182.3515927566054675220@zohocorp.com>
<5fc7d12e-44cc-2f1d-a6a3-bd8c8ab045d6@sqlexec.com>
RAM - 256GB
shared_buffers - 64GB
maintenance_work_mem - 1GB
work_mem - 24MB
there are ~50 active connections at any given time.
from pg_badger - average temp file size for bulk insert/update is about 200MB, max size is multiple GB's depending on the table.
it is a write heavy workload - with inserts/updates happening around the clock.
thanks
karthik
---- On Thu, 23 Mar 2023 15:33:11 +0530 MichaelDBA <mailto:MichaelDBA@sqlexec.com> wrote ---
Perhaps take the other approach: increase
work_mem to make the bulk inserts fit into memory. You can easily undo
work_mem changes. It only requires a sighup: reload, not restart.
What memory do you have now and what is work_mem currently set to?
Also, have many concurrent, active connections do you average at a time?
Regards,
Michael Vitale
Karthik Krishnakumar wrote on 3/23/2023 5:59 AM:
Regards,
Michael Vitale
mailto:michaelvitale@sqlexec.com
703-600-9343
thanks - checked with the devs and
it does look like the application is doing some sort of a bulk insert,
and at the moment it cannot be changed to use "COPY FROM".
will
limiting bulk inserts to match the work_mem(assuming this is the guc
that is used in this case) reduce this disk activity?
thanks,
karthik
---- On Thu, 23 Mar 2023 14:43:19 +0530 mailto:Michaeldba@sqlexec.com wrote ---
Look at
the size of the bind variables and also whether you are doing bulk
inserts
Sent from my iPad
On Mar 23, 2023, at 5:10 AM,
Karthik Krishnakumar <mailto:karthikk@zohocorp.com>
wrote:
Hi admins,
I
am analyzing temp files being generated using pg_badger.
Under
"queries generating the most temporary files", I am finding
insert/update queries (via prepared statements) writing a lot of data to
temp files.
I am trying to figure why inserts are
generating such temp files.
Why are inserts/updates
generating temp files?
what can be done to avoid this ?
thanks,
karthik
Attachments:
[image/jpeg] 1.jpg (20.6K, ../1870df88210.d2d0b80f51669.8368902571691259324@zohocorp.com/3-1.jpg)
download | view image
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-admin@postgresql.org
Cc: karthikk@zohocorp.com, MichaelDBA@sqlexec.com, pgsql-admin@lists.postgresql.org
Subject: Re: Prepared statements generating a lot of temp files.
In-Reply-To: <1870df88210.d2d0b80f51669.8368902571691259324@zohocorp.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