Re: About the tps explanation of pgbench, please help

Hi Yanrui:

On Tue, Nov 18, 2014 at 10:46 AM, Yanrui Hu <yhu(at)appannie(dot)com> wrote:

> It the "connection establishing" means socket connect? If so, that 's the
> only different for my case A and B.
> So the result of "exclude connection establishing" should be similar,
> right?
>

When you connect to the database you need to stablish a socket connection,
the server has to make some things ( like fork a postmaster to connect on
that new sockets ), and then you have to negotiate passwords and this kind
of things. This is the connection stablishing, and it is amortized among
the live of a socket connection, even if you keep it open for months. As
you have to send a lot of things on the net, this time is dependent on the
network bandwidth and latency, typically more on latency as it normally
consists on several small packets exchanges.

Then on every transaction / query you make you have to send the queries to
the server and it has to transmit you the result, so this phase also has a
network dependency. Normally more on latency than bandwidth too ( as
present networks are not normally short and thin ( low latency, small
bandwidth ), more likely balanced or fat and long in the extreme, but in
some cases ( like sending a select * from a big table, which needs very
little server work ) this can be dominated by bandwidth.

BOTH of the operations depend on network, that is why you get two sets of
numbers, but ( with the pgbench case ) one is more dependent than the
other. The including connection time is roughly equivalent to starting
psql, sending the query, exiting, rinse and repear. The excluding one is
roughly equivalent to starting psql ( send many queries ) exit as the end.
Even if you open psql and keep it open forever if your connection bounces
thrice to the geostationay orbit on each way you'll get slow queries, but
you'll save the couple of extra seconds to open it on each query.

Francisco Olarte.