Thread: Prepared statements generating a lot of temp files.

Look at the size of the bind variables and also whether you are doing bulk inserts



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

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:

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

Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343 

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@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:

div.zm_8145969894290705871_parse_5802283459977859639 a:link, div.zm_8145969894290705871_parse_5802283459977859639 span.x_735268042MsoHyperlink { color: blue; text-decoration: underline; } div.zm_8145969894290705871_parse_5802283459977859639 a:visited, div.zm_8145969894290705871_parse_5802283459977859639 span.x_735268042MsoHyperlinkFollowed { color: purple; text-decoration: underline; }

Regards,

Michael Vitale

Michaeldba@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 <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 <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

Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343 

What percentage of the active, concurrent connections are doing the complex queries vs the inserts?  If it's only  a few (2-3) then I still think you are OK with 256MB work_mem.  You could at least increase it to 128MB to see if that has any significant impact.

Regards,
Michael Vitale



Karthik Krishnakumar wrote on 3/23/2023 6:50 AM:

thanks will check this.

is there some formula you are using to arrive at this number for work_mem?

we have queries that frequently have more than a dozen joins/aggregates. so have been conservative in changing work_mem...

btw, using pg version 10, (i know its old and am trying to convince folks to upgrade :)

thanks

karthik

---- On Thu, 23 Mar 2023 15:56:36 +0530 MichaelDBA <MichaelDBA@sqlexec.com> wrote ---

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:

div.zm_6714882901664688387_parse_6264849942657406931 a:link, div.zm_6714882901664688387_parse_6264849942657406931 span.x_381665248MsoHyperlink { color: blue; text-decoration: underline; } div.zm_6714882901664688387_parse_6264849942657406931 a:visited, div.zm_6714882901664688387_parse_6264849942657406931 span.x_381665248MsoHyperlinkFollowed { color: purple; text-decoration: underline; }

Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343
 

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@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:

div.zm_8145969894290705871_parse_5802283459977859639 a:link, div.zm_8145969894290705871_parse_5802283459977859639 span.x_735268042MsoHyperlink { color: blue; text-decoration: underline; } div.zm_8145969894290705871_parse_5802283459977859639 a:visited, div.zm_8145969894290705871_parse_5802283459977859639 span.x_735268042MsoHyperlinkFollowed { color: purple; text-decoration: underline; }

Regards,

Michael Vitale

Michaeldba@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 <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 <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

Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343