Message-ID: <517912C8.2010801@vmware.com>
Date: Thu, 25 Apr 2013 14:26:00 +0300
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.12) Gecko/20130116 Icedove/10.0.12
MIME-Version: 1.0
To: Kelphet Xiong <kelphet@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: =?windows-1252?Q?Re=3A_=5BPERFORM=5D_Question_about_netw?=
	=?windows-1252?Q?ork_bandwidth_usage_between_PostgreSQL=92s_cl?=
	=?windows-1252?Q?ient_and_server?=
References: 
 <CANnt-4Zrm=V1S5ViYnieUr1GVXROZ_BJsRAjcqKJomSEjq7zpA@mail.gmail.com>
In-Reply-To: 
 <CANnt-4Zrm=V1S5ViYnieUr1GVXROZ_BJsRAjcqKJomSEjq7zpA@mail.gmail.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 25.04.2013 02:56, Kelphet Xiong wrote:
> In all the experiments, the lineitem and partsupp tables reside in memory
> because there is no io activities observed from iotop.
> Since there is enough network bandwidth (1Gb/s or 128MB/s) between
> client and server,
> I would like to know what determines the data transferring rate or the
> network bandwidth usage
> between a client and a server when network bandwidth is enough.

Since there's enough network bandwidth available, the bottleneck is 
elsewhere. I don't know what it is in your example - maybe it's the I/O 
capacity, or CPU required to process the result in the server before 
it's sent over the network. It could also be in the client, on how fast 
it can process the results coming from the server.

I'd suggest running 'top' on the server while the query is executed, and 
keeping an eye on the CPU usage. If it's pegged at 100%, the bottleneck 
is the server's CPU.

> For example, given that the size of each tuple of lineitem table is
> 88% of that of partsupp,
> why the average network usage for sequential scan of lineitem table is only 50%
> that of partsupp table? And why the average network usage of their
> join is higher
> than that of sequential scan of lineitem but lower than that of
> sequential scan of partsupp table?

Here's a wild guess: the query on lineitem is bottlenecked by CPU usage 
in the server. A lot of CPU could be spent on converting the date fields 
from on-disk format to the text representation that's sent over the 
network; I've seen that conversion use up a lot of CPU time on some test 
workloads. Try leaving out the date columns from the query to test that 
theory.

If that's the bottleneck, you could try fetching the result in binary 
format, that should consume less CPU in the server. You didn't mention 
what client library you're using, but e.g with libpq, see the manual on 
PQexecParams on how to set the result format.

- Heikki


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance