PostgreSQL vs MySQL benchmark
Pushed by the latest news about MySQL stopping to provide Windows binaries and the split between a community edition and a enterprise server I started to look for other options. The first one is without doubt PostgreSQL. The first things you will hear about PostgreSQL is that it is technically superior to MySQL - and this is true - but way slower. There are few side by side benchmarks to be found so I thought to check the second statement using latest available versions.
The results are from a TPC-C like benchmark ("it combines read-only and update intensive transactions simulating the activities found in complex OLTP enterprise environments"). I used MySQL 5.0.27 (innodb) and PostgreSQL 8.2.1 with comparable memory and disc settings. Database size is about 80MB for each test (5000 - 300000 rows/table) and the number of concurrent users ranges from 1 to 20. I think this is a good testing scenario but if you have suggestions or other results please comment. Here are the results (500MHz PIII, 256 MB RAM, 2 IDE HDDs - client on different computer, 100MB/s network):
Transaction graph PostgreSQL
Timeout errors PostgreSQL
Conclusions:- PostgreSQL was slower at populating the database.
- MySQL is faster overall overall (~2x faster).
- No timeout errors for PostgreSQL. Interesting.
- Maybe there is more room for improvements in PostgreSQL config file.
More links on the subject:
http://monstera.man.poznan.pl/wiki/index.php/Mysql_vs_postgres
http://www.mysqlperformanceblog.com/2006/11/30/interesting-mysql-and-postgresql-benchmarks/
http://tweakers.net/reviews/657/6
http://tweakers.net/reviews/649/9
http://benchw.sourceforge.net/benchw_results_open3.html
http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.htm
18 comments:
I see your database server has 256 of RAM. Can you tell which tests, if any, dipped into swap?
I'd assume none off-hand, but "a useful benchmark load" and "256MB of RAM" would seem to be contradictory forces...
For the record, MySQL will still be releasing Windows binaries. The latest (5.0.33) is only available as source code, no matter what platform. Stable releases will still get binary versions for all supported platforms.
Can you post the SQL that you used for this test? Did you disable autocommit in PGSQL as part of the test?
Do both have real transactions running? That is, does each transaction do a disk sync via:
fcntl(fd, F_FULLFSYNC, NULL);
instead of:
fflush(file);
Would you care to run the same application against FirebirdSQL?
(http://firebirdsql.org)
Thanks.
If you put your pgsql config I am sure that others can help to improve the performance. I was surprised how much pgsql can improve by changing the config file.
jerf said...I see your database server has 256 of RAM. Can you tell which tests, if any, dipped into swap? I'd assume none off-hand, but "a useful benchmark load" and "256MB of RAM" would seem to be contradictory forces...
---
From the performance profile console I can tell you that at any moment there were 24-32MB of physcal RAM available.
Usually I think a server should be able to handle a database at least 5 times bigger than it's available physical RAM for the given user load. And a 80MB transaction database (no logs) is not that small, even for production servers.
According to what I've read, a properly configured postgresql database is often/usually as fast, or faster, than mysql. The postrges is slower thing is one of those myths that keep getting passed on and on.
I'm not an expert with either postgres or mysql, so I wouldn't be able to critique your testing routines. But I have asked around and those that are skilled with both tools say postgres is not only 'better', it's just as fast, or faster.
I understand why you tested, but unless you really know what you're doing with both packages, the results cannot be regarded as substantial.
You should also be testing with Postgresql 8.2
The stock config file is set for a very low powered server and should be adjusted for a server with lots of ram etc.
Could you make your benchmark code available for review? It's possible that if you're not familiar with pgsql you might be making some very basic mistakes in your code that would favor mysql.
Generally, MySQL is faster at things like connecting, so if there's no connection pooling, it might well be that the difference in performance is something like connection overhead.
Also, make sure you're enclosing your postgresql statements in an actual transaction. If you don't explicitly state to run all the statements in one transaction, each one runs as its own, which means you can't roll back the set of statements, etc.
Oh, and yeah, I'd also like to see this benchmark against Firebird as well.
"If you put your pgsql config I am sure that others can help to improve the performance" <- i love to hear that, if only postgre developers knew how to make their default config base of hardware, can somebody please send them info how to get ammount of ram and OS :/
At the Page2RSS have had realy nice performace boost when we switched to the PostgreSQL. Take a look at the chart.
Until you publish your source code and configuration files, this benchmark is meaningless.
It doesn't matter if Windows binaries aren't released. Someone will compile them and make them available. I seem to remember one website in particular, but I can't remember the address... it had campbus as part of the domain name.
Try Linux System, it may reverse the process. Both can perform better than windows system.
for high end database postgresql is better, but for simple applications MySql can be used.
This study has some clear flaws, unfortunately. I'm happy that you performed this, but I'd like to point out some things in hopes that you'll do another test.
1. PostgreSQL is tuned way back on performance by default. You have to tweak its postgresql.conf file to give it more RAM and other resources on the server so that it can fair well. MySQL just so happens to need less of these tweaks, but then again, with MySQL you have to mix InnoDB and MyISAM tables because each has its advantages and disadvantages, which sometimes boxes you into a corner.
2. A lot of people may care about not just bulk insert type tests, but transactional read and write tests. I mean, sure, MySQL's MyISAM table (the default table type) uses table-level locks. PostgreSQL uses row-level locks. So, if you give me a cup of water to hold in my hand (table-level lock), I can easily fill that cup much faster than if you only give it to me for a couple seconds at a time (row-level lock). But most websites don't have bulk insert needs. Most websites have heavy reads and writes, and often just a slight few more reads than writes. This is why row-level locking is very important. Now, InnoDB table types in MySQL support row-level locking, but it comes with some disadvantages, say, no full text search support and a few others. Also, PostgreSQL has had a lot longer time working out row-level locking than the developers have spent working out InnoDB at MySQL Corp -- by as much as 6 years.
3. PostgreSQL really shines when you look at the stats past 10 concurrent users. You say you only had 12 concurrent users hitting the system. Why not go up to 100 and see where you stand?
4. It's no surprise the Windows stats are giving strange results compared to what we mostly know about PostgreSQL having better performance over MySQL during concurrent transactional loads. This is because MySQL and PostgreSQL were built first on Unix and Linux, and Windows was added later as a "nice to have". I eat my hat if anyone can prove to me that their are more core database developers working in C on the MySQL or PostgreSQL development teams that are working on the version for Windows. I can bet you $1000 that there are more devs working on the Linux version.
So the fixes are:
A. Increase the load up to 50 or 100 concurrent users.
B. Separate out bulk-insert data with heavy read-write data so we can see both.
C. Get experts on the web from both the MySQL and PostgreSQL camps to help you tweak the conf files on each to utilize the best performance.
D. Equal the playing field as much as possible. Use the same server with both tests, but stop all services on one test before performing services on the other.
E. Show us MyISAM versus InnoDB on these tests, along with a comparison against PostgreSQL.
F. Without a doubt, stop testing on Windows with MySQL vs. PostgreSQL, or at least show us tests on Linux as well as Windows. It's no wonder you're getting botched results. That's like trying to test MS SQL Server performance on Linux because some unlucky sod migrated it over to Linux on a wild hair as a "nice to have". I mean, Oracle for Windows and MS SQL for Windows might show that Oracle is faster on Windows, but port it to Linux under a WINE library and I'm sure that the results may vary wildly in unpredictable ways.
nice article
When the Wow Gold wolf finally found the wow gold cheap hole in the chimney he crawled cheap wow gold down and KERSPLASH right into that kettle of water and that was cheapest wow gold the end of his troubles with the big bad wolf.
game4power.
The next day the Buy Wow Goldlittle pig invited hisbuy gold wow mother over . She said "You see it is just as mttgamingI told you. The way to get along in the world is to do world of warcraft gold things as well as you can." Fortunately for that little pig, he buy cheap wow gold learned that lesson. And he just agamegold lived happily ever after!.
Post a Comment