Thread: Great Bridge re-runs benchmark with MySQL "tuned"
Folks, We posted the following announcement on our website today, at http://www.greatbridge.com/news/press.html. Please feel free to email me off-list with any questions. Thanks, Ned UPDATE, August 22, 2000: MySQL performance improves with tuning suggestions from development team; PostgreSQL still leads all contenders under heavy usage Following our recent release of AS3AP and TPC-C benchmark test results, Great Bridge offered to re-run the tests with tuning and custom configuration settings suggested by the MySQL development team. We did, and we want to share the results. It's important to note that the MySQL configuration originally tested was the default MySQL installation, using the standard MyODBC.dll Windows driver installed by MySQL (for the benchmark software client machine, which ran Windows NT). Probably the most significant change came from substituting a faster driver, called MyODBC2.dll; according to the MySQL development team, the default driver is used for debugging purposes, and is known to be slower in production environments. At their suggestion, we also implemented the following tuning settings: * key_buffer=64m * table_cache=256 * sort_buffer=1m * record_buffer=1m So what were the results? MySQL did significantly better across the board, averaging 69% more transactions per second in this tuned environment, and exceeding Postgres' raw performance until the seventh concurrent user. Its performance peaked at 1,321 tps (at 3 users), but still started to fall off about the same point as in the previous test (4 users). See graphic (http://www.greatbridge.com/img/as3ap_new.gif). What does this mean? Our interpretation is that, properly configured, MySQL is indeed a faster performer in raw read-only databases with 6 or fewer users. We should note that these tests results do not represent the full suite of AS3AP tests - only the multiuser ir_select (information retrieval) test. Other tests in the AS3AP suite require views, which MySQL does not currently support. We should also note that the TPC-C test, which simulates a more robust OLTP environment, still would not run under the tuned MySQL configuration, primarily due to SQL compliance issues (see Richard Brosnahan's analysis elsewhere in the main story). But overall, MySQL acquitted itself well when expertly tuned for the AS3AP ir_select test.
.){kind=link}
It would be interesting to see how well PostgreSQL performed when it was tuned. Or has it allready been tuned? Ned Lilly wrote: > Folks, > > We posted the following announcement on our website today, at > http://www.greatbridge.com/news/press.html. > > Please feel free to email me off-list with any questions. > > Thanks, > Ned > > UPDATE, August 22, 2000: > > MySQL performance improves with tuning suggestions from development > team; > PostgreSQL still leads all contenders under heavy usage > > Following our recent release of AS3AP and TPC-C benchmark test > results, Great Bridge offered to re-run the tests with tuning and > custom configuration settings suggested by the MySQL development > team. We did, and we want to share the results. > > It's important to note that the MySQL configuration originally > tested was the default MySQL installation, using the standard > MyODBC.dll Windows driver installed by MySQL (for the benchmark > software client machine, which ran Windows NT). Probably the most > significant change came from substituting a faster driver, called > MyODBC2.dll; according to the MySQL development team, the default > driver is used for debugging purposes, and is known to be slower in > production environments. > > At their suggestion, we also implemented the following tuning > settings: > > * key_buffer=64m > * table_cache=256 > * sort_buffer=1m > * record_buffer=1m > > So what were the results? MySQL did significantly better across the > board, averaging 69% more transactions per second in this tuned > environment, and exceeding Postgres' raw performance until the > seventh concurrent user. Its performance peaked at 1,321 tps (at 3 > users), but still started to fall off about the same point as in the > previous test (4 users). See graphic > (http://www.greatbridge.com/img/as3ap_new.gif). > > What does this mean? Our interpretation is that, properly > configured, MySQL is indeed a faster performer in raw read-only > databases with 6 or fewer users. We should note that these tests > results do not represent the full suite of AS3AP tests - only the > multiuser ir_select (information retrieval) test. Other tests in the > AS3AP suite require views, which MySQL does not currently support. > We should also note that the TPC-C test, which simulates a more > robust OLTP environment, still would not run under the tuned MySQL > configuration, primarily due to SQL compliance issues (see Richard > Brosnahan's analysis elsewhere in the main story). But overall, > MySQL acquitted itself well when expertly tuned for the AS3AP > ir_select test.
See http://www.greatbridge.com/news/p_081620001.html - we increased the cache, ran a vacuum analyze, a few minor things. Regards, Ned "Poul L. Christiansen" wrote: > It would be interesting to see how well PostgreSQL performed when it was > tuned. > > Or has it allready been tuned? > > Ned Lilly wrote: > > > Folks, > > > > We posted the following announcement on our website today, at > > http://www.greatbridge.com/news/press.html. > > > > Please feel free to email me off-list with any questions. > > > > Thanks, > > Ned > > > > UPDATE, August 22, 2000: > > > > MySQL performance improves with tuning suggestions from development > > team; > > PostgreSQL still leads all contenders under heavy usage > > > > Following our recent release of AS3AP and TPC-C benchmark test > > results, Great Bridge offered to re-run the tests with tuning and > > custom configuration settings suggested by the MySQL development > > team. We did, and we want to share the results. > > > > It's important to note that the MySQL configuration originally > > tested was the default MySQL installation, using the standard > > MyODBC.dll Windows driver installed by MySQL (for the benchmark > > software client machine, which ran Windows NT). Probably the most > > significant change came from substituting a faster driver, called > > MyODBC2.dll; according to the MySQL development team, the default > > driver is used for debugging purposes, and is known to be slower in > > production environments. > > > > At their suggestion, we also implemented the following tuning > > settings: > > > > * key_buffer=64m > > * table_cache=256 > > * sort_buffer=1m > > * record_buffer=1m > > > > So what were the results? MySQL did significantly better across the > > board, averaging 69% more transactions per second in this tuned > > environment, and exceeding Postgres' raw performance until the > > seventh concurrent user. Its performance peaked at 1,321 tps (at 3 > > users), but still started to fall off about the same point as in the > > previous test (4 users). See graphic > > (http://www.greatbridge.com/img/as3ap_new.gif). > > > > What does this mean? Our interpretation is that, properly > > configured, MySQL is indeed a faster performer in raw read-only > > databases with 6 or fewer users. We should note that these tests > > results do not represent the full suite of AS3AP tests - only the > > multiuser ir_select (information retrieval) test. Other tests in the > > AS3AP suite require views, which MySQL does not currently support. > > We should also note that the TPC-C test, which simulates a more > > robust OLTP environment, still would not run under the tuned MySQL > > configuration, primarily due to SQL compliance issues (see Richard > > Brosnahan's analysis elsewhere in the main story). But overall, > > MySQL acquitted itself well when expertly tuned for the AS3AP > > ir_select test.
Hi! >>>>> "Poul" == Poul L Christiansen <plc@faroenet.fo> writes: Poul> It would be interesting to see how well PostgreSQL performed when it was Poul> tuned. Poul> Or has it allready been tuned? Yes, it was. According to Ned, they did run vacuum() on the tables before running the test and they also started PostgreSQL with some big cache sizes as the default setup is designed to not take too much resources (like with most other databases). Unfortunately the first benchmark press announcement didn't provide all information of how the test was run (which made it impossible to comment or even understand the test results). This was the main reason I did write an comment about the benchmark on Devshed after the results was released. It would be interesting to know Interbase would perform if it was setup optimally... Regards, Monty
Michael Widenius wrote: > > Hi! > > >>>>> "Ned" == Ned Lilly <ned@greatbridge.com> writes: > > Ned> Folks, > Ned> We posted the following announcement on our website today, at > Ned> http://www.greatbridge.com/news/press.html. > > Ned> Please feel free to email me off-list with any questions. > > Ned> Thanks, > Ned> Ned > > Thanks for running a new test. The only big thing I still miss is a > benchmark with the 3.23 RPM; The select code in MySQL 3.23 is not that > much faster than 3.23 (except if you use GROUP BY or DISTINCT, which > is MUCH faster in 3.23), but using 3.23 would cancel the effect > of using RedHat 6.1 with a buggy LinuxThread library. There is another scaling issue involved when running Linux 2.2 kernel - the scheduler is overzealous to thwart fork bombs, and because of that, split the priority of a process after fork() or clone(). This, however, should have affected PostreSQL in a way similiar to MySQL. The fact that PostreSQL scales without any decline at all while other databases do not with performance dropping in the pattern characteristic of priority split issue suggests that there is something like a reading cached result or a similar optimization that PostreSQL was able to do on this particular test. It would be interesting to see what happens after you apply the following patch to the kernel: --- fork.c.orig Mon Sep 27 07:56:32 1999 +++ fork.c Thu May 25 16:10:48 2000 @@ -691,9 +691,11 @@ * more scheduling fairness. This is only important in the first * timeslice, on the long run the scheduling behaviour is unchanged. */ + p->counter = (current->counter + 1) >> 1; current->counter >>= 1; - p->counter = current->counter; + if (!current->counter) + current->need_resched = 1; /* * Ok, add it to the run-queues and make it * visible to the rest of the system. --- exit.c.orig Mon Sep 27 07:56:32 1999 +++ exit.c Thu May 25 16:13:31 2000 @@ -56,6 +56,9 @@ current->cmin_flt += p->min_flt + p->cmin_flt; current->cmaj_flt += p->maj_flt + p->cmaj_flt; current->cnswap += p->nswap + p->cnswap; + current->counter += p->counter; + if (current->counter >= current->priority*2) + current->counter = current->priority*2-1; free_task_struct(p); } else { printk("task releasing itself\n"); or just try 2.4 kernel > I think we will take a look at implementing a similar test as the > AS3AP test in our own test suite so that we can get a open source test > that we can use to benchmark databases under heavy load. > > We would REALLY prefer to have an open source benchmark that everyone > can download and test to verify results! > > By the way, what was the cache parameters you setup for PostgreSQL > when running the test ? I asked this before but never got an answer > for this (I would just like to setup PostgreSQL perfectly for our > benchmarks) Running the benchmark once with --log --log-update and sending us the logs will help us a lot increating an open source benchmark similar to the one you've run. -- Sasha Pachev +------------------------------------------------------------------+ | ____ __ _____ _____ ___ http://www.mysql.com | | /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sasha Pachev | | /*/ /*/ /*/ \*\_ |*| |*||*| sasha@mysql.com | | /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Provo, Utah, USA | | /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____ | | ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ | | /*/ \*\ Developers Team | +------------------------------------------------------------------+
Hi! >>>>> "Ned" == Ned Lilly <ned@greatbridge.com> writes: Ned> Folks, Ned> We posted the following announcement on our website today, at Ned> http://www.greatbridge.com/news/press.html. Ned> Please feel free to email me off-list with any questions. Ned> Thanks, Ned> Ned Thanks for running a new test. The only big thing I still miss is a benchmark with the 3.23 RPM; The select code in MySQL 3.23 is not that much faster than 3.23 (except if you use GROUP BY or DISTINCT, which is MUCH faster in 3.23), but using 3.23 would cancel the effect of using RedHat 6.1 with a buggy LinuxThread library. I assume you didn't optimize the table format in MySQL, as you did for PostgreSQL before running this test ? This would have given some small additional performance, but not significantly compared to the LinuxThread problem. We downloaded ourselves the AS3AP test to take a look at this, but unfortunately the test version can't be used for benchmarking at all :( The reason is that one can't configure this for more than 10 users and there is a 100ms sleep between each query :( I think we will take a look at implementing a similar test as the AS3AP test in our own test suite so that we can get a open source test that we can use to benchmark databases under heavy load. We would REALLY prefer to have an open source benchmark that everyone can download and test to verify results! By the way, what was the cache parameters you setup for PostgreSQL when running the test ? I asked this before but never got an answer for this (I would just like to setup PostgreSQL perfectly for our benchmarks) Regards, Monty