Thread: 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
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 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/
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
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
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/
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
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
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
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 >
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
On Sat, Jul 29, 2006 at 08:43:49AM -0700, Joshua D. Drake wrote: > 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. I'd love to get an english translation that we could use for PR. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On 1-8-2006 19:26, Jim C. Nasby wrote: > On Sat, Jul 29, 2006 at 08:43:49AM -0700, Joshua D. Drake wrote: > > I'd love to get an english translation that we could use for PR. Actually, we have an english version of the Socket F follow-up. http://tweakers.net/reviews/638 which basically displays the same results for Postgres vs MySQL. If and when a translation of the other article arrives, I don't know. Other follow-up stories will follow as well, whether and how soon those will be translated, I also don't know. We are actually pretty interested in doing so, but its a lot of work to translate correctly :) Best regards, Arjen
Hi, Arjen, Arjen van der Meijden wrote: > 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. I'd be interested in the commit_siblings and commit_delay settings, tuning them could give a high increase on throughput for highly concurrent insert/update workloads, at the cost of latency (and thus worse results for low concurrency situations). Different fsync method settings can also make a difference (I presume that syncing was enabled). 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
Hi Markus, As said, our environment really was a read-mostly one. So we didn't do much inserts/updates and thus spent no time tuning those values and left them as default settings. Best regards, Arjen Markus Schaber wrote: > Hi, Arjen, > > Arjen van der Meijden wrote: > >> 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. > > I'd be interested in the commit_siblings and commit_delay settings, > tuning them could give a high increase on throughput for highly > concurrent insert/update workloads, at the cost of latency (and thus > worse results for low concurrency situations). > > Different fsync method settings can also make a difference (I presume > that syncing was enabled). > > HTH, > Markus > >