pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedselect operations that generate disk writes
3+ messages / 3 participants
[nested] [flat]
* select operations that generate disk writes
@ 2012-07-06 06:10 CSS <css@morefoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: CSS @ 2012-07-06 06:10 UTC (permalink / raw)
To: pgsql-performance
Hello,
Time for a broad question. I'm aware of some specific select queries that will generate disk writes - for example, a sort operation when there's not enough work_mem can cause PG to write out some temp tables (not the correct terminology?). That scenario is easily remedied by enabling "log_temp_files" and specifying the threshold in temp file size at which you want logging to happen.
I've recently been trying to put some of my recent reading of Greg's book and other performance-related documentation to use by seeking out queries that take an inordinate amount of time to run. Given that we're usually disk-bound, I've gotten in the habit of running an iostat in a terminal while running and tweaking some of the problem queries. I find this gives me some nice instant feedback on how hard the query is causing PG to hit the disks. What's currently puzzling me are some selects with complex joins and sorts that generate some fairly large bursts of write activity while they run. I was able to reduce this by increasing work_mem (client-side) to give the sorts an opportunity to happen in memory. I now see no temp file writes being logged, and indeed the query sped up.
So my question is, what else can generate writes when doing read-only operations? I know it sounds like a simple question, but I'm just not finding a concise answer anywhere.
Thanks,
Charles
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: select operations that generate disk writes
@ 2012-07-06 06:20 Pavel Stehule <pavel.stehule@gmail.com>
parent: CSS <css@morefoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Pavel Stehule @ 2012-07-06 06:20 UTC (permalink / raw)
To: CSS <css@morefoo.com>; +Cc: pgsql-performance
Hello
2012/7/6 CSS <css@morefoo.com>:
> Hello,
>
> Time for a broad question. I'm aware of some specific select queries that will generate disk writes - for example, a sort operation when there's not enough work_mem can cause PG to write out some temp tables (not the correct terminology?). That scenario is easily remedied by enabling "log_temp_files" and specifying the threshold in temp file size at which you want logging to happen.
>
> I've recently been trying to put some of my recent reading of Greg's book and other performance-related documentation to use by seeking out queries that take an inordinate amount of time to run. Given that we're usually disk-bound, I've gotten in the habit of running an iostat in a terminal while running and tweaking some of the problem queries. I find this gives me some nice instant feedback on how hard the query is causing PG to hit the disks. What's currently puzzling me are some selects with complex joins and sorts that generate some fairly large bursts of write activity while they run. I was able to reduce this by increasing work_mem (client-side) to give the sorts an opportunity to happen in memory. I now see no temp file writes being logged, and indeed the query sped up.
>
> So my question is, what else can generate writes when doing read-only operations? I know it sounds like a simple question, but I'm just not finding a concise answer anywhere.
statistics http://www.postgresql.org/docs/9.1/interactive/runtime-config-statistics.html
Regards
Pavel
>
> Thanks,
>
> Charles
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: select operations that generate disk writes
@ 2012-07-06 06:52 Craig Ringer <ringerc@ringerc.id.au>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Craig Ringer @ 2012-07-06 06:52 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: CSS <css@morefoo.com>; pgsql-performance
On 07/06/2012 02:20 PM, Pavel Stehule wrote:
> Hello
>
> 2012/7/6 CSS <css@morefoo.com>:
>> So my question is, what else can generate writes when doing read-only operations? I know it sounds like a simple question, but I'm just not finding a concise answer anywhere.
> statistics http://www.postgresql.org/docs/9.1/interactive/runtime-config-statistics.html
>
Hint bits, too:
http://wiki.postgresql.org/wiki/Hint_Bits
--
Craig Ringer
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-07-06 06:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-06 06:10 select operations that generate disk writes CSS <css@morefoo.com>
2012-07-06 06:20 ` Pavel Stehule <pavel.stehule@gmail.com>
2012-07-06 06:52 ` Craig Ringer <ringerc@ringerc.id.au>
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