Re: mysql to postgresql, performance questions

От: Stephen Frost
Тема: Re: mysql to postgresql, performance questions
Дата: ,
Msg-id: 20100318150900.GO21875@tamriel.snowman.net
(см: обсуждение, исходный текст)
Ответ на: mysql to postgresql, performance questions  (Corin)
Ответы: Re: mysql to postgresql, performance questions  (Magnus Hagander)
Список: 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, )

Corin,

* Corin () wrote:
> 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.

Can you provide at least your postgresql.conf?  That could be useful,
though this does seem like a really simple query.

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

I wouldn't expect it to matter a whole lot, but have you considered
using prepared queries?

> Unluckily mysql seems to be around 3x as fast as postgresql for this
> simple query. There's no swapping, disc reading involved...everything is
> in ram.
>
> query
> select * from users where birthday_age between 12 and 13 or birthday_age
> between 20 and 22 limit 1000

Do you use every column from users, and do you really want 1000 records
back?

> {"QUERY PLAN"=>"Total runtime: 5.847 ms"}

This runtime is the amount of time it took for the backend to run the
query.

> 44.173002243042

These times are including all the time required to get the data back to
the client.  If you don't use cursors, all data from the query is
returned all at once.  Can you post the script you're using along with
the table schema and maybe some sample or example data?  Also, are you
doing this all inside a single transaction, or are you creating a new
transaction for every query?  I trust you're not reconnecting to the
database for every query..

> I also wonder why the reported runtime of 5.847 ms is so much different
> to the runtime reported of my scripts (both php and ruby are almost the
> same). What's the best tool to time queries in postgresql? Can this be
> done from pgadmin?

As was mentioned elsewhere, certainly the best tool to test with is your
actual application, if that's possible..  Or at least the language your
application is in.

    Thanks,

        Stephen

Вложения

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

От: Hannu Krosing
Дата:
Сообщение: Re: Building multiple indexes concurrently
От: Dave Crooke
Дата:
Сообщение: Re: Forcing index scan on query produces 16x faster