Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1
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 TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1
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 ---(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 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1
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 = 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1
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 = 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 ---(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
Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1
On 7/29/06, 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. 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
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
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=2727p=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
