DevLoop has just published a
free paper (US mirror) [zipped .pdf]
benchmarking some popular database systems such as MySQL, PostgreSQL, Firebird, DB2, Informix, Sybase SQL Anywhere, on Linux using JDBC. The focus here is on non-real setups where we can analyze the individual aspects that might affect system performance. The code is available (GPL) so that anyone can reproduce the results.
It is an interesting read. They are very precise about the hardware and kernel versions in use. However they seem quiet about DBMS versions and variations.
MySQL scored well but is this the variant that supports foreign keys and triggers?
Am more of a PostgreSQL user … I know most of the richer features will be there when I need them (without having to switch DBMS mid project).
Man what a joke of a test. How can you compare MySQL MyISAM storage with PostgreSQL? MyISAM doesn’t even support transactions or row-level locking. Like you use that rubbish in a J2EE application!
What exactly are they comparing here? The database performance may well be hindered by the JDBC driver connecting it.
I get sick of these benchmarks where any old Jimmy looks at it, without any sense of what’s important and says ‘Hey X is so much faster than Y, let’s use this’. Only Sotware coders *NOT Software Developers would make such ignorant conclusions.
Believe it or not, I am doing enterprise application developement which heavily uses MySQL. We are providing large data sets (tens of GBs) to our customers that their application read but (almost) never update these data, so what is better then MySQL here? Free, small, simple to install and maintain. There are many more use-cases for DBs then seems to fit into your head, so try to investigate why and how various enterprises use MySQL *before* judging them. For application I am talking about, switching to twice slower DB just because you decided it is “real” DB is not an option. Don’t know about you, but test is very useful for me, because I *do know* what features these DBs support and it is very informative to know how performace compares for various workloads. As of JDBC layer, yes, it can be buggy and make bad image of DB itself, but then again, In java you and I access DBs using these JDBC drivers.
I can believe it, yes. Of course there will always be exceptions to any case. But this benchmark is not measuring the best DB solution for a particular application profile, merely the speed of their operation. Be careful with how such benchmarks can be interpreted as conclusions by poor coders.
I’m glad to hear that you found a database that fits your needs. The problem with MySQL or rather MySQL AB is their marketing. They tell you they have the fastest DB in the world. In the next sentence they tell you that they now support transactions, referential integrity, stored procedures and triggers. Most people interpret this as the can have all this at the same time. Unfortunately this is not so.
So when I see tests where MyISAM is compared to what you call “real” database engines I get sort of suspicious. It’s bad enough that MySQL AB holds back on telling about the difference in functionality, independent tests doing it is even worse. Most people will look at the performance figures not realizing that the performed task is quite different in e.g. MySQL or Postgresql.
It is only when people have all the relevant facts, that they can make an informed decision on what’s the best database for them.
Your app must have well structured data and a thoughtfully designed schema.
I work with financial data that, for reasons out of my control, is not structured well. I have to make up for the lack of structure by using complex queries to groups, sort and limit the data returned to the app. Even though the data is used mostly readonly, and I have my database configured for MyISAM, MySQL performance is quite a bit worse then Firebird. I have not tested PostgreSQL before. OracleXE in my case performs best.
For 90% of the people developing webapps out there mysql is great. But if your in a position where you need data integrity, or your trying to query large amounts of poorly structured data, and performance is a consideration, then mysql is probably not the right database for you.
I can not find any tuning info for databases. Tuning makes huge difference for big databases.
In one of our past project, we jad a big test postgres 8.1 database, with default config, it took 23 minutes to return the result from a select statemen from a big table. After tuning, it took 21 seconds.
So what they are testing is the default config for all the databases? DBA job is so each now.
Edited 2007-02-15 01:36
Could you explain to us what do you do to tweak postgres?
We’re very interested in that.
sorry about my bad english
TIA
Here are some that I have used in the past:
http://www.varlena.com/GeneralBits/Tidbits/perf.html
http://www.revsys.com/writings/postgresql-performance.html
If you had read the PDf it tells you that they are only using the default settings that comes with the defautl instalallation method. Nothing is tuned.
Just an install and populted database with an created user.
I want to see postgresql 8.2.3 vs Mysql with innodb because http://tweakers.net/reviews/657/6 shows very different benchmarks results. i think postgresql 8.2 performs well then 8.1
It doesn’t look like scientific comparison as one cannot be sure what the author is trying to conclude. The performance of SELECT operation ? The performance of JDBC drivers ? The performance of JDKs ? Any serious System Architect would use it as it didn’t adhere to any scenario in software development (Is here anyone who builds Inf. Systems without database optimization ? (I.S. != LAMP webpage)).
Nice to see some info on the Big Advantage of PreparedStatements. Actually, the Whole Test should have used them.
I don’t know any java dev’s in this company using anything But PS’s or Stored Procedures.
There should have been some Transaction benchmarks.
– Start transaction
– update table 1
– update table 2
– Commit
—-
in some test’s Rollback the update.
( 1% of the time? )
—-
That’s Real World.
Where’s the Stored Procedure Tests?
– Esp. important in Oracle, as the sp area won’t get cleared out when a batch of Inserts are done, vs. the SGA’s clearout with the LRU “least recently used” dumping to make room for INSERTS.
I’m impressed by the work done in version 1.0 to cover the basics, but, these tests are the basics.
So, mysql performs better in very basic operations. I wouldn’t give up Oracle or Postgres just because of this pdf though.
There’s also the question of the SQL engine available. And Postres and Oracle beat MySql here as well. It’s part of the Developer’s job to also know and effectively use the SQL engine for queries.
I have seen benchmarks of MySQL vs. PostgreSQL many times and whether this is a perfect test or not, it matches what I have seen in every other performance comparision between the two.
Sun’s benchmarking on the T2000 looks just like this as well.
Matt Giacomini I liked your second link.
Yes, if your Employee table contains 29 columns, then the first optmization would be to FIRE the developer who writes:
Select * from Employees;
“Yes, if your Employee table contains 29 columns, then the first optmization would be to FIRE the developer”
You should probably also fire the guy who designed a table with 29 columns.