[PERFORM] PostgreSQL scalability on Sun UltraSparc T1
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-performance/2006-06/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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1
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-performance/2006-06/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 [EMAIL PROTECTED] 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
Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1
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
Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1
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-performance/2006-06/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/2006/07/28/returning-to-innodb-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
Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1
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
Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1
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 = 3 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 = 5 Somehow it needed to be set quite high, probably because we only cleaned up after doing over 200k requests. effective_cache_size = 35 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
Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig
> systems could send me their bonnie + benchmarksql results! I am one of the authors of BenchmarkSQL, it is similar to a DBT2. But, its very easy to use (&/or abuse). It's a multithreaded Java Swing client that can run the exact same benchmark (uses JDBC prepared statements) against Postgres/EnterpriseDB/Bizgres, MySQueeL, Horacle, Microsloth, etc, etc. You can find BenchmarkSQL on pgFoundry and SourceForge. As expected, Postgres is good on this benchmark and is getting better all the time. If you run an EnterpriseDB install right out of the box versus a PG install right out of the box you'll notice that EnterpriseDB outperforms PG by better than 2x. This does NOT mean that EnterpriseDB is 3x faster than Postgres... EnterpriseDB is the same speed as Postgres. We do something we call "Dynatune" at db startup time. The algorithm is pretty simple in our current GA version and really only considers the amount of RAM, SHARED Memory, and machine usage pattern. Manual tuning is required to really optimize performance For great insight into the basics of quickly tuning PostgreSQL for a reasonable starting point, check out the great instructions offered by Josh Berkus and Joe Conway at http://www.powerpostgresql.com/PerfList/. The moral of this unreasonably verbose email is that you shouldn't abuse BenchmarkSQL and measure runs without making sure that, at least, quick/simple best practices have been applied to tuning the db's you are choosing to test. --Denis Lussier CTO http://www.enterprisedb.com >
Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig
Denis, On 7/29/06 11:09 AM, "Denis Lussier" <[EMAIL PROTECTED]> wrote: > We do something we call "Dynatune" at db startup time. Sounds great - where do we download it? - 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
Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1
Jochem, On 7/29/06 8:02 AM, "Jochem van Dieten" <[EMAIL PROTECTED]> 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
Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig
Not sure that EnterpriseDB's Dynatune is the general purpose answer that the PG community has been searching to find. Actually, I think it could be, but... the community process will decide. We are presently planning to create a site that will be called http://gforge.enterprisedb.com that will be similar in spirit to BizGres. By this I mean that we will be open sourcing many key small "improvements" (in the eye of the beholder) for PG that will potentially make it into PG (likely in some modified format) depending on the reactions and desires of the general Postgres community. In case anyone is wondering... NO, EnterpriseDB won't be open sourcing the legacy Horacle stuff we've added to our product (at least not yet). This stuff is distributed under our Commercial Open Source license (similar to SugarCRM's). Our Commercial Open Source license simply means that if you buy a Platinum Subscription to our product, then you can keep the source code under your pillow and use it internally at your company however you see fit. --Denis Lussier CTO http://www.enterprisedb.com On 7/29/06, Luke Lonergan <[EMAIL PROTECTED]> wrote: Denis,On 7/29/06 11:09 AM, "Denis Lussier" < [EMAIL PROTECTED]> wrote:> We do something we call "Dynatune" at db startup time.Sounds great - where do we download it?- Luke
