Re: About the tps explanation of pgbench, please help

Hi Yanrui:

On Wed, Nov 19, 2014 at 2:48 AM, Yanrui Hu <yhu(at)appannie(dot)com> wrote:

> I am partly understanding your explain.
>

Enough for me, it isn't a simple thing to write :)

> the "including connection establish" case means the db connection not only
> socket connection, the "excluding connection establish" case include many
> db query and more depends on socket latency, right?
>

Right, as I said before, if you were doing the queries with psql including
should be the time from shell prompt to shell prompt, excluding from psql
prompt to psql prompt.

And what's your suggestion on my testing ? (to test network impact on my
> client user experience, my client on production environment is one dedicate
> machine already connected to database server with several connection, and
> occasionally send database select or update to database server depends on
> business).
> My current test parameters is "-U pgbench -c 150 -j 150 -n -s 500 -T 60 -f
> script_1.sql -r -C"
> Based on your mail, my senario is more like "excluding connection
> establish" case, right? So the network change make the capacity decrease to
> 1/5 (204->42). The network change impact the system so much!
>

I'm not too familiar with pgbench so I cannot comment on it, but it seems
you network is slow and your DB fast. Bear in mind 42 transactions a second
are quite a few. IIRC pgbench can be scripted to use the same type of
queries as your DB, but if your client is a single machine with a single
connection, you can easily do a simulator and test it.

I think the faster you can work with the DB with a normal client is about
one query per RTT in autocommit mode, one per two RTT with explicit
commits, and not knowing your exact network latency I cannot recommend
anything, but if you go from one query each 5ms to one each 25 ms and your
RTT is 20 ms I doubt anything can be done.

And the network changes impact the systems a lot, as it is a network
server. Basically measure RTT ( use 100 copies of a 1k ping at least ), you
are not going to be able to extract more than 1 query per RTT per
connection. Solutions are increasing connections ( if your pipe is fat and
long it can do wonders but the application needs to be able to do it ) and
minimising RTT ( for this I've had good results pushing everything inside a
single query in autocommit mode using stored procedures, so I only need one
RTT per op ), but given the few details's you've given on your setup I
cannot tell you more. Anything, those are the generic recomendations for
any network service having troubles due to slow network.

Regards.
Francisco Olarte.