Postgres Professional
   

Re: TCP network cost

From: Ross J. Reedstrom
Subject: Re: TCP network cost
Date: ,
Msg-id: 20090223194325.GB14802@cooker
(view: Whole thread, Raw)
In response to: Re: TCP network cost  (PFC)
List: pgsql-performance

Tree view

TCP network cost  ("Ross J. Reedstrom", )
 Re: TCP network cost  (Rusty Conover, )
  Re: TCP network cost  (, )
  Re: TCP network cost  ("Ross J. Reedstrom", )
   Re: TCP network cost  (Rusty Conover, )
    Re: TCP network cost  ("Ross J. Reedstrom", )
     Re: TCP network cost  ("Ross J. Reedstrom", )
      Re: TCP network cost  (Aaron Turner, )
     Re: TCP network cost  (PFC, )
      Re: TCP network cost  ("Ross J. Reedstrom", )
   Re: TCP network cost  (Gregory Stark, )
    Re: TCP network cost  ("Ross J. Reedstrom", )
     Re: TCP network cost  (Tom Lane, )
      Re: TCP network cost  ("Ross J. Reedstrom", )
       Re: TCP network cost  (Linos, )
        Re: TCP network cost  (Tom Lane, )
         Re: TCP network cost  (Linos, )
          Re: TCP network cost  (Tom Lane, )
           Re: TCP network cost  (Linos, )
            Re: TCP network cost  (Tom Lane, )
             Re: TCP network cost  (Magnus Hagander, )
              Re: TCP network cost  (Linos, )
 Re: TCP network cost  (Aaron Turner, ) 
On Thu, Feb 19, 2009 at 02:09:04PM +0100, PFC wrote:
>
> >python w/ psycopg (or psycopg2), which wraps libpq. Same results w/
> >either version.
>
>     I've seen psycopg2 saturate a 100 Mbps ethernet connection (direct
> connection with crossover cable) between postgres server and client during
> a benchmark... I had to change the benchmark to not retrieve a large TEXT
> column to remove this bottleneck... this was last year so versions are
> probably different, but I don't think this matters a lot...

Here's the core of the problem: I in fact need to transfer exactly that:
a large single field (bytea in my case). I suspect psycopg[12] is having
issues w/ memory allocation, but that's just an unsupported gut feeling.

The final upshot is that I need to restructure my config to use the
large-object API (and hence a snapshot of psycopg2) to get decent
throughput.

>     You should test with sending a large (>100 MB) amount of data
>     through  Netcat. This should give you your maximum wire speed. Use
> /dev/null as the  test file, and use "pv" (pipe viewer) to measure
> throughput :
>
> box 1 : pv < /dev/zero | nc -lp 12345
> box 2 : nc (ip) 12345 >/dev/null
>
>     On gigabit lan you should get 100 MB/s, on 100BaseT about 10 MB/s.

112 MB/s, and 233 MB/s for localhost. Thanks for the pointer to pv:
looks like a nice tool. Investigating this problem has lead me to a
number of nice 'old school' tools: the other is tcptrace and xplot.org.
I've been hand reading tcpdump output, or clicking around in
ethereal/wireshark. I like tcptrace's approach.

Ross
--
Ross Reedstrom, Ph.D.                                 
Systems Engineer & Admin, Research Scientist        phone: 713-348-6166
The Connexions Project      http://cnx.org            fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

pgsql-performance by date:

Previous:
From: Farhan Husain
Date:
Subject: Re: Abnormal performance difference between Postgres and MySQL
Next:
From: Scott Marlowe
Date:
Subject: Re: Abnormal performance difference between Postgres and MySQL