Re: PostGreSQL Replication and question on maintenance

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

On Thu, Nov 14, 2019 at 11:42 PM Pavel Stehule <[email protected]>
wrote:

>   these numbers looks crazy high - how much memory has your server - more
> than 1TB?
>

The cluster got 244 GB of RAM and storage capacity it has is 64 TB.

>
>
> p=C3=A1 15. 11. 2019 v 6:26 odes=C3=ADlatel github kran <githubkran@gmail=
.com>
> napsal:
>
>>
>> Hello postGreSQL Community ,
>>>
>>>
>>>
>>> Hope everyone is doing great !!.
>>>
>>>
>>> *Background*
>>>
>>> We use PostgreSQL Version 10.6 version and heavily use PostgreSQL for
>>> our 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 =
we
>>> 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 ( =
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.
>>>
>>>
>>>
>>> *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 int=
o
>>> 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 =
and
>>> copy those files to the cluster-2
>>>
>>> instead of writing 1 big file of 450 million records. We were successfu=
l
>>> in the recent migration as the reader instance didn=E2=80=99t restart a=
fter we
>>> split 1 big file into multiple files to copy the data over but did rest=
art
>>> 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.
>>>
>>
>
>
> 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
>>> down the writes to the cluster , with that the reader instance can sync=
 the
>>> data slowly ?.
>>>
>>> 2) Based on the above use case what are your recommendations to keep th=
e
>>> data longer up to 2 years ?
>>>
>>> 3) What other recommendations you recommend ?.
>>>
>>>
>>>
>>>
>>>
>>> Appreciate your replies.
>>>
>>> THanks
>>> githubkran
>>>
>>>>

--000000000000c6e2850597640161
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">On Thu, Nov 14, 2019 at 11:42 PM Pave=
l Stehule &lt;<a href=3D"mailto:[email protected]">pavel.stehule@gmai=
l.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:=
1ex"><div dir=3D"ltr"><div dir=3D"ltr">=C2=A0 these numbers looks crazy hig=
h - how much memory has your server - more than 1TB?=C2=A0</div></div></blo=
ckquote><div><br></div><div>The cluster got 244 GB of RAM and storage capac=
ity it has is 64 TB.=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex"><div dir=3D"ltr"><div dir=3D"ltr">=C2=A0<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:githubkran@gma=
il.com" target=3D"_blank">[email protected]</a>&gt; napsal:<br></div><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-lef=
t:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><br><div cl=
ass=3D"gmail_quote"><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 so=
lid 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%;font-=
size:10pt;font-family:Verdana,sans-serif"><span style=3D"font-family:Calibr=
i,sans-serif">Hello postGreSQL Community ,</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></div><div><br></div><div> <br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><=
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>
</blockquote></div></div>

--000000000000c6e2850597640161--