Re: PostGreSQL Replication and question on maintenance

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

Any reply on this please ?.

On Fri, Nov 15, 2019 at 9:10 AM github kran <[email protected]> wrote:

>
>
> 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 - mor=
e
>> 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@gmai=
l.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 partitio=
n)
>>>> 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 partiti=
ons (
>>>> data from 2018). We also want to keep the data for up to 2 years in th=
e
>>>> cluster-2 to serve the data needs of the customer and so far we reache=
d
>>>> 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 th=
e
>>>> 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
>>>> successful in the recent migration as the reader instance didn=E2=80=
=99t restart
>>>> after we split 1 big file into multiple files to copy the data over bu=
t did
>>>> restart 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 syn=
c the
>>>> data 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
>>>>
>>>>>

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

<div dir=3D"ltr">Any reply on this please ?.</div><br><div class=3D"gmail_q=
uote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Nov 15, 2019 at 9:10 AM=
 github kran &lt;<a href=3D"mailto:[email protected]">[email protected]=
om</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
"><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 Pa=
vel Stehule &lt;<a href=3D"mailto:[email protected]" target=3D"_blank=
">[email protected]</a>&gt; wrote:<br></div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">=C2=A0 these num=
bers looks crazy high - how much memory has your server - more than 1TB?=C2=
=A0</div></div></blockquote><div><br></div><div>The cluster got 244 GB of R=
AM and storage capacity it has is 64 TB.=C2=A0</div><blockquote class=3D"gm=
ail_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">=C2=A0<br></di=
v><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"mai=
lto:[email protected]" target=3D"_blank">[email protected]</a>&gt; na=
psal:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0p=
x 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr"><br><div class=3D"gmail_quote"><div dir=3D"ltr"><div class=3D"gmai=
l_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;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;li=
ne-height:110%;font-size:10pt;font-family:Verdana,sans-serif"><span style=
=3D"font-family:Calibri,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>
</blockquote></div>

--0000000000004268d3059776ccbd--