Re: mysql to postgresql, performance questions

On 03/18/2010 09:31 AM, Corin wrote:
> Hi all,
>
> I'm running quite a large social community website (250k users, 16gb
> database). We are currently preparing a complete relaunch and thinking
> about switching from mysql 5.1.37 innodb to postgresql 8.4.2. The
> database server is a dual dualcore operton 2216 with 12gb ram running on
> debian amd64.
>
> For a first impression I ran a simple query on our users table (snapshot
> with only ~ 45.000 records). The table has an index on birthday_age
> [integer]. The test executes 10 times the same query and simply discards
> the results. I ran the tests using a php and a ruby script, the results
> are almost the same.
>

Don't underestimate mysql. It was written to be fast. But you have to understand the underling points: It was written to be fast at the cost of other things... like concurrent access, and data integrity. If you want to just read from a database, PG probably cant beat mysql. But heres the thing, your site does not just read. Nor does it fire off the same sql 10 times. So not a good test.

Mysql does not have strong concurrent read/write support. Wait.. let me not bash mysql, let me praise PG:

PG has strong concurrent read/write support, it has very strong data integrity, and strict sql syntax. (By strict sql syntax I mean you cant write invalid/imprecise sql and have it just return results to you, which later you realize the question you asked was complete jibberish so what the heck did mysql return? I know this from experience when I converted a website from mysql to pg, and pg did not like my syntax. After looking into it more I realized the sql I was writing was asking an insane question... I could not imagine how mysql knew what I was asking for, or even what it would be returning to me.)

Mysql was built on one underlying principle: there is nothing more important than speed.

I do not believe in that principle, that's why I don't choose mysql. If you bench your website at 150 pages a second on mysql, and 100 pages a second on PG, the only question is, do I really need more than 100 pages a second?

Even if PG is not as fast or faster, but in the same ballpark, the end user will never notice the difference, but you still gain all the other benefits of PG.

It could also be that the database is not your bottleneck. At some point, on mysql or PG, your website wont be fast enough. It might be the database, but might not. You'll have to go through the same thing regardless of which db you are on (fixing sql, organizing the db, adding webservers, adding db servers, caching stuff, etc...).

I guess, for me, once I started using PG and learned enough about it (all db have their own quirks and dark corners) I was in love. It wasnt important which db was fastest at xyz, it was which tool do I know, and trust, that can solve problem xyz.

(I added the "and trust" as an after thought, because I do have one very important 100% uptime required mysql database that is running. Its my MythTV box at home, and I have to ask permission from my GF before I take the box down to upgrade anything. And heaven forbid if it crashes or anything. So I do have experience with care and feeding of mysql. And no, I'm not kidding.)

And I choose PG.

-Andy