Обсуждение: Sun Fire T2000 and PostgreSQL 8.1.3

"Juan Casero \(FL FLC\)" <Juan.Casero@wholefoods.com> writes:
> ... This box has a single Ultrasparc T1 cpu with six execution
> piplelines that can each handle 4 threads.  With the Unix top utility
> the postgresql server appears to bounce around between the available
> threads on the system.

Try sending it more than one query at a time?  If you're testing with
just one client connection issuing queries, that's about what I'd expect.

            regards, tom lane

Juan,

On 4/5/06 11:12 AM, "Juan Casero (FL FLC)" <Juan.Casero@wholefoods.com>
wrote:

> I know the postgresql server is not smp aware but I believe
> parts of it are.  In particular the buffer manager is supposed to scale the
> performance almost linearly with the number of cpu's (including virtual ones).
> I don't know however, if I need to recompile the postgresql server myself to
> get those benefits.

As Tom said, to get the benefits of parallelism on one query, you would need
a parallelizing database like Teradata, Oracle Parallel Query option,
Netezza, or Bizgres MPP.

The announcement about Postgres linear scalability for SMP is only relevant
to statement throughput for highly concurrent environments (web sites, OLTP,
etc).

- Luke

I am not sure about this.  I mean I have postgresql 8.1.3 running on my
Windows XP P4 HT laptop that I use for testing my webapps.  When I hit
this pgsql on this laptop with a large query I can see the load spike up
really high on both of my virtual processors.  Whatever, pgsql is doing
it looks like both cpu's are being used indepently. The usage curve is
not identical on both of them that makes me think that parts of the
server are multithreaded.  Admittedly I am not familiar with the source
code fo postgresql so I was hoping maybe one of the developers who is
could definitely answer this question.

Thanks,
Juan

-----Original Message-----
From: Luke Lonergan [mailto:llonergan@greenplum.com]
Sent: Wednesday, April 05, 2006 4:43 PM
To: Juan Casero (FL FLC); pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3

Juan,

On 4/5/06 11:12 AM, "Juan Casero (FL FLC)" <Juan.Casero@wholefoods.com>
wrote:

> I know the postgresql server is not smp aware but I believe parts of
> it are.  In particular the buffer manager is supposed to scale the
> performance almost linearly with the number of cpu's (including
virtual ones).
> I don't know however, if I need to recompile the postgresql server
> myself to get those benefits.

As Tom said, to get the benefits of parallelism on one query, you would
need a parallelizing database like Teradata, Oracle Parallel Query
option, Netezza, or Bizgres MPP.

The announcement about Postgres linear scalability for SMP is only
relevant to statement throughput for highly concurrent environments (web
sites, OLTP, etc).

- Luke

Juan,

On 4/5/06 1:54 PM, "Juan Casero (FL FLC)" <Juan.Casero@wholefoods.com>
wrote:

> I am not sure about this.  I mean I have postgresql 8.1.3 running on my
> Windows XP P4 HT laptop that I use for testing my webapps.  When I hit
> this pgsql on this laptop with a large query I can see the load spike up
> really high on both of my virtual processors.  Whatever, pgsql is doing
> it looks like both cpu's are being used indepently. The usage curve is
> not identical on both of them that makes me think that parts of the
> server are multithreaded.  Admittedly I am not familiar with the source
> code fo postgresql so I was hoping maybe one of the developers who is
> could definitely answer this question.

There's no part of the Postgres backend that is threaded or multi-processed.
A reasonable explanation for your windows experience is that your web server
or the psql client may be taking some CPU cycles while the backend is
processing your query.  Also, depending on how the CPU load is reported, if
the OS is doing prefetching of I/O, it might show up as load.

- Luke

Luke (or anyone else who may be following this thread)

Do you think bizgres might be a good choice of database server for the
Ultrasparc T1 based T2000?   I have downloaded the source code but I was
hoping to find out if the potential performance gains were worth the
effort to compile and install the code.

Thanks,
Juan

-----Original Message-----
From: Luke Lonergan [mailto:llonergan@greenplum.com]
Sent: Wednesday, April 05, 2006 5:37 PM
To: Juan Casero (FL FLC); pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3

Juan,

On 4/5/06 1:54 PM, "Juan Casero (FL FLC)" <Juan.Casero@wholefoods.com>
wrote:

> I am not sure about this.  I mean I have postgresql 8.1.3 running on
> my Windows XP P4 HT laptop that I use for testing my webapps.  When I
> hit this pgsql on this laptop with a large query I can see the load
> spike up really high on both of my virtual processors.  Whatever,
> pgsql is doing it looks like both cpu's are being used indepently. The

> usage curve is not identical on both of them that makes me think that
> parts of the server are multithreaded.  Admittedly I am not familiar
> with the source code fo postgresql so I was hoping maybe one of the
> developers who is could definitely answer this question.

There's no part of the Postgres backend that is threaded or
multi-processed.
A reasonable explanation for your windows experience is that your web
server or the psql client may be taking some CPU cycles while the
backend is processing your query.  Also, depending on how the CPU load
is reported, if the OS is doing prefetching of I/O, it might show up as
load.

- Luke

Juan,

On 4/5/06 2:45 PM, "Juan Casero (FL FLC)" <Juan.Casero@wholefoods.com>
wrote:

> Do you think bizgres might be a good choice of database server for the
> Ultrasparc T1 based T2000?   I have downloaded the source code but I was
> hoping to find out if the potential performance gains were worth the
> effort to compile and install the code.

Bizgres (non-MPP) does not do any multi-CPU parallelism, so it won't use
more CPUs in your T2000.  It does have the faster sort performance and an
on-disk bitmap index, both of which will make it run many times (3-6) faster
than 8.1 Postgres depending if your queries use larger data or involve
sorts.

Bizgres MPP is closed source and unfortunately for your T2000 experiment it
doesn't currently support Solaris SPARC CPUs, only Solaris x86.  It would
use all of your CPUs and I/O channels on one or more machines for every
query.  Again, it's optimized for queries where that use a lot of data or
have a lot of complexity (sorts, aggregations, joins, etc).

- Luke

Juan Casero (FL FLC) wrote:
> When I hit
> this pgsql on this laptop with a large query I can see the load spike up
> really high on both of my virtual processors.  Whatever, pgsql is doing
> it looks like both cpu's are being used indepently.

Intel HT was partly a marketing thing, you don't really get two CPU's /
cores etc. The virtual CPU is really that, there is only one cpu doing
the actual work with some extra glue to help the "hyperthreading".

As to how hyper intel's hyperthreading is, OSDL did some testing (I
think the dbt2 workload) and I remember HT reducing performance for
pgsql by about 15%. Worth looking up, benchmarks are subject to a lot of
issues but was interesting.

There have been some seriously good recommendations in this newsgroup
for nice high powered servers, including good disk subsystems.

Most involve some AMD Opertons, lots of spindles with a good raid
controller preferred to one or two large disks and a good helping of
ram. Be interesting to get some numbers on the sunfire machine.

- August

Juan,

> When I hit
> this pgsql on this laptop with a large query I can see the load spike up
> really high on both of my virtual processors.  Whatever, pgsql is doing
> it looks like both cpu's are being used indepently.

Nope, sorry, you're being decieved.   Postgres is strictly one process, one
query.

You can use Bizgres MPP to achieve multithreading; it's proprietary and you
have to pay for it.  It does work well, though.

More importantly, though, you haven't really explained why you care about
multithreading.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

On Wed, 2006-04-05 at 15:54, Juan Casero (FL FLC) wrote:
> I am not sure about this.  I mean I have postgresql 8.1.3 running on my
> Windows XP P4 HT laptop that I use for testing my webapps.  When I hit
> this pgsql on this laptop with a large query I can see the load spike up
> really high on both of my virtual processors.  Whatever, pgsql is doing
> it looks like both cpu's are being used indepently. The usage curve is
> not identical on both of them that makes me think that parts of the
> server are multithreaded.  Admittedly I am not familiar with the source
> code fo postgresql so I was hoping maybe one of the developers who is
> could definitely answer this question.

I think that really depends on your workload.

Are you going to have a dozen or so transactions running at a time?
then regular postgresql is probably ok.

If you're gonna be running only one or two big, fat, hairy reporting
queries, then you might wanna look at the bizgress mpp version.

Note that some queries lend themselves to parallel processing more than
others.

Josh Berkus <josh@agliodbs.com> writes:
> Juan,
>> When I hit
>> this pgsql on this laptop with a large query I can see the load spike up
>> really high on both of my virtual processors.  Whatever, pgsql is doing
>> it looks like both cpu's are being used indepently.

> Nope, sorry, you're being decieved.   Postgres is strictly one process, one
> query.

This is not strictly true: we have for instance pushed off some work
into a "background writer" process, and even just having both a client
and a server process active allows some small amount of parallelism.
But you're certainly not going to see effective use of more than about
two CPUs on a single query stream ... at least not without Bizgres or
some other add-on.

            regards, tom lane

Juan.Casero@wholefoods.com ("Juan Casero \(FL FLC\)") writes:
> I am not sure about this.  I mean I have postgresql 8.1.3 running on
> my Windows XP P4 HT laptop that I use for testing my webapps.  When
> I hit this pgsql on this laptop with a large query I can see the
> load spike up really high on both of my virtual processors.
> Whatever, pgsql is doing it looks like both cpu's are being used
> indepently. The usage curve is not identical on both of them that
> makes me think that parts of the server are multithreaded.

This is almost certainly a function of the fact that you're running
the single-threaded backend process, which then feeds a
single-threaded front end process, namely the application that is
being fed data.

For a query that returns a large return set, that will indeed make
both processors get pretty busy; one for the DB server, one for
whatever program is processing the results.
--
select 'cbbrowne' || '@' || 'acm.org';
http://www.ntlug.org/~cbbrowne/rdbms.html
It's hard to tell if someone is inconspicuous.

josh@agliodbs.com (Josh Berkus) writes:
> Juan,
>
>> When I hit
>> this pgsql on this laptop with a large query I can see the load spike up
>> really high on both of my virtual processors.  Whatever, pgsql is doing
>> it looks like both cpu's are being used indepently.
>
> Nope, sorry, you're being decieved.   Postgres is strictly one process, one
> query.

It's not entirely deception; there is indeed independent use of both
CPUs, it's just that it isn't from multithreading...
--
output = reverse("gro.mca" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/internet.html
"Don't use C; In  my opinion, C is  a library programming language not
an  app programming language."   --   Owen  Taylor  (GTK+  and   ORBit
developer)

I am evaluating this SunFire T2000 as a replacement for an Intel P3 1Ghz
postgresql server.  This intel server runs a retail reporting database
on postgresql 8.1.3.  I need to realize significant performance gains on
T2000 server to justify the expense.  So I need to tune the postgresql
server as much as I can for it.  Right now the operating system (solaris
10) sees each thread as a single cpu and only allows each thread 4.16%
of the available cpu resources for processing queries.  Since postgresql
is not multithreaded and since I cannot apparently break past the
operating system imposed limits on a single thread I can't fully realize
the performance benefits of the T2000 server unless and until I start
getting lots of people hitting the database server with requests.  This
doesn't happen right now.  It may happen later on as I write more
applications for the server but I am looking to see if the performance
benefit we can get from this server is worth the price tag right now.
That is why I am looking for ways to tweak postgres on it.


Thanks,
Juan

-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Wednesday, April 05, 2006 6:02 PM
To: pgsql-performance@postgresql.org
Cc: Juan Casero (FL FLC); Luke Lonergan
Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3

Juan,

> When I hit
> this pgsql on this laptop with a large query I can see the load spike
> up really high on both of my virtual processors.  Whatever, pgsql is
> doing it looks like both cpu's are being used indepently.

Nope, sorry, you're being decieved.   Postgres is strictly one process,
one
query.

You can use Bizgres MPP to achieve multithreading; it's proprietary and
you have to pay for it.  It does work well, though.

More importantly, though, you haven't really explained why you care
about multithreading.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Juan,

On 4/5/06 5:33 PM, "Juan Casero (FL FLC)" <Juan.Casero@wholefoods.com>
wrote:

> ... but I am looking to see if the performance
> benefit we can get from this server is worth the price tag right now.

While many people here will look forward to performance results on the
T2000, I can guarantee that your server money will go much further for a
reporting application with an Opteron based system.  Buy a Sun Galaxy with a
pair of Opteron 275s, run Linux on it, and I predict you will see
performance 4-5 times faster than the T2000 running Solaris for handling
single queries, and 2-3 times faster when handling multiple queries.

- Luke

Juan, I think that AMD Opteron is more flex (OS and Hardware Upgrade)
and then, the best solution.¹

What are you think about the Sun Fire X64 X4200 Server?

Take a look in this analysis and performance benchmark².

Regards,
MTada

¹ http://www.anandtech.com/systems/showdoc.aspx?i=2727&p=2
² http://www.anandtech.com/systems/showdoc.aspx?i=2727&p=7

Juan Casero (FL FLC) wrote:

>I am evaluating this SunFire T2000 as a replacement for an Intel P3 1Ghz
>postgresql server.  This intel server runs a retail reporting database
>on postgresql 8.1.3.  I need to realize significant performance gains on
>T2000 server to justify the expense.  So I need to tune the postgresql
>server as much as I can for it.  Right now the operating system (solaris
>10) sees each thread as a single cpu and only allows each thread 4.16%
>of the available cpu resources for processing queries.  Since postgresql
>is not multithreaded and since I cannot apparently break past the
>operating system imposed limits on a single thread I can't fully realize
>the performance benefits of the T2000 server unless and until I start
>getting lots of people hitting the database server with requests.  This
>doesn't happen right now.  It may happen later on as I write more
>applications for the server but I am looking to see if the performance
>benefit we can get from this server is worth the price tag right now.
>That is why I am looking for ways to tweak postgres on it.
>
>
>Thanks,
>Juan
>
>-----Original Message-----
>From: Josh Berkus [mailto:josh@agliodbs.com]
>Sent: Wednesday, April 05, 2006 6:02 PM
>To: pgsql-performance@postgresql.org
>Cc: Juan Casero (FL FLC); Luke Lonergan
>Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
>
>Juan,
>
>
>
>>When I hit
>>this pgsql on this laptop with a large query I can see the load spike
>>up really high on both of my virtual processors.  Whatever, pgsql is
>>doing it looks like both cpu's are being used indepently.
>>
>>
>
>Nope, sorry, you're being decieved.   Postgres is strictly one process,
>one
>query.
>
>You can use Bizgres MPP to achieve multithreading; it's proprietary and
>you have to pay for it.  It does work well, though.
>
>More importantly, though, you haven't really explained why you care
>about multithreading.
>
>--
>--Josh
>
>Josh Berkus
>Aglio Database Solutions
>San Francisco
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
>

Hi Juan Casero,<br /><br /> I've found that serial query clients are best served by PostgreSQL running on fast single
ordual core processors, ( such as the Athlon FX60 ) rather than expensive n-way systems. I was orginally using an 8-way
Xeoncomputer for a similar serial throughput problem. and i wasn't supprised to find that at least 6 of the 8
processorswere idle. The point is, for this type client, you are better off spending the money on the fastest single or
dualcore processors than a multiway box.<br /><br /> Anthony.<br /><br /> Juan Casero (FL FLC) wrote: <blockquote
cite="mid7583B3F1891CC0429FA4A44952AA539C66FA2C@wfm-exchprd2.wfm.pvt"type="cite"><div align="left" dir="ltr"><span
class="812374617-05042006"><fontface="Arial" size="2">Greetings -</font></span></div><div><span
class="812374617-05042006"></span> </div><div><spanclass="812374617-05042006"><font face="Arial" size="2">I am testing
aSun Microsystems Sun Fire T2000 demo server at our company.   I want to know if anyone here has any experience with
thishardware and postgresql 8.1.3.  I installed the copy of postgresql 8.1.3 from blastwave.org onto this demo box and
loadedour production database into it.  This box has a single Ultrasparc T1 cpu with six execution piplelines that can
eachhandle 4 threads.  With the Unix top utility the postgresql server appears to bounce around between the available
threadson the system.  For example I run a single large query and I can see the postgresql server sometimes running on
cpu/0,other times on cpu/1, cpu/3,....etc up to cpu/23.   However, never is the load for the postgres server reported
tobe higher than 4.16%.  I did the math and 4.16% x 24 threads = 98.84% cpu load.  So I wonder if the Solaris 10 kernel
issomehow throttling the processes so that any single virtual processor can do no more than 4.16% load.  We got this
serverlast week and I was able to install it in our rack just yesterday.   Now I need to see how I can optimize the
postgresqlserver to work on this box.  Does anyone have any suggestions?   I know the postgresql server is not smp
awarebut I believe parts of it are.  In particular the buffer manager is supposed to scale the performance almost
linearlywith the number of cpu's (including virtual ones).  I don't know however, if I need to recompile the postgresql
servermyself to get those benefits.   I am using the version of postgresql 8.1.3 that is available on blastwave.org.  I
amalso working with the 64 bit version of the database server.   This machine has over 8GB of ram so I was thinking of
usingthe 64 bit version of the postgresql server so I can access ram beyong the 4gb limit imposed by 32 bit
addressing. Any help or recommendations for performance tweaking of postgresql is very much
appreciated.</font></span></div><div><spanclass="812374617-05042006"></span> </div><div><span
class="812374617-05042006"></span> </div><div><spanclass="812374617-05042006"><font face="Arial"
size="2">Thanks,</font></span></div><div><spanclass="812374617-05042006"><font face="Arial"
size="2">Juan</font></span></div></blockquote><br/> 

Tom is right. Unless your workload can generate lots of simultaneous
queries, you will not reap the full benefit of the Sun Fire T2000
system. I have tested 8.1.3 with an OLTP workload on an 8 cores system.
With 1500-2000 client connections, the CPU was only about 30% utilized.
The UltraSPARC T1 processor was designed for throughput with many cores
running at lower frequency (1-1.2 GHz) to reduce power consumption. To
speed up a single big query, you'd be better off with a parallelize DB
or an Opteron system with higher clock speed like this one
http://www.sun.com/servers/entry/x4200/

Regards,
-Robert

Tom Lane wrote:

>"Juan Casero \(FL FLC\)" <Juan.Casero@wholefoods.com> writes:
>
>
>>... This box has a single Ultrasparc T1 cpu with six execution
>>piplelines that can each handle 4 threads.  With the Unix top utility
>>the postgresql server appears to bounce around between the available
>>threads on the system.
>>
>>
>
>Try sending it more than one query at a time?  If you're testing with
>just one client connection issuing queries, that's about what I'd expect.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>

Juan Casero (FL FLC) wrote:

>I can't fully realize
>the performance benefits of the T2000 server unless and until I start
>getting lots of people hitting the database server with requests.  This
>doesn't happen right now.  It may happen later on as I write more
>applications for the server but I am looking to see if the performance
>benefit we can get from this server is worth the price tag right now.
>That is why I am looking for ways to tweak postgres on it.
>
>
>
If you have a need to use the system for other purposes with the extra
CPU bandwidth, you can partition it using the Solaris Containers
feature. Doing this will give you room to grow your DB usage later and
make full use of the system now.

Just a thought!

Regards,
Robert

Luke Lonergan wrote:
> Juan,
>
> On 4/5/06 5:33 PM, "Juan Casero (FL FLC)" <Juan.Casero@wholefoods.com>
> wrote:
>
>> ... but I am looking to see if the performance
>> benefit we can get from this server is worth the price tag right now.
>
> While many people here will look forward to performance results on the
> T2000, I can guarantee that your server money will go much further for a
> reporting application with an Opteron based system.  Buy a Sun Galaxy with a
> pair of Opteron 275s, run Linux on it, and I predict you will see
> performance 4-5 times faster than the T2000 running Solaris for handling
> single queries, and 2-3 times faster when handling multiple queries.

We've got a Sun Fire V40z and it's quite a nice machine -- 6x 15krpm
drives, 4GB RAM, and a pair of Opteron 850s. This gives us more than
enough power now for what we need, but it's nice to know that we can
shoehorn a lot more RAM, and up it to eight CPU cores if needed.

The newer Sun Opteron systems look nice too, but unless you're using
external storage, their little 2.5" hard drives may not be ideal.

Thanks
Leigh

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

Leigh,

On 4/5/06 9:23 PM, "Leigh Dyer" <leigh@eclinic.com.au> wrote:

> We've got a Sun Fire V40z and it's quite a nice machine -- 6x 15krpm
> drives, 4GB RAM, and a pair of Opteron 850s. This gives us more than
> enough power now for what we need, but it's nice to know that we can
> shoehorn a lot more RAM, and up it to eight CPU cores if needed.

We have one of these too - ours is signed by Scott McNealy.

> The newer Sun Opteron systems look nice too, but unless you're using
> external storage, their little 2.5" hard drives may not be ideal.

Yes - but they end-of-lifed the V20z and V40z!

One big problem with the sun line in general is the tiny internal storage
capacity - already too small on the V40z at 5/6 drives, now ridiculous at 4
SAS drives on the galaxy series.

- Luke

Luke Lonergan wrote:
> Leigh,
>
> On 4/5/06 9:23 PM, "Leigh Dyer" <leigh@eclinic.com.au> wrote:
>
>> We've got a Sun Fire V40z and it's quite a nice machine -- 6x 15krpm
>> drives, 4GB RAM, and a pair of Opteron 850s. This gives us more than
>> enough power now for what we need, but it's nice to know that we can
>> shoehorn a lot more RAM, and up it to eight CPU cores if needed.
>
> We have one of these too - ours is signed by Scott McNealy.
>
Nice :)

>> The newer Sun Opteron systems look nice too, but unless you're using
>> external storage, their little 2.5" hard drives may not be ideal.
>
> Yes - but they end-of-lifed the V20z and V40z!
>
That's quite disappointing to hear -- our V40z isn't even six months
old! We're not a big company, so external storage solutions are outside
our price range, but we still wanted a nice brand-name box, and the V40z
was a great deal compared to smaller boxes like the HP DL385.

> One big problem with the sun line in general is the tiny internal storage
> capacity - already too small on the V40z at 5/6 drives, now ridiculous at 4
> SAS drives on the galaxy series.
I'm sure those little SAS drives would be great for web servers and
other non-IO-intensive tasks though -- I'd love to get some X4100s in to
replace our Poweredge 1750s for that. It's a smart move overall IMHO,
but it's certainly not great for database serving.

Thanks
Leigh
>
> - Luke
>

Ok that is beginning to become clear to me.  Now I need to determine if
this server is worth the investment for us.  Maybe it is not a speed
daemon but to be honest the licensing costs of an SMP aware RDBMS is
outside our budget.  When postgresql starts does it start up a super
server process and then forks copies of itself to handle incoming
requests?  Or do I have to specify how many server processes should be
started up?   I figured maybe I can take advantage of the multiple cpu's
on this system by starting up enough postgres server processes to handle
large numbers of incoming connections.  I have this server available for
sixty days so I may as well explore the performance of postgresql on it.



Thanks,
Juan

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Luke
Lonergan
Sent: Wednesday, April 05, 2006 5:37 PM
To: Juan Casero (FL FLC); pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3

Juan,

On 4/5/06 1:54 PM, "Juan Casero (FL FLC)" <Juan.Casero@wholefoods.com>
wrote:

> I am not sure about this.  I mean I have postgresql 8.1.3 running on
> my Windows XP P4 HT laptop that I use for testing my webapps.  When I
> hit this pgsql on this laptop with a large query I can see the load
> spike up really high on both of my virtual processors.  Whatever,
> pgsql is doing it looks like both cpu's are being used indepently. The

> usage curve is not identical on both of them that makes me think that
> parts of the server are multithreaded.  Admittedly I am not familiar
> with the source code fo postgresql so I was hoping maybe one of the
> developers who is could definitely answer this question.

There's no part of the Postgres backend that is threaded or
multi-processed.
A reasonable explanation for your windows experience is that your web
server or the psql client may be taking some CPU cycles while the
backend is processing your query.  Also, depending on how the CPU load
is reported, if the OS is doing prefetching of I/O, it might show up as
load.

- 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

Leigh Dyer wrote:
> Luke Lonergan wrote:
>
>> Leigh,
>>
>> On 4/5/06 9:23 PM, "Leigh Dyer" <leigh@eclinic.com.au> wrote:
>>
>>> We've got a Sun Fire V40z and it's quite a nice machine -- 6x 15krpm
>>> drives, 4GB RAM, and a pair of Opteron 850s. This gives us more than
>>> enough power now for what we need, but it's nice to know that we can
>>> shoehorn a lot more RAM, and up it to eight CPU cores if needed.
>>
>>
>> We have one of these too - ours is signed by Scott McNealy.
>>
>
> Nice :)
>
>>> The newer Sun Opteron systems look nice too, but unless you're using
>>> external storage, their little 2.5" hard drives may not be ideal.
>>
>>
>> Yes - but they end-of-lifed the V20z and V40z!
>>
> That's quite disappointing to hear -- our V40z isn't even six months
> old! We're not a big company, so external storage solutions are outside
> our price range, but we still wanted a nice brand-name box, and the V40z
> was a great deal compared to smaller boxes like the HP DL385.
>
>> One big problem with the sun line in general is the tiny internal storage
>> capacity - already too small on the V40z at 5/6 drives, now ridiculous
>> at 4
>> SAS drives on the galaxy series.
>
> I'm sure those little SAS drives would be great for web servers and
> other non-IO-intensive tasks though -- I'd love to get some X4100s in to
> replace our Poweredge 1750s for that. It's a smart move overall IMHO,
> but it's certainly not great for database serving.
>
>

I notice that Supermicro have recently brought out some Opteron systems,
they are hiding them here:

http://www.supermicro.com/Aplus/system/


The 4U's have 8 SATA/SCSI drive bays - maybe still not enough, but
better than 6!

Cheers

Mark

Juan,

> Ok that is beginning to become clear to me.  Now I need to determine if
> this server is worth the investment for us.  Maybe it is not a speed
> daemon but to be honest the licensing costs of an SMP aware RDBMS is
> outside our budget.

You still haven't explained why you want multi-threaded queries.  This is
sounding like keeping up with the Joneses.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Mark Kirkwood wrote:

>>
>>>> The newer Sun Opteron systems look nice too, but unless you're using
>>>> external storage, their little 2.5" hard drives may not be ideal.
>>>
>>>
>>>
>>> Yes - but they end-of-lifed the V20z and V40z!
>>>
>> That's quite disappointing to hear -- our V40z isn't even six months
>> old! We're not a big company, so external storage solutions are
>> outside our price range, but we still wanted a nice brand-name box,
>> and the V40z was a great deal compared to smaller boxes like the HP
>> DL385.
>>
>>> One big problem with the sun line in general is the tiny internal
>>> storage
>>> capacity - already too small on the V40z at 5/6 drives, now
>>> ridiculous at 4
>>> SAS drives on the galaxy series.
>>
>>
>> I'm sure those little SAS drives would be great for web servers and
>> other non-IO-intensive tasks though -- I'd love to get some X4100s in
>> to replace our Poweredge 1750s for that. It's a smart move overall
>> IMHO, but it's certainly not great for database serving.
>>
>>
>

Excuse me for this off topic, but i notice that you are very excited
about the sun's hardware, what os do you install on them , slowlaris?,
has that os improved in some espectacular way that i should take a look
again?, i used it until solaris 9 and  the performance was horrible.
im a happy freebsd user now (using hp and dell hardware  though)

---
Miguel

Miguel wrote:
>
> Excuse me for this off topic, but i notice that you are very excited
> about the sun's hardware, what os do you install on them , slowlaris?,
> has that os improved in some espectacular way that i should take a look
> again?, i used it until solaris 9 and  the performance was horrible.
> im a happy freebsd user now (using hp and dell hardware  though)

I'm running Debian Sarge AMD64 on mine, and it works wonderfully. I'm
not a Solaris person, and I never plan on becoming one, but Sun's
Opteron hardware is quite nice. The remote management was one of the
features that sold me -- full control over power, etc. and
serial-over-LAN, through an SSH interface.

Sun don't support Debian officially (but we don't have a software
support contract anyway, so I'm not too fussed), but I'm pretty sure
they support at least SLES and RHEL.

Thanks
Leigh

Hi, Juan,

Juan Casero (FL FLC) wrote:
> Ok that is beginning to become clear to me.  Now I need to determine if
> this server is worth the investment for us.  Maybe it is not a speed
> daemon but to be honest the licensing costs of an SMP aware RDBMS is
> outside our budget.  When postgresql starts does it start up a super
> server process and then forks copies of itself to handle incoming
> requests?

It starts a super server process (Postmaster) and some background
processes (background writer, stats collector). For each incoming
connection, the postmaster forks a single-threaded backend process,
which handles all queries and transactions on this connection, and
terminates when the connection terminates.

So as a thumb-rule, each connection can utilize only a single CPU. You
can utilize a few more CPUs than you have simultaneous connections, due
to the background processes and the OS needing CPU for I/O, but thats
rather marginal.

AFAIK, Bizgres MPP has extended the backend processes to be multi
threaded, so a single connection can utilize several CPUs for some types
of queries (large data sets, sorting/joining/hashing etc.). Btw, I
presume that they might offer you a free test license, and I also
presume their license fee is much lower than Oracle or DB/2.

> Or do I have to specify how many server processes should be
> started up?

You can limit the number of server processes by setting the maximum
connection limit.

> I figured maybe I can take advantage of the multiple cpu's
> on this system by starting up enough postgres server processes to handle
> large numbers of incoming connections.  I have this server available for
> sixty days so I may as well explore the performance of postgresql on it.

Yes, you can take advantage if you have multiple clients (e. G. a wep
app, that's what the T2000 / Niagara were made for). You have a Tomcat
or Jboss sitting on it, each http connection forks its own thread. Each
customer has its own CPU :-)

Then use a connection pool to PostgreSQL, and you're fine. The more
customers, the more CPUs are utilized.

But beware, if you have floating point maths, it will be very slow. All
8 CPUs / 32 Threads share a single FPU. So if you need floating point
(e. G. Mapserver, PostGIS geoprocessing, Java2D chart drawing or
something), T2000 is not the right thing for you.


HTH,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Because I plan to develop a rather large (for us anyway) data warehouse
with PostgreSQL.  I am looking for the right hardware that can handle
queries on a database that might grow to over a 100 gigabytes.  Right
now our decision support system based on postgresql 8.1.3 stores retail
sales information for about 4 four years back *but* only as weekly
summaries.  I want to build the system so it can handle daily sales
transactions also.  You can imagine how many more records this will
involve so I am looking for hardware that can give me the performance I
need to make this project useable.  In other words parsing and loading
the daily transaction logs for our stores is likely to take huge amounts
of effort.  I need a machine that can complete the task in a reasonable
amount of time.  As people start to query the database to find sales
related reports and information I need to make sure the queries will run
reasonably fast for them.  I have already hand optimized all of my
queries on the current system.  But currently I only have weekly sales
summaries.  Other divisions in our company have done a similar project
using MS SQL Server on SMP hardware far outclassing the database server
I currently use and they report heavy loads on the server with less than
ideal query run times.  I am sure I can do my part to optimize the
queries once I start this project but there is only so much you can do.
At some point you just need more powerful hardware.  This is where I am
at right now.  Apart from that since I will only get this one chance to
buy a new server for data processing I need to make sure that I buy
something that can grow over time as our needs change.  I don't want to
buy a server only to find out later that it cannot meet our needs with
future database projects.  I have to balance a limited budget, room for
future performance growth, and current system requirements.  Trust me it
isn't easy.


Juan

-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Thursday, April 06, 2006 2:57 AM
To: pgsql-performance@postgresql.org
Cc: Juan Casero (FL FLC); Luke Lonergan
Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3

Juan,

> Ok that is beginning to become clear to me.  Now I need to determine
> if this server is worth the investment for us.  Maybe it is not a
> speed daemon but to be honest the licensing costs of an SMP aware
> RDBMS is outside our budget.

You still haven't explained why you want multi-threaded queries.  This
is sounding like keeping up with the Joneses.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On 4/6/06, Juan Casero (FL FLC) <Juan.Casero@wholefoods.com> wrote:
> Because I plan to develop a rather large (for us anyway) data warehouse
> with PostgreSQL.  I am looking for the right hardware that can handle
> queries on a database that might grow to over a 100 gigabytes.

You need to look for a server that has fast I/O. 100 GB of data will
take a long time to scan through and won't fit in RAM.

> Right
> now our decision support system based on postgresql 8.1.3 stores retail
> sales information for about 4 four years back *but* only as weekly
> summaries.  I want to build the system so it can handle daily sales
> transactions also.  You can imagine how many more records this will
> involve so I am looking for hardware that can give me the performance I
> need to make this project useable.

Sounds like you need to be doing a few heavy queries when you do this,
not tons of small queries. That likely means you need fewer CPUs that
are very fast.

> In other words parsing and loading
> the daily transaction logs for our stores is likely to take huge amounts
> of effort.  I need a machine that can complete the task in a reasonable
> amount of time.

See my previous comment

> As people start to query the database to find sales
> related reports and information I need to make sure the queries will run
> reasonably fast for them.

Get more than one CPU core and make sure you have a lot of drive
spindles. You will definately want to be able to ensure a long running
query doesn't hog your i/o system. I have a server with a single disk
and when we do a long query the server load will jump from about .2 to
10 until the long query finishes. More cpus won't help this because
the bottle neck is the disk.

>  I have already hand optimized all of my
> queries on the current system.  But currently I only have weekly sales
> summaries.  Other divisions in our company have done a similar project
> using MS SQL Server on SMP hardware far outclassing the database server
> I currently use and they report heavy loads on the server with less than
> ideal query run times.  I am sure I can do my part to optimize the
> queries once I start this project but there is only so much you can do.
> At some point you just need more powerful hardware.  This is where I am
> at right now.

You say "this is where I am at right __now__" but where will you be in
9 months? Sounds like you will be i/o bound by the time you get above
10GB.

> Apart from that since I will only get this one chance to
> buy a new server for data processing I need to make sure that I buy
> something that can grow over time as our needs change.  I don't want to
> buy a server only to find out later that it cannot meet our needs with
> future database projects.  I have to balance a limited budget, room for
> future performance growth, and current system requirements.  Trust me it
> isn't easy.

Isn't it about time we had our annual "what kind of server can I get
for $8k" thread?

--
Matthew Nuzum
www.bearfruit.org

On Apr 5, 2006, at 5:58 PM, August Zajonc wrote:

> Most involve some AMD Opertons, lots of spindles with a good raid
> controller preferred to one or two large disks and a good helping of
> ram. Be interesting to get some numbers on the sunfire machine.

I can highly recommend the SunFire X4100, however the only dual-
channel RAID card that fits in the box is the Adaptec 2230SLP.  It is
not quite as fast as the LSI 320-2x when running freebsd, but is
sufficient for my purpose.

On Apr 5, 2006, at 9:11 PM, Marcelo Tada wrote:

> What are you think about the Sun Fire X64 X4200 Server?

I use the X4100 and like it a lot.  I'm about to buy another.  I see
no advantage to the X4200 unless you want the extra internal disks.
I use an external array.

On Apr 6, 2006, at 12:47 AM, Leigh Dyer wrote:

> I'm sure those little SAS drives would be great for web servers and
> other non-IO-intensive tasks though -- I'd love to get some X4100s
> in to replace our Poweredge 1750s for that. It's a smart move
> overall IMHO,

For this purpose, bang for the buck would lead me to getting Dell
1850 with hardware RAID and U320 drives.  The quick-n-dirty tests
i've seen on the FreeBSD mailing list shows the disks are much faster
on the 1850 than the X4100 with its SAS disks.

Juan,

On 4/6/06 7:01 AM, "Matthew Nuzum" <mattnuzum@gmail.com> wrote:

>> Apart from that since I will only get this one chance to
>> buy a new server for data processing I need to make sure that I buy
>> something that can grow over time as our needs change.  I don't want to
>> buy a server only to find out later that it cannot meet our needs with
>> future database projects.  I have to balance a limited budget, room for
>> future performance growth, and current system requirements.  Trust me it
>> isn't easy.
>
> Isn't it about time we had our annual "what kind of server can I get
> for $8k" thread?

Based on Juan's description, here's a config that will *definitely* be the
fastest possible in an $8K budget:

Buy a dual opteron server with 8 x 400GB SATA II disks on a 3Ware 9550SX
RAID controller with 16GB of RAM pre-installed with Centos 4.3 for $6,000
here:
  http://www.asacomputers.com/

Download the *free* open source Bizgres here:
  http://bgn.greenplum.com/

Use bitmap indexes for columns with less than 10,000 unique values, and your
system will fly through 100GB.

This is the fastest OSS business intelligence kit for the money, guaranteed.

- Luke

Hi Leigh

inline comments


Leigh Dyer wrote:

> Luke Lonergan wrote:
>
>> Juan,
>
> We've got a Sun Fire V40z and it's quite a nice machine -- 6x 15krpm
> drives, 4GB RAM, and a pair of Opteron 850s. This gives us more than
> enough power now for what we need, but it's nice to know that we can
> shoehorn a lot more RAM, and up it to eight CPU cores if needed.
>
> The newer Sun Opteron systems look nice too, but unless you're using
> external storage, their little 2.5" hard drives may not be ideal.
>

Thats because Sun Fire V40z had write cache turned on while the
4200/4100 has the write cache turned off. There is a religious belief
around the "write cache" on the disk in Sun :-)  To really compare the
performance, you have to turn on the write cache (I believe it was
format -e and the cache option.. but that could have changed.. need to
verify that again.. Same goes for T2000 SAS disks too.. Write cache is
turned off on it so be careful before you compare benchmarks on internal
drives :-)

-Jignesh



> Thanks
> Leigh
>
>>
>> - Luke
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

For a DSS type workload with PostgreSQL where you end up with single
long running queries on postgresql with about 100GB, you better use
something like Sun Fire V40z with those fast Ultra320 internal drives.
This might be perfect low cost complete database in a box.

Sun Fire T2000 is great for OLTP where you can end up with hundreds of
users doing quick and small lookups and T2000 can crank simple thread
executions far better than others. However when it comes to long running
queries you end up using 1/32 of the power and may not live up to your
expectations.  For example consider your PostgreSQL talking to Apache
WebServer all on T2000... You can put them in separate zones if you have
different administrators for them. :-)

As for PostgreSQL on Solaris, I already have the best parameters to use
on Solaris based on my tests, the default odatasync hurts performance on
Solaris, so does checkpoint segments, others are tweaked so that they
are set for bigger databases and hence may not show much difference on
performances...

That said I will still be interested to see your app performance with
postgreSQL on Sun Fire T2000 as there are always ways of perseverence to
improve performance :-)


Regards,
Jignesh


Juan Casero (FL FLC) wrote:

>Because I plan to develop a rather large (for us anyway) data warehouse
>with PostgreSQL.  I am looking for the right hardware that can handle
>queries on a database that might grow to over a 100 gigabytes.  Right
>now our decision support system based on postgresql 8.1.3 stores retail
>sales information for about 4 four years back *but* only as weekly
>summaries.  I want to build the system so it can handle daily sales
>transactions also.  You can imagine how many more records this will
>involve so I am looking for hardware that can give me the performance I
>need to make this project useable.  In other words parsing and loading
>the daily transaction logs for our stores is likely to take huge amounts
>of effort.  I need a machine that can complete the task in a reasonable
>amount of time.  As people start to query the database to find sales
>related reports and information I need to make sure the queries will run
>reasonably fast for them.  I have already hand optimized all of my
>queries on the current system.  But currently I only have weekly sales
>summaries.  Other divisions in our company have done a similar project
>using MS SQL Server on SMP hardware far outclassing the database server
>I currently use and they report heavy loads on the server with less than
>ideal query run times.  I am sure I can do my part to optimize the
>queries once I start this project but there is only so much you can do.
>At some point you just need more powerful hardware.  This is where I am
>at right now.  Apart from that since I will only get this one chance to
>buy a new server for data processing I need to make sure that I buy
>something that can grow over time as our needs change.  I don't want to
>buy a server only to find out later that it cannot meet our needs with
>future database projects.  I have to balance a limited budget, room for
>future performance growth, and current system requirements.  Trust me it
>isn't easy.
>
>
>Juan
>
>-----Original Message-----
>From: Josh Berkus [mailto:josh@agliodbs.com]
>Sent: Thursday, April 06, 2006 2:57 AM
>To: pgsql-performance@postgresql.org
>Cc: Juan Casero (FL FLC); Luke Lonergan
>Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
>
>Juan,
>
>
>
>>Ok that is beginning to become clear to me.  Now I need to determine
>>if this server is worth the investment for us.  Maybe it is not a
>>speed daemon but to be honest the licensing costs of an SMP aware
>>RDBMS is outside our budget.
>>
>>
>
>You still haven't explained why you want multi-threaded queries.  This
>is sounding like keeping up with the Joneses.
>
>--
>Josh Berkus
>Aglio Database Solutions
>San Francisco
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: don't forget to increase your free space map settings
>
>

I am thinking the most flexible solution would be to get a dual Operon
machine, and initially do both data loading and queries on the same
machine.  When the load gets too high, buy a second machine and set it
up as a Slony slave and run your queries on that, and do the data loads
on the original machine as master.

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

Juan Casero (FL FLC) wrote:
> Because I plan to develop a rather large (for us anyway) data warehouse
> with PostgreSQL.  I am looking for the right hardware that can handle
> queries on a database that might grow to over a 100 gigabytes.  Right
> now our decision support system based on postgresql 8.1.3 stores retail
> sales information for about 4 four years back *but* only as weekly
> summaries.  I want to build the system so it can handle daily sales
> transactions also.  You can imagine how many more records this will
> involve so I am looking for hardware that can give me the performance I
> need to make this project useable.  In other words parsing and loading
> the daily transaction logs for our stores is likely to take huge amounts
> of effort.  I need a machine that can complete the task in a reasonable
> amount of time.  As people start to query the database to find sales
> related reports and information I need to make sure the queries will run
> reasonably fast for them.  I have already hand optimized all of my
> queries on the current system.  But currently I only have weekly sales
> summaries.  Other divisions in our company have done a similar project
> using MS SQL Server on SMP hardware far outclassing the database server
> I currently use and they report heavy loads on the server with less than
> ideal query run times.  I am sure I can do my part to optimize the
> queries once I start this project but there is only so much you can do.
> At some point you just need more powerful hardware.  This is where I am
> at right now.  Apart from that since I will only get this one chance to
> buy a new server for data processing I need to make sure that I buy
> something that can grow over time as our needs change.  I don't want to
> buy a server only to find out later that it cannot meet our needs with
> future database projects.  I have to balance a limited budget, room for
> future performance growth, and current system requirements.  Trust me it
> isn't easy.
>
>
> Juan
>
> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com]
> Sent: Thursday, April 06, 2006 2:57 AM
> To: pgsql-performance@postgresql.org
> Cc: Juan Casero (FL FLC); Luke Lonergan
> Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
>
> Juan,
>
> > Ok that is beginning to become clear to me.  Now I need to determine
> > if this server is worth the investment for us.  Maybe it is not a
> > speed daemon but to be honest the licensing costs of an SMP aware
> > RDBMS is outside our budget.
>
> You still haven't explained why you want multi-threaded queries.  This
> is sounding like keeping up with the Joneses.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +