Re: [HACKERS] comunication protocol

2007-08-10 Thread Hannu Krosing
Ühel kenal päeval, N, 2007-08-09 kell 17:12, kirjutas Omar Bettin:
 

 So, you aren't agree with the compression...
 
 I have sow the network statistics and in some cases, the network traffic 
 is very big.
 Probably a better setup could increase the performance by a few points 
 percent but
 I think a compressed protocol could increase the communication by 2/3 times.
 
 I think the biggest bottleneck in the whole system is just that.
 
 :..try to get a query from a remote server with a 56k modem! :)

Why not just set up an ssh2 tunnel with both encryption and compression
and access your remote db over that ?

--
Hannu


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] comunication protocol

2007-08-09 Thread Omar Bettin



Merlin Moncure wrote:

On 8/8/07, Omar Bettin [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [HACKERS] comunication protocol

2007-08-09 Thread Omar Bettin



Gustavo Tonini wrote:

Em Quarta 08 Agosto 2007 12:02, Omar Bettin escreveu:
  

Hi,
I have installed postgresql to my client as a server for a progam 
(libpq.dll/VCL based) that I wrote for them.



Borland VCL? What component are you using?

Gustavo.
  

  I use a strong modified PostgresDAC component.

 Omar


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [HACKERS] comunication protocol

2007-08-09 Thread Merlin Moncure
On 8/9/07, Omar Bettin [EMAIL PROTECTED] wrote:
 Merlin Moncure wrote:
  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.
 
 Interesting,do you thing that is possible to implement some send()
 in the interface?

SPI is an interface which allows you to make sql calls from C code
(PostgreSQL allows you to link C code compiled as a .so to the server
and call -- see numerous examples in contrib).  The routine you need
to exploit is SPI_getbinval which gives you Datum (essentially a
variant) pointing to the internal binary representation of your field.
 In theory you could collect the data into a buffer and send() it off
although thats a lot of work IMO.  Also, I would only advise this for
fast dumps from a single table (no joins, etc).

I have tried ZeosLib and for me is unusable (too slow), I use a
 strong modified PostgresDAC.

I'm suprised -- I know the ZeosLib internals and it's a very thin
layer over libpq.  Here is what I suggest:

* turn on statement logging in the server (set log_min_duration_statement)
* make some operations in the app which you suggest are slow  -- they
will show up in the log
* 'explain analyze' the query from the psql console

make note of the times and post back (maybe move this thread to the
-performance list)

merlin

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] comunication protocol

2007-08-09 Thread Omar Bettin



Merlin Moncure wrote:

On 8/9/07, Omar Bettin [EMAIL PROTECTED] wrote:
  

Merlin Moncure wrote:


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.

  

Interesting,do you thing that is possible to implement some send()
in the interface?



SPI is an interface which allows you to make sql calls from C code
(PostgreSQL allows you to link C code compiled as a .so to the server
and call -- see numerous examples in contrib).  The routine you need
to exploit is SPI_getbinval which gives you Datum (essentially a
variant) pointing to the internal binary representation of your field.
 In theory you could collect the data into a buffer and send() it off
although thats a lot of work IMO.  Also, I would only advise this for
fast dumps from a single table (no joins, etc).
  

why not joins?
  

   I have tried ZeosLib and for me is unusable (too slow), I use a
strong modified PostgresDAC.



I'm suprised -- I know the ZeosLib internals and it's a very thin
layer over libpq.  Here is what I suggest:

* turn on statement logging in the server (set log_min_duration_statement)
* make some operations in the app which you suggest are slow  -- they
will show up in the log
* 'explain analyze' the query from the psql console

make note of the times and post back (maybe move this thread to the
-performance list)

merlin

  

So, you aren't agree with the compression...

I have sow the network statistics and in some cases, the network traffic 
is very big.
Probably a better setup could increase the performance by a few points 
percent but

I think a compressed protocol could increase the communication by 2/3 times.

I think the biggest bottleneck in the whole system is just that.

:..try to get a query from a remote server with a 56k modem! :)

Omar


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

  


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [HACKERS] comunication protocol

2007-08-08 Thread Merlin Moncure
On 8/8/07, Omar Bettin [EMAIL PROTECTED] 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 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.

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.

 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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] comunication protocol

2007-08-08 Thread Gustavo Tonini
Em Quarta 08 Agosto 2007 12:02, Omar Bettin escreveu:
 Hi,
 I have installed postgresql to my client as a server for a progam 
 (libpq.dll/VCL based) that I wrote for them.

Borland VCL? What component are you using?

Gustavo.


pgp81qX9X4CeQ.pgp
Description: PGP signature