Re: comunication protocol - Mailing list pgsql-hackers

Merlin Moncure wrote:
> On 8/8/07, Omar Bettin <o.bettin@tiscali.it> wrote:
>   
>> Hi,
>> I have installed postgresql to my client as a server for a progam
>> (libpq.dll/VCL based) that I wrote for them.
>>
>> Every is working fine, but I noted some general slowness,  compared with an
>> older database system (iAnywhere ADS)
>> due (I think) to the  text based communication protocol.
>>     
>
> you should maybe report a couple of specific things (explain analyze,
> etc) for analysis and make sure your expectations are reasonable.  It
> is possible simple configuration issues or query changes might be the
> answer here, then again, maybe not.
>      I think this is not necessary because I have used the same 
application with two different servers and you can see the speed 
differences just using it.
>   
>> I know there is the possibility to adopt a ssl compressed connection but I
>> think a pure compressed connections could be better.
>>     
>
> I think you are looking in the wrong direction here.
>
>   
>> So, I have studied the postgresql sources and I have tried to implement some
>> compression between the backend and the frontend,
>> using pglz_compress/pglz_decompress on be-secure.c and fe-secure.c.
>>
>> At the moment is working good on a local configuration, got some problems on
>> a remote connection due I think a different way to communicate.
>>     
>
> AFAIK, the fastest possible way to get data off the server, skipping
> all data and text processing is to write a SPI routine, and stream the
> data out locally to the server.   I am doing exactly this in a
> particular problem that requires high performance and I can tell you
> that SPI is fast.
>
> http://developer.postgresql.org/pgdocs/postgres/spi-examples.html
>
> That way you will bypass the protocol completely.  On my computer, I
> get roughly 300k records/sec raw read performance using libpq and
> about 1.3m records sec using a hacked SPI and streaming to disk.  This
> may not be helpful for your application but if you are exploring ways
> to bypass protocol overhead this is where I would start.
>      Interesting,do you thing that is possible to implement some send() 
in the interface?
> By the way, your problem might be the VCL driver you are using to
> access the database.  The highest performance driver I have used
> (which wraps libpq) is the Zeos library which is very fast.
>
>     I have tried ZeosLib and for me is unusable (too slow), I use a 
strong modified PostgresDAC.
>> There are other ways (other than be-secure and fe-secure) with which the
>> backend comunicate with the frontend?
>> And, do you think this solution could speed up something?
>>     
>
> Once again, I would start by looking at your application and posting
> here to make sure you are looking at the right bottlenecks (you
> _suspect_ the protocol is the problem, but is it really?).
>
> this means:
> * explain analyze/queries (w/how fast you think it should be going)
> * relevant .conf settings
> * time measurements from the app
>
> merlin
>
>      I just switch form Application1 (IAnywhere Ads) to Application2 
(Postgresql) optimizing the VCL (strong modifications to PostgresDAC 
sources) and sow the results.   My application needs to full open some tables and with this protocol 
is like to download a long text file.

   omar