SEO

Hi -


Can anyone tell me how well PostgreSQL 8.x performs on the new Sun Ultrasparc
T1 processor and architecture on Solaris 10? I have a custom built retail
sales reporting that I developed using PostgreSQL 7.48 and PHP on a Fedora
Core 3 intel box. I want to scale this application upwards to handle a
database that might grow to a 100 GB. Our company is green mission conscious
now so I was hoping I could use that to convince management to consider a Sun
Ultrasparc T1 or T2 system provided that if I can get the best performance
out of it on PostgreSQL. So will newer versions of PostgreSQL (8.1.x) be
able to take of advantage of the multiple cores on a T1 or T2? I cannot
change the database and this will be a hard sell unless I can convince them
that the performance advantages are too good to pass up. The company is
moving in the Win32 direction and so I have to provide rock solid reasons for
why I want to use Solaris Sparc on a T1 or T2 server for this database
application instead of Windows on SQL Server.


Thanks,
Juan

-------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 12/18/05, Juan Casero <caseroj@comcast.net> wrote:
> Can anyone tell me how well PostgreSQL 8.x performs on the new Sun Ultrasparc
> T1 processor and architecture on Solaris 10? I have a custom built retail
> sales reporting that I developed using PostgreSQL 7.48 and PHP on a Fedora
> Core 3 intel box. I want to scale this application upwards to handle a
> database that might grow to a 100 GB. Our company is green mission conscious
> now so I was hoping I could use that to convince management to consider a Sun
> Ultrasparc T1 or T2 system provided that if I can get the best performance
> out of it on PostgreSQL. So will newer versions of PostgreSQL (8.1.x) be
> able to take of advantage of the multiple cores on a T1 or T2? I cannot
> change the database and this will be a hard sell unless I can convince them
> that the performance advantages are too good to pass up. The company is
> moving in the Win32 direction and so I have to provide rock solid reasons for
> why I want to use Solaris Sparc on a T1 or T2 server for this database
> application instead of Windows on SQL Server.

I do not know that anyone outside pilot orgs have received their
orders for the new T1 machines, so real world experience will not be
available yet. The big question is whether or not it manages the
processors only for threads (in which case Postgresql won't benefit
much) or for processes as well.

PostgreSQL takes a "process-parallel" approach to parallelism, not
thread-level. There are lost of historical reasons, but, that's just
hte way it is for now.

Chris
--
| Christopher Petrilli
| petrilli@gmail.com

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Juan,

On 12/18/05 8:35 AM, "Juan Casero" <caseroj@comcast.net> wrote:

> Can anyone tell me how well PostgreSQL 8.x performs on the new Sun Ultrasparc
> T1 processor and architecture on Solaris 10? I have a custom built retail
> sales reporting that I developed using PostgreSQL 7.48 and PHP on a Fedora
> Core 3 intel box. I want to scale this application upwards to handle a
> database that might grow to a 100 GB. Our company is green mission conscious
> now so I was hoping I could use that to convince management to consider a Sun
> Ultrasparc T1 or T2 system provided that if I can get the best performance
> out of it on PostgreSQL. So will newer versions of PostgreSQL (8.1.x) be
> able to take of advantage of the multiple cores on a T1 or T2? I cannot
> change the database and this will be a hard sell unless I can convince them
> that the performance advantages are too good to pass up. The company is
> moving in the Win32 direction and so I have to provide rock solid reasons for
> why I want to use Solaris Sparc on a T1 or T2 server for this database
> application instead of Windows on SQL Server.

The Niagara CPUs are heavily multi-threaded and will require a lot of
parallelism to be exposed to them in order to be effective.

Until Sun makes niagara-based machines with lots of I/O channels, there
won't be much I/O parallelism available to match the CPU parallelism.

Bizgres MPP will use the process and I/O parallelism of these big SMP
machines and the version based on Postgres 8.1 will be out in February.

- Luke



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sun Fire T2000 has 3 PCI-E and 1PCI-X slot free when shipped. Using
dual fiber channel 2G adapters you can get about 200MB x 8 = 1600MB/sec
IO bandwidth. Plus when 4G HBAs are supported that will double up. Now I
think generally that's good enough for 1TB raw data or 2-3 TB Database
size. Of course typically the database size in PostgreSQL space will be
in the 100-500GB range so a Sun Fire T2000 can be a good fit with enough
area to grow at a very reasonable price.

Of course like someone mentioned if all you have is 1 connection using
postgresql which cannot spawn helper processes/threads, this will be
limited by the single thread performance which is about 1.2Ghz compared
on Sun Fire T2000 to AMD64 (Sun Fire X4200) which pretty much has
similar IO Bandwidth, same size chassis, but the individual AMD64 cores
runs at about 2.4Ghz (I believe) and max you can get is 4 cores but you
also have to do a little trade off in terms of power consumption in lei
of faster single thread performance. So Choices are available with both
architecture. .However if you have a webserver driving a postgreSQL
backend, then UltraSPARC T1 might be a better option if you suddenly
wants to do 100s of db connections. The SunFire T2000 gives you 8 cores
with 32 threads in all running on the system.

With PostgreSQL 8.1 fix for SMP Bufferpool performance and with ZFS now
available in Solaris Express release, it would be interesting to see how
the combination of PostgreSQL 8.1 and ZFS works on Solaris since ZFS is
one of the perfect file systems for PostgreSQL where it wants all
complexities (like block allocation, fragmentation, etc) to the
underlying file systems and not re-implement its own infrastructure.

If somebody is already conducting their own tests, do let me know. As
soon as I get some free cycles, I want to run ZFS with PostgreSQL using
Solaris Express. If you have some preferred workloads do let me know.

Regards,
Jignesh


Luke Lonergan wrote:

>Juan,
>
>On 12/18/05 8:35 AM, "Juan Casero" <caseroj@comcast.net> wrote:
>
>
>
>>Can anyone tell me how well PostgreSQL 8.x performs on the new Sun Ultrasparc
>>T1 processor and architecture on Solaris 10? I have a custom built retail
>>sales reporting that I developed using PostgreSQL 7.48 and PHP on a Fedora
>>Core 3 intel box. I want to scale this application upwards to handle a
>>database that might grow to a 100 GB. Our company is green mission conscious
>>now so I was hoping I could use that to convince management to consider a Sun
>>Ultrasparc T1 or T2 system provided that if I can get the best performance
>>out of it on PostgreSQL. So will newer versions of PostgreSQL (8.1.x) be
>>able to take of advantage of the multiple cores on a T1 or T2? I cannot
>>change the database and this will be a hard sell unless I can convince them
>>that the performance advantages are too good to pass up. The company is
>>moving in the Win32 direction and so I have to provide rock solid reasons for
>>why I want to use Solaris Sparc on a T1 or T2 server for this database
>>application instead of Windows on SQL Server.
>>
>>
>
>The Niagara CPUs are heavily multi-threaded and will require a lot of
>parallelism to be exposed to them in order to be effective.
>
>Until Sun makes niagara-based machines with lots of I/O channels, there
>won't be much I/O parallelism available to match the CPU parallelism.
>
>Bizgres MPP will use the process and I/O parallelism of these big SMP
>machines and the version based on Postgres 8.1 will be out in February.
>
>- Luke
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: don't forget to increase your free space map settings
>
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Jignesh,


On 12/19/05 6:27 AM, "Jignesh K. Shah" <J.K.Shah@Sun.COM> wrote:

> Sun Fire T2000 has 3 PCI-E and 1PCI-X slot free when shipped. Using
> dual fiber channel 2G adapters you can get about 200MB x 8 = 1600MB/sec
> IO bandwidth. Plus when 4G HBAs are supported that will double up. Now I
> think generally that's good enough for 1TB raw data or 2-3 TB Database
> size. Of course typically the database size in PostgreSQL space will be
> in the 100-500GB range so a Sun Fire T2000 can be a good fit with enough
> area to grow at a very reasonable price.

The free PCI slots don't indicate the I/O speed of the machine, otherwise
I'll just go back 4 years and use a Xeon machine.

Can you educate us a bit on the T-2000, like where can we find a technical
publication that can answer the following:

Are all of the PCI-E and PCI-X independent, mastering channels? Are they
connected via a crossbar or is it using the JBus? Is the usable memory
bandwidth available to the HBAs and CPU double the 1,600MB/s, or 3,200MB/s?

> Of course like someone mentioned if all you have is 1 connection using
> postgresql which cannot spawn helper processes/threads, this will be
> limited by the single thread performance which is about 1.2Ghz compared
> on Sun Fire T2000 to AMD64 (Sun Fire X4200) which pretty much has
> similar IO Bandwidth, same size chassis, but the individual AMD64 cores
> runs at about 2.4Ghz (I believe) and max you can get is 4 cores but you
> also have to do a little trade off in terms of power consumption in lei
> of faster single thread performance. So Choices are available with both
> architecture. .However if you have a webserver driving a postgreSQL
> backend, then UltraSPARC T1 might be a better option if you suddenly
> wants to do 100s of db connections. The SunFire T2000 gives you 8 cores
> with 32 threads in all running on the system.

So - OLTP / webserver, that makes sense.

- Luke



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hi Luke,

I have gone to the max with 4 fibers on Sun Fire T2000. But I am not sure about the answers that you asked. Let me see if I can get answers for them. I am going to try to max out the IO on these systems with 8 fibers as soon as I get additional storage so stay tuned for that.

By the way you don't have to wait for my tests. Just get a trial server and try it on your own. If you don't like it return it.

https://www.sun.com/emrkt/trycoolthreads/contactme.html

Check out Jonathan's blog for more details http://blogs.sun.com/jonathan

However if you do try it with PostgreSQL, do let me know also with your experience.

Regards,
Jignesh



----- Original Message -----
From: Luke Lonergan <llonergan@greenplum.com>
Date: Monday, December 19, 2005 12:31 pm
Subject: Re: [PERFORM] PostgreSQL and Ultrasparc T1
To: Jignesh Shah <J.K.Shah@Sun.COM>
Cc: Juan Casero <caseroj@comcast.net>, pgsql-performance@postgresql.org

> Jignesh,
>
>
> On 12/19/05 6:27 AM, "Jignesh K. Shah" <J.K.Shah@Sun.COM> wrote:
>
> > Sun Fire T2000 has 3 PCI-E and 1PCI-X slot free when shipped. Using
> > dual fiber channel 2G adapters you can get about 200MB x 8 =
> 1600MB/sec> IO bandwidth. Plus when 4G HBAs are supported that will
> double up. Now I
> > think generally that's good enough for 1TB raw data or 2-3 TB
> Database> size. Of course typically the database size in PostgreSQL
> space will be
> > in the 100-500GB range so a Sun Fire T2000 can be a good fit with
> enough> area to grow at a very reasonable price.
>
> The free PCI slots don't indicate the I/O speed of the machine,
> otherwiseI'll just go back 4 years and use a Xeon machine.
>
> Can you educate us a bit on the T-2000, like where can we find a
> technicalpublication that can answer the following:
>
> Are all of the PCI-E and PCI-X independent, mastering channels?
> Are they
> connected via a crossbar or is it using the JBus? Is the usable
> memorybandwidth available to the HBAs and CPU double the 1,600MB/s,
> or 3,200MB/s?
>
> > Of course like someone mentioned if all you have is 1 connection
> using> postgresql which cannot spawn helper processes/threads, this
> will be
> > limited by the single thread performance which is about 1.2Ghz
> compared> on Sun Fire T2000 to AMD64 (Sun Fire X4200) which pretty
> much has
> > similar IO Bandwidth, same size chassis, but the individual
> AMD64 cores
> > runs at about 2.4Ghz (I believe) and max you can get is 4 cores
> but you
> > also have to do a little trade off in terms of power consumption
> in lei
> > of faster single thread performance. So Choices are available
> with both
> > architecture. .However if you have a webserver driving a postgreSQL
> > backend, then UltraSPARC T1 might be a better option if you suddenly
> > wants to do 100s of db connections. The SunFire T2000 gives you 8
> cores> with 32 threads in all running on the system.
>
> So - OLTP / webserver, that makes sense.
>
> - Luke
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Jignesh,

On 12/19/05 11:29 AM, "Jignesh Shah" <J.K.Shah@Sun.COM> wrote:

> I have gone to the max with 4 fibers on Sun Fire T2000. But I am not sure
> about the answers that you asked. Let me see if I can get answers for them. I
> am going to try to max out the IO on these systems with 8 fibers as soon as I
> get additional storage so stay tuned for that.

Cool - how close did you get to 800MB/s?

> By the way you don't have to wait for my tests. Just get a trial server and
> try it on your own. If you don't like it return it.
>
> https://www.sun.com/emrkt/trycoolthreads/contactme.html

Done - I'll certainly test Postgres / Bizgres on it - you know me ;-)

> However if you do try it with PostgreSQL, do let me know also with your
> experience.

See above.

The Niagara is UltraSparc III compatible - so the GCC compiler should emit
good code for it, right?

- Luke



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Hi Luke,

I got about 720 MB/sec to 730 MB/sec with plain dd tests on my current storage configuration (8 LUNS on 4 fibers) which slowed me down (10K rpm 146 GB disks FC) with 4 LUNS going through a longer pass to the disks (via a controller master array to slave JBODs to provide ) .

extended device statistics
r/s w/s Mr/s Mw/s wait actv wsvc_t asvc_t %w %b device
0.8 14.0 0.0 0.0 0.0 0.3 0.0 17.8 0 4 c3t0d0
91.4 0.0 91.4 0.0 0.0 1.0 0.0 10.5 0 96 c0t40d0
96.0 0.0 96.0 0.0 0.0 1.0 0.0 10.0 0 96 c5t40d1
95.8 0.0 95.8 0.0 0.0 1.0 0.0 10.0 0 96 c0t40d1
96.8 0.0 96.8 0.0 0.0 1.0 0.0 9.9 0 96 c5t40d0
84.6 0.0 84.6 0.0 0.0 1.0 0.0 11.4 0 96 c4t46d1
85.6 0.0 85.6 0.0 0.0 1.0 0.0 11.2 0 96 c4t46d0
85.2 0.0 85.2 0.0 0.0 1.0 0.0 11.3 0 96 c2t46d1
85.4 0.0 85.4 0.0 0.0 1.0 0.0 11.3 0 96 c2t46d0

I can probably bump it up a bit with fine storage tuning (LUN) but there is no limitation on the Sun Fire T2000 to bottleneck on anything plus dd tests are not the best throughput measurement tool.

Yes UltraSPARC T1 supports the SPARC V9 architecture and can support all the SPARC binaries already generated or newly generated using gcc or Sun Studio 11 which is also free.
http://developers.sun.com/prodtech/c...ds/sun_studio/



Regards,
Jignesh


----- Original Message -----
From: Luke Lonergan <llonergan@greenplum.com>
Date: Monday, December 19, 2005 2:38 pm
Subject: Re: [PERFORM] PostgreSQL and Ultrasparc T1
To: Jignesh Shah <J.K.Shah@Sun.COM>
Cc: Juan Casero <caseroj@comcast.net>, pgsql-performance@postgresql.org

> Jignesh,
>
> On 12/19/05 11:29 AM, "Jignesh Shah" <J.K.Shah@Sun.COM> wrote:
>
> > I have gone to the max with 4 fibers on Sun Fire T2000. But I am
> not sure
> > about the answers that you asked. Let me see if I can get answers
> for them. I
> > am going to try to max out the IO on these systems with 8 fibers
> as soon as I
> > get additional storage so stay tuned for that.
>
> Cool - how close did you get to 800MB/s?
>
> > By the way you don't have to wait for my tests. Just get a trial
> server and
> > try it on your own. If you don't like it return it.
> >
> > https://www.sun.com/emrkt/trycoolthreads/contactme.html
>
> Done - I'll certainly test Postgres / Bizgres on it - you know me ;-)
>
> > However if you do try it with PostgreSQL, do let me know also
> with your
> > experience.
>
> See above.
>
> The Niagara is UltraSparc III compatible - so the GCC compiler
> should emit
> good code for it, right?
>
> - Luke
>
>
>
> ---------------------------(end of broadcast)-----------------------
> ----
> TIP 2: Don't 'kill -9' the postmaster
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Jignesh,

On 12/19/05 12:21 PM, "Jignesh Shah" <J.K.Shah@Sun.COM> wrote:

> I got about 720 MB/sec to 730 MB/sec with plain dd tests on my current
> storage configuration (8 LUNS on 4 fibers) which slowed me down (10K rpm 146
> GB disks FC) with 4 LUNS going through a longer pass to the disks (via a
> controller master array to slave JBODs to provide ) .
>
> extended device statistics
> r/s w/s Mr/s Mw/s wait actv wsvc_t asvc_t %w %b device
> 0.8 14.0 0.0 0.0 0.0 0.3 0.0 17.8 0 4 c3t0d0
> 91.4 0.0 91.4 0.0 0.0 1.0 0.0 10.5 0 96 c0t40d0
> 96.0 0.0 96.0 0.0 0.0 1.0 0.0 10.0 0 96 c5t40d1
> 95.8 0.0 95.8 0.0 0.0 1.0 0.0 10.0 0 96 c0t40d1

Can you please explain these columns? R/s, is that millions of pages or
extents or something? How do I translate this to 730 million bytes per
second?

- Luke



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Jignesh,

On 12/19/05 12:21 PM, "Jignesh Shah" <J.K.Shah@Sun.COM> wrote:

> extended device statistics
> r/s w/s Mr/s Mw/s wait actv wsvc_t asvc_t %w %b device
> 0.8 14.0 0.0 0.0 0.0 0.3 0.0 17.8 0 4 c3t0d0
> 91.4 0.0 91.4 0.0 0.0 1.0 0.0 10.5 0 96 c0t40d0
> 96.0 0.0 96.0 0.0 0.0 1.0 0.0 10.0 0 96 c5t40d1
> 95.8 0.0 95.8 0.0 0.0 1.0 0.0 10.0 0 96 c0t40d1
> 96.8 0.0 96.8 0.0 0.0 1.0 0.0 9.9 0 96 c5t40d0
> 84.6 0.0 84.6 0.0 0.0 1.0 0.0 11.4 0 96 c4t46d1
> 85.6 0.0 85.6 0.0 0.0 1.0 0.0 11.2 0 96 c4t46d0
> 85.2 0.0 85.2 0.0 0.0 1.0 0.0 11.3 0 96 c2t46d1
> 85.4 0.0 85.4 0.0 0.0 1.0 0.0 11.3 0 96 c2t46d0

Doh! Forget my last message, each of these is a single drive.

Wacky layout though - it looks like c0,c2,c3,c4,c5 - is that 5 controllers
there?

Also - what are the RAID options on this unit?

To get optimal performance on an 8 core unit, would we want to map 1 active
process to each of these drives? Can the CPU run all 8 threads
simultaneously?

- Luke



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org