Обсуждение: How many threads/cores Postgres can utilise?
Dears,
Sorry to be a royal pain, but I cannot find it anywhere in the
documentation: how many threads/CPU cores Postgres v. 8.4 can utilise?
We're thinking about installing Postgres on a virtual machine (RedHat 5
64-bits), however not sure how many CPUs can be wisely assigned, without
wasting of resources. Can Postgres utilise multi-core/multi-threaded
architecture in a reasonably extent?
Cheers, Pete
This email (and attachments) are confidential and intended for the addressee(s) only. If you are not the intended recipient please notify the sender, delete any copies and do not take action in reliance on it. Any views expressed are the author's and do not represent those of IOP, except where specifically stated. IOP takes reasonable precautions to protect against viruses but accepts no responsibility for loss or damage arising from virus infection. For the protection of IOP's systems and staff emails are scanned automatically.”
Institute of Physics Registered in England under Registration No 293851
Registered Office: 76/78 Portland Place, London W1B 1NT
On 28/04/10 17:15, Piotr Kublicki wrote: > > > > Dears, > > Sorry to be a royal pain, but I cannot find it anywhere in the > documentation: how many threads/CPU cores Postgres v. 8.4 can utilise? > We're thinking about installing Postgres on a virtual machine (RedHat 5 > 64-bits), however not sure how many CPUs can be wisely assigned, without > wasting of resources. Can Postgres utilise multi-core/multi-threaded > architecture in a reasonably extent? PostgreSQL is good at using multiple cores for multiple concurrent queries/clients, but is almost completely incapable of using multiple cores to speed up single big queries. If your workload is "lots and lots of simpler queries" then you want many cores. If you workload is a few big complex queries, you want fewer cores but very fast ones. About the only benefit that a single big query gets from spare cores is that the OS can do some other housekeeping work using the other core(s). -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/
On Wed, Apr 28, 2010 at 3:15 AM, Piotr Kublicki <Piotr.Kublicki@iop.org> wrote: > > Dears, > > Sorry to be a royal pain, but I cannot find it anywhere in the > documentation: how many threads/CPU cores Postgres v. 8.4 can utilise? > We're thinking about installing Postgres on a virtual machine (RedHat 5 > 64-bits), however not sure how many CPUs can be wisely assigned, without > wasting of resources. Can Postgres utilise multi-core/multi-threaded > architecture in a reasonably extent? Like Craig mentioned, each connection uses one core basically, and the OS can use one or maybe two. But that means that on even moderately busy servers 4 to 8 cores is very reasonable. On modern hardware it's easy to get 6 or 8 cores pretty cheaply. 2P machines can have 12 or 16 cores for pretty cheap too. Pgsql will get faster quickly as you increase parallel load to the number of cores you have (assuming enough memory bw to keep up) and slowly trail off as you add concurrent connections. If you're likely to have hundreds of concurrent connections then adding more cores past 8 or 16 makes a lot of sense. AMD's magny cours look promising for that role. You can build a 4P machine with 48 cores for a pretty reasonable price.
On 28/04/10 18:25, Scott Marlowe wrote: > On Wed, Apr 28, 2010 at 3:15 AM, Piotr Kublicki <Piotr.Kublicki@iop.org> wrote: >> >> Dears, >> >> Sorry to be a royal pain, but I cannot find it anywhere in the >> documentation: how many threads/CPU cores Postgres v. 8.4 can utilise? >> We're thinking about installing Postgres on a virtual machine (RedHat 5 >> 64-bits), however not sure how many CPUs can be wisely assigned, without >> wasting of resources. Can Postgres utilise multi-core/multi-threaded >> architecture in a reasonably extent? > > Like Craig mentioned, each connection uses one core basically, and the > OS can use one or maybe two. But that means that on even moderately > busy servers 4 to 8 cores is very reasonable. On modern hardware it's > easy to get 6 or 8 cores pretty cheaply. 2P machines can have 12 or > 16 cores for pretty cheap too. > > Pgsql will get faster quickly as you increase parallel load to the > number of cores you have (assuming enough memory bw to keep up) and > slowly trail off as you add concurrent connections. If you're likely > to have hundreds of concurrent connections then adding more cores past > 8 or 16 makes a lot of sense. ... if you expect them to all be actually doing work. Often people with huge connection counts have mostly idle connections. In this case they really need to use a connection pooler, and limit the actual live connections to Pg its self to something more reasonable. More cores never hurts, but if your workload isn't all that high you may actually not need them. Even if the connections *aren't* mostly idle and are all competing to get work done, then as Scott says better throughput can usually be achieved with many tens of connections and a connection pooler than with many hundreds of connections. In this case, you not only need more CPU cores but more disk I/O (more and faster disk spindles) to improve your throughput. It's worth realizing that Pg by its self doesn't scale all that well to very large numbers of connections as it has fairly high per-connection overheads (backend instance ram use, inter-backend communication, lock management, etc). It works extremely well with server-side connection poolers, though, so in practice it can be happily used with huge connection counts. (I suspect that most DBs that *do* scale really well to high connection counts really just do internal connection pooling, separating connection state housekeeping from query execution. This would be a nice-to-have but probably isn't practical without a multi-threaded single-process query execution core and that's not a nice notion with Pg's architecture.) -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/
On Wed, Apr 28, 2010 at 6:14 AM, Craig Ringer <craig@postnewspapers.com.au> wrote: > On 28/04/10 18:25, Scott Marlowe wrote: >> On Wed, Apr 28, 2010 at 3:15 AM, Piotr Kublicki <Piotr.Kublicki@iop.org> wrote: >>> >>> Dears, >>> >>> Sorry to be a royal pain, but I cannot find it anywhere in the >>> documentation: how many threads/CPU cores Postgres v. 8.4 can utilise? >>> We're thinking about installing Postgres on a virtual machine (RedHat 5 >>> 64-bits), however not sure how many CPUs can be wisely assigned, without >>> wasting of resources. Can Postgres utilise multi-core/multi-threaded >>> architecture in a reasonably extent? >> >> Like Craig mentioned, each connection uses one core basically, and the >> OS can use one or maybe two. But that means that on even moderately >> busy servers 4 to 8 cores is very reasonable. On modern hardware it's >> easy to get 6 or 8 cores pretty cheaply. 2P machines can have 12 or >> 16 cores for pretty cheap too. >> >> Pgsql will get faster quickly as you increase parallel load to the >> number of cores you have (assuming enough memory bw to keep up) and >> slowly trail off as you add concurrent connections. If you're likely >> to have hundreds of concurrent connections then adding more cores past >> 8 or 16 makes a lot of sense. > > ... if you expect them to all be actually doing work. > > Often people with huge connection counts have mostly idle connections. > In this case they really need to use a connection pooler, and limit the > actual live connections to Pg its self to something more reasonable. > More cores never hurts, but if your workload isn't all that high you may > actually not need them. I was definitely referring to active connections.
On 28/04/10 20:20, Scott Marlowe wrote: > I was definitely referring to active connections. Sure - and I didn't mean to sound like I was trying to correct you. Sorry if I did. Just trying to add a little more info. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/
Scott Marlowe wrote: > On Wed, Apr 28, 2010 at 3:15 AM, Piotr Kublicki <Piotr.Kublicki@iop.org> wrote: > >> Dears, >> >> Sorry to be a royal pain, but I cannot find it anywhere in the >> documentation: how many threads/CPU cores Postgres v. 8.4 can utilise? >> We're thinking about installing Postgres on a virtual machine (RedHat 5 >> 64-bits), however not sure how many CPUs can be wisely assigned, without >> wasting of resources. Can Postgres utilise multi-core/multi-threaded >> architecture in a reasonably extent? >> > > Like Craig mentioned, each connection uses one core basically, and the > OS can use one or maybe two. But that means that on even moderately > busy servers 4 to 8 cores is very reasonable. On modern hardware it's > easy to get 6 or 8 cores pretty cheaply. 2P machines can have 12 or > 16 cores for pretty cheap too. > the author mentions virtual machines, where you're trying to squeeze as much workload as possible onto those 8 or 12 cores ...
On Wed, Apr 28, 2010 at 5:15 AM, Piotr Kublicki <Piotr.Kublicki@iop.org> wrote: > Sorry to be a royal pain, but I cannot find it anywhere in the > documentation: how many threads/CPU cores Postgres v. 8.4 can utilise? > We're thinking about installing Postgres on a virtual machine (RedHat 5 > 64-bits), however not sure how many CPUs can be wisely assigned, without > wasting of resources. Can Postgres utilise multi-core/multi-threaded > architecture in a reasonably extent? postgresql will gladly munch on everything you throw at it unless you are running a single user app. how many cores to assign has more to do with the VM technology you are using than anything else. merlin
On Wed, Apr 28, 2010 at 6:30 AM, Craig Ringer <craig@postnewspapers.com.au> wrote: > On 28/04/10 20:20, Scott Marlowe wrote: > >> I was definitely referring to active connections. > > Sure - and I didn't mean to sound like I was trying to correct you. > Sorry if I did. Just trying to add a little more info. No offense taken at all. Just clarifying was all.
Piotr Kublicki wrote: > We're thinking about installing Postgres on a virtual machine (RedHat 5 > 64-bits), however not sure how many CPUs can be wisely assigned, without > wasting of resources. The database will use as many cores as you have available, so long as you have multiple simultaneous queries to keep each of them busy--no single query will use more than one core. In practice, on a VM install you may discover you're limited by either I/O rate or VM overhead long before you reach the scalability limits of the database though. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
Greg Smith wrote:
> Piotr Kublicki wrote:
> > We're thinking about installing Postgres on a virtual machine (RedHat 5
> > 64-bits), however not sure how many CPUs can be wisely assigned, without
> > wasting of resources.
>
> The database will use as many cores as you have available, so long as
> you have multiple simultaneous queries to keep each of them busy--no
> single query will use more than one core. In practice, on a VM install
> you may discover you're limited by either I/O rate or VM overhead long
> before you reach the scalability limits of the database though.
I thought we had an FAQ item on this topic, but it seems it was removed
or was never there. :-(
I have added one:
http://wiki.postgresql.org/wiki/FAQ#How_does_PostgreSQL_use_CPU_resources.3F
I also added information to the FAQ about focusing on I/O and memory
issues before CPU:
http://wiki.postgresql.org/wiki/FAQ#What_computer_hardware_should_I_use.3F
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com