Обсуждение: PostgreSQL publishes first real benchmark
Hello all, I think this result will be useful for performance discussions of postgresql against other databases. http://www.spec.org/jAppServer2004/results/res2007q3/ More on Josh Berkus's blog: http://blogs.ittoolbox.com/database/soup/archives/postgresql-publishes-first-real-benchmark-17470 Regards, Jignesh
On 7/9/07, Jignesh K. Shah <J.K.Shah@sun.com> wrote: > I think this result will be useful for performance discussions of > postgresql against other databases. I'm happy to see an industry-standard benchmark result for PostgreSQL. Great work guys! -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
Jonah H. Harris wrote:
> On 7/9/07, Jignesh K. Shah <J.K.Shah@sun.com> wrote:
>> I think this result will be useful for performance discussions of
>> postgresql against other databases.
>
> I'm happy to see an industry-standard benchmark result for PostgreSQL.
> Great work guys!
I would note that if you track through the other results that we indeed
beat MySQL ;)
Joshua D. Drake
>
--
=== 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/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Jignesh K. Shah wrote: > Hello all, > > I think this result will be useful for performance discussions of > postgresql against other databases. > > http://www.spec.org/jAppServer2004/results/res2007q3/ > > More on Josh Berkus's blog: > > http://blogs.ittoolbox.com/database/soup/archives/postgresql-publishes-first-real-benchmark-17470 That's really exciting news! I'm sure you spent a lot of time tweaking the settings, so let me ask you something topical: How did you end up with the bgwriter settings you used? Did you experiment with different values? How much difference did it make? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Mon, Jul 09, 2007 at 11:57:13AM -0400, Jignesh K. Shah wrote: > I think this result will be useful for performance discussions of > postgresql against other databases. > > http://www.spec.org/jAppServer2004/results/res2007q3/ Am I right if this is for a T2000 (Niagara) database server? It sure is interesting, but I can't help thinking it's not a very common configuration... /* Steinar */ -- Homepage: http://www.sesse.net/
Steinar H. Gunderson wrote: > On Mon, Jul 09, 2007 at 11:57:13AM -0400, Jignesh K. Shah wrote: >> I think this result will be useful for performance discussions of >> postgresql against other databases. >> >> http://www.spec.org/jAppServer2004/results/res2007q3/ > > Am I right if this is for a T2000 (Niagara) database server? It sure is > interesting, but I can't help thinking it's not a very common > configuration... I have yet to see a benchmark that was valid on a common configuration. Common configurations don't make people in suits go... "oooohhhh sexy!". It is also the reason that those in the know typically ignore all benchmarks and do their own testing. Joshua D. Drake > > /* Steinar */ -- === 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
Hi Heikki, Heikki Linnakangas wrote: > > That's really exciting news! > > I'm sure you spent a lot of time tweaking the settings, so let me ask > you something topical: > > How did you end up with the bgwriter settings you used? Did you > experiment with different values? How much difference did it make? > Background writer is still a pain to get it right.. I say it is a necessary evil since you are trying to balance it with trying to level writes to the disks and lock contentions caused by the writer itself to the postgresql connections. Our typical problem will arise at the high number of users where all users are suddenly locked due to the bgwriter holding the locks.. Using the hotuser script (which uses pearl/Dtrace combination) we ran quite a bit of numbers trying to see which ones results in less overall time spent in PGLock* calls and yet gave good uniform writes to the disks. After reaching the published settings, everynow and then we would try playing with different values to see if it improves but generally seemed to degrade if changed.. (Of course your mileage will vary depending on config, workload, etc). Still I believe the locking mechanism needs to be revisited at some point since that seems to be the one which will eventually limit the number of users in such a workload. (Specially if you dont hit the right settings for your workload) Hopefully soon we will get access to bigger capacity servers and redo SMP tests on it with the background writer. Regards, Jignesh
Joshua D. Drake wrote: > Steinar H. Gunderson wrote: >> On Mon, Jul 09, 2007 at 11:57:13AM -0400, Jignesh K. Shah wrote: >>> I think this result will be useful for performance discussions of >>> postgresql against other databases. >>> >>> http://www.spec.org/jAppServer2004/results/res2007q3/ >> >> Am I right if this is for a T2000 (Niagara) database server? It sure is >> interesting, but I can't help thinking it's not a very common >> configuration... > > I have yet to see a benchmark that was valid on a common configuration. > Common configurations don't make people in suits go... "oooohhhh sexy!". > It is also the reason that those in the know typically ignore all > benchmarks and do their own testing. This kind of benchmarks are primarily a marketing thing. And as such, it's a very good one to have, because there are certainly a large amounts of PHBs who will just dispose a solution that's not "proven" (we all know what that means, but they don't) without even looking at the details. From a *technical* perspective it doesn't mean anything that you can apply directly to your application. All it says is "yes, you can make it fast enough". But again, as a marketing thing, it's great. //Magnus
On Jul 9, 2007, at 1:02 PM, Joshua D. Drake wrote: > It is also the reason that those in the know typically ignore all > benchmarks and do their own testing. Heresy!
On Mon, 9 Jul 2007, Joshua D. Drake wrote: > I would note that if you track through the other results that we indeed beat > MySQL ;) There's just enough hardware differences between the two configurations that it's not quite a fair fight. For example, the MySQL test had 10K RPM drives in the database server storage array, while the PostgreSQL one had 15K RPM ones. A few other small differences as well if you dig into the configurations, all of which I noted favored the PG system. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On 7/9/07, Greg Smith <gsmith@gregsmith.com> wrote: > There's just enough hardware differences between the two configurations > that it's not quite a fair fight. For example, the MySQL test had 10K RPM > drives in the database server storage array, while the PostgreSQL one had > 15K RPM ones. A few other small differences as well if you dig into the > configurations, all of which I noted favored the PG system. Agreed. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
Jonah H. Harris wrote:
> On 7/9/07, Greg Smith <gsmith@gregsmith.com> wrote:
>> There's just enough hardware differences between the two configurations
>> that it's not quite a fair fight. For example, the MySQL test had 10K
>> RPM
>> drives in the database server storage array, while the PostgreSQL one had
>> 15K RPM ones. A few other small differences as well if you dig into the
>> configurations, all of which I noted favored the PG system.
>
> Agreed.
>
PostgreSQL still beats MySQL ;)
Joshua D. Drake
--
=== 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/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On 7/9/07, Joshua D. Drake <jd@commandprompt.com> wrote: > PostgreSQL still beats MySQL ;) Agreed. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
On Mon, Jul 09, 2007 at 01:48:44PM -0400, Jignesh K. Shah wrote: > > Hi Heikki, > > Heikki Linnakangas wrote: > > > >That's really exciting news! > > > >I'm sure you spent a lot of time tweaking the settings, so let me ask > >you something topical: > > > >How did you end up with the bgwriter settings you used? Did you > >experiment with different values? How much difference did it make? > > > > Background writer is still a pain to get it right.. I say it is a > necessary evil since you are trying to balance it with trying to level > writes to the disks and lock contentions caused by the writer itself to > the postgresql connections. Our typical problem will arise at the high > number of users where all users are suddenly locked due to the bgwriter > holding the locks.. Using the hotuser script (which uses pearl/Dtrace > combination) we ran quite a bit of numbers trying to see which ones > results in less overall time spent in PGLock* calls and yet gave good > uniform writes to the disks. After reaching the published settings, > everynow and then we would try playing with different values to see if > it improves but generally seemed to degrade if changed.. (Of course your > mileage will vary depending on config, workload, etc). > > Still I believe the locking mechanism needs to be revisited at some > point since that seems to be the one which will eventually limit the > number of users in such a workload. (Specially if you dont hit the right > settings for your workload) Do you know specifically what locks were posing the problem? I have a theory that having individual backends run the clock sweep limits concurrency and I'm wondering if you were seeing any of that. The lock in question would be BufFreelistLock. -- Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Вложения
Hi, > I think this result will be useful for performance discussions of > postgresql against other databases. > > http://www.spec.org/jAppServer2004/results/res2007q3/ > > More on Josh Berkus's blog: > > http://blogs.ittoolbox.com/database/soup/archives/postgresql- > publishes-first-real-benchmark-17470 > Congrats to everyone that worked to make this happen. While I will never get customers to buy that nice hardware (and I would recommend the JBoss Appserver anyway :-), it really is a big sign telling "yes postgres can be really fast" - as oposed to the urban legends around. Heiko -- Reg. Adresse: Red Hat GmbH, Hauptstätter Strasse 58, 70178 Stuttgart Handelsregister: Amtsgericht Stuttgart HRB 153243 Geschäftsführer: Brendan Lane, Charlie Peters, Michael Cunningham, Werner Knoblich
am I wrong or DB2 9.1 is faster on less powerfull hardware ? Le lundi 09 juillet 2007 à 11:57 -0400, Jignesh K. Shah a écrit : > Hello all, > > I think this result will be useful for performance discussions of > postgresql against other databases. > > http://www.spec.org/jAppServer2004/results/res2007q3/ > > More on Josh Berkus's blog: > > http://blogs.ittoolbox.com/database/soup/archives/postgresql-publishes-first-real-benchmark-17470 > > Regards, > Jignesh > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
Its hard to do direct comparison since that one used a different commercial application server than the PostgreSQL test.. As we do more tests with the help of the community, hopefully we can understand where the CPU cycles are spent and see how to make them more efficient... Stay tuned!! -Jignesh Philippe Amelant wrote: > am I wrong or DB2 9.1 is faster on less powerfull hardware ? > > Le lundi 09 juillet 2007 à 11:57 -0400, Jignesh K. Shah a écrit : > >> Hello all, >> >> I think this result will be useful for performance discussions of >> postgresql against other databases. >> >> http://www.spec.org/jAppServer2004/results/res2007q3/ >> >> More on Josh Berkus's blog: >> >> http://blogs.ittoolbox.com/database/soup/archives/postgresql-publishes-first-real-benchmark-17470 >> >> Regards, >> Jignesh >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: explain analyze is your friend >> > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate >
Jignesh K. Shah wrote: > Hello all, > > I think this result will be useful for performance discussions of > postgresql against other databases. > > http://www.spec.org/jAppServer2004/results/res2007q3/ > > More on Josh Berkus's blog: > > http://blogs.ittoolbox.com/database/soup/archives/postgresql-publishes-first-real-benchmark-17470 May I ask you why you set max_prepared_transactions to 450, while you're apparently not using prepared transactions, according to this quote: > Recoverable 2-phase transactions were used to coordinate the interaction between > the database server and JMS server using Sun's Last Agent Logging > Optimization; the 1PC database transactions and transaction log records are > written to the database in a single transaction. Did you perhaps use 2PC at first, but didn't bother to change the config after switching to the last agent optimization? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote: > > > May I ask you why you set max_prepared_transactions to 450, while > you're apparently not using prepared transactions, according to this > quote: > >> Recoverable 2-phase transactions were used to coordinate the >> interaction between >> the database server and JMS server using Sun's Last Agent Logging >> Optimization; the 1PC database transactions and transaction log >> records are >> written to the database in a single transaction. > > Did you perhaps use 2PC at first, but didn't bother to change the > config after switching to the last agent optimization? > Yep.. one of the things that we didn't revert back and got strayed out there. -Jignesh
I don't this so, because DB2 is running on a Sun Sparc T1 processor (<a class="moz-txt-link-freetext" href="http://www.sun.com/processors/UltraSPARC-T1/">http://www.sun.com/processors/UltraSPARC-T1/</a>)that's implements muchmore features, like thread level parelelism, than AMD Opteron.<br /><br /> the DB2 installation:<br /><br /><pre>J2EEAppServer HW (SUT hardware) Hardware Vendor: Sun Microsystems, Inc. Model Name: Sun Fire T2000 ServerProcessor: Sun UltraSPARC T1 MHz: 1400 # of CPUs: 8 cores, 1 chip, 8 cores/chip (4 threads/core)Memory (MB): 65536 L1 Cache: 16KB(I)+8KB(D) per core L2 Cache: 3MB per chip</pre> thepostgres installation:<br /><pre>J2EE AppServer HW (SUT hardware) Hardware Vendor: Sun Microsystems, Inc. Model Name: Sun Fire X4200 M2 Processor: AMD Opteron 2220 SE MHz: 2800 # of CPUs: 4 cores,2 chips, 2 cores/chip Memory (MB): 8192 L1 Cache: 64KB(I)+16KB(D) per core L2 Cache: 1MB percore</pre><br /> postgres with a inferior hardware has better performance than DB2 <span class="moz-smiley-s1"><span>:-) </span></span><br /><br /><br /> Best Regards,<br /> André<br /><br /> Philippe Amelant escreveu:<blockquote cite="mid1184163398.16023.103.camel@phil.companeo.local" type="cite"><pre wrap="">am I wrong or DB29.1 is faster on less powerfull hardware ? Le lundi 09 juillet 2007 à 11:57 -0400, Jignesh K. Shah a écrit : </pre><blockquote type="cite"><pre wrap="">Hello all, I think this result will be useful for performance discussions of postgresql against other databases. <a class="moz-txt-link-freetext" href="http://www.spec.org/jAppServer2004/results/res2007q3/">http://www.spec.org/jAppServer2004/results/res2007q3/</a> More on Josh Berkus's blog: <a class="moz-txt-link-freetext" href="http://blogs.ittoolbox.com/database/soup/archives/postgresql-publishes-first-real-benchmark-17470">http://blogs.ittoolbox.com/database/soup/archives/postgresql-publishes-first-real-benchmark-17470</a> Regards, Jignesh ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend </pre></blockquote><pre wrap=""> ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at <a class="moz-txt-link-freetext" href="http://www.postgresql.org/about/donate">http://www.postgresql.org/about/donate</a> </pre></blockquote>
"Jignesh K. Shah" <J.K.Shah@Sun.COM> writes:
> Heikki Linnakangas wrote:
>> May I ask you why you set max_prepared_transactions to 450, while
>> you're apparently not using prepared transactions, according to this
>> quote:
> Yep.. one of the things that we didn't revert back and got strayed out
> there.
There were quite a few settings in that list that looked like random
experimentation rather than recommendable good practice to me.
regards, tom lane
Can you list others that seemed out of place? Thanks. Regards, Jignesh Tom Lane wrote: > "Jignesh K. Shah" <J.K.Shah@Sun.COM> writes: > >> Heikki Linnakangas wrote: >> >>> May I ask you why you set max_prepared_transactions to 450, while >>> you're apparently not using prepared transactions, according to this >>> quote: >>> > > >> Yep.. one of the things that we didn't revert back and got strayed out >> there. >> > > There were quite a few settings in that list that looked like random > experimentation rather than recommendable good practice to me. > > regards, tom lane >
Jignesh K. Shah wrote: > Can you list others that seemed out of place? well to me the ones that look most questionable are: work_mem=100MB - so this benchmark is really low concurrency(which does not fit with max_connections=1000) and with trivial queries ? enable_seqscan = off - why ? effective_cache_size = 40GB - on a box with 16GB this seems wrong especially since there are some indications out there that suggest that while overestimating effective_cache_size was not a problem in versions <8.2 it might not be so in 8.2 and up wal_buffers = 2300 - there have been some numbers reported that going over the default of 8 helps but it is generally considered that going beyond 500 or maybe 1000 does not help at all ... and one more is that you claim you used "-fast -O4 -xtarget=ultraT1" which is something we explicitly advise against in our own FAQ(http://www.postgresql.org/docs/faqs.FAQ_Solaris.html): "Do not use any flags that modify behavior of floating point operations and errno processing (e.g.,-fast). These flags could raise some nonstandard PostgreSQL behavior for example in the date/time computing." Stefan
"Stefan Kaltenbrunner" <stefan@kaltenbrunner.cc> writes: > Jignesh K. Shah wrote: >> Can you list others that seemed out of place? The one which surprised me the most was the commit_delay setting. What results led you to set that? The common wisdom on this setting is that it doesn't accomplish its goals and does more harm than good for most cases and should be replaced with something more effective. In any case I wouldn't think the use case for a feature like this would actually apply in the case of a benchmark. The use case where something like this is needed is where there are not enough concurrent requests to keep the server busy during the fsync of the wal. If for example each query does 5ms of actual work and fsyncs take 15ms then you could be committing up to 3 transactions in one fsync and need another 3 busy connections to keep the server busy during that fsync so you would need at least 6 concurrently busy connections. If you have a more cpu-bound system then that number might be higher but 100+ connections ought to be enough and in any case I would expect a benchmark to be mostly disk-bound. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On Thu, 12 Jul 2007, Gregory Stark wrote: > In any case I wouldn't think the use case for a feature like this would > actually apply in the case of a benchmark. I've also seen a tiny setting for commit_delay (like the 10 they used) as helping improve throughput under a heavy commit load with many processors. I'm not sure why a quick yield of the processor at that point helps, but there seem to be cases where it does. Don't think it has anything to do with the originally intended use for this parameter, probably some sort of OS scheduler quirk. > The use case where something like this is needed is where there are not > enough concurrent requests to keep the server busy during the fsync of > the wal. I've actually finished an long investigation of this recently that will be on my web page soon. On a non-caching controller where you'd think there's the most benefit here, I was only able to get about 10% more commits at low client loads by setting the delay to about 1/2 of the fsync time, and a few percent more at high loads by setting a delay longer than the fsync time. It's really a slippery setting though--very easy to set in a way that will degrade performance significantly if you're not very systematic about testing it many times at various client counts. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Le mercredi 11 juillet 2007 à 13:37 -0300, André Gomes Lamas Otero a écrit : > I don't this so, because DB2 is running on a Sun Sparc T1 processor > (http://www.sun.com/processors/UltraSPARC-T1/) that's implements much > more features, like thread level parelelism, than AMD Opteron. > > the DB2 installation: > This is J2EE server not DB server > J2EE AppServer HW (SUT hardware) > Hardware Vendor: Sun Microsystems, Inc. > Model Name: Sun Fire T2000 Server > Processor: Sun UltraSPARC T1 > MHz: 1400 > # of CPUs: 8 cores, 1 chip, 8 cores/chip (4 threads/core) > Memory (MB): 65536 > L1 Cache: 16KB(I)+8KB(D) per core > L2 Cache: 3MB per chip > the postgres installation: > J2EE AppServer HW (SUT hardware) > Hardware Vendor: Sun Microsystems, Inc. > Model Name: Sun Fire X4200 M2 > Processor: AMD Opteron 2220 SE > MHz: 2800 > # of CPUs: 4 cores, 2 chips, 2 cores/chip > Memory (MB): 8192 > L1 Cache: 64KB(I)+16KB(D) per core > L2 Cache: 1MB per core >
I had such great hopes for this thread. "Alas, poor Yorick! I knew him, Horatio ..." On Thu, Jul 12, 2007 at 11:00:54AM -0400, Greg Smith wrote: > On Thu, 12 Jul 2007, Gregory Stark wrote: > > >In any case I wouldn't think the use case for a feature like this would > >actually apply in the case of a benchmark. > > I've also seen a tiny setting for commit_delay (like the 10 they used) as > helping improve throughput under a heavy commit load with many processors. > I'm not sure why a quick yield of the processor at that point helps, but > there seem to be cases where it does. Don't think it has anything to do > with the originally intended use for this parameter, probably some sort of > OS scheduler quirk. > > >The use case where something like this is needed is where there are not > >enough concurrent requests to keep the server busy during the fsync of > >the wal. > > I've actually finished an long investigation of this recently that will be > on my web page soon. On a non-caching controller where you'd think > there's the most benefit here, I was only able to get about 10% more > commits at low client loads by setting the delay to about 1/2 of the fsync > time, and a few percent more at high loads by setting a delay longer than > the fsync time. It's really a slippery setting though--very easy to set > in a way that will degrade performance significantly if you're not very > systematic about testing it many times at various client counts. > > -- > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD > > ---------------------------(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