Slashdot Mirror
MySQL Developer Contests PostgreSQL Benchmarks
Lucas Marshall writes: "Developer Shed has an article written by the main MySQL developer Michael "Monty" Widenius in response to Great Bridge's press release reported here on Slashdot on Monday. He makes some pretty good points about fair benchmarking and software competition."
ACID stands for Atomicity, Consistancy, Isolation and Duribility. Atomicity means, basically, a transaction. A series of commands are issued, then either commited or rolled back. All happens, or none happens. The classic example is doing an update on Joes_Account_Balance to subtract 100 dollars, then failing to do the update on Suzys_Account_Balance to add 100 dollars. The transaction failed, so it should be rolled back so that the 100 doesn't get removed from Joe. Consistency means that the database must always be in a valid state, and that usually means rules are enforced. If there's a database rule that states that any subtraction of 100 dollars from an account needs to add a record to the audit table, so you subtract 100 dollars from Joes_Account, but the audit table cannot be updated due to disk full/network break/act of Gods/whatever, then the subtract needs to be rolled back, or not happen in the first place. Isolation means that users don't see other users. If two people are both doing multi-stage updates to Joes_Account, user one doesn't see the fact that user two just did an insert, and an update. Durability means that if bad things happen, the database survives. Logs, backups, that sort of thing.
Vintage computer games and RPG books available. Email me if you're interested.
I agree, MySQL has its uses. It is, in some instances, a vastly superior tool to the alternatives. MySQL's problem, however, is largely created by themselves and their zealots. They have effectively billed MySQL as a replacement for commercial RDBMSs. That is equivelent to marketing a swiss army knife as a be all and end all replacement for every tool it offers. Yes, it looks something like it. It functions something like it. But that doesn't mean it is it. Nor does it mean that the swiss army knife has even 1/10th the engineering as the devices it replaces. That is essentially what MySQL is, an overglorified swiss army knife where people _might_ want to use an RDBMS. ...It's lighter, it's cheaper, it's quicker in some cases...but that doesn't mean people haven't made horrible mistakes by employing it. I can understand how many developers and database types get a bit annoyed with this....
PS: What exactly does "improving at open source speed" mean? If it means that you think MySQL, or for that matter any truely open source developed RDBMS, will ever be able to go toe to toe with the likes of Oracle or Sybase, then I'd take your bet any day. No previous Open Source project has ever taken on something of this size and complexity, and come out on top. So please don't tell me about this much fabled "open source speed" until it is a reality.
How many of you are just reading the comments on this article for the inevitable mysql vs postgresql vs commercial db flame fest? :)
Take a close look at what you are writing people. Especially those who have said "MySQL is useless in the real world." I can't imagine any sane definition of "real world" that doesn't include Slashdot.org, countless e-commerce sites and other non-Web applications.
Of course MySQL has limitations! Of course it does not meet the definition of an RDBMS! It meets the needs of a significant set of *real world* problems with great efficiency.
PostgreSQL meets a different, but overlapping, set of problems with ever increasing speed and efficiency.
They are both open source.
They are both improving at open source speed.
They both rock.
I posted this yesterday but probably no one read it due to the fact that it was one of the latter posts (past 400). Anyway here goes...
According to the original article the names of the proprietary databases benchmarked were not given because it violated licensing agreements. Flawed benchmarks like this are the reason why.
As someone who has downloaded, installed and used Oracle 8i, IBM's DB2 and Borland's Interbase I can testify that configuring any of these DB's properly is a non-trivial task that can easily be messed up by someone who has no idea what he/she is doing. Using non-native drivers, not indexing tables properly, improper tablespace sizing, choosing an improper number of data files, improperly managing data blocks, etc can all lead to creation of a suboptimal database application performance.
Most of the major DB companies provide DB's for independent benchmarking from organisations like the Transaction Processing Performance Council. As can be seen from this story these tests involve several thousand transactions per second and not several hundred as reached by this Great Bridge sponsored benchmark. I suggest that someone with a deep pockets or a vested interest in seeing Open Source DBs succeed should enter PostgreSQl or MySQL in these TPC-C tests.
The Queue Principle
>As they haven't released any information about :(
>the test or even the results one can just assume
>that they have done a test that doesn't have
>anything to do with the real world
"In the AS3AP tests... Postgres peaked at 1127.8 transactions per second with five users, and still processed at a steady rate of 1070.4 with 100 users... MySQL reached a peak of 803.48 with two users, but its performance fell precipitously under the stress of additional users to a rate of 117.87 transactions per second with 100 users."
The test is standard, so the definition of what it entails is easy to come by and they provided what the results were. So your above complaint really has no substance to it.
However, I do agree there should have been more information - namely the software configuration.
>PostgreSQL has of course also a lot of weak
>areas, like slow connection, slow insert, slow
>create/drop tables, running long multiple
>transactions where you get a conflict
>at the end (in this page/row locking is better)
Yes, Postgres is slower to connect, though not grotesqely so.
Yes, Postgres is slower to do inserts, but updates and inserts don't block. So though MySQL may be impressive if you bench insert speed without considering its impact on overall performance, in real world applications you'll see very different behaviour.
I'm not sure exactly what you're refering to when you mention "conflicts" at the end of transaction blocks. This is one area where I find MySQL falls short, since a) it has no transactions and b) it doesn't have the framework for transactions (e.g. multi-version concurency control, etc).
>We here at MySQL has always tried to design very
>fair test that no one can misinterpret or lie
>about.
Unfortunately the benchmarks MySQL uses are all single client load, which is completely meaningless in a production environment and covers up the weakest parts of MySQL (e.g. all selects stalling while an insert or update is happening).
>It's a shame that Great Bridge funds a test that
>is solely done to confuse users instead of
>telling the truth; PostgreSQL is good in some
>areas, and bad in others, just like all other
>databases.
I fail to see where they state what you are claiming they did. They merely point that a) on the AS3AP test, using the current production release of MySQL with its ODBC implementation, the performance was lackluster, and that MySQL currently lacks the feature set to run TPC-C.
>The article doesn't mention anything about this >or even with which ODBC driver they used the
>different databases.
As opposed to the benchmark on the MySQL website, where they make no mention of how the databases were compiled, what limits were set, what version of Perl was used, what version of DBI was used, what version of the DBD drivers were used, what hardware platform it was run on, what operating system it was run on, etc, etc.
And futhermore they are benchtesting an alpha product against a faily old stable release (6.5 being current over a year before these benchmarks were posted, with a half dozen released in the meantime).
>I also don't agree with the argument that they
>are not testing MySQL 3.23 as this is still
>marked 'beta'. We have here at MySQL a completely
>different release schedule on our versions...
>Compared to our release schedule, PostgreSQL 7.0 >would be called alpha.
Nice way to imply that the Postgres team doesn't properly beta test. Fact of the matter is that the 7.0 release spend many many months in its alpha/beta/gamma cycle.
There were a couple bugs that slipped through the cracks, but the subsequent releases resolved all known issues, and came very promptly (15 days between 7.0 and 7.0.1, and then 3 days between 7.0.1 and 7.0.2).
As for the development cycles being different, I agree. Though I would go the other direction. The goals of the 7.0 development series were much less ambitious than those of the 3.23 series of MySQL has; e.g. replication, two new backends, transaction support, a real locking mechanism, etc. Not to mention that they stopped adding new features to 7.0 about 6 months ago, whereas MySQL is continuing to add features (and change existing features) of 3.23.
>The net result is that the posted test is about
>as wrong as you can do a test, the important
>thing is just to get the people that reads that
>page to understand that.
Funny, I would say the epitomy of both misleading and incorrect testing would be the sql-bench suite run by MySQL.
I dunno -- I like Postgres better cause I need transactions. MySQL wouldn't work for me as well.
OTOH, I like it a lot when nerds take punches at each other. Kinda like mud wrestling, but without the swinging breasts.
Maybe a Postgres/MySQL Nerf fight?
Potato chips are a by-yourself food.
All this talk about DB's for linux, but nobody ever talks about DBMaker (www.dbmaker.com). It is a free database for linux, just not open source. It has support for PHP - PERL - PYTHON - ODBC - JDBC, excellent documentation, and supports transactions, locking, triggers, proceedures, and etc.
I have not had the time to evaluate it in depth, but plan to in a few weeks. Soon I will have to make a choice between MySQL, PostgreSQL, and DBMaker. Has anyone had good results with DBMaker?
I'm surprised that in neither of the MySQL vs Postgres SQL stories has anyone commented on the near release of MaxSQL.
MaxSQL, made by the mySQL folks, will have transaction support, long the last line of defense in postgresSQL's "we're better because" flamewar.
Frankly, the next year is going to be very interesting for the open source SQL market, and not so good for the big players. (I hear Informix had to lay off hundreds on the peninsula last week).
Kevin Fox
Kevin Fox
Following the links from the page with the article, I find MySQL's site with their own benchmarks of MySQL vs. Postgres: http://www.mysql.com/information/benchmarks.html .
The same page contains benchmarks against other popular databases as well.
NOTE: Pay attention to the numbers, as well as the bar diagrams. The bars are all clipped at the right side of the chart, which is misleading for a few rows.