-----Original Message-----
From: ldh@xxxxxxxxxxxxxxxxxx <ldh@xxxxxxxxxxxxxxxxxx>
Sent: Thursday, July 22, 2021 09:37
To: David Rowley <dgrowleyml@xxxxxxxxx>
Cc: Tom Lane <tgl@xxxxxxxxxxxxx>; Peter Geoghegan <pg@xxxxxxx>; Justin Pryzby <pryzby@xxxxxxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxx
Subject: RE: Big performance slowdown from 11.2 to 13.3
OK. Will do another round of testing.
-----Original Message-----
From: David Rowley <dgrowleyml@xxxxxxxxx>
Sent: Thursday, July 22, 2021 00:44
To: ldh@xxxxxxxxxxxxxxxxxx
Cc: Tom Lane <tgl@xxxxxxxxxxxxx>; Peter Geoghegan <pg@xxxxxxx>; Justin Pryzby <pryzby@xxxxxxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: Big performance slowdown from 11.2 to 13.3
On Thu, 22 Jul 2021 at 16:37, ldh@xxxxxxxxxxxxxxxxxx <ldh@xxxxxxxxxxxxxxxxxx> wrote:
> Seems like no cigar ☹ See plan pasted below. I changed the conf as follows:
> - hash_mem_multiplier = '2'
> - work_mem = '1GB'
> Batches: 5 Memory Usage: 2400305kB Disk Usage: 126560kB
You might want to keep going higher with hash_mem_multiplier until you see no "Disk Usage" there. As mentioned, v11 didn't spill to disk and just used all the memory it pleased. That was a bit dangerous as it could result in OOM, so it was fixed.
David
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hello all,
So, I went possibly nuclear, and still no cigar. Something's not right.
- hash_mem_multiplier = '10'
- work_mem = '1GB'
The results are
Batches: 5 Memory Usage: 2,449,457kB Disk Usage: 105,936kB
Execution Time: 1,837,126.766 ms
It's still spilling to disk and seems to cap at 2.5GB of memory usage in spite of configuration. More importantly
- I am not understanding how spilling to disk 100MB (which seems low to me and should be fast on our SSD), causes the query to slow down by a factor of 10.
- It seems at the very least that memory consumption on 11 was more moderate? This process of ours was running several of these types of queries concurrently and I don't think I ever saw the machine go over 40GB in memory usage.
HashAggregate (cost=1774568.21..1774579.21 rows=200 width=1260) (actual time=84860.629..1836583.909 rows=722853 loops=1)
Group Key: assessmenticcqa_raw.iccqar_iccassmt_fk
Batches: 1 Memory Usage: 1277985kB
Buffers: shared hit=46 read=169822, temp read=13144 written=23035
-> HashAggregate (cost=1360804.75..1374830.63 rows=1402588 width=56) (actual time=27890.422..39975.074 rows=13852618 loops=1)
Group Key: assessmenticcqa_raw.iccqar_iccassmt_fk, assessmenticcqa_raw.iccqar_ques_code
Batches: 5 Memory Usage: 2449457kB Disk Usage: 105936kB
Buffers: shared hit=32 read=169819, temp read=13144 written=23035
-> Seq Scan on assessmenticcqa_raw (cost=0.00..1256856.62 rows=13859750 width=38) (actual time=0.053..13623.310 rows=13852618 loops=1)
Filter: ((iccqar_ques_code)::text = ANY ('{"DEBRIDEMENT DATE","DEBRIDEMENT THIS VISIT","DEBRIDEMENT TYPE","DEPTH (CM)","DEPTH DESCRIPTION","DOES PATIENT HAVE PAIN ASSOCIATED WITH THIS WOUND?","DRAIN PRESENT","DRAIN TYPE","EDGE / SURROUNDING TISSUE - MACERATION",EDGES,EPITHELIALIZATION,"EXUDATE AMOUNT","EXUDATE TYPE","GRANULATION TISSUE","INDICATE OTHER TYPE OF WOUND CLOSURE","INDICATE TYPE","INDICATE WOUND CLOSURE","IS THIS A CLOSED SURGICAL WOUND OR SUSPECTED DEEP TISSUE INJURY?","LENGTH (CM)","MEASUREMENTS TAKEN","NECROTIC TISSUE AMOUNT","NECROTIC TISSUE TYPE",ODOR,"OTHER COMMENTS REGARDING DEBRIDEMENT TYPE","OTHER COMMENTS REGARDING DRAIN TYPE","OTHER COMMENTS REGARDING PAIN INTERVENTIONS","OTHER COMMENTS REGARDING PAIN QUALITY","OTHER COMMENTS REGARDING REASON MEASUREMENTS NOT TAKEN","PAIN FREQUENCY","PAIN INTERVENTIONS","PAIN QUALITY","PERIPHERAL TISSUE EDEMA","PERIPHERAL TISSUE INDURATION","REASON MEASUREMENTS NOT TAKEN","RESPONSE TO PAIN INTERVENTIONS",SHAPE,"SIGNS AND SYMPTOMS OF INFECTION","SKIN COLOR SURROUNDING WOUND",STATE,"SURFACE AREA (SQ CM)","TOTAL NECROTIC TISSUE ESCHAR","TOTAL NECROTIC TISSUE SLOUGH",TUNNELING,"TUNNELING SIZE(CM)/LOCATION - 12 - 3 O''CLOCK","TUNNELING SIZE(CM)/LOCATION - 3 - 6 O''CLOCK","TUNNELING SIZE(CM)/LOCATION - 6 - 9 O''CLOCK","TUNNELING SIZE(CM)/LOCATION - 9 - 12 O''CLOCK",UNDERMINING,"UNDERMINING SIZE(CM)/LOCATION - 12 - 3 O''CLOCK","UNDERMINING SIZE(CM)/LOCATION - 3 - 6 O''CLOCK","UNDERMINING SIZE(CM)/LOCATION - 6 - 9 O''CLOCK","UNDERMINING SIZE(CM)/LOCATION - 9 - 12 O''CLOCK","WIDTH (CM)","WOUND PAIN LEVEL, WHERE 0 = \"NO PAIN\" AND 10 = \"WORST POSSIBLE PAIN\""}'::text[]))
Rows Removed by Filter: 171680
Buffers: shared hit=32 read=169819
Settings: effective_cache_size = '52GB', from_collapse_limit = '24', hash_mem_multiplier = '10', jit = 'off', jit_above_cost = '2e+08', jit_inline_above_cost = '5e+08', jit_optimize_above_cost = '5e+08', join_collapse_limit = '24', max_parallel_workers = '20', max_parallel_workers_per_gather = '8', random_page_cost = '1.1', temp_buffers = '4GB', work_mem = '1GB'
Planning:
Buffers: shared hit=3
Planning Time: 1.038 ms
Execution Time: 1837126.766 ms
Thank you,
