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 (cms)
2. Re: Swapfile by default instead of partiton for... (Amias Channer)
3. Re: Postgres optimised h/w (Martin Moore)
4. Re: Postgres optimised h/w (Jonathan Stone)
5. Re: Postgres optimised h/w (Martin Moore)
6. Fwd: Last LUG Meeting of 2016 this Saturday! Tommorow!
(Samir Benmendil)
----------------------------------------------------------------------
Message: 1
Date: Sat, 17 Dec 2016 12:06:36 +0000
From: cms <cms@beatworm.co.uk>
To: Martin Moore <martinm@it-helps.co.uk>, Bristol and Bath Linux User
Group <bristol@mailman.lug.org.uk>
Subject: Re: [bristol] Postgres optimised h/w
Message-ID: <4349cf96-8f38-9bd7-f0c9-7f038fcd07ac@beatworm.co.uk>
Content-Type: text/plain; charset=utf-8; format=flowed
On 16/12/16 11:01, Martin Moore via Bristol 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.
The 32 bit restriction is interesting in 2016. Is it because you're
locked into architecture dependent binary data pages and don't have the
option to dump and restore to a fresh build? You could replicate to a
warm spare and switch over that way, a full database rebuild doesn't
have to mean outage for the duration of the rebuild.
I think it's very unlikely that your database workload will be either
CPU or IO bound, so worrying about the minutiae of hardware storage
configuration is shaving the wrong end of the yak. 50k transactions a
day and no writes is trivial, you could probably do that on a netbook!
The general critical performance path to do for postgresql is keep the
synchronous WAL throughput isolated from the general system IO -
typically on a separate filesystem, maybe on a different disk
controller, and ensure that has enough IOPs to keep up with your write
load and checkpointing. You need to keep your shared buffers low, your
concurrent connection count ideally smaller than your cpu core count,
and then let linux buffer cache soak up all the data IO. More system RAM
than your working set if you can.
I am also raising an eyebrow at 'unindexable'. PG indexing is really
flexible, and sophisticated in a way that extends way beyond 'btree on
fixed size atomic value' you can define your own index types even, if
you have a particularly weird requirement. (If you are doing spatial
co-ordinates and geometrical sets then yes postgis will definitely help)
Indexing is all about reducing the set size as early as you can, so
there may be additional range fields you can use to partition the search
space. If you can make the sequential sub scans smaller than buffer
cache you're still going to win much harder than any hard disk type.
With regards to reliability and availability, I think again, its 2017,
you should probably be worrying less about defensive hardware planning
for uptime and more about service level rendundancy - given that it's
just a few dozen GB of data and no throughput, many cheap replicas in a
HA arrangement with health monitoring and automated replacement will
beat the hardware failure odds, especially if you scatter it through
'the cloud'.
--
Regards,
Colin M. Strickland, cms, 'that guy'
------------------------------
Message: 2
Date: Sat, 17 Dec 2016 12:14:20 +0000
From: Amias Channer <me@amias.net>
To: Sebastian <sebsebseb_mageia@gmx.com>, Bristol and Bath Linux User
Group <bristol@mailman.lug.org.uk>
Subject: Re: [bristol] Swapfile by default instead of partiton for...
Message-ID:
<CAMgU7XVzzMP6AcTDzHwjyUKVe7e1bodF2fQnczDmRVObtV92CQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Hello luggers,
This is an interesting choice , i presume it won't prevent suspend to
disk from resuming properly.
There isn't any difference in speed between swap files and partitions,
unless you are using non solid state storage and need to position the
swap partition at the beginning, something thats very tricky to
achieve and maintain with a swapfile.
I'm not sure there is much to gain from reducing partition counts now
that msdos partition tables are mostly a thing of the past , gpt and
lvm allow as many partitions as anyone will need.
My guess is this is driven by greater ubuntu usage in virtual
environments where swapping is less common due to the different way
workloads are deployed and the fact that the hypervisors do crazy
things with memory pages compared to kernels.
Cheers
Amias
On 16 December 2016 at 19:32, Sebastian via Bristol
<bristol@mailman.lug.org.uk> wrote:
> HI
>
> Been some intereresting news today, Ubuntu news, that for Ubuntu 17.04
> will be a swapfile by default instead of a partition. Not quite sure how
> wthat would work with multiple distros if stored inside Ubuntu. Also
> looking at where I found out again, some comments saying how that woudn't
> really work with BTFS it seems. Anyway wanted to share here for some
> general comments to do with that, (and actually doing so now, was going to
> maybe earlier on it's own or later on in another email),
> http://www.omgubuntu.co.uk/2016/12/ubuntu-17-04-drops-swaps-swap-partitions-swap-files
>
> Regards
>
> Sebastian
>
>
> --
> Sent using Dekko from my Ubuntu device
>
> _______________________________________________
> Bristol mailing list
> Bristol@mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/bristol
------------------------------
Message: 3
Date: Sat, 17 Dec 2016 12:14:22 +0000
From: Martin Moore <martinm@it-helps.co.uk>
To: Bristol and Bath Linux User Group <bristol@mailman.lug.org.uk>
Subject: Re: [bristol] Postgres optimised h/w
Message-ID: <BACC5AB8-1B49-4050-B6CE-EF8E0683534D@it-helps.co.uk>
Content-Type: text/plain; charset="UTF-8"
On 17/12/2016, 12:06, "cms" <cms@beatworm.co.uk> wrote:
> The 32 bit restriction is interesting in 2016. Is it because you're
> locked into architecture dependent binary data
No – it's other executables, DB can just be dumped and restored.
> I think it's very unlikely that your database workload will be either
> CPU or IO bound, so worrying about the minutiae of hardware storage
> configuration is shaving the wrong end of the yak. 50k transactions a
> day and no writes is trivial, you could probably do that on a netbook!
Not if the queries take a few seconds on a fairly average server!! Also it's not just the ability to keep up with demand, it's how long the user has to wait.
> The general critical performance path to do for postgresql is keep the
> synchronous WAL throughput isolated from the general system IO -
> typically on a separate filesystem, maybe on a different disk
> controller, and ensure that has enough IOPs to keep up with your write
> load and checkpointing.
Not much writing going on so shouldn't be an issue
> I am also raising an eyebrow at 'unindexable'.
The main issue is determining if two rectangles overlap. Not sure how to index that.
Some interesting stuff coming out of this – keep it up!
--
Regards,
Colin M. Strickland, cms, 'that guy'
------------------------------
Message: 4
Date: Sat, 17 Dec 2016 13:49:16 +0000
From: Jonathan Stone <lug@jonstone.org.uk>
To: Martin Moore via Bristol <bristol@mailman.lug.org.uk>
Subject: Re: [bristol] Postgres optimised h/w
Message-ID: <20161217134916.GA972@discordia.qaotiq.co.uk>
Content-Type: text/plain; charset=iso-8859-1
On Sat, Dec 17, 2016 at 12:14:22PM +0000, Martin Moore via Bristol wrote:
> On 17/12/2016, 12:06, "cms" <cms@beatworm.co.uk> wrote:
>> I think it's very unlikely that your database workload will be either
>> CPU or IO bound, so worrying about the minutiae of hardware storage
>> configuration is shaving the wrong end of the yak. 50k transactions a
>> day and no writes is trivial, you could probably do that on a netbook!
>
> Not if the queries take a few seconds on a fairly average server!!
> Also it???s not just the ability to keep up with demand, it???s how
> long the user has to wait.
If the goal is to reduce the users' response times, then first you
should to get a feel for where the time is being spent today. Is
most of the time being spent waiting on physical IO, on CPU scanning
through the data or on the network throughput/latency getting the
queries and results to and from the database server?
Once you know that you can start to make decisions backed by data.
If most of the time is spent on IO, then either cache more by buying
more RAM, or increase the throughput of the IO system by striping
across more disks or using flash. If most of the time is spent on
CPU, then can the work be parallelised across multiple cores? If
not, then you want a CPU with good single threaded performance in
preference to one with many cores.
The biggest improvements are likely to come from reducing the amount
of work necessary to respond to the user through indexing, pre-computing
or caching parts of the response, etc.
Regards,
Jonathan
------------------------------
Message: 5
Date: Sat, 17 Dec 2016 14:07:19 +0000
From: Martin Moore <martinm@it-helps.co.uk>
To: Bristol and Bath Linux User Group <bristol@mailman.lug.org.uk>
Subject: Re: [bristol] Postgres optimised h/w
Message-ID: <6ED44088-A069-4277-BD26-B39E366223D1@it-helps.co.uk>
Content-Type: text/plain; charset="UTF-8"
On 17/12/2016, 13:49, "Bristol on behalf of Jonathan Stone via Bristol" <bristol-bounces@mailman.lug.org.uk on behalf of bristol@mailman.lug.org.uk> wrote:
> If the goal is to reduce the users' response times, then first you
should to get a feel for where the time is being spent today. Is
most of the time being spent waiting on physical IO, on CPU scanning
through the data or on the network throughput/latency getting the
queries and results to and from the database server?
It's the actual query – lots of table scans.
------------------------------
Message: 6
Date: Sat, 17 Dec 2016 14:58:33 +0000
From: Samir Benmendil <me@rmz.io>
To: Sebastian via Bristol <bristol@mailman.lug.org.uk>
Subject: [bristol] Fwd: Last LUG Meeting of 2016 this Saturday!
Tommorow!
Message-ID:
<CABOikHG+2exoqQ=VieMk+uYQ4N9Y7r7cY4dohBPkTvMF_1NUuw@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"
Anyone there already? Couldn't find anyone.
On Friday, 16 December 2016, Sebastian via Bristol <
bristol@mailman.lug.org.uk
<javascript:_e(%7B%7D,'cvml','bristol@mailman.lug.org.uk');>> wrote:
> Hi
>
> WOW what a great turn out, WOW, WOW, indeed, what a great turn out, for
> last months November LUG meeting. I remember emailing here on the list
> about how it would be basically good to have a 10 people turn out, and we
> had like 11, including ourselves people who turned up Three from the event
> the previous month it seems as well, well ok two don't really count I guess
> then since were already on the mailing list apparnatly, but still one guy
> did come in from the event. That was good, after running the event and the
> type of event it was, and general turn out it ended up getting, and of
> what kind of people. The big question would be though, can we have a
> about 10 people turn out at the LUG meeting tommorow as well or not? 10
> people turn out is rather impressive, since a lot of people don't come to
> the meetings anymore or rather rarely, that are still on this list. Its not
> just, because quite a few people have moved away from the general, big area
> of, Bristol, Bath, and sourunding areas. Yes our LUG may be mostly social
> chat now for the meetings, rather than helping people with basic to
> advanced support issues, but thats since Desktop Linux is generally much
> easier to use now for most distros, than it used to be, and its been like
> that for many years now.. I been to the LUG meetings since I think it was
> April 2012 I think last month was the biggest turn out of people that
> have ever had it in that time, whilst I have been there, or if not for
> quite a while.
> I would like to meet some of the people who used to come to the LUG
> meetings a lot this Saturday ideally, but I think to keep the in person
> side of this LUG nicely alive, with good turn outs of people for most LUG
> meetings etc, its good now to do things at times that will hopefuly bring
> new people into them, such as events. Next year 2017 should have three
> events it seems, two Linux Presentaiton Day events, and one bigger event
> organised by someone else as well it seems, with public speaking even.
> Which would be quite impressive right?
>
> Tommorow is the Christmas meeting at the Knight's Templar hence a week
> earlier than usual as well, we should be sitting at the table in the left
> hand side corner by the plugs at the back of the pub on the lower level as
> usuaul, or near there instead if someone else not us is already there. The
> first one of us will probably arrive at about 2pm, but for anyone who may
> be new that might be coming tommorow I would suggest not turning up untill
> 2:30pm by the earliest, or may be there on own. As usaul I hope to meet
> new people at the LUG meeting, and I probably won't be there untill about
> 3pm myself, and then laving late about 6pm/7pm. What about other people
> who are intending on coming tommorow? We can eat Christmas dinner there
> tommorow as well, already had some last month, but will again ;). Also I
> been wondering about Samir and what plan he actusally booked for FOSDEM,
> and if our newer person from the event, who is hopefuly on this list now,
> is intending on coming still or not as well.
>
> Anyone got some USB's to bring along tommorow? Yes Linux Live USB's!
> Ideally of more fun distros to so not just common things like Fedora and
> OpenSUSE, and Ubuntu 16.10, but yes please someone bring those along to.
> Oh and the USB's I am after must have support for UEFI. I guess the USB's
> don't have to be Linux though. You see I have bought a interesting mini
> PC, that was going to crowd fund earlier this the year, but didn't reallly
> have the spare money then. The pre installed Windows 10 didn't last that
> long on there at all, more details about that at the LUG meeting
> tommorow. I had a USB stick with Ubuntu 16.04 on it, so put that on.
> Ubuntu 16.04 mostly works on it, altough with some hardware issues, because
> of how the device was only really intended to be made for Windows 10, but
> considering that and how much still just works, that's quite good really.
> For a few things can probably fix onself though. More details tommorow at
> the LUG meeting.
>
> 2016 Regards
>
> Sebastian
>
>
> --
> Sent using Dekko from my Ubuntu device
>
> _______________________________________________
> Bristol mailing list
> Bristol@mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/bristol
--
Samir Benmendil
"[The World Wide Web is] the only thing I know of whose shortened form —
www — takes three times longer to say than what it's short for." Douglas
Adams
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.lug.org.uk/mailman/private/bristol/attachments/20161217/3e76b5fa/attachment.html>
------------------------------
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 12
****************************************
Tidak ada komentar:
Posting Komentar