Re: About the tps explanation of pgbench, please help - Mailing list pgsql-general

Hi Yanrui:

On Tue, Nov 18, 2014 at 10:46 AM, Yanrui Hu <yhu@appannie.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.