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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
