Re: [HACKERS] SO_SNDBUF size is small on win32?
Hi, From: Tom Lane [EMAIL PROTECTED] Subject: Re: [HACKERS] SO_SNDBUF size is small on win32? Date: Tue, 27 Jun 2006 14:43:57 -0400 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. It could definitly be a good idea to have a patch there *as well*, but I think they'd both be affected. On the libpq side, sending large messages is probably rare except for COPY IN mode. Has anyone noticed performance issues specifically with COPY IN? I think libpq interface does not use non-blocking socket. Because 'FRONTEND' symbol is enabled. src/include/port/win32.h #ifndef FRONTEND #define socket(af, type, protocol) pgwin32_socket(af, type, protocol) #define accept(s, addr, addrlen) pgwin32_accept(s, addr, addrlen) #define connect(s, name, namelen) pgwin32_connect(s, name, namelen) #define select(n, r, w, e, timeout) pgwin32_select(n, r, w, e, timeout) #define recv(s, buf, len, flags) pgwin32_recv(s, buf, len, flags) #define send(s, buf, len, flags) pgwin32_send(s, buf, len, flags) I think this is only server-side problem. Is this right? Regards, -- Yoshiyuki Asaba [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SO_SNDBUF size is small on win32?
Yoshiyuki Asaba [EMAIL PROTECTED] writes: I think libpq interface does not use non-blocking socket. Not unless the Windows port has also disabled pg_set_noblock ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SO_SNDBUF size is small on win32?
From: Tom Lane [EMAIL PROTECTED]
Subject: Re: [HACKERS] SO_SNDBUF size is small on win32?
Date: Wed, 28 Jun 2006 09:54:21 -0400
Yoshiyuki Asaba [EMAIL PROTECTED] writes:
I think libpq interface does not use non-blocking socket.
Not unless the Windows port has also disabled pg_set_noblock ...
Sorry, I misunderstood.
I tried to occur this issue on msys.
% cat test.sh
export PGHOST=xxx
export PGPORT=5432
export PGDATABASE=test
dropdb $PGDATABASE
createdb
psql -c 'CREATE TABLE t1 (a int, b text)'
i=0
while [ $i -lt 50 ]; do
psql -c insert into t1 values ($i, repeat('x', 1))
i=`expr $i + 1`
done
pg_dump -a dump
time psql -f dump
% sh test.sh
But, I did not occur this issue... Does anyone occur this issue?
--
Yoshiyuki Asaba
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[HACKERS] SO_SNDBUF size is small on win32?
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
[EMAIL PROTECTED]
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.c5 Mar 2006 15:58:27 - 1.184
--- pqcomm.c27 Jun 2006 15:17:18 -
***
*** 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
Re: [HACKERS] SO_SNDBUF size is small on win32?
Yoshiyuki Asaba [EMAIL PROTECTED] 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
Re: [HACKERS] SO_SNDBUF size is small on win32?
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. signature.asc Description: Digital signature
Re: [HACKERS] SO_SNDBUF size is small on win32?
From: Tom Lane [EMAIL PROTECTED] Subject: Re: [HACKERS] SO_SNDBUF size is small on win32? Date: Tue, 27 Jun 2006 11:30:56 -0400 Yoshiyuki Asaba [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SO_SNDBUF size is small on win32?
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
Re: [HACKERS] SO_SNDBUF size is small on win32?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Martijn van Oosterhout 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... The article also says there is a problem if they are the same size. -rocco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SO_SNDBUF size is small on win32?
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. signature.asc Description: Digital signature
Re: [HACKERS] SO_SNDBUF size is small on win32?
Andrew Dunstan [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SO_SNDBUF size is small on win32?
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SO_SNDBUF size is small on win32?
From: Tom Lane [EMAIL PROTECTED] Subject: Re: [HACKERS] SO_SNDBUF size is small on win32? Date: Tue, 27 Jun 2006 12:28:35 -0400 Andrew Dunstan [EMAIL PROTECTED] 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/default.asp?url=/library/en-us/wcecomm5/html/wce50lrfWSAEventSelect.asp pgwin32_send() calls pgwin32_waitforsinglesocket() before WSASend(). And pgwin32_waitforsinglesocket() calls WSAEventSelect(). Regards, -- Yoshiyuki Asaba [EMAIL PROTECTED] ---(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] SO_SNDBUF size is small on win32?
Yoshiyuki Asaba [EMAIL PROTECTED] writes: From: Tom Lane [EMAIL PROTECTED] 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
Re: [HACKERS] SO_SNDBUF size is small on win32?
From: Tom Lane [EMAIL PROTECTED] 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. For once, I beleive you are :-) We use non-blocking sockets in backend/port/win32/socket.c so we are able to deliver our faked signals while waiting for I/O on the socket. We specifically set it in pgwin32_socket(). Given that, it might be a good idea to actually put the code there instead, to localise it. With a comment and a reference to that Q article. 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. We have definitly seen weird timing issues sometimes when both client and server were on Windows, but have been unable to pin it exactly on what. From Yoshiykis other mail it looks like this could possibly be it, since he did experience a speedup in the range we've been looking for in those cases. 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... It could definitly be a good idea to have a patch there *as well*, but I think they'd both be affected. //Magnus ---(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] SO_SNDBUF size is small on win32?
I would set the SO_SNDBUF to 32768.
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
[EMAIL PROTECTED]
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 - 1.184
--- pqcomm.c 27 Jun 2006 15:17:18 -
***
*** 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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] SO_SNDBUF size is small on win32?
Magnus Hagander [EMAIL PROTECTED] writes: We use non-blocking sockets in backend/port/win32/socket.c so we are able to deliver our faked signals while waiting for I/O on the socket. We specifically set it in pgwin32_socket(). Hm, that seems a bit grotty, but anyway I stand corrected. Given that, it might be a good idea to actually put the code there instead, to localise it. With a comment and a reference to that Q article. No, I think the patch has it in the right place, because pgwin32_socket would have no defensible way of knowing what the max send size would be. (Indeed, with a slightly different implementation in pqcomm.c, there would not *be* any hard upper limit; the current code wastes cycles copying data around, when with a large message it probably should just send() directly from the message buffer...) I agree it needs a comment though. 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. It could definitly be a good idea to have a patch there *as well*, but I think they'd both be affected. On the libpq side, sending large messages is probably rare except for COPY IN mode. Has anyone noticed performance issues specifically with COPY IN? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] SO_SNDBUF size is small on win32?
We have definitly seen weird timing issues sometimes when both client and server were on Windows, but have been unable to pin it exactly on what. From Yoshiykis other mail it looks like this could possibly be it, since he did experience a speedup in the range we've been looking for in those cases. 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... It could definitly be a good idea to have a patch there *as well*, but I think they'd both be affected. As I said earlier, I would boost the socket buffer to something larger than merely 2x the packet size. I'd try for 32K (32768), that way we have some space for additional buffers before we hit the problem. It is presumed that we should have enough data in the socket buffer to at least try to match the expected amount of data that would be sent while waiting for the defered ACK. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
