Обсуждение: temporary file log lines
Hi all, 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. Regards, Michael Vitale
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
hmmm, I think spilling over to disk for temporary tables is handled by an entirely different branch in the PG source code. In fact, some other folks have chimed in and said log_temp_files doesn't relate to temp files at all use by temporary tables, just queries as you mentioned below elsewhere. This seems to be a dark area of PG that is not convered well. Regards, Michael Vitale Laurenz Albe wrote on 7/12/2021 8:01 AM: > 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
Hi,
Le lun. 12 juil. 2021 à 14:13, MichaelDBA <MichaelDBA@sqlexec.com> a écrit :
hmmm, I think spilling over to disk for temporary tables is handled by
an entirely different branch in the PG source code. In fact, some other
folks have chimed in and said log_temp_files doesn't relate to temp
files at all use by temporary tables, just queries as you mentioned
below elsewhere. This seems to be a dark area of PG that is not
convered well.
As far as I know, log_temp_files only relates to sort/hash going to disks, not to temporary objects (tables and indexes).
But, even if they are, they are definitely distinguishable by name. Sort/hash temp files are located in pgsql_tmp, and have a specific template name. Temp files for temporary objects are located in the database subdirectory, and also have a specific template name, different from the sort/hash temp files' one.
Regards.
On 7/13/21 9:29 AM, Guillaume Lelarge wrote: > Hi, > > Le lun. 12 juil. 2021 à 14:13, MichaelDBA <MichaelDBA@sqlexec.com > <mailto:MichaelDBA@sqlexec.com>> a écrit : > > hmmm, I think spilling over to disk for temporary tables is handled by > an entirely different branch in the PG source code. In fact, some > other > folks have chimed in and said log_temp_files doesn't relate to temp > files at all use by temporary tables, just queries as you mentioned > below elsewhere. This seems to be a dark area of PG that is not > convered well. > > > As far as I know, log_temp_files only relates to sort/hash going to > disks, not to temporary objects (tables and indexes). > Right. log_temp_files does not cover temporary tables. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company