Re: mysql to postgresql, performance questions

От: Kenneth Marshall
Тема: Re: mysql to postgresql, performance questions
Дата: ,
Msg-id: 20100318150017.GA1069@it.is.rice.edu
(см: обсуждение, исходный текст)
Ответ на: mysql to postgresql, performance questions  (Corin)
Список: 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, )

If you expect this DB to be memory resident, you should update
the cpu/disk cost parameters in postgresql.conf. There was a
post earlier today with some more reasonable starting values.
Certainly your test DB will be memory resident.

Ken

On Thu, Mar 18, 2010 at 03:31:18PM +0100, 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.
>
> 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
>
> mysql
> {"select_type"=>"SIMPLE", "key_len"=>"1", "id"=>"1", "table"=>"users",
> "type"=>"range", "possible_keys"=>"birthday_age", "rows"=>"7572",
> "Extra"=>"Using where", "ref"=>nil, "key"=>"birthday_age"}
> 15.104055404663
> 14.209032058716
> 18.857002258301
> 15.714883804321
> 14.73593711853
> 15.048027038574
> 14.589071273804
> 14.847040176392
> 15.192985534668
> 15.115976333618
>
> postgresql
> {"QUERY PLAN"=>"Limit (cost=125.97..899.11 rows=1000 width=448) (actual
> time=0.927..4.990 rows=1000 loops=1)"}
> {"QUERY PLAN"=>" -> Bitmap Heap Scan on users (cost=125.97..3118.00
> rows=3870 width=448) (actual time=0.925..3.420 rows=1000 loops=1)"}
> {"QUERY PLAN"=>" Recheck Cond: (((birthday_age >= 12) AND (birthday_age <=
> 13)) OR ((birthday_age >= 20) AND (birthday_age <= 22)))"}
> {"QUERY PLAN"=>" -> BitmapOr (cost=125.97..125.97 rows=3952 width=0)
> (actual time=0.634..0.634 rows=0 loops=1)"}
> {"QUERY PLAN"=>" -> Bitmap Index Scan on birthday_age (cost=0.00..41.67
> rows=1341 width=0) (actual time=0.260..0.260 rows=1327 loops=1)"}
> {"QUERY PLAN"=>" Index Cond: ((birthday_age >= 12) AND (birthday_age <=
> 13))"}
> {"QUERY PLAN"=>" -> Bitmap Index Scan on birthday_age (cost=0.00..82.37
> rows=2611 width=0) (actual time=0.370..0.370 rows=2628 loops=1)"}
> {"QUERY PLAN"=>" Index Cond: ((birthday_age >= 20) AND (birthday_age <=
> 22))"}
> {"QUERY PLAN"=>"Total runtime: 5.847 ms"}
> 44.173002243042
> 41.156768798828
> 39.988040924072
> 40.470123291016
> 40.035963058472
> 40.077924728394
> 40.94386100769
> 40.183067321777
> 39.83211517334
> 40.256977081299
>
> 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?
>
> Thanks,
> Corin
>
>
> --
> Sent via pgsql-performance mailing list ()
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>


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

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