Bristol and Bath Linux: Bristol Digest, Vol 673, Issue 6

Send Bristol mailing list submissions to
bristol@mailman.lug.org.uk

To subscribe or unsubscribe via the World Wide Web, visit
https://mailman.lug.org.uk/mailman/listinfo/bristol
or, via email, send a message with subject or body 'help' to
bristol-request@mailman.lug.org.uk

You can reach the person managing the list at
bristol-owner@mailman.lug.org.uk

When replying, please edit your Subject line so it is more specific
than "Re: Contents of Bristol digest..."

Today's Topics:

1. Re: Postgres optimised h/w (mark chard)
2. Re: Postgres optimised h/w (Martin Moore)
3. Re: Postgres optimised h/w (mark chard)

----------------------------------------------------------------------

Message: 1
Date: Fri, 16 Dec 2016 12:10:25 +0000
From: mark chard <machard.1984@gmail.com>
To: Martin Moore <martinm@it-helps.co.uk>
Cc: Bristol and Bath Linux User Group <bristol@mailman.lug.org.uk>
Subject: Re: [bristol] Postgres optimised h/w
Message-ID:
<CAA7DJ2kj_uPY7Buru434UGrgVD+98X388=vBjn167F_+=Mw88A@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"

As my colleague mentioned, friends don't recommend that friends use
i386/686.

On 16 Dec 2016 11:55 am, "Martin Moore" <martinm@it-helps.co.uk> wrote:

I was advised to use 32bit for compatibility reasons IIRC. No problem to
go 64bit if it's advantageous and won't give me any grief with my s/w
though.

*From: *mark chard <machard.1984@gmail.com>
*Date: *Friday, 16 December 2016 at 11:45
*To: *Bristol and Bath Linux User Group <bristol@mailman.lug.org.uk>,
Martin Moore <martinm@it-helps.co.uk>
*Subject: *Re: [bristol] Postgres optimised h/w

Why are you using a 686-pae kernel?

On 16 Dec 2016 11:03 am, "Martin Moore via Bristol" <
bristol@mailman.lug.org.uk> wrote:

Morning all.

I have a potential requirement for a Debian/Postgres 9.3 server that will
handle a relatively high number of unindexable queries (co-ord based, I'll
look at GIS but not sure it'll help) that take some time – currently about
6 secs with one query (although that is with a lot of redundant data which
I'm currently removing). There could be 50k queries/day. Writes will be
minimum. I'll look at further tuning the query, but as it's a function I
can't seem to get any plan details for it ☹

So, 1st question – will the 4Gb per process limit of the 32bit pae kernel
limit the caching of the database which will be in the 10's of Gb.

Are SSD's now at a stage where they're suitable/reliable for this use – am
I safer using RAID 1 mdadm with 1 ssd and 1 hdd (how will it know use the
ssd for reads?)

And I presume as much RAM as will fit in the box!

Any other thoughts welcome.

Martin.

_______________________________________________
Bristol mailing list
Bristol@mailman.lug.org.uk
https://mailman.lug.org.uk/mailman/listinfo/bristol
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.lug.org.uk/mailman/private/bristol/attachments/20161216/3133c684/attachment-0001.html>

------------------------------

Message: 2
Date: Fri, 16 Dec 2016 12:16:30 +0000
From: Martin Moore <martinm@it-helps.co.uk>
To: Chris Boot <bootc@bootc.net>, Bristol and Bath Linux User Group
<bristol@mailman.lug.org.uk>
Subject: Re: [bristol] Postgres optimised h/w
Message-ID: <B28D4F68-609F-4259-9F7C-AF81BB768A19@it-helps.co.uk>
Content-Type: text/plain; charset="UTF-8"

OK, 64 bit it is!
Cost isn't a major issue for the drives – plus they don't need to be that big so I'll look at those mentioned.
Are most decent RAID controllers admin-able from a running system or is it a BIOS level thing still? Also need how-swap.

According to Wikipedia, RAID6 is no faster at reading the RAID1 – in which case RAID1 would be fine. Does this sound right?

On 16/12/2016, 11:33, "Chris Boot" <bootc@bootc.net> wrote:

Hi Martin,

I'm no PostgreSQL expert (particularly when it comes to GIS and indexes)
but it sounds like your biggest issue is figuring out how to index your
data properly. I'd definitely tackle that problem first, before
considering any shiny new hardware.

Assuming this is for a business, you would probably gain a tremendous
amount from hiring a PostgreSQL expert for a few hours' consultancy to
help you figure this out.

> So, 1st question – will the 4Gb per process limit of the 32bit pae kernel limit the caching of the database which will be in the 10's of Gb.

Yes, friends don't let friends use 32-bit Linux in 2016.

> Are SSD's now at a stage where they're suitable/reliable for this use – am I safer using RAID 1 mdadm with 1 ssd and 1 hdd (how will it know use the ssd for reads?)

Yes, SSDs are very reliable, even crappy desktop ones are good. That
being said, for a database server, I'd definitely recommend some of the
Intel DC S3520 (if you're doing more reading than writing) or S3710 (for
a more write-heavy workload).

Adding a hard disk will only serve to slow things down. SSDs are more
reliable than hard disks now too. Just don't.

The *only* downside of SSD now is cost.

I would also recommend a hardware RAID controller for a database: MD
RAID is great but far less flexible, and the more processing and IO you
can farm off to dedicated hardware the faster your database server will go.

> And I presume as much RAM as will fit in the box!

Yes, up to a point. If your database is 10GB and you're not doing that
much with temp tables and so on, 16GB might just be enough to squeeze
the whole thing into RAM. I'd probably go for 32GB and see how it goes.
No point going for 256GB+ if it's going to sit around unused.

> Any other thoughts welcome.

For new hardware, think carefully about CPUs. Intel is the only horse in
the race at the moment, so the choice is essentially Xeon E3 or Xeon E5.
E3 means fewer, faster cores while E5 means more slower cores. If you
don't have a very parallel workload and the 64GB RAM ceiling isn't an
issue, the cheaper E3 will be much faster for you. Faster E5s get
expensive very quickly, but you get lots of cores and can add obscene
amounts of RAM.

<plug>
My day job is designing, building and supporting Linux systems for
businesses. Send me a mail off-list if you're interested in what we can
do for you.
</plug>

Hope this helps!

Cheers,
Chris

--
Chris Boot
bootc@bootc.net

------------------------------

Message: 3
Date: Fri, 16 Dec 2016 12:18:22 +0000
From: mark chard <machard.1984@gmail.com>
To: Bristol and Bath Linux User Group <bristol@mailman.lug.org.uk>,
Martin Moore <martinm@it-helps.co.uk>
Cc: Chris Boot <bootc@bootc.net>
Subject: Re: [bristol] Postgres optimised h/w
Message-ID:
<CAA7DJ2nHgfzKHkkQhvveh_H0NsgBQGGyaLbDLZjYT_TWrwscnQ@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"

I admin my raid from the command line.

On 16 Dec 2016 12:17 pm, "Martin Moore via Bristol" <
bristol@mailman.lug.org.uk> wrote:

OK, 64 bit it is!
Cost isn't a major issue for the drives – plus they don't need to be that
big so I'll look at those mentioned.
Are most decent RAID controllers admin-able from a running system or is it
a BIOS level thing still? Also need how-swap.

According to Wikipedia, RAID6 is no faster at reading the RAID1 – in which
case RAID1 would be fine. Does this sound right?

On 16/12/2016, 11:33, "Chris Boot" <bootc@bootc.net> wrote:

Hi Martin,

I'm no PostgreSQL expert (particularly when it comes to GIS and indexes)
but it sounds like your biggest issue is figuring out how to index your
data properly. I'd definitely tackle that problem first, before
considering any shiny new hardware.

Assuming this is for a business, you would probably gain a tremendous
amount from hiring a PostgreSQL expert for a few hours' consultancy to
help you figure this out.

> So, 1st question – will the 4Gb per process limit of the 32bit pae
kernel limit the caching of the database which will be in the 10's of Gb.

Yes, friends don't let friends use 32-bit Linux in 2016.

> Are SSD's now at a stage where they're suitable/reliable for this use
– am I safer using RAID 1 mdadm with 1 ssd and 1 hdd (how will it know use
the ssd for reads?)

Yes, SSDs are very reliable, even crappy desktop ones are good. That
being said, for a database server, I'd definitely recommend some of the
Intel DC S3520 (if you're doing more reading than writing) or S3710 (for
a more write-heavy workload).

Adding a hard disk will only serve to slow things down. SSDs are more
reliable than hard disks now too. Just don't.

The *only* downside of SSD now is cost.

I would also recommend a hardware RAID controller for a database: MD
RAID is great but far less flexible, and the more processing and IO you
can farm off to dedicated hardware the faster your database server will
go.

> And I presume as much RAM as will fit in the box!

Yes, up to a point. If your database is 10GB and you're not doing that
much with temp tables and so on, 16GB might just be enough to squeeze
the whole thing into RAM. I'd probably go for 32GB and see how it goes.
No point going for 256GB+ if it's going to sit around unused.

> Any other thoughts welcome.

For new hardware, think carefully about CPUs. Intel is the only horse in
the race at the moment, so the choice is essentially Xeon E3 or Xeon E5.
E3 means fewer, faster cores while E5 means more slower cores. If you
don't have a very parallel workload and the 64GB RAM ceiling isn't an
issue, the cheaper E3 will be much faster for you. Faster E5s get
expensive very quickly, but you get lots of cores and can add obscene
amounts of RAM.

<plug>
My day job is designing, building and supporting Linux systems for
businesses. Send me a mail off-list if you're interested in what we can
do for you.
</plug>

Hope this helps!

Cheers,
Chris

--
Chris Boot
bootc@bootc.net

------------------------------

Subject: Digest Footer

_______________________________________________
Bristol mailing list
Bristol@mailman.lug.org.uk
https://mailman.lug.org.uk/mailman/listinfo/bristol

------------------------------

End of Bristol Digest, Vol 673, Issue 6
***************************************

Bristol and Bath Linux

Jumat, 16 Desember 2016

Bristol Digest, Vol 673, Issue 6

Tidak ada komentar:

Posting Komentar