pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTemp files for simple queries
7+ messages / 4 participants
[nested] [flat]
* Temp files for simple queries
@ 2021-06-22 11:39 Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:51 ` Re: Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:52 ` Re: Temp files for simple queries Holger Jakobs <holger@jakobs.com>
0 siblings, 2 replies; 7+ messages in thread
From: Radoulov, Dimitre @ 2021-06-22 11:39 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello all,
We have a small Google Cloud SQL PostgreSQL 11 instance.
The instance is configured with 8G of memory and 4 vCPUs, work_mem is 4MB.
I was wondering why the instance uses disk temp files for such a small
amount of data (see size in the log below)?
I suppose that it's not only the work_mem limit that could trigger disk
temp file creation or the reported SQL statement is not the only one
that contributes to it:
I 2021-06-22T09:12:59.164913Z 2021-06-22 09:12:59.163 UTC [1957798]:
[2-1] db=<db>,user=<user> STATEMENT: SELECT c1, c2 FROM schema1.t1 WHERE
c1 >= $1 ORDER BY c1
I 2021-06-22T09:12:59.164379Z 2021-06-22 09:12:59.163 UTC [1957798]:
[1-1] db=<db>,user=<user> LOG: temporary file: path
"base/pgsql_tmp/pgsql_tmp1957798.0", size 7380992
The t1 table (table and column names have been masked for privacy)
occupies only 440 kB and has 160 records.
Best regards
Dimitre
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Temp files for simple queries
2021-06-22 11:39 Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
@ 2021-06-22 11:51 ` Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:55 ` Re: Temp files for simple queries Julien Rouhaud <rjuju123@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Radoulov, Dimitre @ 2021-06-22 11:51 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Just to clarify: the size of the temp file is 7MB and with work_mem of
4MB it clearly doesn't fit.
But why an "order by" of a 440kB of data would require 7MB.
One possible reason is that that the number of records in that table
varies significantly during the day, but - if that's the case - I would
expect that the table would be much bigger than 440kB (size taken using
\dt+).
Regards
Dimitre
On 22/06/2021 13.39, Radoulov, Dimitre wrote:
>
> Hello all,
>
> We have a small Google Cloud SQL PostgreSQL 11 instance.
> The instance is configured with 8G of memory and 4 vCPUs, work_mem is
> 4MB.
>
> I was wondering why the instance uses disk temp files for such a small
> amount of data (see size in the log below)?
>
> I suppose that it's not only the work_mem limit that could trigger
> disk temp file creation or the reported SQL statement is not the only
> one that contributes to it:
>
> I 2021-06-22T09:12:59.164913Z 2021-06-22 09:12:59.163 UTC [1957798]:
> [2-1] db=<db>,user=<user> STATEMENT: SELECT c1, c2 FROM schema1.t1
> WHERE c1 >= $1 ORDER BY c1
> I 2021-06-22T09:12:59.164379Z 2021-06-22 09:12:59.163 UTC [1957798]:
> [1-1] db=<db>,user=<user> LOG: temporary file: path
> "base/pgsql_tmp/pgsql_tmp1957798.0", size 7380992
>
> The t1 table (table and column names have been masked for privacy)
> occupies only 440 kB and has 160 records.
>
>
> Best regards
> Dimitre
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Temp files for simple queries
2021-06-22 11:39 Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:51 ` Re: Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
@ 2021-06-22 11:55 ` Julien Rouhaud <rjuju123@gmail.com>
2021-06-22 12:00 ` Re: Temp files for simple queries MichaelDBA <MichaelDBA@sqlexec.com>
2021-06-22 12:05 ` Re: Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Julien Rouhaud @ 2021-06-22 11:55 UTC (permalink / raw)
To: Radoulov, Dimitre <cichomitiko@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Tue, Jun 22, 2021 at 01:51:55PM +0200, Radoulov, Dimitre wrote:
> Just to clarify: the size of the temp file is 7MB and with work_mem of 4MB
> it clearly doesn't fit.
>
> But why an "order by" of a 440kB of data would require 7MB.
>
> One possible reason is that that the number of records in that table varies
> significantly during the day,
That would be the logical explanation. You could configure auto_explain to
make sure of that (https://www.postgresql.org/docs/current/auto-explain.html).
> but - if that's the case - I would expect that
> the table would be much bigger than 440kB (size taken using \dt+).
Not necessarily. autovacuum can truncate the file if all the trailing blocks
are empty (and if it can acquire an exclusive lock fast enough), so if some
client inserts a lot of rows, process them and remove them all, and later on
slowly start to insert new rows you will get that behavior.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Temp files for simple queries
2021-06-22 11:39 Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:51 ` Re: Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:55 ` Re: Temp files for simple queries Julien Rouhaud <rjuju123@gmail.com>
@ 2021-06-22 12:00 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-06-22 12:16 ` Re: Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: MichaelDBA @ 2021-06-22 12:00 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: Radoulov, Dimitre <cichomitiko@gmail.com>; pgsql-admin@lists.postgresql.org
I'm wondering if bloat could be causing a problem. Perhaps more blocks
are written to disk including a lot of blocks with little or no data due
to bloat.
Julien Rouhaud wrote on 6/22/2021 7:55 AM:
> On Tue, Jun 22, 2021 at 01:51:55PM +0200, Radoulov, Dimitre wrote:
>> Just to clarify: the size of the temp file is 7MB and with work_mem of 4MB
>> it clearly doesn't fit.
>>
>> But why an "order by" of a 440kB of data would require 7MB.
>>
>> One possible reason is that that the number of records in that table varies
>> significantly during the day,
> That would be the logical explanation. You could configure auto_explain to
> make sure of that (https://www.postgresql.org/docs/current/auto-explain.html).
>
>> but - if that's the case - I would expect that
>> the table would be much bigger than 440kB (size taken using \dt+).
> Not necessarily. autovacuum can truncate the file if all the trailing blocks
> are empty (and if it can acquire an exclusive lock fast enough), so if some
> client inserts a lot of rows, process them and remove them all, and later on
> slowly start to insert new rows you will get that behavior.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Temp files for simple queries
2021-06-22 11:39 Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:51 ` Re: Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:55 ` Re: Temp files for simple queries Julien Rouhaud <rjuju123@gmail.com>
2021-06-22 12:00 ` Re: Temp files for simple queries MichaelDBA <MichaelDBA@sqlexec.com>
@ 2021-06-22 12:16 ` Radoulov, Dimitre <cichomitiko@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Radoulov, Dimitre @ 2021-06-22 12:16 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: pgsql-admin@lists.postgresql.org, Julien Rouhaud <rjuju123@gmail.com>
Hello Michael,
On 22/06/2021 14.00, MichaelDBA wrote:
> I'm wondering if bloat could be causing a problem. Perhaps more
> blocks are written to disk including a lot of blocks with little or no
> data due to bloat.
>
Using the query suggested here:
www.citusdata.com/blog/2017/10/20/monitoring-your-bloat-in-postgres/, I
get the following:
databasename | db1
schemaname | schema1
tablename | t1
can_estimate | t
est_rows | 160
pct_bloat | 18
mb_bloat | 0.06
table_mb | 0.352
So the bloat doesn't seem significant.
Best regards
Dimitre
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Temp files for simple queries
2021-06-22 11:39 Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:51 ` Re: Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:55 ` Re: Temp files for simple queries Julien Rouhaud <rjuju123@gmail.com>
@ 2021-06-22 12:05 ` Radoulov, Dimitre <cichomitiko@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Radoulov, Dimitre @ 2021-06-22 12:05 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hello Julien,
On 22/06/2021 13.55, Julien Rouhaud wrote:
> On Tue, Jun 22, 2021 at 01:51:55PM +0200, Radoulov, Dimitre wrote:
>> Just to clarify: the size of the temp file is 7MB and with work_mem of 4MB
>> it clearly doesn't fit.
>>
>> But why an "order by" of a 440kB of data would require 7MB.
>>
>> One possible reason is that that the number of records in that table varies
>> significantly during the day,
> That would be the logical explanation. You could configure auto_explain to
> make sure of that (https://www.postgresql.org/docs/current/auto-explain.html).
>
>> but - if that's the case - I would expect that
>> the table would be much bigger than 440kB (size taken using \dt+).
> Not necessarily. autovacuum can truncate the file if all the trailing blocks
> are empty (and if it can acquire an exclusive lock fast enough), so if some
> client inserts a lot of rows, process them and remove them all, and later on
> slowly start to insert new rows you will get that behavior.
I thought that only a vacuum full could do that, thanks for pointing it out.
Best regards
Dimitre
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Temp files for simple queries
2021-06-22 11:39 Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
@ 2021-06-22 11:52 ` Holger Jakobs <holger@jakobs.com>
1 sibling, 0 replies; 7+ messages in thread
From: Holger Jakobs @ 2021-06-22 11:52 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Default settings might be too low. Try to set work_mem higher. If
operations like sorting don't fit into work_mem, temporary files are used.
Am 22.06.21 um 13:39 schrieb Radoulov, Dimitre:
>
> Hello all,
>
> We have a small Google Cloud SQL PostgreSQL 11 instance.
> The instance is configured with 8G of memory and 4 vCPUs, work_mem is
> 4MB.
>
> I was wondering why the instance uses disk temp files for such a small
> amount of data (see size in the log below)?
>
> I suppose that it's not only the work_mem limit that could trigger
> disk temp file creation or the reported SQL statement is not the only
> one that contributes to it:
>
> I 2021-06-22T09:12:59.164913Z 2021-06-22 09:12:59.163 UTC [1957798]:
> [2-1] db=<db>,user=<user> STATEMENT: SELECT c1, c2 FROM schema1.t1
> WHERE c1 >= $1 ORDER BY c1
> I 2021-06-22T09:12:59.164379Z 2021-06-22 09:12:59.163 UTC [1957798]:
> [1-1] db=<db>,user=<user> LOG: temporary file: path
> "base/pgsql_tmp/pgsql_tmp1957798.0", size 7380992
>
> The t1 table (table and column names have been masked for privacy)
> occupies only 440 kB and has 160 records.
>
>
> Best regards
> Dimitre
>
>
>
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-06-22 12:16 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-22 11:39 Temp files for simple queries Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:51 ` Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:55 ` Julien Rouhaud <rjuju123@gmail.com>
2021-06-22 12:00 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-06-22 12:16 ` Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 12:05 ` Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-22 11:52 ` Holger Jakobs <holger@jakobs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox