From: | MichaelDBA <MichaelDBA(at)sqlexec(dot)com> |
---|---|
To: | Karthik Krishnakumar <karthikk(at)zohocorp(dot)com> |
Cc: | pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Prepared statements generating a lot of temp files. |
Date: | 2023-03-23 10:26:36 |
Message-ID: | 149abb3b-09fb-fd3b-9ff0-bf9990b74b31@sqlexec.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Looks like you can handle 256MB work_mem. Give it a shot and monitor
temp_files. You do have log_temp_files = 0, right? Also, keep an eye
out for "out of memory" log file errors. What PG version are you using?
Karthik Krishnakumar wrote on 3/23/2023 6:21 AM:
> 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
> <MichaelDBA(at)sqlexec(dot)com <mailto:MichaelDBA(at)sqlexec(dot)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
>
> Michaeldba(at)sqlexec(dot)com <mailto:michaelvitale(at)sqlexec(dot)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
> *<Michaeldba(at)sqlexec(dot)com> <mailto:Michaeldba(at)sqlexec(dot)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
> <karthikk(at)zohocorp(dot)com <mailto:karthikk(at)zohocorp(dot)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
>
>
>
>
>
>
Regards,
Michael Vitale
Michaeldba(at)sqlexec(dot)com <mailto:michaelvitale(at)sqlexec(dot)com>
703-600-9343
From | Date | Subject | |
---|---|---|---|
Next Message | Karthik Krishnakumar | 2023-03-23 10:50:54 | Re: Prepared statements generating a lot of temp files. |
Previous Message | Karthik Krishnakumar | 2023-03-23 10:21:43 | Re: Prepared statements generating a lot of temp files. |