Обсуждение: Advice on machine specs for growth
[I sent this to the performance list a couple of days ago and received
no replies. Apologies for the cross-post]
We are looking to upgrade our current database server infrastructure so
that it is suitable for the next 3 years or so.
Presently we have two physical servers with the same specs:
- 220GB database partition on RAID10 SSD on HW RAID
- 128GB RAM
- 8 * Xeon E5-2609
(The HW RAID card is a MegaRAID SAS 9361-8i with BBU)
The second server is a hot standby to the first, and we presently have
about 350 databases in the cluster.
We envisage needing about 800GB of primary database storage in the next
three years, with 1000 databases in the cluster.
We are imagining either splitting the cluster into two and (to have four
main servers) or increasing the disk capacity and RAM in each server.
The second seems preferable from a day-to-day management basis, but it
wouldn't be too difficult to deploy our software upgrades across two
machines rather than one.
Resources on the main machines seem to be perfectly adequate at present
but it is difficult to know at what stage queries might start spilling
to disk. We presently occasionally hit 45% CPU utilisation, load average
peaking at 4.0 and we occasionally go into swap in a minor way (although
we can't determine the reason for going into swap). There is close to no
iowait in normal operation.
It also seems a bit incongruous writing about physical machines these
days, but I can't find pricing on a UK data protection compatible cloud
provider that beats physical price amortised over three years (including
rack costs). The ability to more easily "make" machines to help with
upgrades is attractive, though.
Some comments and advice on how to approach this would be very
gratefully received.
Thanks
Rory
Rory Campbell-Lange wrote: > We are looking to upgrade our current database server infrastructure so > that it is suitable for the next 3 years or so. > > We envisage needing about 800GB of primary database storage in the next > three years, with 1000 databases in the cluster. 1000 is a lot, but should still be ok. > We are imagining either splitting the cluster into two and (to have four > main servers) or increasing the disk capacity and RAM in each server. > The second seems preferable from a day-to-day management basis, but it > wouldn't be too difficult to deploy our software upgrades across two > machines rather than one. If you can scale horizontally by splitting the load across several independent database servers, then do so by all means. This may be more administration work initially, but scaling will come easy when you need it. It is much more difficult to scale a single monolithic database server. > Resources on the main machines seem to be perfectly adequate at present > but it is difficult to know at what stage queries might start spilling > to disk. We presently occasionally hit 45% CPU utilisation, load average > peaking at 4.0 and we occasionally go into swap in a minor way (although > we can't determine the reason for going into swap). There is close to no > iowait in normal operation. Disable memory overcommit and set swappiness to 0 on database servers. > It also seems a bit incongruous writing about physical machines these > days, but I can't find pricing on a UK data protection compatible cloud > provider that beats physical price amortised over three years (including > rack costs). The ability to more easily "make" machines to help with > upgrades is attractive, though. I think physical machines are cool. The resulting system becomes simpler with fewer dependencies, and it is much easier to debug performance problems. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On 18/09/18, Laurenz Albe (laurenz.albe@cybertec.at) wrote: > Rory Campbell-Lange wrote: > > We are looking to upgrade our current database server infrastructure so > > that it is suitable for the next 3 years or so. > > > > We envisage needing about 800GB of primary database storage in the next > > three years, with 1000 databases in the cluster. > > 1000 is a lot, but should still be ok. ... > It is much more difficult to scale a single monolithic database server. ... > Disable memory overcommit and set swappiness to 0 on database servers. Thanks for your advice, Laurenz. As a matter of interest, is 800GB considered "large"? I appreciate that, given your advice, it might be better to start dividing up the load now rather than, say, in three years, partly so we become adept at working across more than one server. Rory
> Disable memory overcommit and set swappiness to 0 on database servers.
Just to be clear, are you suggesting (on linux) setting overcommit_memory = 2 but leaving overcommit_ratio alone, which defaults to 50%?
I’ve tried setting them to 2 and 0% respectively in the past and it didn’t end well…
Also I’ve read, and now use, swappiness = 1 which is supposed to disable swapping entirely except when not swapping would cause OOM.
Any thoughts on that?
Cheers,
Steve.
Steven Winfield wrote: > Just to be clear, are you suggesting (on linux) setting overcommit_memory = 2 > but leaving overcommit_ratio alone, which defaults to 50%? > I’ve tried setting them to 2 and 0% respectively in the past and it didn’t end well… Yes, vm.overcommit_memory = 2 As for vm.overcommit ratio, set it to 100 * (RAM - swap) / RAM That is because Linux will commit memory up to swap + overcommit_ratio * RAM / 100, and you don't want to commit more than the available RAM. > Also I’ve read, and now use, swappiness = 1 which is supposed to disable swapping > entirely except when not swapping would cause OOM. > Any thoughts on that? My thought is vm.swappiness = 0 If you don't overcommit memory, you should never have to swap. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com