Re: PostGreSQL Replication and question on maintenance

--0000000000004149a105975c0f2f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

p=C3=A1 15. 11. 2019 v 6:26 odes=C3=ADlatel github kran <[email protected]=
om> napsal:

>
> Hello postGreSQL Community ,
>>
>>
>>
>> Hope everyone is doing great !!.
>>
>>
>> *Background*
>>
>> We use PostgreSQL Version 10.6 version and heavily use PostgreSQL for ou=
r
>> day to day activities to write and read data. We have 2 clusters running
>> PostgreSQL engine , one cluster
>>
>> keeps data up to 60 days and another cluster retains data beyond 1 year.
>> The data is partitioned close to a week( ~evry 5 days a partition) and w=
e
>> have around 5 partitions per month per each table and we have 2 tables
>> primarily so that will be 10 tables a week. So in the cluster-1 we have
>> around  20 partitions and in cluster-2 we have around 160 partitions ( d=
ata
>> from 2018). We also want to keep the data for up to 2 years in the
>> cluster-2 to serve the data needs of the customer and so far we reached
>> upto 1 year of maintaining this data.
>>
>>
>>
>> *Current activity*
>>
>> We have a custom weekly migration DB script job that moves data from 1
>> cluster to another cluster what it does is the below things.
>>
>> 1) COPY command to copy the data from cluster-1 and split that data into
>> binary files
>>
>> 2) Writing the binary data into the cluster-2 table
>>
>> 3) Creating indexes after the data is copied.
>>
>>
>>
>> *Problem what we have right now. *
>>
>> When the migration activity runs(weekly) from past 2 times , we saw the
>> cluster read replica instance has restarted as it fallen behind the
>> master(writer instance). Everything
>>
>> after that worked seamlessly but we want to avoid the replica getting
>> restarted. To avoid from restart we started doing smaller binary files a=
nd
>> copy those files to the cluster-2
>>
>> instead of writing 1 big file of 450 million records. We were successful
>> in the recent migration as the reader instance didn=E2=80=99t restart af=
ter we
>> split 1 big file into multiple files to copy the data over but did resta=
rt
>> after the indexes are created on the new table as it could be write
>> intensive.
>>
>>
>>
>> *DB parameters set on migration job*
>>
>> work_mem set to 8 GB  and maintenace_work_mem=3D32 GB.
>>
>
these numbers looks crazy high - how much memory has your server - more
than 1TB?


Indexes per table =3D 3
>>
>> total indexes for 2 tables =3D 5
>>
>>
>>
>> *DB size*
>>
>> Cluster-2 =3D 8.6 TB
>>
>> Cluster-1 =3D 3.6 TB
>>
>> Peak Table relational rows =3D 400 - 480 million rows
>>
>> Average table relational rows =3D 300 - 350 million rows.
>>
>> Per table size =3D 90 -95 GB , per table index size is about 45 GB
>>
>>
>>
>> *Questions*
>>
>> 1) Can we decrease the maintenace_work_mem to 16 GB and will it slow dow=
n
>> the writes to the cluster , with that the reader instance can sync the d=
ata
>> slowly ?.
>>
>> 2) Based on the above use case what are your recommendations to keep the
>> data longer up to 2 years ?
>>
>> 3) What other recommendations you recommend ?.
>>
>>
>>
>>
>>
>> Appreciate your replies.
>>
>> THanks
>> githubkran
>>
>>>

--0000000000004149a105975c0f2f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">p=C3=A1 15. 11. 2019 v=C2=A06:26 odes=
=C3=ADlatel github kran &lt;<a href=3D"mailto:[email protected]">githubk=
[email protected]</a>&gt; napsal:<br></div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex"><div dir=3D"ltr"><br><div class=3D"gmail_quote"><div dir=3D"l=
tr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><p class=3D"MsoNormal" style=3D"mar=
gin:4.8pt 0in 2.4pt;line-height:110%;font-size:10pt;font-family:Verdana,san=
s-serif"><span style=3D"font-family:Calibri,sans-serif">Hello postGreSQL Co=
mmunity ,</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p><p class=3D"MsoNormal" style=3D"margin:4.8=
pt 0in 2.4pt;line-height:110%;font-size:10pt;font-family:Verdana,sans-serif=
"><span style=3D"font-family:Calibri,sans-serif">Hope everyone is doing gre=
at !!.</span></p><p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;lin=
e-height:110%;font-size:10pt;font-family:Verdana,sans-serif"><span style=3D=
"font-family:Calibri,sans-serif"><br></span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif">Background</span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">We use PostgreSQL Version
10.6 version and heavily use PostgreSQL for our day to day activities to wr=
ite
and read data. We have 2 clusters running PostgreSQL engine , one cluster</=
span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">keeps data up to 60 days and
another cluster retains data beyond 1 year. The data is partitioned close t=
o a
week( ~evry 5 days a partition) and we have around 5 partitions per month p=
er
each table and we have 2 tables primarily so that will be 10 tables a week.=
 So
in the cluster-1 we have around=C2=A0 20
partitions and in cluster-2 we have around 160 partitions ( data from 2018)=
. We
also want to keep the data for up to 2 years in the cluster-2 to serve the =
data
needs of the customer and so far we reached upto 1 year of maintaining this
data.</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif">Current activity</span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">We have a custom weekly
migration DB script job that moves data from 1 cluster to another cluster w=
hat
it does is the below things. </span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">1) COPY command to copy the
data from cluster-1 and split that data into binary files</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">2) Writing the binary data
into the cluster-2 table</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">3) Creating indexes after the
data is copied. </span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif"><span style=3D"text-decoration-line:none">=C2=A0</spa=
n></span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif">Problem what we have right now. </span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">When the migration activity
runs(weekly) from past 2 times , we saw the cluster read replica instance h=
as
restarted as it fallen behind the master(writer instance). Everything</span=
></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">after that worked seamlessly
but we want to avoid the replica getting restarted. To avoid from restart w=
e
started doing smaller binary files and copy those files to the cluster-2 </=
span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">instead of writing 1 big file
of 450 million records. We were successful in the recent migration as the
reader instance didn=E2=80=99t restart after we split 1 big file into multi=
ple files to
copy the data over but did restart after the indexes are created on the new
table as it could be write intensive. </span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif">DB parameters set on migration job</span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">work_mem set to 8 GB=C2=A0 and maintenace_work_mem=3D32 GB.=
</span></p></div></div></blockquote></div></div></div></div></blockquote><d=
iv><br></div><div>these numbers looks crazy high - how much memory has your=
 server - more than 1TB?</div><div><br></div><div> <br></div><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_qu=
ote"><div dir=3D"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204=
,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Indexes per table =3D 3</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">total indexes for 2 tables =3D
5</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif">DB size</span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Cluster-2 =3D 8.6 TB</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Cluster-1 =3D 3.6 TB</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Peak Table relational rows =3D
400 - 480 million rows</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Average table relational rows
=3D 300 - 350 million rows.</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Per table size =3D 90 -95 GB ,
per table index size is about 45 GB</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><b><u><span style=3D"font-famil=
y:Calibri,sans-serif">Questions</span></u></b></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">1) Can we decrease the
maintenace_work_mem to 16 GB and will it slow down the writes to the cluste=
r ,
with that the reader instance can sync the data slowly ?.</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">2) Based on the above use
case what are your recommendations to keep the data longer up to 2 years ? =
</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">3) What other recommendations
you recommend ?.</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">=C2=A0</span></p>

<p class=3D"MsoNormal" style=3D"margin:4.8pt 0in 2.4pt;line-height:110%;fon=
t-size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Cali=
bri,sans-serif">Appreciate your replies.</span></p></div><br><div class=3D"=
gmail_quote"><div class=3D"gmail_attr">THanks</div><div class=3D"gmail_attr=
">githubkran</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px=
 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div cl=
ass=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
dir=3D"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_quote=
"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex"><div class=3D"gmail_quo=
te"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div=
 dir=3D"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_qu=
ote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex"><div class=3D"gmail_=
quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;=
border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div class=3D=
"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div c=
lass=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div=
 dir=3D"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex">
</blockquote></div></div>
</blockquote></div></div>
</blockquote></div></div>
</blockquote></div></div>
</blockquote></div>
</blockquote></div></div>
</blockquote></div></div>
</blockquote></div>
</blockquote></div></div></div>
</blockquote></div>
</blockquote></div></div></div>
</blockquote></div>
</blockquote></div></div>
</blockquote></div>
</blockquote></div></div>
</blockquote></div></div>
</div></div>
</blockquote></div></div>

--0000000000004149a105975c0f2f--