SEO

Hi,

I see a performance issue on win32. This problem is causes by the
following URL.

http://support.microsoft.com/kb/823764/EN-US/

On win32, default SO_SNDBUF value is 8192 bytes. And libpq's buffer is
8192 too.

pqcomm.c:117
#define PQ_BUFFER_SIZE 8192

send() may take as long as 200ms. So, I think we should increase
SO_SNDBUF to more than 8192. I attache the patch.

Regards,
--
Yoshiyuki Asaba
y-asaba@sraoss.co.jp

Index: pqcomm.c
================================================== =================
RCS file: /projects/cvsroot/pgsql/src/backend/libpq/pqcomm.c,v
retrieving revision 1.184
diff -c -r1.184 pqcomm.c
*** pqcomm.c 5 Mar 2006 15:58:27 -0000 1.184
--- pqcomm.c 27 Jun 2006 15:17:18 -0000
***************
*** 593,598 ****
--- 593,608 ----
return STATUS_ERROR;
}

+ #ifdef WIN32
+ on = PQ_BUFFER_SIZE * 2;
+ if (setsockopt(port->sock, SOL_SOCKET, SO_SNDBUF,
+ (char *) &on, sizeof(on)) < 0)
+ {
+ elog(LOG, "setsockopt(SO_SNDBUF) failed: %m");
+ return STATUS_ERROR;
+ }
+ #endif
+
/*
* Also apply the current keepalive parameters. If we fail to set a
* parameter, don't error out, because these aren't universally


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Yoshiyuki Asaba <y-asaba@sraoss.co.jp> writes:
> send() may take as long as 200ms. So, I think we should increase
> SO_SNDBUF to more than 8192. I attache the patch.

Why would that help? We won't be sending more than 8K at a time.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Wed, Jun 28, 2006 at 12:23:13AM +0900, Yoshiyuki Asaba wrote:
> Hi,
>
> I see a performance issue on win32. This problem is causes by the
> following URL.
>
> http://support.microsoft.com/kb/823764/EN-US/
>
> On win32, default SO_SNDBUF value is 8192 bytes. And libpq's buffer is
> 8192 too.

Ok, so there's a difficiency in Windows TCP code. Do you have any
benchmarks to show this actually makes a difference. According to the
URL you give, the problem occurs if the libpq buffer is *bigger* than
the socket buffer, which it isn't...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEoU9IIB7bNG8LQkwRAtYLAJ0VNCsjhrdveqwM72TzYG fruoAtWQCaA51J
RC0unitFUbDLmRMyiDSsPMc=
=wMo2
-----END PGP SIGNATURE-----

From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [HACKERS] SO_SNDBUF size is small on win32?
Date: Tue, 27 Jun 2006 11:30:56 -0400

> Yoshiyuki Asaba <y-asaba@sraoss.co.jp> writes:
> > send() may take as long as 200ms. So, I think we should increase
> > SO_SNDBUF to more than 8192. I attache the patch.
>
> Why would that help? We won't be sending more than 8K at a time.

MSDN is,

Method2: Make the Socket Send Buffer Size Larger Than the Program
Send Buffer Size
....
Modify the send call or the WSASend call to specify a buffer size
at least 1 byte smaller than the SO_SNDBUF value.

--
Yoshiyuki Asaba
y-asaba@sraoss.co.jp

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Martijn van Oosterhout wrote:

>On Wed, Jun 28, 2006 at 12:23:13AM +0900, Yoshiyuki Asaba wrote:
>
>
>>Hi,
>>
>>I see a performance issue on win32. This problem is causes by the
>>following URL.
>>
>>http://support.microsoft.com/kb/823764/EN-US/
>>
>>On win32, default SO_SNDBUF value is 8192 bytes. And libpq's buffer is
>>8192 too.
>>
>>
>
>Ok, so there's a difficiency in Windows TCP code. Do you have any
>benchmarks to show this actually makes a difference. According to the
>URL you give, the problem occurs if the libpq buffer is *bigger* than
>the socket buffer, which it isn't...
>
>

No, it says it occurs if this condition is met: "A single *send* call or
*WSASend* call fills the whole underlying socket send buffer."

This will surely be true if the buffer sizes are the same. They
recommend making the socket buffer at least 1 byte bigger.

cheers

andrew



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

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

On Tue, Jun 27, 2006 at 11:45:53AM -0400, Andrew Dunstan wrote:
> No, it says it occurs if this condition is met: "A single *send* call or
> *WSASend* call fills the whole underlying socket send buffer."
>
> This will surely be true if the buffer sizes are the same. They
> recommend making the socket buffer at least 1 byte bigger.

Ok, but even then, are there any benchmarks to show it makes a
difference. The articles suggests there should be but it would be nice
to see how much difference it makes...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEoVkeIB7bNG8LQkwRAnWGAJ9luJXiOyLVQ6lB/nES0pN9UyLf5ACZAfZP
NXfYOrGgUE1JgZK2gsWmzDI=
=2Rib
-----END PGP SIGNATURE-----

Andrew Dunstan <andrew@dunslane.net> writes:
> Martijn van Oosterhout wrote:
>> On Wed, Jun 28, 2006 at 12:23:13AM +0900, Yoshiyuki Asaba wrote:
>>> http://support.microsoft.com/kb/823764/EN-US/

> No, it says it occurs if this condition is met: "A single *send* call or
> *WSASend* call fills the whole underlying socket send buffer."

It also says that the condition only occurs if the program uses
non-blocking sockets ... which the backend does not. So this page
offers no support for the proposed patch.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

From: Martijn van Oosterhout <kleptog@svana.org>
Subject: Re: [HACKERS] SO_SNDBUF size is small on win32?
Date: Tue, 27 Jun 2006 18:13:18 +0200

> On Tue, Jun 27, 2006 at 11:45:53AM -0400, Andrew Dunstan wrote:
> > No, it says it occurs if this condition is met: "A single *send* call or
> > *WSASend* call fills the whole underlying socket send buffer."
> >
> > This will surely be true if the buffer sizes are the same. They
> > recommend making the socket buffer at least 1 byte bigger.
>
> Ok, but even then, are there any benchmarks to show it makes a
> difference. The articles suggests there should be but it would be nice
> to see how much difference it makes...

I see the problem in this environment.

* client
- Windows XP
- using ODBC driver

* server
- Windows XP
- 8.1.4

* query time
- original -> about 12sec.
- patch version -> about 3sec.

However, this problem did not occur when I changed a client
machine...

Regards,
--
Yoshiyuki Asaba
y-asaba@sraoss.co.jp

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [HACKERS] SO_SNDBUF size is small on win32?
Date: Tue, 27 Jun 2006 12:28:35 -0400

> Andrew Dunstan <andrew@dunslane.net> writes:
> > Martijn van Oosterhout wrote:
> >> On Wed, Jun 28, 2006 at 12:23:13AM +0900, Yoshiyuki Asaba wrote:
> >>> http://support.microsoft.com/kb/823764/EN-US/
>
> > No, it says it occurs if this condition is met: "A single *send* call or
> > *WSASend* call fills the whole underlying socket send buffer."
>
> It also says that the condition only occurs if the program uses
> non-blocking sockets ... which the backend does not. So this page
> offers no support for the proposed patch.

WSAEventSelect() sets a socket to nonblocking mode.

http://msdn.microsoft.com/library/de...ventSelect.asp

pgwin32_send() calls pgwin32_waitforsinglesocket() before
WSASend(). And pgwin32_waitforsinglesocket() calls WSAEventSelect().

Regards,
--
Yoshiyuki Asaba
y-asaba@sraoss.co.jp

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Yoshiyuki Asaba <y-asaba@sraoss.co.jp> writes:
> From: Tom Lane <tgl@sss.pgh.pa.us>
>> It also says that the condition only occurs if the program uses
>> non-blocking sockets ... which the backend does not. So this page
>> offers no support for the proposed patch.

> WSAEventSelect() sets a socket to nonblocking mode.

Yeah, but that socket is only used for inter-backend signaling with
small (1 byte, I think) messages. The socket used for communication
with the frontend is not in nonblocking mode, unless I'm totally
confused.

Have you actually measured any performance benefit from this patch,
and if so what was the test case? I'm not opposed to the patch if it
does something useful, but the info currently available does not suggest
that it will help.

What I would think might help is a patch on the libpq side (because it
*does* use a nonblocking socket) to avoid sending more than 8K per
WSASend call. The effect would just be to break a long send into a
series of shorter sends, which wouldn't really do anything useful on a
well-designed TCP stack, but then this is Windows we're talking about...

regards, tom lane

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

http://archives.postgresql.org