Comments on Page2RSS: PostgreSQL vs. MySQL Performance

Using InnoDB tables is not just for transactions. ...

2008-10-30T03:49:00.000-07:00

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

Finally. A real world scenario that proves what I'...

2008-10-08T12:44:00.000-07:00

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.

I guess you haven't optimized your SQL. Innodb nee...

2008-07-18T15:53:00.000-07:00

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.

I have linux VPS machines with 256 and 512 ram. i ...

2008-07-09T01:59:00.000-07:00

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.

if you know how to configure mysql properly you wi...

2008-07-09T01:57:00.000-07:00

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.

@TeddySo Google and NASA are using "gay" software?...

2008-05-12T06:21:00.000-07:00

@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./

MySQL IS SO GAY!!!!!!And anyone with real developm...

2008-02-14T14:18:00.000-08:00

MySQL IS SO GAY!!!!!!

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

Thanks for the nice post!Free PS3

2007-09-12T10:13:00.000-07:00

Thanks for the nice post!

Free PS3

How did you collect your data for "Google's Time s...

2007-02-26T09:42:00.000-08:00

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

By default, InnoDB uses a more severe isolation le...

2007-01-19T17:08:00.000-08:00

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.

@Russ, Although MySQL doesn't have row level locki...

2007-01-19T10:03:00.000-08:00

@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.

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

2007-01-19T09:26:00.000-08:00

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

Depending on your usage pattern, MyISAM can be vas...

2007-01-19T08:27:00.000-08:00

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...

At Feed Digest we use MySQL. Approximately 10 giga...

2007-01-19T08:10:00.000-08:00

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.

Depending on the number of queries both databases ...

2007-01-19T07:57:00.000-08:00

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.

I'm not for one database or another, but I'm willi...

2007-01-19T07:16:00.000-08:00

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.

Any particular reason for using the innodb on the ...

2007-01-19T06:51:00.000-08:00

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

jaba: even if he did not properly index the MySQL ...

2007-01-19T04:42:00.000-08:00

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.

Are you sure you had properly indexed your MySQL d...

2007-01-19T00:55:00.000-08:00

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.

Even if MySQL performance was two times better in ...

2007-01-18T12:26:00.000-08:00

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.