Thread: PostgreSQL on Dual Processors, Dual-Core AMD Chips
Hello Everyone,
Does anyone know if PostgreSQL supports running on dual processors, and will efficiently utilize the dual processors for performance? More specifically, will PostgreSQL utilize the dual processor performance of using dual-core AMD chips? Any information would be helpful...
Thank you!
- Onyx
---
Onyx Mueller
Software Engineer
i-cubed : information integration & imaging LLC
201 Linden Street : Third Floor
Fort Collins, CO 80524
970-482-4400 voice
970-482-4499 fax
www.i3.com
On Tue, 2005-10-18 at 16:35, Onyx wrote: > Hello Everyone, > Does anyone know if PostgreSQL supports running on dual processors, > and will efficiently utilize the dual processors for performance? > More specifically, will PostgreSQL utilize the dual processor > performance of using dual-core AMD chips? Any information would be > helpful... Yes. As long as it's on an OS that supports SMP, PostgreSQL can use >1 CPU. However, it each process can only use ONE CPU at at time, so if you only are running batch files or other single user type things, then the second CPU may not be as much use. OTOH, only certain data warehouse queries could really benefit from spreading a single query across multiple processors, so it's not a big deal for 95% of all postgresql installs. Note that there's usually plenty for the OS to do, so even in the case of datawarehousing, the second CPU often makes a marked difference in performance.
On Oct 18, 2005, at 4:35 PM, Onyx wrote: > Hello Everyone, > Does anyone know if PostgreSQL supports running on dual processors, > and will efficiently utilize the dual processors for performance? > More specifically, will PostgreSQL utilize the dual processor > performance of using dual-core AMD chips? Any information would be > helpful... > > Thank you! Each connection is a separate process, so yes it will, assuming multiple connections to the backend. There are numerous threads in the archives on why we don't use threading. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611
"Onyx" <omueller@i3.com> writes: > Hello Everyone, > Does anyone know if PostgreSQL supports running on dual processors, and > will efficiently utilize the dual processors for performance? More > specifically, will PostgreSQL utilize the dual processor performance of > using dual-core AMD chips? Any information would be helpful... Each session is an independent non-threaded process, so you won't get any speedup on a single query. But multiple sessions can be scheduled simultaneously on different processors, so in the usual case of many client connections it will definitely help. -Doug
Hi Onyx, From what i understand Postgresql will scale with more cpus, but not in the same way as threaded server would. I believe that as each process(backend) is created it will get assigned to a CPU. From what I can tell it works in a similar fashion to the Firebird classic server. Hopefully a developer will chime in and add/correct me :-) Thanks, Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightnint Admin Onyx wrote: > Hello Everyone, > Does anyone know if PostgreSQL supports running on dual processors, > and will efficiently utilize the dual processors for performance? > More specifically, will PostgreSQL utilize the dual processor > performance of using dual-core AMD chips? Any information would be > helpful... > > Thank you! > > > - Onyx > --- > Onyx Mueller > Software Engineer > i-cubed : information integration & imaging LLC > 201 Linden Street : Third Floor > Fort Collins, CO 80524 > 970-482-4400 voice > 970-482-4499 fax > _www.i3.com_ <file://www.i3.com> > >
Onyx wrote: > Hello Everyone, > Does anyone know if PostgreSQL supports running on dual processors, and > will efficiently utilize the dual processors for performance? More > specifically, will PostgreSQL utilize the dual processor performance of > using dual-core AMD chips? Any information would be helpful... We've got a 2x265 production server that runs like gangbusters. This was a CPU drop-in replacement upgrade so we were able to do some simple testing of 1x265 versus 2x244 (both setups w/ 2 1.8GHz cores). The 1x265 was faster in pretty much everything we threw at it by 10%-15%. I suspect the performance difference of a 2x265 versus a 4x844 would be even greater due to the big latency hit in a 4 socket Opteron system. Expect to need to upgrade to later Linux cores though. Previous kernel on this server was 2.6.9+ (FC3 64-bit) -- promptly kernel panic'd upon install of the DCs. FC3 installer did the same thing. Went to FC4 (2.6.11+) and it has run perfect for the past 3 months. We'll probably do a yum update to 2.6.13+ to take advantage of the better NUMA code now that we've successfully done this update on a less critical server.
On Wed, Oct 19, 2005 at 12:32:36AM -0700, William Yu wrote:
> Expect to need to upgrade to later Linux cores though. Previous kernel
> on this server was 2.6.9+ (FC3 64-bit) -- promptly kernel panic'd upon
> install of the DCs. FC3 installer did the same thing. Went to FC4
> (2.6.11+) and it has run perfect for the past 3 months. We'll probably
> do a yum update to 2.6.13+ to take advantage of the better NUMA code now
> that we've successfully done this update on a less critical server.
You should definitely do the upgrade to 2.6.13, the previous
versions had problems were they would allocate memory to
the wrong bank of memory when using dual core CPUs. So you
would find only about a 20% improvement over a single core.
With the newer kernels you should find that a dual core will
be giving you about 80% increase over a single core.
-Mark
On Tue, 18 Oct 2005, Onyx wrote: > Hello Everyone, > Does anyone know if PostgreSQL supports running on dual processors, and will > efficiently utilize the dual processors for performance? More specifically, > will PostgreSQL utilize the dual processor performance of using dual-core > AMD chips? Any information would be helpful... Not directly Postgres related, but anandtech had a review of DB2 & MySQL on dual Xeon & Opteron systems. More focused on the differences between 32bit & 64 bit OS, & Opteron vs Xeon, but possibly of interest: http://www.anandtech.com/IT/showdoc.aspx?i=2291 http://www.anandtech.com/IT/showdoc.aspx?i=2447&p=4 I have not benchmarked it, but my impression of moving from SuSE 9.2 32bit to SuSE 9.3 64bit, on the same box, both with a custom compiled similar Postgres install, is that the 9.3 64bit is faster & more responsive running Postgres. How much the OS version upgrade vs 32-64 change I can't say. Cheers, Brent Wood
Mark Rae wrote: > On Wed, Oct 19, 2005 at 12:32:36AM -0700, William Yu wrote: > >>Expect to need to upgrade to later Linux cores though. Previous kernel >>on this server was 2.6.9+ (FC3 64-bit) -- promptly kernel panic'd upon >>install of the DCs. FC3 installer did the same thing. Went to FC4 >>(2.6.11+) and it has run perfect for the past 3 months. We'll probably >>do a yum update to 2.6.13+ to take advantage of the better NUMA code now >>that we've successfully done this update on a less critical server. > > > You should definitely do the upgrade to 2.6.13, the previous > versions had problems were they would allocate memory to > the wrong bank of memory when using dual core CPUs. So you > would find only about a 20% improvement over a single core. > > With the newer kernels you should find that a dual core will > be giving you about 80% increase over a single core. I'm not experiencing this problem right now because I have NUMA disabled in the BIOS. :) I'm not sure NUMA will help that much for Postgres due to Postgres shared memory + OS caching architecture -- but I'll run it with NUMA on for a week and then compare it to NUMA off after we do the kernel upgrade. (Ordinarily, I would be afraid to do this type of testing on a production server but this 2xDC is so freaking fast, I can afford to give away 80%+ of the processing power and still be within my performance target.)
On Wed, Oct 19, 2005 at 09:55:18AM -0700, William Yu wrote:
> Mark Rae wrote:
> >With the newer kernels you should find that a dual core will
> >be giving you about 80% increase over a single core.
>
> I'm not experiencing this problem right now because I have NUMA disabled
> in the BIOS. :)
That is the recommended 'fix' :-D
> I'm not sure NUMA will help that much for Postgres due
> to Postgres shared memory + OS caching architecture --
The 80% figure is for general data processing code. I actually got
a 72% performance increase using postgres for read only queries.
I would think that any write activity would still be limited by
how fast you can write the WAL to disk.
-Mark
On Tue, 2005-18-10 at 22:21 -0500, Tony Caduto wrote: > From what i understand Postgresql will scale with more cpus, but not in > the same way as threaded server would. Threading isn't really relevant. PostgreSQL currently forks a new process for each client connection, and each process can be scheduled on a different CPU. If PostgreSQL used threads and allocated one thread per client connection, each thread could be scheduled on a different CPU (assuming we used kernel threads). So either way the number of concurrent connections would need to exceed the number of CPUs to scale effectively. Now, rather than dedicating a single process/thread to each connection, another approach would be to share the work of query processing among threads/processes differently. For example, we could perform a large sort operation by splitting the input data among N threads/processes, which could then do the sort in parallel. Postgres currently doesn't do this, but it doesn't have much to do with threads vs. processes per se (threads might make this somewhat easier to implement, though). -Neil
In article <4355BBBA.6030800@amsoftwaredesign.com>, Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote: >I believe that as each process(backend) is created it will get assigned >to a CPU. At least with Linux the process can run on any CPU. It isn't restricted to some assignment at the time of its creation. -- http://yosemitenews.info/