Re: Slow response to my query
Goke Aruna <[email protected]> Fri, 29 Nov 2019 17:35:03 +0100
| Newsgroups | gmane.comp.db.postgresql.novice |
|---|---|
| Message-ID | <CAE=Dito07gTFDuFECVY5zQ7U6BuyowKSFyqqn4kA=a9Mw5wtNw@mail.gmail.com> |
--000000000000cdcd5105987ed0cb
Content-Type: text/plain; charset="UTF-8"
This is great, thank you so much!
are you debit or IOPS bounded,
IOPS
how fast is your DB growing,
Like 1.5GB per hour
how many simultaneous accesses you have/need,
Application - 5 and probably additional 5 for operator.
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
etc.
After that,
which stripe size, 32
Thanks for your guide
On Fri, 29 Nov 2019, 5:19 PM Bzzzz <[email protected]> wrote:
> On Fri, 29 Nov 2019 16:47:44 +0100
> Goke Aruna <[email protected]> wrote:
>
> > I want to setup a fresh Centos 7.5 OS for a new postgresql 11/12. on
> > my dev server that has 7 1TB SAS drives.
> > Kindly advise on which is better, RAID 1+0 or RAID 10 ADM or RAID 6.
>
> It depends on many parms, ie:
>
> are you debit or IOPS bounded,
> how fast is your DB growing,
> how many simultaneous accesses you have/need,
> what is the distribution between reads & writes,
> what is the alignement & size of one row,
> etc.
>
> After that,
> which stripe size,
> etc.
>
> Once your speed problem will be definitely solved, you might also
> consider ZFS, it is a bit slower than RAID but it adds data integrity to
> data redundancy (but it need fiddling sometimes.)
>
> > > *EXPLAIN SELECT count(*) AS aggregate FROM allcalls;*
> > >
> > > Finalize Aggregate (cost=2707819.51..2707819.52 rows=1 width=8)
> > > -> Gather (cost=2707819.30..2707819.51 rows=2 width=8)
> > > Workers Planned: 2
> > > -> Partial Aggregate (cost=2706819.30..2706819.31 rows=1
> > > width=8) -> Parallel Append (cost=0.00..2635105.63 rows=28685466
> > > width=0)
> > > -> Parallel Seq Scan on allcalls_p20190603
> > > (cost=0.00..703632.78 rows=8035778 width=0)
> > > -> Parallel Seq Scan on allcalls_p20190611
> > > (cost=0.00..639557.82 rows=7182082 width=0)
> > > -> Parallel Seq Scan on allcalls_p20190601
>
> I'm not accustomed to partitioning, but all your scans are <Seq>uentials,
> so you might miss one/some index(es) - specialists will tell you more
> about that, but from what I skimmed from Keith link, my contentionis
> you're missing index(es.)
>
> Also, into Debian, there's a: "postgresql-12-hypopg" package that brings
> an extension with which you can create hypothetical indexes which in turn
> will tell you if your queries need the real ones or not, this should
> also exist into your distro and could help.
>
> JY
>
--000000000000cdcd5105987ed0cb
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"auto"><div>This is great, thank you so much!=C2=A0</div><div di=
r=3D"auto"><br></div><div dir=3D"auto"><span style=3D"font-family:sans-seri=
f">are you debit or IOPS bounded,</span></div><div dir=3D"auto">=C2=A0 =C2=
=A0 =C2=A0IOPS<br style=3D"font-family:sans-serif"><span style=3D"font-fami=
ly:sans-serif">how fast is your DB growing,</span></div><div dir=3D"auto">=
=C2=A0 =C2=A0 =C2=A0Like 1.5GB per hour<br style=3D"font-family:sans-serif"=
><span style=3D"font-family:sans-serif">how many simultaneous accesses you =
have/need,=C2=A0</span></div><div dir=3D"auto">=C2=A0 =C2=A0 =C2=A0Applicat=
ion - 5 and probably additional 5 for operator.<br style=3D"font-family:san=
s-serif"><span style=3D"font-family:sans-serif">what is the distribution be=
tween reads & writes,</span></div><div dir=3D"auto">=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 70 read while 30 writ<br style=3D"font-family:sans-serif"><span =
style=3D"font-family:sans-serif">what is the alignement & size of one r=
ow,</span></div><div dir=3D"auto">=C2=A0 =C2=A0 =C2=A0 Most used table has =
42 columns while next after it has 18 columns<br style=3D"font-family:sans-=
serif"><span style=3D"font-family:sans-serif">etc.</span><br style=3D"font-=
family:sans-serif"><br style=3D"font-family:sans-serif"><span style=3D"font=
-family:sans-serif">After that,</span><br style=3D"font-family:sans-serif">=
<span style=3D"font-family:sans-serif">which stripe size, 32</span></div><d=
iv dir=3D"auto"><font face=3D"sans-serif"><br></font></div><div dir=3D"auto=
"><font face=3D"sans-serif">Thanks for your guide<br></font><br><div class=
=3D"gmail_quote" dir=3D"auto"><div dir=3D"ltr" class=3D"gmail_attr">On Fri,=
29 Nov 2019, 5:19 PM Bzzzz <<a href=3D"mailto:[email protected]" target=
=3D"_blank" rel=3D"noreferrer">[email protected]</a>> wrote:<br></div><b=
lockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px =
#ccc solid;padding-left:1ex">On Fri, 29 Nov 2019 16:47:44 +0100<br>
Goke Aruna <<a href=3D"mailto:[email protected]" rel=3D"noreferrer norefe=
rrer" target=3D"_blank">[email protected]</a>> wrote:<br>
<br>
> I want to setup a fresh Centos 7.5 OS for a new postgresql 11/12. on<b=
r>
> my dev server that has 7 1TB SAS drives.<br>
> Kindly advise on which is better, RAID 1+0 or RAID 10 ADM or RAID 6.<b=
r>
<br>
It depends on many parms, ie:<br>
<br>
are you debit or IOPS bounded,<br>
how fast is your DB growing,<br>
how many simultaneous accesses you have/need,<br>
what is the distribution between reads & writes,<br>
what is the alignement & size of one row,<br>
etc.<br>
<br>
After that,<br>
which stripe size,<br>
etc.<br>
<br>
Once your speed problem will be definitely solved, you might also<br>
consider ZFS, it is a bit slower than RAID but it adds data integrity to<br=
>
data redundancy (but it need fiddling sometimes.)<br>
<br>
> > *EXPLAIN SELECT count(*) AS aggregate FROM allcalls;*<br>
> ><br>
> > Finalize Aggregate=C2=A0 (cost=3D2707819.51..2707819.52 rows=3D1 =
width=3D8)<br>
> >=C2=A0 =C2=A0->=C2=A0 Gather=C2=A0 (cost=3D2707819.30..2707819.=
51 rows=3D2 width=3D8)<br>
> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Workers Planned: 2<br>
> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Partial Aggregate=C2=
=A0 (cost=3D2706819.30..2706819.31 rows=3D1<br>
> > width=3D8) ->=C2=A0 Parallel Append=C2=A0 (cost=3D0.00..263510=
5.63 rows=3D28685466<br>
> > width=3D0)<br>
> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0->=C2=A0 Parallel Seq Scan on allcalls_p20190603<br>
> >=C2=A0 (cost=3D0.00..703632.78 rows=3D8035778 width=3D0)<br>
> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0->=C2=A0 Parallel Seq Scan on allcalls_p20190611<br>
> >=C2=A0 (cost=3D0.00..639557.82 rows=3D7182082 width=3D0)<br>
> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0->=C2=A0 Parallel Seq Scan on allcalls_p20190601<br>
<br>
I'm not accustomed to partitioning, but all your scans are <Seq>u=
entials,<br>
so you might miss one/some index(es) - specialists will tell you more<br>
about that, but from what I skimmed from Keith link, my contentionis<br>
you're missing index(es.)<br>
<br>
Also, into Debian, there's a: "postgresql-12-hypopg" package =
that brings<br>
an extension with which you can create hypothetical indexes which in turn<b=
r>
will tell you if your queries need the real ones or not, this should<br>
also exist into your distro and could help.<br>
<br>
JY<br>
</blockquote></div></div></div>
--000000000000cdcd5105987ed0cb--