[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: RE: Postgres server 12.2 crash with process exited abnormally and possibly corrupted shared memory
From: Ishan Joshi <Ishan.Joshi () amdocs ! com>
Date: 2020-06-26 6:49:47
Message-ID: AM6PR0602MB3398C6FDAACB091CE39F5CA586930 () AM6PR0602MB3398 ! eurprd06 ! prod ! outlook ! com
[Download RAW message or body]
[Attachment #2 (text/plain)]
Hi,
Adding more, We have started to test individual tables now. We have tried two tables \
with 24 partitions and this increased the memory utilization from 30% to 50-60%. Then \
we have added another table with 500 partition and our test got crashed after few \
hours of consumption with 100% memory utilization.
Last night I had executed test with 200 partition on same table and executed 15 hrs \
test run and the memory utilization was stable with 80% of memory utilization. With \
referencing to this result I have executed log test run for 3 days with same 200 \
partitions.
But again suspect is going to number of partition only.
How can we check the memory utilization by a table object or a specific query ?
Thanks & Regards,
Ishan Joshi
From: Ishan Joshi
Sent: Wednesday, June 10, 2020 10:42 PM
To: Michael Lewis <mlewis@entrata.com>
Cc: pgsql-general@postgresql.org
Subject: RE: Postgres server 12.2 crash with process exited abnormally and possibly \
corrupted shared memory
Hi Michael,
We have table having rows 2.5 millions records inserted and updated in each hour and \
another table having about 1 million records in an hour. WE have dedicated column \
added in table that have list of 1-500 and this column is used as partition key. Idea \
behind the 500 partition is to store 1 year data in the table with partition key \
value change at every 20hrs.
As the data is huge in these tables we are approaching to partition and the list \
partition for performing the maintenance on these tables.
This is not a test as we have partition on these tables with oracle but as we migrate \
to postgres, we are enabling the feature in postgres as well.
As we want to see the impact from beginning 0 size and understanding the details for \
next 72 hrs under heavy load.
As I just executed the same environment with 100 partition on these tables, Run was \
running for 12 hrs with constant 70% RAM utilization and 50% cpu utilization.
So I am suspecting the number of partition is the issue behind the memory \
utilization.
Thanks & Regards,
Ishan Joshi
From: Michael Lewis <mlewis@entrata.com<mailto:mlewis@entrata.com>>
Sent: Wednesday, June 10, 2020 10:28 PM
To: Ishan Joshi <Ishan.Joshi@amdocs.com<mailto:Ishan.Joshi@amdocs.com>>
Cc: pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>
Subject: Re: Postgres server 12.2 crash with process exited abnormally and possibly \
corrupted shared memory
On Wed, Jun 10, 2020 at 12:05 AM Ishan Joshi \
<Ishan.Joshi@amdocs.com<mailto:Ishan.Joshi@amdocs.com>> wrote: How many rows did \
these tables have before partitioning? --> We starts test with 0 rows in \
partition table.
Partitions are far from free and pruning is great but not guaranteed. How many total \
rows do you currently have or foresee having in the biggest table (all partitions \
combined, or without partitioning) within the next 1-3 years? 1 million? 100 million? \
2 billion? You may have partitioned before it was prudent to do so is the point. \
Just because something can be done, doesn't mean it should be. What sort of key are \
you partitioning on?
Also, what do you mean about tests? There are some assumptions made about empty \
tables since stats may indicate they are empty when they are not. If your tests \
involve many empty tables, then it may give rather different performance than real \
life where there are few or no empty partitions.
This email and the information contained herein is proprietary and confidential and \
subject to the Amdocs Email Terms of Service, which you may review at \
https://www.amdocs.com/about/email-terms-of-service \
<https://www.amdocs.com/about/email-terms-of-service>
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"> <head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@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:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#2F5496;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#2F5496;}
span.EmailStyle21
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:#2F5496;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#2F5496">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#2F5496"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#2F5496">Adding more, We have started to test \
individual tables now. We have tried two tables with 24 partitions and this increased \
the memory utilization from 30% to 50-60%. Then we have added another table with 500 \
partition and our test got crashed after few hours of consumption with 100% memory \
utilization.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496">Last night I had executed test with 200 partition on same table \
and executed 15 hrs test run and the memory utilization was stable with 80% of memory \
utilization. With referencing to this result I have executed log test run for 3 days \
with same 200 partitions.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496">But again suspect is going to number of partition \
only.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496">How can we check the memory utilization by a table object \
or a specific query ?<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496"><o:p> </o:p></span></p> <div>
<p class="MsoNormal"><span style="font-size:12.0pt;color:#2F5496">Thanks & \
Regards,<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="font-size:12.0pt;color:#2F5496">Ishan Joshi<o:p></o:p></span></p> </div>
<p class="MsoNormal"><span style="color:#2F5496"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> Ishan Joshi <br>
<b>Sent:</b> Wednesday, June 10, 2020 10:42 PM<br>
<b>To:</b> Michael Lewis <mlewis@entrata.com><br>
<b>Cc:</b> pgsql-general@postgresql.org<br>
<b>Subject:</b> RE: Postgres server 12.2 crash with process exited abnormally and \
possibly corrupted shared memory<o:p></o:p></p> </div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color:#2F5496">Hi Michael,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#2F5496"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#2F5496">We have table having rows 2.5 \
millions records inserted and updated in each hour and another table having about 1 \
million records in an hour. WE have dedicated column added in table that have list of \
1-500 and this column is used as partition key.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#2F5496">Idea behind the 500 partition is to \
store 1 year data in the table with partition key value change at every \
20hrs.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496">As the data is huge in these tables we are approaching to \
partition and the list partition for performing the maintenance on these \
tables.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496">This is not a test as we have partition on these tables with \
oracle but as we migrate to postgres, we are enabling the feature in postgres as \
well.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496">As we want to see the impact from beginning 0 size and \
understanding the details for next 72 hrs under heavy load.<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="color:#2F5496"><o:p> </o:p></span></p> <p \
class="MsoNormal"><span style="color:#2F5496">As I just executed the same environment \
with 100 partition on these tables, Run was running for 12 hrs with constant 70% RAM \
utilization and 50% cpu utilization.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496">So I am suspecting the number of partition is the issue behind \
the memory utilization.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="color:#2F5496">Thanks & Regards,<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="color:#2F5496">Ishan Joshi<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="color:#2F5496"><o:p> </o:p></span></p> <p \
class="MsoNormal"><b>From:</b> Michael Lewis <<a \
href="mailto:mlewis@entrata.com">mlewis@entrata.com</a>> <br>
<b>Sent:</b> Wednesday, June 10, 2020 10:28 PM<br>
<b>To:</b> Ishan Joshi <<a \
href="mailto:Ishan.Joshi@amdocs.com">Ishan.Joshi@amdocs.com</a>><br> <b>Cc:</b> <a \
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a><br> \
<b>Subject:</b> Re: Postgres server 12.2 crash with process exited abnormally and \
possibly corrupted shared memory<o:p></o:p></p> <p \
class="MsoNormal"><o:p> </o:p></p> <div>
<div>
<p class="MsoNormal">On Wed, Jun 10, 2020 at 12:05 AM Ishan Joshi <<a \
href="mailto:Ishan.Joshi@amdocs.com">Ishan.Joshi@amdocs.com</a>> \
wrote:<o:p></o:p></p> </div>
<div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in \
6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt"> <div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">How \
many rows did these tables have before partitioning? <span \
style="font-family:Wingdings">Ã </span> We starts test with 0 rows in \
partition table.<o:p></o:p></p> </div>
</div>
</blockquote>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Partitions are far from free and pruning is great but not \
guaranteed. How many total rows do you currently have or foresee having in the \
biggest table (all partitions combined, or without partitioning) within the next 1-3 \
years? 1 million? 100 million? 2 billion? You may have partitioned before it \
was prudent to do so is the point. Just because something can be done, doesn't mean \
it should be. What sort of key are you partitioning on?<o:p></o:p></p> </div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Also, what do you mean about tests? There are some assumptions \
made about empty tables since stats may indicate they are empty when they are not. If \
your tests involve many empty tables, then it may give rather different performance \
than real life where there are few or no empty partitions.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</div>
</div>
<p class="MsoNormal"><span style="FONT-SIZE: 11pt"><strong><span style="font-family: \
Calibri;">This email and the information contained herein is proprietary and \
confidential and subject to the Amdocs Email Terms of Service, which you may review \
at</span></strong><span style="font-family: Calibri;"> </span><a \
href="https://www.amdocs.com/about/email-terms-of-service"><strong><span \
style="font-family: Calibri;">https://www.amdocs.com/about/email-terms-of-service</span></strong></a></span></p></body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic