Re: Slow response to my query

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

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