Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
Jaime Casanova jcasa...@systemguards.com.ec writes: why we don't show some of that info in explain? Lack of round tuits; plus concern about breaking programs that read EXPLAIN output, which I guess will be alleviated in 8.5. the reason i say most of the temp files is that when i removed #ifdef HJDEBUG it says that in total i was using 10 batchs but there were 14 temp files created (i guess we use 1 file per batch, no?) Two files per batch, in general --- I suppose some of the buckets were empty. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Wed, Jan 13, 2010 at 1:31 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Jan 11, 2010 at 3:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: Hmm. Not clear where the temp files are coming from, but it's *not* the sort --- the internal sort ended line shows that that sort never went to disk. What kind of plan is feeding the sort node? some time ago, you said: It might be useful to turn on trace_sort to see if the small files are coming from sorts. If they're from hashes I'm afraid there's no handy instrumentation ... and is clearly what was bother me... because most of all temp files are coming from hash... why we don't show some of that info in explain? for example: we can show memory used, no? or if the hash goes to disk... if i remove #ifdef HJDEBUG seems like we even know how many batchs the hash used... I had an idea at one point of making explain show the planned and actual # of batches for each hash join. I believe that actual # of batches 1 is isomorphic to hash join went to disk. The code is actually pretty easy; the hard part is figuring out what to do about the UI. The choices seem to be: 1. Create a new EXPLAIN option just for this - what would we call it? 2. Think of some more, similar things and come up with a new EXPLAIN option covering all of them - what else would go along with? 3. Sandwhich it into an existing EXPLAIN option, most likely VERBOSE. 4. Display it by default. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
Robert Haas robertmh...@gmail.com writes: I had an idea at one point of making explain show the planned and actual # of batches for each hash join. I believe that actual # of batches 1 is isomorphic to hash join went to disk. The code is actually pretty easy; the hard part is figuring out what to do about the UI. The choices seem to be: 1. Create a new EXPLAIN option just for this - what would we call it? 2. Think of some more, similar things and come up with a new EXPLAIN option covering all of them - what else would go along with? 3. Sandwhich it into an existing EXPLAIN option, most likely VERBOSE. 4. Display it by default. Treat it the same as the Sort-node actual usage information. We did not add a special option when we added that. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Wed, Jan 13, 2010 at 10:42 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I had an idea at one point of making explain show the planned and actual # of batches for each hash join. I believe that actual # of batches 1 is isomorphic to hash join went to disk. The code is actually pretty easy; the hard part is figuring out what to do about the UI. The choices seem to be: 1. Create a new EXPLAIN option just for this - what would we call it? 2. Think of some more, similar things and come up with a new EXPLAIN option covering all of them - what else would go along with? 3. Sandwhich it into an existing EXPLAIN option, most likely VERBOSE. 4. Display it by default. Treat it the same as the Sort-node actual usage information. We did not add a special option when we added that. Well, what about when we're just doing EXPLAIN, not EXPLAIN ANALYZE? It'll add another line to the output for the expected number of batches. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Wed, Jan 13, 2010 at 11:11 AM, Robert Haas robertmh...@gmail.com wrote: Well, what about when we're just doing EXPLAIN, not EXPLAIN ANALYZE? It'll add another line to the output for the expected number of batches. and when we are in EXPLAIN ANALYZE the real number as well? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Wed, Jan 13, 2010 at 11:14 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Wed, Jan 13, 2010 at 11:11 AM, Robert Haas robertmh...@gmail.com wrote: Well, what about when we're just doing EXPLAIN, not EXPLAIN ANALYZE? It'll add another line to the output for the expected number of batches. and when we are in EXPLAIN ANALYZE the real number as well? Yeah. My question is whether it's acceptable to add an extra line to the EXPLAIN output for every hash join, even w/o ANALYZE. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
Robert Haas robertmh...@gmail.com writes: Yeah. My question is whether it's acceptable to add an extra line to the EXPLAIN output for every hash join, even w/o ANALYZE. We could add it if either VERBOSE or ANALYZE appears. Not sure if that's just too much concern for backwards compatibility, though. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Wed, Jan 13, 2010 at 11:53 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Yeah. My question is whether it's acceptable to add an extra line to the EXPLAIN output for every hash join, even w/o ANALYZE. We could add it if either VERBOSE or ANALYZE appears. Not sure if that's just too much concern for backwards compatibility, though. I think having it controlled by either of two options is to weird. I'm not worried so much about backward compatibility as I am about cluttering the output. Maybe making it controlled by VERBOSE is the right thing to do, although I'm sort of tempted to figure out if there is more useful instrumentation that could be done and put it all under a new option called, say, HASH_DETAILS. Not sure what else we could show though. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Mon, Jan 11, 2010 at 3:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: Hmm. Not clear where the temp files are coming from, but it's *not* the sort --- the internal sort ended line shows that that sort never went to disk. What kind of plan is feeding the sort node? some time ago, you said: It might be useful to turn on trace_sort to see if the small files are coming from sorts. If they're from hashes I'm afraid there's no handy instrumentation ... and is clearly what was bother me... because most of all temp files are coming from hash... why we don't show some of that info in explain? for example: we can show memory used, no? or if the hash goes to disk... if i remove #ifdef HJDEBUG seems like we even know how many batchs the hash used... the reason i say most of the temp files is that when i removed #ifdef HJDEBUG it says that in total i was using 10 batchs but there were 14 temp files created (i guess we use 1 file per batch, no?) nbatch = 1, nbuckets = 1024 nbatch = 1, nbuckets = 1024 nbatch = 8, nbuckets = 2048 -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: It might be useful to turn on trace_sort to see if the small files are coming from sorts. If they're from hashes I'm afraid there's no handy instrumentation ... yes they are, this is the log (i deleted the STATEMENT lines because they were redundant), seems like all the temp files are used to execute the same sentence... BTW, this is my laptop no the server. -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: It might be useful to turn on trace_sort to see if the small files are coming from sorts. If they're from hashes I'm afraid there's no handy instrumentation ... yes they are, this is the log (i deleted the STATEMENT lines because they were redundant), seems like all the temp files are used to execute the same sentence... BTW, this is my laptop no the server. I think maybe there was supposed to be an attachment here? ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Mon, Jan 11, 2010 at 2:07 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: It might be useful to turn on trace_sort to see if the small files are coming from sorts. If they're from hashes I'm afraid there's no handy instrumentation ... yes they are, this is the log (i deleted the STATEMENT lines because they were redundant), seems like all the temp files are used to execute the same sentence... BTW, this is my laptop no the server. I think maybe there was supposed to be an attachment here? i knew i was forgotting something ;) ah! and this is in 8.5dev but it's the same in 8.3 -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Mon, Jan 11, 2010 at 2:14 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Jan 11, 2010 at 2:07 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: It might be useful to turn on trace_sort to see if the small files are coming from sorts. If they're from hashes I'm afraid there's no handy instrumentation ... yes they are, this is the log (i deleted the STATEMENT lines because they were redundant), seems like all the temp files are used to execute the same sentence... BTW, this is my laptop no the server. I think maybe there was supposed to be an attachment here? i knew i was forgotting something ;) ah! and this is in 8.5dev but it's the same in 8.3 oh! boy this can't be happen! attaching again -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 log Description: Binary data -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
Jaime Casanova jcasa...@systemguards.com.ec writes: LOG: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f LOG: switching to bounded heapsort at 641 tuples: CPU 0.08s/0.13u sec elapsed 0.25 sec LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.5, size 471010 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.10, size 81096 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.0, size 467373 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.9, size 110200 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.3, size 470011 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.8, size 157192 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.4, size 468681 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.12, size 101624 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.1, size 472285 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.11, size 100744 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.6, size 467173 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.7, size 141888 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.2, size 476227 LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.13, size 89072 LOG: performsort starting: CPU 0.10s/0.19u sec elapsed 0.33 sec LOG: performsort done: CPU 0.10s/0.19u sec elapsed 0.33 sec LOG: internal sort ended, 118 KB used: CPU 0.10s/0.19u sec elapsed 0.33 sec Hmm. Not clear where the temp files are coming from, but it's *not* the sort --- the internal sort ended line shows that that sort never went to disk. What kind of plan is feeding the sort node? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Mon, Jan 11, 2010 at 3:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jaime Casanova jcasa...@systemguards.com.ec writes: LOG: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f LOG: switching to bounded heapsort at 641 tuples: CPU 0.08s/0.13u sec elapsed 0.25 sec LOG: temporary file: path base/pgsql_tmp/pgsql_tmp8507.5, size 471010 [... some more temp files logged ...] LOG: internal sort ended, 118 KB used: CPU 0.10s/0.19u sec elapsed 0.33 sec Hmm. Not clear where the temp files are coming from, but it's *not* the sort --- the internal sort ended line shows that that sort never went to disk. What kind of plan is feeding the sort node? i'm sure i have seen on disk sorts even when the files are small, but still i see a problem here... the temp files shoul be coming from hash operations but AFAICS the files are small and every hash operation should be using until work_mem memory, right? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 QUERY PLAN - Limit (cost=9090.60..9090.65 rows=20 width=166) (actual time=441.619..441.674 rows=20 loops=1) - Sort (cost=9089.85..9106.26 rows=6567 width=166) (actual time=441.062..441.362 rows=320 loops=1) Sort Key: tcf.fech_hora Sort Method: top-N heapsort Memory: 118kB - Hash Join (cost=6660.18..8783.76 rows=6567 width=166) (actual time=310.561..427.393 rows=6511 loops=1) Hash Cond: (tcsc.id_soli_compra = tcf.id_soli_compra) - Hash Join (cost=5600.47..7551.97 rows=6548 width=158) (actual time=281.279..378.434 rows=6544 loops=1) Hash Cond: (tcsc.esta_solicitud = tgdc.seq_det_cat) - Hash Join (cost=5577.86..7439.33 rows=6548 width=147) (actual time=279.590..361.704 rows=6545 loops=1) Hash Cond: ((tcsc.persona_id)::numeric = tgp.persona_id) - Seq Scan on tcom_solicitud_compra tcsc (cost=0.00..799.77 rows=6548 width=125) (actual time=0.013..18.433 ro ws=6545 loops=1) Filter: ((esta_solicitud 425) AND (esta_solicitud 424) AND (esta_solicitud 416)) - Hash (cost=3878.94..3878.94 rows=83594 width=34) (actual time=278.837..278.837 rows=83594 loops=1) - Seq Scan on tgen_persona tgp (cost=0.00..3878.94 rows=83594 width=34) (actual time=0.006..136.582 rows =83594 loops=1) - Hash (cost=14.49..14.49 rows=649 width=15) (actual time=1.657..1.657 rows=649 loops=1) - Seq Scan on tgen_det_cat tgdc (cost=0.00..14.49 rows=649 width=15) (actual time=0.008..0.803 rows=649 loops= 1) - Hash (cost=977.62..977.62 rows=6567 width=16) (actual time=29.264..29.264 rows=6556 loops=1) - Seq Scan on tcom_fecha tcf (cost=0.00..977.62 rows=6567 width=16) (actual time=0.016..20.532 rows=6556 loops=1) Filter: (seq_det_cat = 388) Total runtime: 443.434 ms (20 rows) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
Jaime Casanova jcasa...@systemguards.com.ec writes: the temp files shoul be coming from hash operations but AFAICS the files are small and every hash operation should be using until work_mem memory, right? No, when a hash spills to disk the code has to guess the partition sizes (number of buckets per partition) in advance. So it wouldn't be at all surprising if the actual sizes come out substantially different from work_mem. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Aug 19, 2009, at 7:45 PM, Jaime Casanova wrote: AFAIUI, work_mem is used for some operations (sort, hash, etc) for avoiding the use of temp files on disk... In a client server i'm monitoring (pg 8.3.7, 32GB of ram) work_mem is set to 8MB, however i'm seeing a lot of temp files (3 in 4 hours) with small sizes (ie: 2021520 obviously lower than 8MB). so, why? maybe we use work_mem until we find isn't enough and we send just the difference to a temp file? i'm not thinking in raising work_mem until i understand this well, what's the point if we still create temp files that could fit in work_mem... Are you using temp tables? Those end up in pgsql_tmp as well. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
decibel deci...@decibel.org writes: On Aug 19, 2009, at 7:45 PM, Jaime Casanova wrote: AFAIUI, work_mem is used for some operations (sort, hash, etc) for avoiding the use of temp files on disk... In a client server i'm monitoring (pg 8.3.7, 32GB of ram) work_mem is set to 8MB, however i'm seeing a lot of temp files (3 in 4 hours) with small sizes (ie: 2021520 obviously lower than 8MB). so, why? maybe we use work_mem until we find isn't enough and we send just the difference to a temp file? i'm not thinking in raising work_mem until i understand this well, what's the point if we still create temp files that could fit in work_mem... Are you using temp tables? Those end up in pgsql_tmp as well. Uh, no, they don't. It might be useful to turn on trace_sort to see if the small files are coming from sorts. If they're from hashes I'm afraid there's no handy instrumentation ... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] [PERFORMANCE] work_mem vs temp files issue
Hi, AFAIUI, work_mem is used for some operations (sort, hash, etc) for avoiding the use of temp files on disk... In a client server i'm monitoring (pg 8.3.7, 32GB of ram) work_mem is set to 8MB, however i'm seeing a lot of temp files (3 in 4 hours) with small sizes (ie: 2021520 obviously lower than 8MB). so, why? maybe we use work_mem until we find isn't enough and we send just the difference to a temp file? i'm not thinking in raising work_mem until i understand this well, what's the point if we still create temp files that could fit in work_mem... PS: i have max_connections to 1024, i know i need a pool but the app is still opening persistent conecctions to the db, so is not like i could raise work_mem just easy until the app gets fixed -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
