Re: mysql to postgresql, performance questions - Mailing list pgsql-performance

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
writtento be fast at the cost of other things... like concurrent access, and data integrity.  If you want to just read
froma database, PG probably cant beat mysql.  But heres the thing, your site does not just read.  Nor does it fire off
thesame 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
syntaxI mean you cant write invalid/imprecise sql and have it just return results to you, which later you realize the
questionyou asked was complete jibberish so what the heck did mysql return?  I know this from experience when I
converteda website from mysql to pg, and pg did not like my syntax.  After looking into it more I realized the sql I
waswriting was asking an insane question... I could not imagine how mysql knew what I was asking for, or even what it
wouldbe 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
onmysql, 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
stillgain 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
areon (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
wasin love.  It wasnt important which db was fastest at xyz, it was which tool do I know, and trust, that can solve
problemxyz. 

(I added the "and trust" as an after thought, because I do have one very important 100% uptime required mysql database
thatis running.  Its my MythTV box at home, and I have to ask permission from my GF before I take the box down to
upgradeanything.  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