Tweakers.net has done a database performance test between a Sun T2000 (8
core T1) and a Sun X4200 (2 dual core Opteron 280). The database
benchmark is developed inhouse and represents the average query pattern
from their website. It is MySQL centric because Tweakers.net runs on
MySQL, but Arjen van der Meijden has ported it to PostgreSQL and has
done basic optimizations like adding indexes.

Arjen wrote about some of the preliminary results previously in
http://archives.postgresql.org/pgsql...6/msg00358.php
but the article has now been published http://tweakers.net/reviews/633/7
This is all the more impressive if you scroll down and look at the
behaviour of MySQL (after tweaking by both MySQL AB and Sun).

Jochem

---------------------------(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

Jochem van Dieten wrote:
> Tweakers.net has done a database performance test between a Sun T2000 (8
> core T1) and a Sun X4200 (2 dual core Opteron 280). The database
> benchmark is developed inhouse and represents the average query pattern
> from their website. It is MySQL centric because Tweakers.net runs on
> MySQL, but Arjen van der Meijden has ported it to PostgreSQL and has
> done basic optimizations like adding indexes.
>
> Arjen wrote about some of the preliminary results previously in
> http://archives.postgresql.org/pgsql...6/msg00358.php
> but the article has now been published http://tweakers.net/reviews/633/7
> This is all the more impressive if you scroll down and look at the
> behaviour of MySQL (after tweaking by both MySQL AB and Sun).

I would love to get my hands on that postgresql version and see how much
farther it could be optimized.

Joshua D. Drake


>
> Jochem
>
> ---------------------------(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
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 29-7-2006 17:43, Joshua D. Drake wrote:
>
> I would love to get my hands on that postgresql version and see how much
> farther it could be optimized.

You probably mean the entire installation? As said in my reply to
Jochem, I've spent a few days testing all queries to improve their
performance. I'm not sure what kind of improvements that yielded, but if
I remember correctly its in the order of 3-5 times for the entire
benchmark, compared to the initial MySQL-layout and queries.

If you mean the configuration and which version it was, I can look that
up for you if you'd like. Including the compilation switches used on the
T2000.

If we get to keep the machine (which we're going to try, but that's with
worse performance than with their x4200 a bit doubtful), I'm sure we can
work something out.
Then again, we regularly have other server hardware on which the same
database is used, so even without the T2000 we could still do some
effort to further improve postgresql's performance.
It might be interesting to have some Postgres experts do some more
tuning and allowing MySQL AB to do the same... But I'm not sure if we're
willing to spent that much extra time on a benchmark (just testing one
database costs us about a day and a half...)

Best regards,

Arjen

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

http://archives.postgresql.org

On 29-7-2006 17:02, Jochem van Dieten wrote:
> Tweakers.net has done a database performance test between a Sun T2000 (8
> core T1) and a Sun X4200 (2 dual core Opteron 280). The database
> benchmark is developed inhouse and represents the average query pattern
> from their website. It is MySQL centric because Tweakers.net runs on
> MySQL, but Arjen van der Meijden has ported it to PostgreSQL and has
> done basic optimizations like adding indexes.

There were a few minor datatype changes (like enum('Y', 'N') to boolean,
but on the other hand also 'int unsigned' to 'bigint'), a few small
query changes (i.e. rewriting join orders, most turned out to be
necessary for mysql 5.0 and 5.1 as well anyway) and even fewer larger
query changes (including a subquery, instead of the results of another
query).

The indexes also included adding partial indexes and several combined
indexes.

All in all I think it took about a week to do the conversion and test
the specific queries. Luckily PostgreSQL allows for much clearer
information on what a specific query is doing and much faster
adding/removing of indexes (mysql rewrites the entire table).

> Arjen wrote about some of the preliminary results previously in
> http://archives.postgresql.org/pgsql...6/msg00358.php
> but the article has now been published http://tweakers.net/reviews/633/7
> This is all the more impressive if you scroll down and look at the
> behaviour of MySQL (after tweaking by both MySQL AB and Sun).

Actually, we haven't had contact with MySQL AB. But as far as I know,
the Sun engineers have contacted them about this.
As it turns out there are some suboptimal machine codes generated from
MySQL's source for the Niagara T1 and MySQL has some issues with
InnoDB's scaling in the later 5.0-versions:
http://www.mysqlperformanceblog.com/...b-scalability/

Then again, we weren't able to compile the PG8.2 dev using all
optimizations of Sun's Studio Compiler (the mlibopt-switch failed), so
there is very likely more room for improvement on that field as well.

Best regards,

Arjen

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Arjen van der Meijden wrote:
> On 29-7-2006 17:43, Joshua D. Drake wrote:
>>
>> I would love to get my hands on that postgresql version and see how
>> much farther it could be optimized.
>
> You probably mean the entire installation? As said in my reply to
> Jochem, I've spent a few days testing all queries to improve their
> performance. I'm not sure what kind of improvements that yielded, but if
> I remember correctly its in the order of 3-5 times for the entire
> benchmark, compared to the initial MySQL-layout and queries.
>
> If you mean the configuration and which version it was, I can look that
> up for you if you'd like. Including the compilation switches used on the
> T2000.

Well I would be curious about the postgresql.conf and how much ram
etc... it had.

> Then again, we regularly have other server hardware on which the same
> database is used, so even without the T2000 we could still do some
> effort to further improve postgresql's performance.
> It might be interesting to have some Postgres experts do some more
> tuning and allowing MySQL AB to do the same... But I'm not sure if we're
> willing to spent that much extra time on a benchmark (just testing one
> database costs us about a day and a half...)

I understand, I just have a feeling that we could do even better I do
appreciate all your efforts.

Sincerely,

Joshua D. Drake


>
> Best regards,
>
> Arjen
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/



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

On 29-7-2006 19:01, Joshua D. Drake wrote:
> Well I would be curious about the postgresql.conf and how much ram
> etc... it had.

It was the 8core version with 16GB memory... but actually that's just
overkill, the active portions of the database easily fits in 8GB and a
test on another machine with just 2GB didn't even show that much
improvements when going to 7GB (6x1G, 2x 512M), it was mostly in the
range of 10% improvement or less.

Anyway, the differences to the default postgresql.conf:
shared_buffers = 30000
Tests with 40k, 50k en 60k didn't really show improvements.

work_mem = 2048
This probably could've been set higher with the sheer amount of
not-really-used memory.

maintenance_work_mem = 65535
Not really important of course

max_fsm_pages = 50000
Somehow it needed to be set quite high, probably because we only cleaned
up after doing over 200k requests.

effective_cache_size = 350000
As said, the database fitted in 8GB of memory, so I didn't see a need to
set this higher than for the 8GB machines (x4200 and another T2000 we had).

default_statistics_target = 200
For a few columns on the largest tables I manually raised it to 1000

log_min_duration_statement = 1000
I'm not sure if this has much overhead? Stats logging was turned/left on
as well.
Turning that off improved it a few percent.

> I understand, I just have a feeling that we could do even better I do
> appreciate all your efforts.

Well, I'll keep that in mind
What it makes even worse for MySQL is that it had (on another machine)
about 8M hits on the query cache for 4M inserts, i.e. half of the
queries weren't even executed on it.

Best regards,

Arjen

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Jochem,

On 7/29/06 8:02 AM, "Jochem van Dieten" <jochemd@oli.tudelft.nl> wrote:

> Tweakers.net has done a database performance test between a Sun T2000 (8
> core T1) and a Sun X4200 (2 dual core Opteron 280). The database
> benchmark is developed inhouse and represents the average query pattern
> from their website. It is MySQL centric because Tweakers.net runs on
> MySQL, but Arjen van der Meijden has ported it to PostgreSQL and has
> done basic optimizations like adding indexes.

Excellent article/job on performance profiling - thanks!

Back in March, Anandtech also did a Niagara article profiling web + database
performance:
http://www.anandtech.com/IT/showdoc.aspx?i=2727&p=7

and the results for the T2000/Niagara were also lesser to the multi-core
Opteron. Now maybe this article will help Sun to improve the processor's
performance.

- Luke



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

Hi Arjen,

I am curious about your Sun Studio compiler options also.
Can you send that too ?

Any other tweakings that you did on Solaris?

Thanks.

Regards,
Jignesh


Arjen van der Meijden wrote:
> On 29-7-2006 19:01, Joshua D. Drake wrote:
>> Well I would be curious about the postgresql.conf and how much ram
>> etc... it had.
>
> It was the 8core version with 16GB memory... but actually that's just
> overkill, the active portions of the database easily fits in 8GB and a
> test on another machine with just 2GB didn't even show that much
> improvements when going to 7GB (6x1G, 2x 512M), it was mostly in the
> range of 10% improvement or less.
>
> Anyway, the differences to the default postgresql.conf:
> shared_buffers = 30000
> Tests with 40k, 50k en 60k didn't really show improvements.
>
> work_mem = 2048
> This probably could've been set higher with the sheer amount of
> not-really-used memory.
>
> maintenance_work_mem = 65535
> Not really important of course
>
> max_fsm_pages = 50000
> Somehow it needed to be set quite high, probably because we only
> cleaned up after doing over 200k requests.
>
> effective_cache_size = 350000
> As said, the database fitted in 8GB of memory, so I didn't see a need
> to set this higher than for the 8GB machines (x4200 and another T2000
> we had).
>
> default_statistics_target = 200
> For a few columns on the largest tables I manually raised it to 1000
>
> log_min_duration_statement = 1000
> I'm not sure if this has much overhead? Stats logging was turned/left
> on as well.
> Turning that off improved it a few percent.
>
>> I understand, I just have a feeling that we could do even better I
>> do appreciate all your efforts.
>
> Well, I'll keep that in mind
> What it makes even worse for MySQL is that it had (on another machine)
> about 8M hits on the query cache for 4M inserts, i.e. half of the
> queries weren't even executed on it.
>
> Best regards,
>
> Arjen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Hi Jignesh,

It was a cvs-checkout of 8.2 devel, compiled using:
CPPFLAGS="-fast -xtarget=ultraT1 -xnolibmopt" CC=/opt/SUNWspro/bin/cc
../configure --without-readline

We'd gotten a specially adjusted Solaris version from Sun Holland for
the T2000. It was a dvd with a Solaris flar archive from 11 april 2006
and patches from 25 april 2006. It also had the preferred Solaris System
settings already applied. If you need more details about that dvd, I
think your best option is to contact Hans Nijbacker or Bart Muijzer,
since we're no Solaris-experts

Appart from that, we did no extra tuning of the OS, nor did Hans for the
MySQL-optimizations (afaik, but then again, he knows best).

Best regards,

Arjen van der Meijden

Jignesh Shah wrote:
> Hi Arjen,
>
> I am curious about your Sun Studio compiler options also.
> Can you send that too ?
>
> Any other tweakings that you did on Solaris?
>
> Thanks.
>
> Regards,
> Jignesh
>
>
> Arjen van der Meijden wrote:
>> On 29-7-2006 19:01, Joshua D. Drake wrote:
>>> Well I would be curious about the postgresql.conf and how much ram
>>> etc... it had.
>>
>> It was the 8core version with 16GB memory... but actually that's just
>> overkill, the active portions of the database easily fits in 8GB and a
>> test on another machine with just 2GB didn't even show that much
>> improvements when going to 7GB (6x1G, 2x 512M), it was mostly in the
>> range of 10% improvement or less.
>>
>> Anyway, the differences to the default postgresql.conf:
>> shared_buffers = 30000
>> Tests with 40k, 50k en 60k didn't really show improvements.
>>
>> work_mem = 2048
>> This probably could've been set higher with the sheer amount of
>> not-really-used memory.
>>
>> maintenance_work_mem = 65535
>> Not really important of course
>>
>> max_fsm_pages = 50000
>> Somehow it needed to be set quite high, probably because we only
>> cleaned up after doing over 200k requests.
>>
>> effective_cache_size = 350000
>> As said, the database fitted in 8GB of memory, so I didn't see a need
>> to set this higher than for the 8GB machines (x4200 and another T2000
>> we had).
>>
>> default_statistics_target = 200
>> For a few columns on the largest tables I manually raised it to 1000
>>
>> log_min_duration_statement = 1000
>> I'm not sure if this has much overhead? Stats logging was turned/left
>> on as well.
>> Turning that off improved it a few percent.
>>
>>> I understand, I just have a feeling that we could do even better I
>>> do appreciate all your efforts.
>>
>> Well, I'll keep that in mind
>> What it makes even worse for MySQL is that it had (on another machine)
>> about 8M hits on the query cache for 4M inserts, i.e. half of the
>> queries weren't even executed on it.
>>
>> Best regards,
>>
>> Arjen
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

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

On 7/29/06, Jochem van Dieten <jochemd@oli.tudelft.nl> wrote:
> Tweakers.net has done a database performance test between a Sun T2000 (8
> core T1) and a Sun X4200 (2 dual core Opteron 280). The database
> benchmark is developed inhouse and represents the average query pattern
> from their website. It is MySQL centric because Tweakers.net runs on
> MySQL, but Arjen van der Meijden has ported it to PostgreSQL and has
> done basic optimizations like adding indexes.

anandtech did a comparison of opteron/xeon/sun t1 not to long ago and
published some mysql/postgresql results. however, they were careful
not to publish the quad core data for pg to compare vs. mysql, which
in my opinion would have shown a blowout victory for pg. (also was pg
8.0).

the fact is, postgresql is often faster than mysql under real
workloads, especially when utilizing features such as stored
procedures and such.

merlin

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