Re: Worse performance with higher work_mem?
Israel Brewster <[email protected]> Tue, 14 Jan 2020 08:02:35 -0900
| Newsgroups | gmane.comp.db.postgresql.general |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail=_990E01AA-C803-439C-A489-9E2EEA81E0CD Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jan 13, 2020, at 6:34 PM, Dilip Kumar <[email protected]> = wrote: >=20 > On Tue, Jan 14, 2020 at 5:29 AM Israel Brewster <[email protected] = <mailto:[email protected]>> wrote: >>=20 >> I was working on diagnosing a =E2=80=9Cslow=E2=80=9D (about 6 second = run time) query: >>=20 >> SELECT >> to_char(bucket,'YYYY-MM-DD"T"HH24:MI:SS') as dates, >> x_tilt, >> y_tilt, >> rot_x, >> rot_y, >> date_part('epoch', bucket) as timestamps, >> temp >> FROM >> (SELECT >> time_bucket('1 week', read_time) as bucket, >> avg(tilt_x::float) as x_tilt, >> avg(tilt_y::float) as y_tilt, >> avg(rot_x::float) as rot_x, >> avg(rot_y::float) as rot_y, >> avg(temperature::float) as temp >> FROM tilt_data >> WHERE station=3D'c08883c0-fbe5-11e9-bd6e-aec49259cebb' >> AND read_time::date<=3D'2020-01-13'::date >> GROUP BY bucket) s1 >> ORDER BY bucket; >>=20 >> In looking at the explain analyze output, I noticed that it had an = =E2=80=9Cexternal merge Disk=E2=80=9D sort going on, accounting for = about 1 second of the runtime (explain analyze output here: = https://explain.depesz.com/s/jx0q). Since the machine has plenty of RAM = available, I went ahead and increased the work_mem parameter. Whereupon = the query plan got much simpler, and performance of said query = completely tanked, increasing to about 15.5 seconds runtime = (https://explain.depesz.com/s/Kl0S), most of which was in a = HashAggregate. >>=20 >> I am running PostgreSQL 11.6 on a machine with 128GB of ram (so, like = I said, plenty of RAM) >>=20 >> How can I fix this? Thanks. >=20 > I have noticed that after increasing the work_mem your plan has > switched from a parallel plan to a non-parallel plan. Basically, > earlier it was getting executed with 3 workers. And, after it becomes > non-parallel plan execution time is 3x. For the analysis can we just > reduce the value of parallel_tuple_cost and parallel_setup_cost and > see how it behaves? That was it. Setting the parallel_tuple_cost parameter to .05 and the = parallel_setup_cost parameter to 500 (so, both to half their default = values) caused this query to run in parallel again with the higher = work_mem setting (and do the sorts in memory, as was the original goal). = New explain output at https://explain.depesz.com/s/rX3m = <https://explain.depesz.com/s/rX3m> Granted, doing the sorts in memory = didn=E2=80=99t speed things up as much as I would have hoped - 5.58 = seconds vs 5.9 - but at least the higher work_mem setting isn=E2=80=99t = slowing down the query any more. Would be nice if the query could run a little faster - even six seconds = is a relatively long time to wait - but I can live with that at least. = So thanks! Hmmm=E2=80=A6 now I wonder how things would change if I = increased the max_parallel_workers value? Might be something to play = around with. Maybe grab a few more cores for the VM. --- Israel Brewster Software Engineer Alaska Volcano Observatory=20 Geophysical Institute - UAF=20 2156 Koyukuk Drive=20 Fairbanks AK 99775-7320 Work: 907-474-5172 cell: 907-328-9145 >=20 > --=20 > Regards, > Dilip Kumar > EnterpriseDB: http://www.enterprisedb.com = <http://www.enterprisedb.com/> --Apple-Mail=_990E01AA-C803-439C-A489-9E2EEA81E0CD Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 <html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; = charset=3Dutf-8"></head><body style=3D"word-wrap: break-word; = -webkit-nbsp-mode: space; line-break: after-white-space;" class=3D""><br = class=3D""><div class=3D""><div class=3D""> </div> <div><br class=3D""><blockquote type=3D"cite" class=3D""><div = class=3D"">On Jan 13, 2020, at 6:34 PM, Dilip Kumar <<a = href=3D"mailto:[email protected]" = class=3D"">[email protected]</a>> wrote:</div><br = class=3D"Apple-interchange-newline"><div class=3D""><span = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none; float: none; = display: inline !important;" class=3D"">On Tue, Jan 14, 2020 at 5:29 AM = Israel Brewster <</span><a href=3D"mailto:[email protected]" = style=3D"font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = orphans: auto; text-align: start; text-indent: 0px; text-transform: = none; white-space: normal; widows: auto; word-spacing: 0px; = -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px;" = class=3D"">[email protected]</a><span style=3D"caret-color: rgb(0, = 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none; float: none; display: inline !important;" = class=3D"">> wrote:</span><br style=3D"caret-color: rgb(0, 0, 0); = font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none;" class=3D""><blockquote type=3D"cite" = style=3D"font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = orphans: auto; text-align: start; text-indent: 0px; text-transform: = none; white-space: normal; widows: auto; word-spacing: 0px; = -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; = text-decoration: none;" class=3D""><br class=3D"">I was working on = diagnosing a =E2=80=9Cslow=E2=80=9D (about 6 second run time) query:<br = class=3D""><br class=3D"">SELECT<br = class=3D""> &nb= sp;to_char(bucket,'YYYY-MM-DD"T"HH24:MI:SS') as dates,<br = class=3D""> &nb= sp;x_tilt,<br = class=3D""> &nb= sp;y_tilt,<br = class=3D""> &nb= sp;rot_x,<br = class=3D""> &nb= sp;rot_y,<br = class=3D""> &nb= sp;date_part('epoch', bucket) as timestamps,<br = class=3D""> &nb= sp;temp<br class=3D""> FROM<br = class=3D""> &nb= sp;(SELECT<br = class=3D""> &nb= sp; time_bucket('1 week', read_time) as bucket,<br = class=3D""> &nb= sp; avg(tilt_x::float) as x_tilt,<br = class=3D""> &nb= sp; avg(tilt_y::float) as y_tilt,<br = class=3D""> &nb= sp; avg(rot_x::float) as rot_x,<br = class=3D""> &nb= sp; avg(rot_y::float) as rot_y,<br = class=3D""> &nb= sp; avg(temperature::float) as temp<br = class=3D""> &nb= sp;FROM tilt_data<br = class=3D""> &nb= sp;WHERE station=3D'c08883c0-fbe5-11e9-bd6e-aec49259cebb'<br = class=3D""> &nb= sp;AND read_time::date<=3D'2020-01-13'::date<br = class=3D""> &nb= sp;GROUP BY bucket) s1<br = class=3D""> ORDER BY bucket;<br = class=3D""><br class=3D"">In looking at the explain analyze output, I = noticed that it had an =E2=80=9Cexternal merge Disk=E2=80=9D sort going = on, accounting for about 1 second of the runtime (explain analyze output = here: <a href=3D"https://explain.depesz.com/s/jx0q" = class=3D"">https://explain.depesz.com/s/jx0q</a>). Since the machine has = plenty of RAM available, I went ahead and increased the work_mem = parameter. Whereupon the query plan got much simpler, and performance of = said query completely tanked, increasing to about 15.5 seconds runtime = (<a href=3D"https://explain.depesz.com/s/Kl0S" = class=3D"">https://explain.depesz.com/s/Kl0S</a>), most of which was in = a HashAggregate.<br class=3D""><br class=3D"">I am running PostgreSQL = 11.6 on a machine with 128GB of ram (so, like I said, plenty of RAM)<br = class=3D""><br class=3D"">How can I fix this? Thanks.<br = class=3D""></blockquote><br style=3D"caret-color: rgb(0, 0, 0); = font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none;" class=3D""><span style=3D"caret-color: rgb(0, 0, = 0); font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none; float: none; display: inline !important;" = class=3D"">I have noticed that after increasing the work_mem your plan = has</span><br style=3D"caret-color: rgb(0, 0, 0); font-family: = Helvetica; font-size: 12px; font-style: normal; font-variant-caps: = normal; font-weight: normal; letter-spacing: normal; text-align: start; = text-indent: 0px; text-transform: none; white-space: normal; = word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: = none;" class=3D""><span style=3D"caret-color: rgb(0, 0, 0); font-family: = Helvetica; font-size: 12px; font-style: normal; font-variant-caps: = normal; font-weight: normal; letter-spacing: normal; text-align: start; = text-indent: 0px; text-transform: none; white-space: normal; = word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: = none; float: none; display: inline !important;" class=3D"">switched from = a parallel plan to a non-parallel plan. Basically,</span><br = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none;" class=3D""><span = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none; float: none; = display: inline !important;" class=3D"">earlier it was getting executed = with 3 workers. And, after it becomes</span><br = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none;" class=3D""><span = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none; float: none; = display: inline !important;" class=3D"">non-parallel plan execution time = is 3x. For the analysis can we just</span><br style=3D"caret-color: = rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: = normal; font-variant-caps: normal; font-weight: normal; letter-spacing: = normal; text-align: start; text-indent: 0px; text-transform: none; = white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none;" class=3D""><span style=3D"caret-color: rgb(0, 0, = 0); font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none; float: none; display: inline !important;" = class=3D"">reduce the value of parallel_tuple_cost and = parallel_setup_cost and</span><br style=3D"caret-color: rgb(0, 0, 0); = font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none;" class=3D""><span style=3D"caret-color: rgb(0, 0, = 0); font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none; float: none; display: inline !important;" = class=3D"">see how it behaves?</span><br style=3D"caret-color: rgb(0, 0, = 0); font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none;" class=3D""></div></blockquote><div><br = class=3D""></div><div>That was it. Setting the parallel_tuple_cost = parameter to .05 and the parallel_setup_cost parameter to 500 (so, both = to half their default values) caused this query to run in parallel again = with the higher work_mem setting (and do the sorts in memory, as was the = original goal). New explain output at <a = href=3D"https://explain.depesz.com/s/rX3m" = class=3D"">https://explain.depesz.com/s/rX3m</a> Granted, doing the = sorts in memory didn=E2=80=99t speed things up as much as I would have = hoped - 5.58 seconds vs 5.9 - but at least the higher work_mem setting = isn=E2=80=99t slowing down the query any more.<div class=3D""><br = class=3D""></div><div class=3D"">Would be nice if the query could run a = little faster - even six seconds is a relatively long time to wait - but = I can live with that at least. So thanks! Hmmm=E2=80=A6 now I wonder how = things would change if I increased the max_parallel_workers value? Might = be something to play around with. Maybe grab a few more cores for the = VM.<br class=3D""><div class=3D""><div dir=3D"auto" style=3D"word-wrap: = break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" = class=3D""><div>---</div><div>Israel Brewster<br class=3D"">Software = Engineer<br class=3D"">Alaska Volcano Observatory <br = class=3D"">Geophysical Institute - UAF <br class=3D"">2156 Koyukuk = Drive <br class=3D"">Fairbanks AK 99775-7320</div><div>Work: = 907-474-5172<br class=3D"">cell: = 907-328-9145</div></div></div></div></div><br class=3D""><blockquote= type=3D"cite" class=3D""><div class=3D""><br style=3D"caret-color: = rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: = normal; font-variant-caps: normal; font-weight: normal; letter-spacing: = normal; text-align: start; text-indent: 0px; text-transform: none; = white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none;" class=3D""><span style=3D"caret-color: rgb(0, 0, = 0); font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none; float: none; display: inline !important;" = class=3D"">--<span class=3D"Apple-converted-space"> </span></span><br= style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none;" class=3D""><span = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none; float: none; = display: inline !important;" class=3D"">Regards,</span><br = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none;" class=3D""><span = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none; float: none; = display: inline !important;" class=3D"">Dilip Kumar</span><br = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none;" class=3D""><span = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none; float: none; = display: inline !important;" class=3D"">EnterpriseDB:<span = class=3D"Apple-converted-space"> </span></span><a = href=3D"http://www.enterprisedb.com/" style=3D"font-family: Helvetica; = font-size: 12px; font-style: normal; font-variant-caps: normal; = font-weight: normal; letter-spacing: normal; orphans: auto; text-align: = start; text-indent: 0px; text-transform: none; white-space: normal; = widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; = -webkit-text-stroke-width: 0px;" = class=3D"">http://www.enterprisedb.com</a></div></blockquote></div><br = class=3D""></div></body></html>= --Apple-Mail=_990E01AA-C803-439C-A489-9E2EEA81E0CD--