Re: mysql to postgresql, performance questions

От: Pierre C
Тема: Re: mysql to postgresql, performance questions
Дата: ,
Msg-id: op.u9tsgffkeorkce@localhost
(см: обсуждение, исходный текст)
Ответ на: 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, )

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

I've seen differences like that. Benchmarking isn't easy. The client
libraries, the particular language bindings you use, the connection... all
that can add overhead that is actually mych larger that what you're trying
to measure.

- On "localhost", some MySQL distros will default to a UNIX Socket, some
Postgres distros will default to a TCP socket, or even SSL, and vice versa.

Needless to say, on a small query like "SELECT * FROM users WHERE
user_id=$1", this makes a lot of difference, since the query time (just a
few tens of microseconds) is actually shorter than the TCP overhead.
Depending on how you connect you can get a 2-3x variation in throughput
with client and server on the same machine, just between TCP and UNIX
socket.

On queries that retrieve lots of data, overheads are also quite different
(especially with SSL...)

- I've seen postgres saturate a 1 GB/s ethernet link between server and
client during benchmark.

- Performance depends a LOT on your language bindings. For instance :

php : PDO is quite a lot slower than pg_query() especially if you use
prepared statements which are used only once,
python : psycopg, pygresql, mysql-python behave quite differently (psycopg
being by far the fastest of the bunch), especially when retrieving lots of
results, and converting those results back to python types...

So, what are you benchmarking exactly ?...


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

От: Kenneth Marshall
Дата:
Сообщение: Re: GiST index performance
От: Tom Lane
Дата:
Сообщение: Re: PG using index+filter instead only use index