RE: performance degredation after upgrade from 9.6 to 12
"Andrew Zakharov" <[email protected]> Mon, 16 Dec 2019 15:27:01 +0300
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <[email protected]> |
This is a multipart message in MIME format. ------=_NextPart_000_0171_01D5B425.4355BE20 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi there =E2=80=93 I have no idea why this happening. But I suspect the parallel requires = more internal machine resources like CPU etc because you can faster = retrieve the disk data from the one hand but you ought to spend more = resources for maintaining several threads and theirs coordination (px = coordinator process in Oracle terms) from another one. Thus there could = be more serious hardware requirements even just to keep performance the = same. I believe that the real benefit of the parallel will be shown when = you have pair of large and wide tables (30M or more each) with hash join = (typical task for mart construction) but such class of databases is = supposed to be big and required enough resources initially. =20 =20 From: Mariel Cherkassky <[email protected]>=20 Sent: Monday, December 16, 2019 2:48 PM To: Jeff Janes <[email protected]> Cc: Andrew Zakharov <[email protected]>; = [email protected] Subject: Re: performance degredation after upgrade from 9.6 to 12 =20 Hey Jeff,Andrew, I continued testing the 12version vs the 96 version and it seems that = there is almost non diff and in some cases pg96 is faster than 12. I = compared the content of pg_stat_statements after each test that I have = done and it seems that the db time is almost the same and sometimes 96 = is faster by 5%. =20 Any idea why there isnt any improvement even when I enabled the parallel = params in 12 ? I can add a few examples if needed.. ------=_NextPart_000_0171_01D5B425.4355BE20 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <html xmlns:v=3D"urn:schemas-microsoft-com:vml" = xmlns:o=3D"urn:schemas-microsoft-com:office:office" = xmlns:w=3D"urn:schemas-microsoft-com:office:word" = xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" = xmlns=3D"http://www.w3.org/TR/REC-html40"><head><meta = http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8"><meta = name=3DGenerator content=3D"Microsoft Word 15 (filtered = medium)"><style><!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:12.0pt; font-family:"Times New Roman",serif;} a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;} p.msonormal0, li.msonormal0, div.msonormal0 {mso-style-name:msonormal; mso-margin-top-alt:auto; margin-right:0cm; mso-margin-bottom-alt:auto; margin-left:0cm; font-size:12.0pt; font-family:"Times New Roman",serif;} span.EmailStyle18 {mso-style-type:personal-reply; font-family:"Calibri",sans-serif; color:#1F497D;} .MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;} @page WordSection1 {size:612.0pt 792.0pt; margin:2.0cm 42.5pt 2.0cm 3.0cm;} div.WordSection1 {page:WordSection1;} --></style><!--[if gte mso 9]><xml> <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" /> </xml><![endif]--><!--[if gte mso 9]><xml> <o:shapelayout v:ext=3D"edit"> <o:idmap v:ext=3D"edit" data=3D"1" /> </o:shapelayout></xml><![endif]--></head><body lang=3DRU = link=3D"#0563C1" vlink=3D"#954F72"><div class=3DWordSection1><p = class=3DMsoNormal><span lang=3DEN-US = style=3D'font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;= mso-fareast-language:EN-US'>Hi there =E2=80=93<o:p></o:p></span></p><p = class=3DMsoNormal><span lang=3DEN-US = style=3D'font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;= mso-fareast-language:EN-US'>I have no idea why this happening. But I = suspect the parallel requires more internal machine resources like CPU = etc because you can faster retrieve the disk data from the one hand but = you ought to spend more resources for maintaining several threads and = theirs coordination (px coordinator process in Oracle terms) from = another one. Thus there could be more serious hardware requirements even = just to keep performance the same. I believe that the real benefit of = the parallel will be shown when you have pair of large and wide tables = (30M or more each) with hash join (typical task for mart construction) = but such class of databases is supposed to be big and required enough = resources initially.<o:p></o:p></span></p><p class=3DMsoNormal><span = lang=3DEN-US = style=3D'font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;= mso-fareast-language:EN-US'><o:p> </o:p></span></p><p = class=3DMsoNormal><span lang=3DEN-US = style=3D'font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;= mso-fareast-language:EN-US'><o:p> </o:p></span></p><div = style=3D'border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm = 4.0pt'><div><div style=3D'border:none;border-top:solid #E1E1E1 = 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=3DMsoNormal><b><span = style=3D'font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span><= /b><span style=3D'font-size:11.0pt;font-family:"Calibri",sans-serif'> = Mariel Cherkassky <[email protected]> <br><b>Sent:</b> = Monday, December 16, 2019 2:48 PM<br><b>To:</b> Jeff Janes = <[email protected]><br><b>Cc:</b> Andrew Zakharov = <[email protected]>; = [email protected]<br><b>Subject:</b> Re: = performance degredation after upgrade from 9.6 to = 12<o:p></o:p></span></p></div></div><p = class=3DMsoNormal><o:p> </o:p></p><div><div><p = class=3DMsoNormal>Hey Jeff,Andrew,<o:p></o:p></p></div><div><p = class=3DMsoNormal>I continued testing the 12version vs the 96 version = and it seems that there is almost non diff and in some cases pg96 is = faster than 12. I compared the content of pg_stat_statements after each = test that I have done and it seems that the db time is almost the same = and sometimes 96 is faster by 5%.<o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p = class=3DMsoNormal>Any idea why there isnt any improvement even when I = enabled the parallel params in 12 ?<o:p></o:p></p></div><div><p = class=3DMsoNormal>I can add a few examples if = needed..<o:p></o:p></p></div></div></div></div></body></html> ------=_NextPart_000_0171_01D5B425.4355BE20--