Message-ID: <4827fef49e0d0410eedca61e235a16efc1d835de.camel@cybertec.at>
Subject: Re: temporary file log lines
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: MichaelDBA <MichaelDBA@sqlexec.com>, PostgreSQL Performance
	 <pgsql-performance@postgresql.org>
Date: Mon, 12 Jul 2021 14:01:52 +0200
In-Reply-To: <d658f01b-7a1d-da5e-e996-3403e6b7cf49@sqlexec.com>
References: <d658f01b-7a1d-da5e-e996-3403e6b7cf49@sqlexec.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/4827fef49e0d0410eedca61e235a16efc1d835de.camel%40cybertec.at>
Precedence: bulk

On Thu, 2021-07-08 at 17:22 -0400, MichaelDBA wrote:
> I got a question about PG log lines with temporary file info like this:
> 
> case 1: log line with no contextual info
> 2021-07-07 20:28:15 UTC:10.100.11.95(50274):myapp@mydb:[35200]:LOG: 
> temporary file: path "base/pgsql_tmp/pgsql_tmp35200.0", size 389390336
> 
> case 2: log line with contextual info
> 2021-07-07 20:56:18 UTC:172.16.193.118(56080):myapp@mydb:[22418]:LOG: 
> temporary file: path "base/pgsql_tmp/pgsql_tmp22418.0", size 1048576000
> 2021-07-07 20:56:18 
> UTC:172.16.193.118(56080):myapp@mydb:[22418]:CONTEXT:  PL/pgSQL function 
> memory.f_memory_usage(boolean) line 13 at RETURN QUERY
> 
> There are at least 2 cases where stuff can spill over to disk:
> * queries that don't fit in work_mem, and
> * temporary tables that don't fit in temp_buffers
> 
> Question, if log_temp_files is turned on (=0), then how can you tell 
> from where the temporary log line comes from?
> I see a pattern where work_mem spill overs have a CONTEXT line that 
> immediately follows the LOG LINE with keyword, temporary. See case 2 above.
> 
> For other LOG lines with keyword, temporary, there is no such pattern. 
> Could those be the ones caused by temp_buffer spill overs to disk?  case 
> 1 above.
> 
> I really want to tune temp_buffers, but I would like to be able to 
> detect when temporary tables are spilling over to disk, so that I can 
> increase temp_buffers.
> 
> Any help would be appreciated.

I am not sure if you can istinguish those two cases from the log.

What I would do is identify the problematic query and run it with
EXPLAIN (ANALYZE, BUFFERS).  Then you should see which part of the query
creates the temporary files.

If it is a statement in a function called from your top level query,
auto_explain with the correct parameters can get you that output for
those statements too.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com