Re: mysql to postgresql, performance questions

От: Andy Colson
Тема: Re: mysql to postgresql, performance questions
Дата: ,
Msg-id: 4BA596D2.4050406@squeakycode.net
(см: обсуждение, исходный текст)
Ответ на: mysql to postgresql, performance questions  (Corin)
Ответы: Re: mysql to postgresql, performance questions  (Merlin Moncure)
Re: mysql to postgresql, performance questions  ("Ross J. Reedstrom")
Список: pgsql-performance

Скрыть дерево обсуждения

mysql to postgresql, performance questions  (Corin, )
 Re: mysql to postgresql, performance questions  (, )
 Re: mysql to postgresql, performance questions  (Kenneth Marshall, )
 Re: mysql to postgresql, performance questions  (Thom Brown, )
  Re: mysql to postgresql, performance questions  (Grzegorz Jaśkiewicz, )
 Re: mysql to postgresql, performance questions  (Stephen Frost, )
  Re: mysql to postgresql, performance questions  (Magnus Hagander, )
 Re: mysql to postgresql, performance questions  (Scott Marlowe, )
  Re: mysql to postgresql, performance questions  (Arjen van der Meijden, )
 Re: mysql to postgresql, performance questions  (Ivan Voras, )
 Re: mysql to postgresql, performance questions  (Dimitri Fontaine, )
  Re: mysql to postgresql, performance questions  (Scott Marlowe, )
 Re: mysql to postgresql, performance questions  ("Pierre C", )
 Re: mysql to postgresql, performance questions  (Merlin Moncure, )
 Re: mysql to postgresql, performance questions  (Andy Colson, )
  Re: mysql to postgresql, performance questions  (Merlin Moncure, )
  Re: mysql to postgresql, performance questions  ("Ross J. Reedstrom", )
   Re: mysql to postgresql, performance questions  (Tom Lane, )
    Re: mysql to postgresql, performance questions  (Scott Marlowe, )
    Re: mysql to postgresql, performance questions  (Greg Smith, )
     Re: mysql to postgresql, performance questions  (Dave Crooke, )
      Re: mysql to postgresql, performance questions  (Scott Marlowe, )
       Re: mysql to postgresql, performance questions  (Tom Lane, )
        Re: mysql to postgresql, performance questions  (Scott Marlowe, )
       Re: mysql to postgresql, performance questions  (Dave Crooke, )
     Re: mysql to postgresql, performance questions  (Yeb Havinga, )
      Re: mysql to postgresql, performance questions  (Yeb Havinga, )
      Re: mysql to postgresql, performance questions  (Greg Smith, )
      Re: mysql to postgresql, performance questions  (Hannu Krosing, )
       Re: mysql to postgresql, performance questions  (James Mansion, )
        Re: mysql to postgresql, performance questions  (Scott Marlowe, )
        Re: mysql to postgresql, performance questions  ("Pierre C", )
         Re: mysql to postgresql, performance questions  (Scott Marlowe, )
          Re: mysql to postgresql, performance questions  (Yeb Havinga, )
        Re: mysql to postgresql, performance questions  (Bruce Momjian, )
         Re: mysql to postgresql, performance questions  (Thom Brown, )
          Re: mysql to postgresql, performance questions  (Scott Marlowe, )
    Re: mysql to postgresql, performance questions  ("Ross J. Reedstrom", )
 Re: mysql to postgresql, performance questions  (Dave Crooke, )
  Re: mysql to postgresql, performance questions  (Merlin Moncure, )
   Re: mysql to postgresql, performance questions  ("Pierre C", )
    Re: mysql to postgresql, performance questions  (Dave Crooke, )
 Re: mysql to postgresql, performance questions  (Chris Browne, )
 Re: mysql to postgresql, performance questions  (Chris Browne, )

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


В списке pgsql-performance по дате сообщения:

От: Andy Colson
Дата:
Сообщение: Re: mysql to postgresql, performance questions
От: David Newall
Дата:
Сообщение: Re: pg_dump far too slow