PATCH:do not set Win32 server-side socket buffer size on windows 2012
Hi,all
PostgreSQL set Win32 server-side socket buffer size to 32k since 2006, for performance reasons.
While,on the newer version of Windows,such as windows 2012,the default socket buffer size is 64k,
and seem has better performance(high throughput).
So, i propose to apply the attached patch(based on the snapshot of 9.5dev) to set Win32 server-side
socket buffer size to 32k only when the default value is less than 32k.
OSdefault socket buffer size(get from getsockopt(SO_SNDBUF))
Window7: 8k
Windows2003:8k
Windows2008:8k
Windows8: 64k
Windows2012:64k
The following is my performance test for various SO_SNDBUF setting.
Test method:
Use psql to fetch about 100MB data from PostgreSQL(Windows) with various SO_SNDBUF setting.
[chenhj@node2 ~]$ time psql -h dbsvr -p 5432 -U postgres -A -t -c "select '1'::char(10000000),generate_series(1,10)">/dev/null
real0m3.295s
user0m0.222s
sys0m0.250s
Environment1(default SO_SNDBUF >32k):
Client: PostgreSQL 9.4.1 at RHEL6(x64)
Server: PostgreSQL 9.4.1 at Windows 2012(x64)
Network:1Gbit LAN
Result(execute time):
default(64K), 1.118s
set SO_SNDBUF to 32K, 3.295s(the current implement)
set SO_SNDBUF to 64K, 2.048s
set SO_SNDBUF to 128K, 1.404s
set SO_SNDBUF to 256K, 1.290s
1)When use Windows as client OS,the result is similar,but there's no "/dev/null" used by my test in windows.
2)I think the reason that the default(64K) is fast than "set SO_SNDBUF to 64K" is
that dynamic send buffering was disabled after set SO_SNDBUF option.
https://msdn.microsoft.com/en-us/library/windows/desktop/bb736549(v=vs.85).aspx
------------------------------------------------------------
Dynamic send buffering for TCP was added on Windows 7 and Windows Server 2008 R2. By default,
dynamic send buffering for TCP is enabled unless an application sets the SO_SNDBUF socket option on the stream socket.
------------------------------------------------------------
Environment2(default SO_SNDBUF <32k):
Client: PostgreSQL 9.4.1 at RHEL6(x64)
Server: PostgreSQL 9.4.1 at Windows 2008 R2(x64)
Network:1Gbit LAN
Result(execute time):
default(8K), 7.370s
set SO_SNDBUF to 32K, 4.159s(the current implement)
set SO_SNDBUF to 64K, 2.875s
set SO_SNDBUF to 128K, 1.593s
set SO_SNDBUF to 256K, 1.324s
Best regards,
Chen Huajun
Attachments:
not_set_sndbuf_for_win2012.patchapplication/octet-stream; name=not_set_sndbuf_for_win2012.patchDownload
diff --git a/src/backend/libpq/pqcomm.c b/src/backend/libpq/pqcomm.c
index 34efac4..711ca2f 100644
--- a/src/backend/libpq/pqcomm.c
+++ b/src/backend/libpq/pqcomm.c
@@ -713,6 +713,8 @@ StreamConnection(pgsocket server_fd, Port *port)
if (!IS_AF_UNIX(port->laddr.addr.ss_family))
{
int on;
+ int oldopt;
+ int optlen;
#ifdef TCP_NODELAY
on = 1;
@@ -734,16 +736,34 @@ StreamConnection(pgsocket server_fd, Port *port)
#ifdef WIN32
/*
- * This is a Win32 socket optimization. The ideal size is 32k.
+ * This is a Win32 socket optimization. The ideal size is 32k except
+ * for Windows 2012(and later versions).
* http://support.microsoft.com/kb/823764/EN-US/
+ * On Windows 2012, the default TCP send buffer size is 64k and with
+ * dynamic send buffering. And, the default setting is better.
+ * (Note:Dynamic send buffering will be disabled if sets the SO_SNDBUF
+ * socket option.)
+ * https://msdn.microsoft.com/en-us/library/windows/desktop/bb736549(v=vs.85).aspx
+ * On Windows 2008(and earlier versions), the default TCP send buffer
+ * size is 8k, enlarging it to 32k is better.
*/
- on = PQ_SEND_BUFFER_SIZE * 4;
- if (setsockopt(port->sock, SOL_SOCKET, SO_SNDBUF, (char *) &on,
- sizeof(on)) < 0)
+ optlen = sizeof(oldopt);
+ if (getsockopt(server_fd, SOL_SOCKET, SO_SNDBUF, (char *) &oldopt,
+ &optlen) < 0)
{
- elog(LOG, "setsockopt(SO_SNDBUF) failed: %m");
+ elog(LOG, "getsockopt(SO_SNDBUF) failed: %m");
return STATUS_ERROR;
}
+ on = PQ_SEND_BUFFER_SIZE * 4;
+ if(oldopt < on)
+ {
+ if (setsockopt(port->sock, SOL_SOCKET, SO_SNDBUF, (char *) &on,
+ sizeof(on)) < 0)
+ {
+ elog(LOG, "setsockopt(SO_SNDBUF) failed: %m");
+ return STATUS_ERROR;
+ }
+ }
#endif
/*
On Fri, Apr 10, 2015 at 7:46 PM, chenhj <chjischj@163.com> wrote:
PostgreSQL set Win32 server-side socket buffer size to 32k since 2006, for
performance reasons.While,on the newer version of Windows,such as windows 2012,the default
socket buffer size is 64k,
and seem has better performance(high throughput).
So, i propose to apply the attached patch(based on the snapshot of 9.5dev)
to set Win32 server-side
socket buffer size to 32k only when the default value is less than 32k.[...]
Interesting. I think that for the time being you should add it to the
next commit fest to target an integration in 9.6 as these days we are
busy wrapping up the last commit fest of 9.5:
https://commitfest.postgresql.org/5/
In any case, this is definitely something worth looking at IMO, in
2015 requirements are surely different than in 2006.
Regards,
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
At 2015-04-10 20:00:35, "Michael Paquier" <michael.paquier@gmail.com> wrote:
Interesting. I think that for the time being you should add it to the
next commit fest to target an integration in 9.6 as these days we are
busy wrapping up the last commit fest of 9.5:
https://commitfest.postgresql.org/5/
I had add it to the commit fest.
https://commitfest.postgresql.org/5/212/
Best Regards,
Chen Huajun
On 04/10/2015 01:46 PM, chenhj wrote:
PostgreSQL set Win32 server-side socket buffer size to 32k since 2006, for performance reasons.
While,on the newer version of Windows,such as windows 2012,the default socket buffer size is 64k,
and seem has better performance(high throughput).
So, i propose to apply the attached patch(based on the snapshot of 9.5dev) to set Win32 server-side
socket buffer size to 32k only when the default value is less than 32k.
Seems reasonable. I edited the comment somewhat, and added #ifdefs on
the new variables to avoid compiler warnings on other platforms.
OSdefault socket buffer size(get from getsockopt(SO_SNDBUF))
Window7: 8k
Windows2003:8k
Windows2008:8k
Windows8: 64k
Windows2012:64kThe following is my performance test for various SO_SNDBUF setting.
Test method:
Use psql to fetch about 100MB data from PostgreSQL(Windows) with various SO_SNDBUF setting.
[chenhj@node2 ~]$ time psql -h dbsvr -p 5432 -U postgres -A -t -c "select '1'::char(10000000),generate_series(1,10)">/dev/nullreal0m3.295s
user0m0.222s
sys0m0.250sEnvironment1(default SO_SNDBUF >32k):
Client: PostgreSQL 9.4.1 at RHEL6(x64)
Server: PostgreSQL 9.4.1 at Windows 2012(x64)
Network:1Gbit LANResult(execute time):
default(64K), 1.118s
set SO_SNDBUF to 32K, 3.295s(the current implement)
set SO_SNDBUF to 64K, 2.048s
set SO_SNDBUF to 128K, 1.404s
set SO_SNDBUF to 256K, 1.290s1)When use Windows as client OS,the result is similar,but there's no "/dev/null" used by my test in windows.
2)I think the reason that the default(64K) is fast than "set SO_SNDBUF to 64K" is
that dynamic send buffering was disabled after set SO_SNDBUF option.
https://msdn.microsoft.com/en-us/library/windows/desktop/bb736549(v=vs.85).aspx
------------------------------------------------------------
Dynamic send buffering for TCP was added on Windows 7 and Windows Server 2008 R2. By default,
dynamic send buffering for TCP is enabled unless an application sets the SO_SNDBUF socket option on the stream socket.
------------------------------------------------------------
Environment2(default SO_SNDBUF <32k):
Client: PostgreSQL 9.4.1 at RHEL6(x64)
Server: PostgreSQL 9.4.1 at Windows 2008 R2(x64)
Network:1Gbit LANResult(execute time):
default(8K), 7.370s
set SO_SNDBUF to 32K, 4.159s(the current implement)
set SO_SNDBUF to 64K, 2.875s
set SO_SNDBUF to 128K, 1.593s
set SO_SNDBUF to 256K, 1.324s
I was about to commit the attached, but when I tested this between my
Windows 8.1 virtual machine and Linux host, I was not able to see any
performance difference. It may be because the case is hobbled by other
inefficiencies, in the virtualization or somewhere else, but I wonder if
others can reproduce the speedup?
- Heikki
Attachments:
0001-Don-t-set-SO_SNDBUF-on-recent-Windows-versions-that-.patchtext/x-diff; name=0001-Don-t-set-SO_SNDBUF-on-recent-Windows-versions-that-.patchDownload
>From 2a8b9c5fe10d5b8dc4e4bcf00ed0025bc6d24a23 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date: Thu, 2 Jul 2015 22:09:08 +0300
Subject: [PATCH 1/1] Don't set SO_SNDBUF on recent Windows versions that have
a bigger default.
It's unnecessary to set it if the default is higher in the first place.
Furthermore, setting SO_SNDBUF disables the so-called "dynamic send
buffering" feature, which hurts performance further.
Chen Huajun
diff --git a/src/backend/libpq/pqcomm.c b/src/backend/libpq/pqcomm.c
index a4b37ed..7672287 100644
--- a/src/backend/libpq/pqcomm.c
+++ b/src/backend/libpq/pqcomm.c
@@ -726,6 +726,11 @@ StreamConnection(pgsocket server_fd, Port *port)
if (!IS_AF_UNIX(port->laddr.addr.ss_family))
{
int on;
+#ifdef WIN32
+ int oldopt;
+ int optlen;
+ int newopt;
+#endif
#ifdef TCP_NODELAY
on = 1;
@@ -747,16 +752,43 @@ StreamConnection(pgsocket server_fd, Port *port)
#ifdef WIN32
/*
- * This is a Win32 socket optimization. The ideal size is 32k.
- * http://support.microsoft.com/kb/823764/EN-US/
+ * This is a Win32 socket optimization. The OS send buffer should be
+ * large enough to send the whole Postgres send buffer in one go, or
+ * performance suffers. The Postgres send buffer can be enlarged if a
+ * very large message needs to be sent, but we won't attempt to
+ * enlarge the OS buffer if that happens, so somewhat arbitrarily
+ * ensure that the OS buffer is at least PQ_SEND_BUFFER_SIZE * 4.
+ * (That's 32kB with the current default).
+ *
+ * The default OS buffer size used to be 8kB in earlier Windows
+ * versions, but was raised to 64kB in Windows 2012. So it shouldn't
+ * be necessary to change it in later versions anymore. Changing it
+ * unnecessarily can even reduce performance, because setting
+ * SO_SNDBUF in the application disables the "dynamic send buffering"
+ * feature that was introduced in Windows 7. So before fiddling with
+ * SO_SNDBUF, check if the current buffers size is already large
+ * enough and only increase it if necessary.
+ *
+ * See https://support.microsoft.com/kb/823764/EN-US/ and
+ * https://msdn.microsoft.com/en-us/library/bb736549%28v=vs.85%29.aspx
*/
- on = PQ_SEND_BUFFER_SIZE * 4;
- if (setsockopt(port->sock, SOL_SOCKET, SO_SNDBUF, (char *) &on,
- sizeof(on)) < 0)
+ optlen = sizeof(oldopt);
+ if (getsockopt(server_fd, SOL_SOCKET, SO_SNDBUF, (char *) &oldopt,
+ &optlen) < 0)
{
- elog(LOG, "setsockopt(SO_SNDBUF) failed: %m");
+ elog(LOG, "getsockopt(SO_SNDBUF) failed: %m");
return STATUS_ERROR;
}
+ newopt = PQ_SEND_BUFFER_SIZE * 4;
+ if (oldopt < newopt)
+ {
+ if (setsockopt(port->sock, SOL_SOCKET, SO_SNDBUF, (char *) &newopt,
+ sizeof(newopt)) < 0)
+ {
+ elog(LOG, "setsockopt(SO_SNDBUF) failed: %m");
+ return STATUS_ERROR;
+ }
+ }
#endif
/*
--
2.1.4
On Fri, Jul 3, 2015 at 5:56 AM, Heikki Linnakangas wrote:
On 04/10/2015 01:46 PM, chenhj wrote:
I was about to commit the attached, but when I tested this between my
Windows 8.1 virtual machine and Linux host, I was not able to see any
performance difference. It may be because the case is hobbled by other
inefficiencies, in the virtualization or somewhere else, but I wonder if
others can reproduce the speedup?
I just gave this a shot in a 8.1 VM, but I could not reproduce a
speedup of more than a couple of percents (sorry no servers
available), still this seemed to be some noise.
The approach taken by this patch sounds safe enough to me that it
should be applied. I mean, we know that on Win8/2k12 the default
socket buffer size is 64k so reducing it to 32k would hurt
performance.
By the way, perhaps the link mentioned in this code should be updated
to this one, visibly microsoft has changed an bit the URL shape:
https://support.microsoft.com/en-us/kb/823764
A refresh would not hurt.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2015-07-02 23:56:16 +0300, Heikki Linnakangas wrote:
On 04/10/2015 01:46 PM, chenhj wrote:
Result(execute time):
default(8K), 7.370s
set SO_SNDBUF to 32K, 4.159s(the current implement)
set SO_SNDBUF to 64K, 2.875s
set SO_SNDBUF to 128K, 1.593s
set SO_SNDBUF to 256K, 1.324sI was about to commit the attached, but when I tested this between my
Windows 8.1 virtual machine and Linux host, I was not able to see any
performance difference. It may be because the case is hobbled by other
inefficiencies, in the virtualization or somewhere else, but I wonder if
others can reproduce the speedup?
Given that too small sockets incur significantly smaller latency bumps
in a virtualized environment than in a real network, and hit another set
of buffers inside the virtualization technology,, I'm not particularly
surprised by that.
Greetings,
Andres Freund
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 3 July 2015 at 20:06, Andres Freund <andres@anarazel.de> wrote:
On 2015-07-02 23:56:16 +0300, Heikki Linnakangas wrote:
On 04/10/2015 01:46 PM, chenhj wrote:
Result(execute time):
default(8K), 7.370s
set SO_SNDBUF to 32K, 4.159s(the current implement)
set SO_SNDBUF to 64K, 2.875s
set SO_SNDBUF to 128K, 1.593s
set SO_SNDBUF to 256K, 1.324sI was about to commit the attached, but when I tested this between my
Windows 8.1 virtual machine and Linux host, I was not able to see any
performance difference. It may be because the case is hobbled by other
inefficiencies, in the virtualization or somewhere else, but I wonder if
others can reproduce the speedup?Given that too small sockets incur significantly smaller latency bumps
in a virtualized environment than in a real network, and hit another set
of buffers inside the virtualization technology,, I'm not particularly
surprised by that.
I'm wondering what the original test setup was. I'm assuming psql and
postgres both running on separate windows machines?
I've tested the patch just connecting to a database running on localhost
and I'm not getting much of a speedup. Perhaps 1%, if that's not noise. I
don't have enough hardware here to have client and server on separate
machines, at least not with a stable network that goes through copper.
Here's the results.
Unpatched:
-- 100MB
Measure-Command { .\psql.exe -d postgres -t -A -c "select
'1'::char(10000000),generate_series(1,10)" > $null }
TotalMilliseconds : 1997.3908
TotalMilliseconds : 2111.4119
TotalMilliseconds : 2040.4415
TotalMilliseconds : 2167.5532
TotalMilliseconds : 2087.6444
TotalMilliseconds : 2117.3759
TotalMilliseconds : 2100.3229
TotalMilliseconds : 2132.3522
TotalMilliseconds : 2129.9487
TotalMilliseconds : 2101.675
Median: 2106.54345
Average: 2098.61165
-- 500MB
Measure-Command { .\psql.exe -d postgres -t -A -c "select
'1'::char(10000000),generate_series(1,50)" > $null }
TotalMilliseconds : 10344.4251
TotalMilliseconds : 10248.3671
TotalMilliseconds : 10370.3856
TotalMilliseconds : 10412.507
TotalMilliseconds : 10469.173
TotalMilliseconds : 10248.8889
TotalMilliseconds : 10331.9476
TotalMilliseconds : 10320.7841
TotalMilliseconds : 10470.3022
TotalMilliseconds : 10333.4203
Median: 10338.9227
Average: 10355.02009
Patched:
-- 100MB
Measure-Command { .\psql.exe -d postgres -t -A -c "select
'1'::char(10000000),generate_series(1,10)" > $null }
TotalMilliseconds : 2066.3701
TotalMilliseconds : 2106.6628
TotalMilliseconds : 2110.2459
TotalMilliseconds : 2047.8337
TotalMilliseconds : 2081.9166
TotalMilliseconds : 2034.7086
TotalMilliseconds : 2082.9072
TotalMilliseconds : 2146.6878
TotalMilliseconds : 2133.351
TotalMilliseconds : 2076.6862
Median: 2082.4119
Average: 2088.73699
-- 500MB
Measure-Command { .\psql.exe -d postgres -t -A -c "select
'1'::char(10000000),generate_series(1,50)" > $null }
TotalMilliseconds : 10217.4794
TotalMilliseconds : 10244.8074
TotalMilliseconds : 10451.7265
TotalMilliseconds : 10162.9862
TotalMilliseconds : 10304.1866
TotalMilliseconds : 10374.7922
TotalMilliseconds : 10227.9632
TotalMilliseconds : 10145.5825
TotalMilliseconds : 10298.7048
TotalMilliseconds : 10170.3754
Median: 10236.3853
Average: 10259.86042
Comparison (Unpatched / Patched)
100MB 500MB
Median 101.16% 101.00%
Average 100.47% 100.93%
Regards
David Rowley
--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services
On 3 July 2015 at 20:49, David Rowley <david.rowley@2ndquadrant.com> wrote:
On 3 July 2015 at 20:06, Andres Freund <andres@anarazel.de> wrote:
I've tested the patch just connecting to a database running on localhost
and I'm not getting much of a speedup. Perhaps 1%, if that's not noise. I
don't have enough hardware here to have client and server on separate
machines, at least not with a stable network that goes through copper.Here's the results.
I forgot to mention that I was running windows 8.1 64 bit directly on
hardware. No VM.
--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services
2015-07-03 16:49:44,"David Rowley" <david.rowley@2ndquadrant.com> wrote：
I'm wondering what the original test setup was. I'm assuming psql and postgres both running on separate windows machines?
I've tested the patch just connecting to a database running on localhost and I'm not getting much of a speedup. Perhaps 1%, if that's not noise. I don't have enough hardware here to have client and server on separate machines, at least not with a stable network that goes through copper.
My original test environments is as the following
Environment1:
Server:Windows 2012(x64)
The host is a VM in a private cloud
Client:RHEL6(x64)
The host is another VM in the same private cloud
Network:1Gbit LAN
Environment2:
Server:Windows 2012(x64)
The host is a VM in a private cloud
Client:Windows 7(x64)
The host is a physical machine(in fact it is My PC).
Network:1Gbit LAN
This Patch should only can speedup the environment which satisfy the following conditions.
1. The OS of the server is Windows 2012 or Win8(but i only tested it in Windows 2012).
2. The client and the server is separate machines.
3. The performance bottleneck is network throughput.
4. The utilization rate of network bandwidth is not full(such as only 50% or lower).
Best Regards
Chen Huajun
On 07/04/2015 02:03 PM, chenhj wrote:
2015-07-03 16:49:44,"David Rowley" <david.rowley@2ndquadrant.com> wrote：
I'm wondering what the original test setup was. I'm assuming psql
and postgres both running on separate windows machines?I've tested the patch just connecting to a database running on
localhost and I'm not getting much of a speedup. Perhaps 1%, if
that's not noise. I don't have enough hardware here to have client
and server on separate machines, at least not with a stable network
that goes through copper.My original test environments is as the following
Environment1:
Server:Windows 2012(x64)
The host is a VM in a private cloud
Client:RHEL6(x64)
The host is another VM in the same private cloud
Network:1Gbit LANEnvironment2:
Server:Windows 2012(x64)
The host is a VM in a private cloud
Client:Windows 7(x64)
The host is a physical machine(in fact it is My PC).
Network:1Gbit LANThis Patch should only can speedup the environment which satisfy the following conditions.
1. The OS of the server is Windows 2012 or Win8(but i only tested it in Windows 2012).
2. The client and the server is separate machines.
3. The performance bottleneck is network throughput.
4. The utilization rate of network bandwidth is not full(such as only 50% or lower).
I was able to reproduce huge gains from this, after I introduced an
artificial latency to all network packets with:
tc qdisc add dev eth2 root netem delay 100ms
With that, and with the client on different host so that the traffic
goes through that high-latency network, I saw over 10x difference with
the same psql test you ran.
Committed, thanks!
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Heikki Linnakangas <hlinnaka@iki.fi> writes:
I was able to reproduce huge gains from this, after I introduced an
artificial latency to all network packets with:
tc qdisc add dev eth2 root netem delay 100ms
With that, and with the client on different host so that the traffic
goes through that high-latency network, I saw over 10x difference with
the same psql test you ran.
Committed, thanks!
Why does the committed patch do getsockopt() on server_fd rather than
port->sock? This seems at best confusing and at worst wrong.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 07/06/2015 04:30 PM, Tom Lane wrote:
Heikki Linnakangas <hlinnaka@iki.fi> writes:
I was able to reproduce huge gains from this, after I introduced an
artificial latency to all network packets with:tc qdisc add dev eth2 root netem delay 100ms
With that, and with the client on different host so that the traffic
goes through that high-latency network, I saw over 10x difference with
the same psql test you ran.Committed, thanks!
Why does the committed patch do getsockopt() on server_fd rather than
port->sock? This seems at best confusing and at worst wrong.
Good catch, fixed!
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers