pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTemporary Files
7+ messages / 5 participants
[nested] [flat]
* Temporary Files
@ 2021-03-29 18:10 Campbell, Lance <lance@illinois.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Campbell, Lance @ 2021-03-29 18:10 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
PostgreSQL 12
I just increased my work_mem to 24 MB. I cleared my statistics. I then went back later and saw the following:
Temporary files: 21
Size of temporary files 451 MB.
If you divide 451 by 21 you get 21.4 . I would have expected to see an average size greater than 24 MB. This implies to me that PostgreSQL is creating temp files that are smaller than 24 MB.
What am I not understanding?
Thanks,
LANCE CAMPBELL<https://directory.illinois.edu/person/lance;
Software Architect
Web Services<https://webtools.illinois.edu/;
Public Affairs<https://publicaffairs.illinois.edu/;
Contact the Webtools Team<https://go.illinois.edu/contactUs;
217.333.0382
lance@illinois.edu<mailto:lance@illinois.edu>
[/var/folders/wp/1f6l7hw95y718z976kgnl5f9kr5rtc/T/com.microsoft.Outlook/WebArchiveCopyPasteTempFiles/signature_logo.png]<http://illinois.edu/;
Under the Illinois Freedom of Information Act any written communication to or from university employees regarding university business is a public record and may be subject to public disclosure.
Attachments:
image001.png (image/png, 2.5K) download
view image
* Re: Temporary Files
@ 2021-03-29 20:49 Peter Geoghegan <pg@bowt.ie>
parent: Campbell, Lance <lance@illinois.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Peter Geoghegan @ 2021-03-29 20:49 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Mon, Mar 29, 2021 at 11:10 AM Campbell, Lance <lance@illinois.edu> wrote:
> If you divide 451 by 21 you get 21.4 . I would have expected to see an average size greater than 24 MB. This implies to me that PostgreSQL is creating temp files that are smaller than 24 MB.
>
> What am I not understanding?
work_mem limits the in-memory space used by sort and hash operations.
It doesn't necessarily follow that the on-disk footprint is exactly
(or even approximately) the same as the in-memory footprint for any
given operation that barely can't fit in memory. The precise space
overheads will vary based on lots of factors, most of which are
implementation details.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 7+ messages in thread
* Temporary Files
@ 2021-09-14 13:37 Campbell, Lance <lance@illinois.edu>
0 siblings, 2 replies; 7+ messages in thread
From: Campbell, Lance @ 2021-09-14 13:37 UTC (permalink / raw)
To: pgsql-admin
PostgreSQL 13.3
Is there a way to see, track or log the queries that result in temporary file creation?
Thanks,
Lance Campbell
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Temporary Files
@ 2021-09-14 13:42 Muhammet Kurtoğlu <muhammet.kurtoglu@bisoft.com.tr>
parent: Campbell, Lance <lance@illinois.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Muhammet Kurtoğlu @ 2021-09-14 13:42 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin
Hi,
pg_stat_statements
pgbadger
Campbell, Lance <lance@illinois.edu>, 14 Eyl 2021 Sal, 16:37 tarihinde şunu
yazdı:
> PostgreSQL 13.3
>
>
>
> Is there a way to see, track or log the queries that result in temporary
> file creation?
>
>
>
> Thanks,
>
>
>
> Lance Campbell
>
--
*Muhammet KURTOĞLU*
T: +90(312) 220 12 20 <%2B90%28374%29%20262%2098%2000>
F: +90(312) 286 00 10 <%2B90%28374%29%20262%2090%2091>
muhammet.kurtoglu@bisoft.com.tr <caner.olkan@bisoft.com.tr>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Temporary Files
@ 2021-09-14 13:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Campbell, Lance <lance@illinois.edu>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2021-09-14 13:58 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin
"Campbell, Lance" <lance@illinois.edu> writes:
> Is there a way to see, track or log the queries that result in temporary file creation?
The log_temp_files setting might be of use.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Temporary Files
@ 2021-09-14 14:20 Campbell, Lance <lance@illinois.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Campbell, Lance @ 2021-09-14 14:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thanks Tom for responding. I think this will help me out.
Lance
On 9/14/21, 8:58 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
"Campbell, Lance" <lance@illinois.edu> writes:
> Is there a way to see, track or log the queries that result in temporary file creation?
The log_temp_files setting might be of use.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Temporary Files
@ 2021-09-14 14:22 Jayson Hreczuck <jayson.hreczuck@studentuniverse.com>
parent: Campbell, Lance <lance@illinois.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Jayson Hreczuck @ 2021-09-14 14:22 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
I'll second pg_badger. Very useful tool for this.
On Tue, Sep 14, 2021 at 10:20 AM Campbell, Lance <lance@illinois.edu> wrote:
> Thanks Tom for responding. I think this will help me out.
>
> Lance
>
> On 9/14/21, 8:58 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>
> "Campbell, Lance" <lance@illinois.edu> writes:
> > Is there a way to see, track or log the queries that result in
> temporary file creation?
>
> The log_temp_files setting might be of use.
>
> regards, tom lane
>
>
--
*Jayson Hreczuck | Principal Database AdministratorC: +1 617.571.9861*130
Turner St, Ste 530, Waltham, MA 02453
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-09-14 14:22 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-29 18:10 Temporary Files Campbell, Lance <lance@illinois.edu>
2021-03-29 20:49 ` Peter Geoghegan <pg@bowt.ie>
2021-09-14 13:37 Temporary Files Campbell, Lance <lance@illinois.edu>
2021-09-14 13:42 ` Muhammet Kurtoğlu <muhammet.kurtoglu@bisoft.com.tr>
2021-09-14 13:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-09-14 14:20 ` Campbell, Lance <lance@illinois.edu>
2021-09-14 14:22 ` Jayson Hreczuck <jayson.hreczuck@studentuniverse.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