Re: Slow response to my query

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 <lazyvirus(at)gmx(dot)com> wrote:

> On Fri, 29 Nov 2019 16:47:44 +0100
> Goke Aruna <goksie(at)gmail(dot)com> 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
>