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
