PostgreSQL vs. MySQL Performance

-

When I read MySQL vs PostgeSQL benchmarks my question was always: "How close is that to real life workloads. My real life workloads." I don’t want to argue about how close TPC-C test simulates real scenarios for a majority of MySQL installations. The only thing this test tells is how well MySQL runs TPC-C Test. That test suits MySQL very well: database doesn’t have any variable length text fields; total database size is small. But if you try to run forum or portal software, that is there MySQL has major installed base, you will see completely different picture.

At the Page2RSS.com we have experienced very poor MySQL performance. Workload is essentially one ~2Gb table with dozen of fields. Couple of fields is variable length text fields with up to 32Kb of data. There are 1-2 inserts per second and up to 10 read requests per second. We used to have MySQL 5.0.24, innodb, no transactions. Now we have PostgreSQL 8.2.0. Server is PC-class Linux box: P4 2.8GHz, 1Gb memory, SATA disks - noting special at all.

Graph below is a Google's Time spent downloading a page (in milliseconds).



That sharp drop in the middle of December is when we switched to the PostgreSQL.



24 comments:

wskills said...

Even if MySQL performance was two times better in my scenario you can see that PostgreSQL had no transaction timeout errors. It seems that MySQL has some trouble with concurrency in select/update/insert although it can't be beaten for select only requests.

jaba said...

Are you sure you had properly indexed your MySQL databases? With a 2 GB database and such a low level of traffic MySQL shouldn't even sweat.

Richard Freytag said...

jaba: even if he did not properly index the MySQL tables you can assume they ALSO did not properly index the PostgreSQL tables making this still an interesting apples-to-apples comparision.

Wish I knew why PostgreSQL performs better in this case. I agree that the MySQL performance is a surprise. After all Google uses it and they are unlikely to make silly mistakes.

peternewman said...

Any particular reason for using the innodb on the MySQL installation?
Unless you're using the transaction functionality, it's a real performance killer. (Compare making 53,000 inserts on a 8 coloumn table in around 5 minutes with innodb, ~5 seconds with myisam).

Regards

m said...

I'm not for one database or another, but I'm willing to bet the database structure for MySQL is not normalized properly, and with that, indexing is probably garbage too. It's interesting to see that you are using InnoDB and no transactions. As peternewman pointed out, InnoDB is generally used for transaction support, and also for extremely large amounts of data. I work with an 80gb database spread across just two MySQL servers, InnoDB, about 50 tables (most are referential) and MySQL actually smoked PostgreSQL across the board.

dblood said...

Depending on the number of queries both databases use different methods to update rows. Postgres will delete and insert a new one without worrying about removing the deleted row from the table (the information isn't used, but the space is still allocated). This is why VACCUM needs to be run frequently. Depending on the queries the locking could be different between databases as well. I know MySQL used to only lock the tables and postgres would lock on the row level. This allows multiple updates/insert/deletes to occur at the same time.

coop said...

At Feed Digest we use MySQL. Approximately 10 gigabytes of data (anything over a month old is erased on the fly) over 10 tables, 50~150 requests per second (half INSERTs, half SELECTs, no UPDATEs, some DELETEs) and MySQL doesn't even break a sweat.

I have been considering Postgres, but I always find MySQL performance problems are due to using MySQL poorly. One thing MySQL can suck on is doing ordered joins on large tables, since it tries to build a temporary table for the ORDER BY each time.. the solution? A meta-data table and some denormalization.

Performance used to be poor at one tenth the database load before I undertook a significant amount of research and reading into MySQL's operation and came up with solutions to use it properly.

Now, the MySQL performance using the new techniques beats Postgres performance using the old techniques. I'm certainly a big believer that improving overall technique will /usually/ (but not always!) yield better results than throwing more power or different software at a problem.

I'm intrigued where your variable length text field of up to 32KB is coming from.. you're hashing rather than storing page content, right? :)

Anyway, thanks for the results, it's always useful to see people testing these things even if the advice does not apply for every situation.

Russ said...

Depending on your usage pattern, MyISAM can be vastly slower than InnoDB due to its table-level locking.

If you have a lot of concurrent read/write threads, MyISAM effectively serialises your queries against often-hit tables. Not good for concurrent performance...

Post Paint Boy said...

What graphing software are you using? It's pretty.

Matt said...

@Russ, Although MySQL doesn't have row level locking, if you don't delete rows, mysql can concurrently select and insert. This is critical for concurrency performance.

perrin said...

By default, InnoDB uses a more severe isolation level for transactions than PostgreSQL does. To make it equivalent, you would set MySQL's isolation level to READ-COMMITTED. Otherwise, it uses SERIALIZABLE, which is overkill for more most web apps.

markstos said...

How did you collect your data for "Google's Time spent downloading a page"?

butlimous said...

Thanks for the nice post!

Free PS3

Teddy said...

MySQL IS SO GAY!!!!!!

And anyone with real development or dba experience (not just a script kiddie) knows why

marcusiddybiddydicus said...

@Teddy

So Google and NASA are using "gay" software? So much for your vast experience. I'll go with what's good for the rich developers with proven solutions./

fred said...

if you know how to configure mysql properly you will be surprised how good it performs...
Most people who use mysql and complain its performance doesn't even know what is the my.cnf file.

read, research, experiment before u make conclusions.

fred said...

I have linux VPS machines with 256 and 512 ram. i configure mysql use less than 8 or 16 Mb of ram and it is really fast... even on large datasets (200-500mb db).

using myisam is a good solution.

Henrik said...

I guess you haven't optimized your SQL. Innodb needs some tweaking to get ok performance. count(*) is a killer, etc. Give myisam a try too.

Mr. Grey said...

Finally. A real world scenario that proves what I've known all along. PostgreSQL is better.

Also, even if the MySQL guys want to argue with me about performance, I want to ask them why any serious database design on their platform requires that I have InnoDB tables and MyISAM tables, mixed together, only to get cornered into gotchas later on with advantages and disadvantages to each. That's just plain crazy.

The only reason the pendulum has swung towards MySQL is because they had a huge marketing arm behind it. And as we can see from marketing at Microsoft Corp, for instance, we're not always given the best facts.

Harry said...

Using InnoDB tables is not just for transactions. You can never use transactions and still benefit from the fact that InnoDB can quickly recover from a crach. While MyISAM will need a time consuming table check!

In general I would love to see PosgreSQL taking over the market share from MySQL.. because that is the only thing PosgreSQL is missing. The huge user base.

And at the end of the day, I think the world would be a better place with PostgreSQL in the place of MySQL! :D

iq9 said...

MyISAM has no support for foreign key constraints (among other things) and is not ACID compliant. This makes it unusable in nearly all scenarios [unless you like your data integrity compromised].

Secondly, having to choose between two storage engines, both good at some things but lacking in others, is ridiculous. Choose PostgreSQL, good at all things out of the box.

I have used both MySQL and PostgreSQL for 10 years in solutions from entry-level to Enterprise. I will never use MySQL ever again. It does not hold even the tiniest candle to the power, performance, stability, ease of use, and flexibility of PostgreS.

Brian said...

Well I hate to say it, but you guys have convinced me. If I'm wrong, I'll be back here in a few months, madder than hell. I would have thought with Sun behind MySql, that would now be the better choice. We'll see. Thanks for the advice.
Brian

arsenalist said...

Saying somebody should switch to MyIsam just to make things faster is not a solution to the performance issue. A database without transactions isn't really a database and table-level locking is part of the problems a DB vendor must deal with. Obviously, MySQL is lagging back here.

user120 said...

@marcusiddybiddydicus

With regard to your comment to Teddy, I write three things:
I.
I agree the words he used was not good

II.
I accept that google makes good decisions.

III.
But does NASA using MySQL, makes MySQL the best tool? I disagree. Just as a television viewer I found in news that columbia space shuttle crashed killing people on board. So techincal misktakes can be possible even for NASA. So I don't count your opinion.

cheers,
sivalr