Thread: Packet sizes on transfers between client/server

Packet sizes on transfers between client/server

From

"Walker, Jed S"

Date:

26 August 2005, 16:10:01

We are needing to cut-down network traffic between our PostgreSQL client and server. The network team says that PostgreSQL is transferring data in 256k packets with acks of around half that size. Is there a parameter or a connection string setting that can be changed to force the system to use larger packet sizes?

Thanks,

Jed.

Re: Packet sizes on transfers between client/server

From

Tom Lane

Date:

26 August 2005, 16:54:53

"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
> We are needing to cut-down network traffic between our PostgreSQL client
> and server. The network team says that PostgreSQL is transferring data
> in 256k packets with acks of around half that size. Is there a parameter
> or a connection string setting that can be changed to force the system
> to use larger packet sizes?

Postgres has no idea whatsoever about packet sizes.  This would be an
issue for your kernel vendor.

            regards, tom lane

Re: Packet sizes on transfers between client/server

From

Michael Fuhr

Date:

26 August 2005, 17:10:51

On Fri, Aug 26, 2005 at 10:02:55AM -0600, Walker, Jed S wrote:
> We are needing to cut-down network traffic between our PostgreSQL client
> and server. The network team says that PostgreSQL is transferring data
> in 256k packets with acks of around half that size. Is there a parameter
> or a connection string setting that can be changed to force the system
> to use larger packet sizes?

Are you sure those packet size figures are correct?  What link layer
are you using that allows 256k packet sizes?  Might the size be 256
instead of 256k?  And what ACKs are they talking about?  In a TCP
connection, *all* segments except the first have the ACK flag set,
and many of those segments will be carrying data.  ACKs carrying
no data should be small -- just big enough for the IP and TCP headers
(20 bytes + 20 bytes + 0 or more bytes for IP and/or TCP options;
network engineers might also be adding link-layer framing) -- and
the operating system determines when to send those.

If I'm reading the source correctly, PostgreSQL sends data in
8192-byte chunks, but how those writes are broken up or aggregated
into link-layer packets is up to the operating system.  If you're
seeing a lot of packets smaller than the link layer's MTU then I'd
guess the application is doing a lot of small queries.  If that's
the case, then maybe some of that work could be moved into server-side
functions.

Have you run a sniffer (tcpdump, ethereal, etc.) to verify what the
network team is telling you?  If so then it might be useful to see
some of that data.

--
Michael Fuhr

Re: Packet sizes on transfers between client/server

From

Michael Fuhr

Date:

26 August 2005, 18:35:18

On Fri, Aug 26, 2005 at 11:09:42AM -0600, Michael Fuhr wrote:
> ACKs carrying no data should be small -- just big enough for the
> IP and TCP headers (20 bytes + 20 bytes + 0 or more bytes for IP
> and/or TCP options; network engineers might also be adding link-layer
> framing)

BTW, these are IPv4 sizes.  I see IPv6 so seldom that sometimes I
forget about it :-)

--
Michael Fuhr

Re: Packet sizes on transfers between client/server

From

"Walker, Jed S"

Date:

26 August 2005, 19:19:34

Thanks for the detailed response. I've forwarded this on to our network
and server teams. There is one process that could be sending small
queries/data back and forth that we are already working to move to the
server. Once that's done we'll see if the small packets go away (the
only other process pulling data regularly should be pulling schedules
which should be fairly large).

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Friday, August 26, 2005 11:10 AM
To: Walker, Jed S
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Packet sizes on transfers between client/server

On Fri, Aug 26, 2005 at 10:02:55AM -0600, Walker, Jed S wrote:
> We are needing to cut-down network traffic between our PostgreSQL
> client and server. The network team says that PostgreSQL is
> transferring data in 256k packets with acks of around half that size.
> Is there a parameter or a connection string setting that can be
> changed to force the system to use larger packet sizes?

Are you sure those packet size figures are correct?  What link layer are
you using that allows 256k packet sizes?  Might the size be 256 instead
of 256k?  And what ACKs are they talking about?  In a TCP connection,
*all* segments except the first have the ACK flag set, and many of those
segments will be carrying data.  ACKs carrying no data should be small
-- just big enough for the IP and TCP headers (20 bytes + 20 bytes + 0
or more bytes for IP and/or TCP options; network engineers might also be
adding link-layer framing) -- and the operating system determines when
to send those.

If I'm reading the source correctly, PostgreSQL sends data in 8192-byte
chunks, but how those writes are broken up or aggregated into link-layer
packets is up to the operating system.  If you're seeing a lot of
packets smaller than the link layer's MTU then I'd guess the application
is doing a lot of small queries.  If that's the case, then maybe some of
that work could be moved into server-side functions.

Have you run a sniffer (tcpdump, ethereal, etc.) to verify what the
network team is telling you?  If so then it might be useful to see some
of that data.

--
Michael Fuhr

SQL "OR" Problem

From

Date:

26 August 2005, 19:58:09

hi all,

i'm trying to populate a list box with the names of
employees linked to certain positions (each name
listed once)...

SELECT DISTINCT t_emp.emp_id, t_emp.first_name || ' '
|| t_emp.last_name, t_pos.pos

FROM t_inspect, t_emp, t_pos
WHERE t_emp.pos_id = t_pos.pos_id
AND t_inspect.inspect_emp_id = t_emp.emp_id
AND t_pos.pos = 'Assembler'
OR t_pos.pos = 'Quality Inspector'
OR t_pos.pos = 'Test Technician'

i get the following results:

1. if an employee is an Assembler then s/he is listed
3 times - once with each position "pos".

2. if an employee is a Qaulity Inspector then s/he is
listed 2 times - once with each position "pos"
exlcuding "Assembler".

1. if an employee is a Test Technician then s/he will
likely be listed once with "Test Technician".  i don't
have a Test Technician in my dev db.

i'm not sure if the OR keyword is supported or if i
just made it up.  again, i'm just trying to list each
name once in each of these three positions (a name can
only be in one position category).

i think i'm either abusing "OR", missing something in
my where clause or, most probably, both.

any help would be appreciated.

tia...



____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs

Re: SQL "OR" Problem

From

Frank Bax

Date:

26 August 2005, 20:20:01

At 03:55 PM 8/26/05, operationsengineer1@yahoo.com wrote:

>hi all,
>
>i'm trying to populate a list box with the names of
>employees linked to certain positions (each name
>listed once)...
>
>SELECT DISTINCT t_emp.emp_id, t_emp.first_name || ' '
>|| t_emp.last_name, t_pos.pos
>
>FROM t_inspect, t_emp, t_pos
>WHERE t_emp.pos_id = t_pos.pos_id
>AND t_inspect.inspect_emp_id = t_emp.emp_id
>AND t_pos.pos = 'Assembler'
>OR t_pos.pos = 'Quality Inspector'
>OR t_pos.pos = 'Test Technician'
>
>i get the following results:
>
>1. if an employee is an Assembler then s/he is listed
>3 times - once with each position "pos".
>
>2. if an employee is a Qaulity Inspector then s/he is
>listed 2 times - once with each position "pos"
>exlcuding "Assembler".
>
>1. if an employee is a Test Technician then s/he will
>likely be listed once with "Test Technician".  i don't
>have a Test Technician in my dev db.
>
>i'm not sure if the OR keyword is supported or if i
>just made it up.  again, i'm just trying to list each
>name once in each of these three positions (a name can
>only be in one position category).
>
>i think i'm either abusing "OR", missing something in
>my where clause or, most probably, both.
>
>any help would be appreciated.


I'll guess the problem is operator precedence.  Try:

WHERE t_emp.pos_id = t_pos.pos_id
AND t_inspect.inspect_emp_id = t_emp.emp_id
AND ( t_pos.pos = 'Assembler'
OR t_pos.pos = 'Quality Inspector'
OR t_pos.pos = 'Test Technician' )

Check out table 4.1 on this page.

http://www.postgresql.org/docs/8.0/interactive/sql-syntax.html

Re: SQL "OR" Problem

From

Philip Hallstrom

Date:

26 August 2005, 20:21:03

> i'm trying to populate a list box with the names of
> employees linked to certain positions (each name
> listed once)...
>
> SELECT DISTINCT t_emp.emp_id, t_emp.first_name || ' '
> || t_emp.last_name, t_pos.pos
>
> FROM t_inspect, t_emp, t_pos
> WHERE t_emp.pos_id = t_pos.pos_id
> AND t_inspect.inspect_emp_id = t_emp.emp_id
> AND t_pos.pos = 'Assembler'
> OR t_pos.pos = 'Quality Inspector'
> OR t_pos.pos = 'Test Technician'

Try:

SELECT DISTINCT t_emp.emp_id,
        t_emp.first_name || ' ' || t_emp.last_name,
        t_pos.pos
FROM t_inspect, t_emp, t_pos
WHERE
      t_emp.pos_id = t_pos.pos_id
      AND t_inspect.inspect_emp_id = t_emp.emp_id
      AND (
         t_pos.pos = 'Assembler'
             OR t_pos.pos = 'Quality Inspector'
             OR t_pos.pos = 'Test Technician'
          )


>
> i get the following results:
>
> 1. if an employee is an Assembler then s/he is listed
> 3 times - once with each position "pos".
>
> 2. if an employee is a Qaulity Inspector then s/he is
> listed 2 times - once with each position "pos"
> exlcuding "Assembler".
>
> 1. if an employee is a Test Technician then s/he will
> likely be listed once with "Test Technician".  i don't
> have a Test Technician in my dev db.
>
> i'm not sure if the OR keyword is supported or if i
> just made it up.  again, i'm just trying to list each
> name once in each of these three positions (a name can
> only be in one position category).
>
> i think i'm either abusing "OR", missing something in
> my where clause or, most probably, both.
>
> any help would be appreciated.
>
> tia...
>
>
>
> ____________________________________________________
> Start your day with Yahoo! - make it your home page
> http://www.yahoo.com/r/hs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Re: SQL "OR" Problem

From

Tom Lane

Date:

26 August 2005, 20:30:03

<operationsengineer1@yahoo.com> writes:
> WHERE t_emp.pos_id = t_pos.pos_id
> AND t_inspect.inspect_emp_id = t_emp.emp_id
> AND t_pos.pos = 'Assembler'
> OR t_pos.pos = 'Quality Inspector'
> OR t_pos.pos = 'Test Technician'

You probably want some parentheses with that:

WHERE t_emp.pos_id = t_pos.pos_id
      AND t_inspect.inspect_emp_id = t_emp.emp_id
      AND (t_pos.pos = 'Assembler'
           OR t_pos.pos = 'Quality Inspector'
           OR t_pos.pos = 'Test Technician')

I believe AND binds more tightly than OR by default, so your original
means

WHERE (t_emp.pos_id = t_pos.pos_id
       AND t_inspect.inspect_emp_id = t_emp.emp_id
       AND t_pos.pos = 'Assembler')
      OR t_pos.pos = 'Quality Inspector'
      OR t_pos.pos = 'Test Technician'

which is unlikely to be what you want.

            regards, tom lane

Re: SQL "OR" Problem

From

Stephan Szabo

Date:

26 August 2005, 20:33:49

On Fri, 26 Aug 2005 operationsengineer1@yahoo.com wrote:

> hi all,
>
> i'm trying to populate a list box with the names of
> employees linked to certain positions (each name
> listed once)...
>
> SELECT DISTINCT t_emp.emp_id, t_emp.first_name || ' '
> || t_emp.last_name, t_pos.pos
>
> FROM t_inspect, t_emp, t_pos
> WHERE t_emp.pos_id = t_pos.pos_id
> AND t_inspect.inspect_emp_id = t_emp.emp_id
> AND t_pos.pos = 'Assembler'
> OR t_pos.pos = 'Quality Inspector'
> OR t_pos.pos = 'Test Technician'


Have you tried it with parens?  I'm guessing you probably wanted something
more like:

WHERE t_emp.pos_id = t_pos.pos_id
 AND t_inspect.inspect_emp_id = t_emp.emp_id
 AND (t_pos.pos = 'Assembler'
  OR t_pos.pos = 'Quality Inspector'
  OR t_pos.pos = 'Test Technician')

Re: SQL "OR" Problem

From

Steve Crawford

Date:

26 August 2005, 22:06:14

On Friday 26 August 2005 12:55 pm, operationsengineer1@yahoo.com
wrote:
> hi all,
>
> i'm trying to populate a list box with the names of
> employees linked to certain positions (each name
> listed once)...
>
> SELECT DISTINCT t_emp.emp_id, t_emp.first_name || ' '
>
> || t_emp.last_name, t_pos.pos
>
> FROM t_inspect, t_emp, t_pos
> WHERE t_emp.pos_id = t_pos.pos_id
> AND t_inspect.inspect_emp_id = t_emp.emp_id
> AND t_pos.pos = 'Assembler'
> OR t_pos.pos = 'Quality Inspector'
> OR t_pos.pos = 'Test Technician'

1) Is the t_inspect in there for a reason? I don't see it referenced
in your output or elsewhere in the where clause. Perhaps you are
looking only for inspectors? If so, an alternate method of writing
this where the intent is easier to grok is:
AND exists (select 1 from t_inspect where inspect_emp_id =
t.emp.emp_id)

2) If an employee has several positions then you should see several
lines as you have included the position in the output. My assumed
picture of your schema indicates that an employee could hold multiple
positions and, completely independently, could be an inspector.

3) I would need to know more about your data and think about this
query for a couple minutes to know if the parens will actually change
your output but as a safeguard and to make the intent clear, you
might want to consider parens around the positions or use "IN", ie.:
WHERE t_emp.pos_id = t_pos.pos_id
AND t_inspect.inspect_emp_id = t_emp.emp_id
AND (t_pos.pos = 'Assembler'
OR t_pos.pos = 'Quality Inspector'
OR t_pos.pos = 'Test Technician')

or alternately
...t_pos.pos IN ('Assembler', 'Quality Inspector', 'Test Technician')

> 2. if an employee is a Qaulity Inspector...
This line gave my best chuckle of the day :). Thanks.

Cheers,
Steve