From: "Peter T. Breuer" <ptb@inv.it.uc3m.es>
Message-Id: <200705251857.l4PIvYZ31429@inv.it.uc3m.es>
Subject: Re: general PG network slowness (possible cure) (repost)
In-Reply-To: <20070525133640.GF7069@it.is.rice.edu> from "Kenneth Marshall" at
	"May 25, 2007 08:36:40 am"
To: "Kenneth Marshall" <ktm@rice.edu>
Date: Fri, 25 May 2007 20:57:34 +0200 (MET DST)
CC: "Peter T. Breuer" <ptb@inv.it.uc3m.es>, pgsql-performance@postgresql.org
Reply-To: ptb@inv.it.uc3m.es
Reply-By: Sat, 1 Apr 2006 14:21:08 -0700

"Also sprach Kenneth Marshall:"
> improvement from coalescing the packets. Good luck in your investigations.

While I am recompiling stuff, just some stats.

Typical network traffic analysis during the PG runs:

        Total Packets Processed 493,499
        Unicast 100.0%  493,417
        Broadcast       0.0%    82
        Multicast       0.0%    0
        pktCast distribution chart
        Shortest         42 bytes
        Average Size    192 bytes
        Longest       1,514 bytes
        <= 64 bytes             0.0%       158
        64 to 128 bytes        77.3%   381,532
        129 to 256 bytes        6.8%    33,362
        257 to 512 bytes        8.6%    42,535
        513 to 1024 bytes       4.0%    19,577
        1025 to 1518 bytes      3.3%    16,335
 
Typical application rusage stats:

       time ./c -timeout 12000 -database postgresql://pebbles/d /tmp/tty_io..c
       user   system elapsed cpu
       7.866u 6.038s 5:49.13 3.9%      0+0k 0+0io 0pf+0w
       
Those stats show the system lost in i/o. It's neither in kernel nor in
userspace. Presumably the other side plus networking was the holdup.

For comparison, against localhost via loopback ("fake" networking):

       time ./c -timeout 12000 -database postgresql://localhost/d /tmp/tty_io..c
       user   system elapsed cpu
       9.483u 5.321s 2:41.78 9.1%      0+0k 0+0io 0pf+0w

but in that case postmaster was doing about 54% cpu, so the overall
cpu for server + client is 63%.

I moved to a unix domain socket and postmaster alone went to 68%.


       time ./c -timeout 12000 -database postgresql://unix/var/run/postgresql/d /tmp/tty_io..c
       user   system elapsed cpu
       9.569u 3.698s 2:52.41 7.6%      0+0k 0+0io 0pf+0w

The elapsed time is not much different between unix and localhost. One can
see that there is some i/o holdup because the two threads ought to do 100%
between them if handover of info were costless. The difference (the system
was queiscent o/w apart from the monitoring software, which shows only a
fraction of a percent loading). There were no memory shortages and swap
was disabled for the test (both sides)

For comparison, running against gdbm straignt to disk

      time ./c -timeout 12000  /tmp/tty_io..c
      user   system elapsed cpu
      2.637u 0.735s 0:05.34 62.9%     0+0k 0+0io 0pf+0w

Through localhost:

      time ./c -timeout 12000 -database gdbm://localhost/ptb/c /tmp/tty_io..c
      user   system elapsed cpu
      2.746u 3.699s 0:16.00 40.1%     0+0k 0+0io 0pf+0w

(the server process was at 35% cpu, for 75% total).
 
Across the net:

      time ./c -timeout 12000 -database gdbm://pebbles/ptb/c /tmp/tty_io..c
      user   system elapsed cpu
      2.982u 4.430s 1:03.44 7.9%      0+0k 0+0io 0pf+0w

(the server was at 7% cpu)


Have to go shopping ....
  
Peter