Обсуждение: [PERFORM] More cores or higer frequency ?
Hello! I've heavy loaded PostgreSQL server, which I want to upgrade, so it will handle more traffic. Can I estimate what is better: more cores or higher frequency ? I expect that pg_stat should give some tips, but don't know where to start... best regards Jarek
The answer, as always, is "it depends."
Can you give us an overview of your setup? The appropriate setup for small numbers of long-running analytical queries (typically faster CPUs) will be different than a setup for handling numerous simultaneous connections (typically more cores).
But CPU is often not the limiting factor. With a better understanding of your needs, people here can offer suggestions for memory, storage, pooling, network, etc.
Cheers,
Steve
On Tue, May 23, 2017 at 11:29 AM, Jarek <jarek@poczta.srv.pl> wrote:
Hello!
I've heavy loaded PostgreSQL server, which I want to upgrade, so it will
handle more traffic. Can I estimate what is better: more cores or
higher frequency ? I expect that pg_stat should give some tips, but
don't know where to start...
best regards
Jarek
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Dnia 2017-05-23, wto o godzinie 11:39 -0700, Steve Crawford pisze: > The answer, as always, is "it depends." > > > Can you give us an overview of your setup? The appropriate setup for > small numbers of long-running analytical queries (typically faster > CPUs) will be different than a setup for handling numerous > simultaneous connections (typically more cores). I have pool of clients (~30) inserting to database about 50 records per second (in total from all clients) and small numer (<10) clients querying database for those records once per 10s. Other queries are rare and irregular. The biggest table has ~ 100mln records (older records are purged nightly). Database size is ~13GB. I near future I'm expecting ~150 clients and 250 inserts per second and more clients querying database. Server is handling also apache with simple web application written in python. For the same price, I can get 8C/3.2GHz or 14C/2.6GHz. Which one will be better ? > > But CPU is often not the limiting factor. With a better understanding > of your needs, people here can offer suggestions for memory, storage, > pooling, network, etc. > > > Cheers, > Steve > > > > On Tue, May 23, 2017 at 11:29 AM, Jarek <jarek@poczta.srv.pl> wrote: > Hello! > > I've heavy loaded PostgreSQL server, which I want to upgrade, > so it will > handle more traffic. Can I estimate what is better: more cores > or > higher frequency ? I expect that pg_stat should give some > tips, but > don't know where to start... > > best regards > Jarek > > > > -- > Sent via pgsql-performance mailing list > (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > >
On 23.05.2017 22:14, Jarek wrote: > I have pool of clients (~30) inserting to database about 50 records per > second (in total from all clients) and small numer (<10) clients > querying database for those records once per 10s. > Other queries are rare and irregular. > The biggest table has ~ 100mln records (older records are purged > nightly). Database size is ~13GB. > I near future I'm expecting ~150 clients and 250 inserts per second and > more clients querying database. > Server is handling also apache with simple web application written in > python. > For the same price, I can get 8C/3.2GHz or 14C/2.6GHz. Which one will be > better ? > > > Hi Jarek, in case of your increasing parallel requirements (more clients, more inserts), I would tend to the 14C setup. However, as usual take this with a grain of salt. My word is not a guarantee for success in this case. Best would be to setup a test scenario where you can simulate with both hardware setups. Regards, Sven
On Tue, May 23, 2017 at 2:14 PM, Jarek <jarek@poczta.srv.pl> wrote: > Dnia 2017-05-23, wto o godzinie 11:39 -0700, Steve Crawford pisze: >> The answer, as always, is "it depends." >> >> >> Can you give us an overview of your setup? The appropriate setup for >> small numbers of long-running analytical queries (typically faster >> CPUs) will be different than a setup for handling numerous >> simultaneous connections (typically more cores). > > I have pool of clients (~30) inserting to database about 50 records per > second (in total from all clients) and small numer (<10) clients > querying database for those records once per 10s. OK how many of those clients are typically hitting the db at the same time? If you see 3 or 4 clients at a time working with the rest idle that's a completely different load than if you've got 30 running near full throttle. I'd say build a simple synthetic workload that approximates your current work load and see what it does on a smaller machine first. If all 30 clients are keeping the db busy at once then definitely more cores. But also faster memory. A 32 core machine running ancient 800MHz memory is gonna get stomped by something with 16 cores running faster GHz while the memory is 2000MHz or higher. Always pay attention to memory speed, esp in GB/s etc. DB CPUs are basically mostly data pumps, moving data as fast as possible from place to place. Bigger internal piping means just as much as core speed and number of cores. Also if all 30 clients are working hard then see how it runs with a db pooler. You should be able to find the approximate knee of performance by synthetic testing and usually best throughput will be at somewhere around 1x to 2x # of cores. Depending on io and memory. > Other queries are rare and irregular. > The biggest table has ~ 100mln records (older records are purged > nightly). Database size is ~13GB. > I near future I'm expecting ~150 clients and 250 inserts per second and OK so yeah definitely look at connection pooling. You don't want to start out handling 150 backends on any server if you don't have to. Performance-wise a 14c machine fall off a cliff by 28 or so active connections. > more clients querying database. OK if you're gonna let users throw random sql at it, then you need connection pooling even more. Assuming writes have a priority then you'd want to limit reads to some number of cores etc to keep it out of your hair. > Server is handling also apache with simple web application written in > python. > For the same price, I can get 8C/3.2GHz or 14C/2.6GHz. Which one will be > better ? CPU names / models please. If intel look up on arc, look for memory bandwidth. > > > or so >> >> But CPU is often not the limiting factor. With a better understanding >> of your needs, people here can offer suggestions for memory, storage, >> pooling, network, etc. >> >> >> Cheers, >> Steve >> >> >> >> On Tue, May 23, 2017 at 11:29 AM, Jarek <jarek@poczta.srv.pl> wrote: >> Hello! >> >> I've heavy loaded PostgreSQL server, which I want to upgrade, >> so it will >> handle more traffic. Can I estimate what is better: more cores >> or >> higher frequency ? I expect that pg_stat should give some >> tips, but >> don't know where to start... >> >> best regards >> Jarek >> >> >> >> -- >> Sent via pgsql-performance mailing list >> (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> >> > > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- To understand recursion, one must first understand recursion.
On Tue, May 23, 2017 at 1:49 PM, Sven R. Kunze <srkunze@mail.de> wrote:
On 23.05.2017 22:14, Jarek wrote:I have pool of clients (~30) inserting to database about 50 records per
second (in total from all clients) and small numer (<10) clients
querying database for those records once per 10s.
Other queries are rare and irregular.
The biggest table has ~ 100mln records (older records are purged
nightly). Database size is ~13GB.
I near future I'm expecting ~150 clients and 250 inserts per second and
more clients querying database.
Server is handling also apache with simple web application written in
python.
For the same price, I can get 8C/3.2GHz or 14C/2.6GHz. Which one will be
better ?
I would start by trying a few things on your existing equipment.
If your inserts are coming from individual connections, say, via the web-app in a connect-insert-disconnect fashion then pooling can be a huge win. Connection overhead is a bigger factor than you might imagine and I've seen as much as a 10x improvement in small queries when pooling was added.
If the every-10-second queries are running on the recently inserted data then partitioning by time range could substantially improve the speed of inserts, queries and purging. It's pretty easy to do, now, with pg_partman or similar but built-in auto-partitioning is coming in version 10.
Fast commit to disk is a win - think SSD or RAID with BBU cache and with a relatively modest 13GB database you should be able to spec enough RAM to keep everything in memory.
Cheers,
Steve
Are you already on SSDs? That will be the dominant factor I think. Then memory.... After that, more cores are good for parallelism(especially with 9.6, although that requires solid memory support). Faster cores will be better if you expectcomplex calculations in memory, i.e., some analytics perhaps, but for your fairly straightforward write-throughputscenario, I think SSDs and memory will be king. LDH -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jarek Sent: Tuesday, May 23, 2017 14:30 To: pgsql-performance@postgresql.org Subject: [PERFORM] More cores or higer frequency ? Hello! I've heavy loaded PostgreSQL server, which I want to upgrade, so it will handle more traffic. Can I estimate what is better:more cores or higher frequency ? I expect that pg_stat should give some tips, but don't know where to start... best regards Jarek -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance