Re: Slow response to my query

--0000000000006c1dac05988b00aa
Content-Type: text/plain; charset="UTF-8"

This is great, thank you!
Very educative. I am still reading on the zfs.
From your explanation, you prefer software raid to hardware raid, though
greater preference for ZFS.

Do you have a pointer to what I can read on how this is achieved be it LVM
or ZFS?

By the way, based on your experience, what Linux variants will you
recommend for postgresql and what version.
I have read from severalnines.com site that CentOS 7 should be avoided as
much as possible unfortunately I am currently running it.

I really appreciate your guide.

Regards

On Fri, Nov 29, 2019 at 6:13 PM Bzzzz <[email protected]> wrote:

> On Fri, 29 Nov 2019 17:35:03 +0100
> Goke Aruna <[email protected]> wrote:
>
> > are you debit or IOPS bounded,
> >      IOPS
>
> Then prefer 1|10, others are bounded to the R/W speed of only the
> slowest disk while 1|10 is distributed among 1/2 disk Nb, so faster for
> reads.
> NB: This is less true with ZFS as other factors are at work.
>
> Note that if you plan to use ZFS, extending an array of disks will mean
> most of new writings will happen only onto the new disks until they reach
> the same filling level than others - often crucial whatever the RAIDZ
> level is.
>
> Also do not forget about backupS!
>
> > how fast is your DB growing,
> >      Like 1.5GB per hour
>
> Hmm, this lead to a bit more than 1TB/month30d, you'll either need more
> SSDz or larger.
>
> This is here that you can see carriers' names in the plain is
> eating too place for nothing: one regular INTEGER (int4) = 4 bytes
> (used as a foreign key) or even less if you do not have a lot of
> carriers and the y fit into 32,768 (small int), when each character is
> _at least_ one byte and often more in UTF8.
>
> Also, the link I supplied you with (the one w/ rock and sand) is about
> aligning row according to Pg internal constraints (in terms of memory) -
> as it is said in this article, you could quite easily reach -20% on table
> place taken on disk - this is almost a nonsense for any small app, but
> with your number of rows, this becomes almost mandatory to scan faster at
> a lower cost with less than an hour of tests/calculations to reorder
> columns in your main table.
>
> > how many simultaneous accesses you have/need,
> >      Application - 5 and probably additional 5 for operator.
>
> Ok almost nothing, connections speaking, but with a big debit each.
>
> > what is the distribution between reads & writes,
> >         70 read while 30 writ
> > what is the alignement & size of one row,
> >       Most used table has 42 columns while next after it has 18 columns
>
> No, see above about what kind of alignment it is.
>
> > etc.
> >
> > After that,
> > which stripe size, 32
>
> I do not use RAID anymore (and used it only in software version), so I do
> not know if it is a good size taking in account the Pg page size (8kB),
> the SSD sector (normally 2kB) and the clustering (min size of a
> logical sector.)
>
> JY
>

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

<div dir=3D"auto"><div dir=3D"ltr"><div>This is great, thank you! <br></div=
><div>Very educative. I am still reading on the zfs.<br></div><div dir=3D"a=
uto">From your explanation, you prefer software raid to hardware raid, thou=
gh greater preference for ZFS.</div><div dir=3D"auto"><br></div><div dir=3D=
"auto">Do you have a pointer to what I can read on how this is achieved be =
it LVM or ZFS?</div><div dir=3D"auto"><br></div><div dir=3D"auto">By the wa=
y, based on your experience, what Linux variants will you recommend for pos=
tgresql and what version.</div><div dir=3D"auto">I have read from <a href=
=3D"http://severalnines.com">severalnines.com</a> site that CentOS 7 should=
 be avoided as much=C2=A0as possible unfortunately I am currently running i=
t.</div><div dir=3D"auto"><br></div><div dir=3D"auto">I really appreciate y=
our guide.</div><div dir=3D"auto"><br></div><div>Regards<br></div></div></d=
iv><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On =
Fri, Nov 29, 2019 at 6:13 PM Bzzzz &lt;<a href=3D"mailto:[email protected]"=
 target=3D"_blank" rel=3D"noreferrer">[email protected]</a>&gt; wrote:<br><=
/div><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">On Fri, 29 Nov 2019 =
17:35:03 +0100<br>
Goke Aruna &lt;<a href=3D"mailto:[email protected]" target=3D"_blank" rel=3D=
"noreferrer">[email protected]</a>&gt; wrote:<br>
<br>
&gt; are you debit or IOPS bounded,<br>
&gt;=C2=A0 =C2=A0 =C2=A0 IOPS<br>
<br>
Then prefer 1|10, others are bounded to the R/W speed of only the<br>
slowest disk while 1|10 is distributed among 1/2 disk Nb, so faster for<br>
reads.<br>
NB: This is less true with ZFS as other factors are at work.<br>
<br>
Note that if you plan to use ZFS, extending an array of disks will mean<br>
most of new writings will happen only onto the new disks until they reach<b=
r>
the same filling level than others - often crucial whatever the RAIDZ<br>
level is.<br>
<br>
Also do not forget about backupS!<br>
<br>
&gt; how fast is your DB growing,<br>
&gt;=C2=A0 =C2=A0 =C2=A0 Like 1.5GB per hour<br>
<br>
Hmm, this lead to a bit more than 1TB/month30d, you&#39;ll either need more=
<br>
SSDz or larger.<br>
<br>
This is here that you can see carriers&#39; names in the plain is<br>
eating too place for nothing: one regular INTEGER (int4) =3D 4 bytes<br>
(used as a foreign key) or even less if you do not have a lot of<br>
carriers and the y fit into 32,768 (small int), when each character is<br>
_at least_ one byte and often more in UTF8.<br>
<br>
Also, the link I supplied you with (the one w/ rock and sand) is about<br>
aligning row according to Pg internal constraints (in terms of memory) -<br=
>
as it is said in this article, you could quite easily reach -20% on table<b=
r>
place taken on disk - this is almost a nonsense for any small app, but<br>
with your number of rows, this becomes almost mandatory to scan faster at<b=
r>
a lower cost with less than an hour of tests/calculations to reorder<br>
columns in your main table.<br>
<br>
&gt; how many simultaneous accesses you have/need,<br>
&gt;=C2=A0 =C2=A0 =C2=A0 Application - 5 and probably additional 5 for oper=
ator.<br>
<br>
Ok almost nothing, connections speaking, but with a big debit each.<br>
<br>
&gt; what is the distribution between reads &amp; writes,<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A070 read while 30 writ<br>
&gt; what is the alignement &amp; size of one row,<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0Most used table has 42 columns while next af=
ter it has 18 columns<br>
<br>
No, see above about what kind of alignment it is.<br>
<br>
&gt; etc.<br>
&gt;<br>
&gt; After that,<br>
&gt; which stripe size, 32<br>
<br>
I do not use RAID anymore (and used it only in software version), so I do<b=
r>
not know if it is a good size taking in account the Pg page size (8kB),<br>
the SSD sector (normally 2kB) and the clustering (min size of a<br>
logical sector.)<br>
<br>
JY<br>
</blockquote></div>

--0000000000006c1dac05988b00aa--