Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: Corin <wakathane@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: mysql to postgresql, performance questions
References: <4BA23936.2030507@gmail.com>
Date: Fri, 19 Mar 2010 18:34:53 +0100
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.u9tsgffkeorkce@localhost>
In-Reply-To: <4BA23936.2030507@gmail.com>
User-Agent: Opera Mail/10.10 (Linux)


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