On Wed, Nov 19, 2014 at 2:48 AM, Yanrui Hu <yhu@appannie.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.
