Date: Tue, 18 Oct 2005 14:58:48 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Bytea poor performance
In-reply-to: <3311.85.206.197.88.1129500120.squirrel@fmf.vtu.lt>
To: NSO <nso@fmf.vtu.lt>
Cc: Andreas Pflug <pgadmin@pse-consulting.de>,
 pgsql-performance@postgresql.org
Message-id: <435456D8.8070303@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=UTF-8
Content-transfer-encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050726)
References: <17260.81.7.89.65.1129382454.squirrel@fmf.vtu.lt>
	<29778.1129391647@sss.pgh.pa.us>
	<18133.81.7.89.65.1129392990.squirrel@fmf.vtu.lt>
	<4351DCB6.3040307@paradise.net.nz>
	<23627.81.7.89.65.1129460506.squirrel@fmf.vtu.lt>
	<4352C113.80104@pse-consulting.de>
	<3311.85.206.197.88.1129500120.squirrel@fmf.vtu.lt>

NSO wrote:
> 
> Well, no. Delphi isn't better, same time just for downloading data... But
> as I told before, if for ex. pgAdminIII is running on server machine it is
> a lot faster, I do not know why, I was monitoring network connection
> between client and server and it is using only up to 2% of full speed.. is
> server can't send faster? or client is not accepting data faster?
> 
>  

That difference is suspiciously high - you need to get one of your 
network boys to check that the NIC in your client box is operating at 
full speed (and/or does not clash with whatever network device it is 
plugged into). The other thing to check that that your client box is 
reasonably spec'ed : e.g. not running out of ram or disk in particular - 
or suffering from massively fragmented disk (the latter if its win32).

With respect to the Delphi, you can probably narrow where it has issues 
by running test versions of your app that have bits of functionality 
removed:

- retrieves the bytea but does not display it
- retrieves the bytea but displays it unformatted, or truncated
- does not retrieve the bytea at all

The difference between these should tell you where your issue is!

By way of comparison, I have a Php page (no Delphi sorry) that 
essentially shows 50 rows from your files table over a 100Mbit network. 
Some experiments with that show:

- takes 2 seconds to display in Firefox
- takes 0.2 seconds to complete a request (i.e. "display") using httperf

This indicates that (in my case) most of the 2 seconds is being used by 
Firefox (not being very good at) formatting the wide output for display.

The figure of about 2-5 seconds seems about right, so your 20-30 seconds 
certainly seems high!


cheers

Mark