Thread: Got no response last time on setsockopt post, so I thought I would reiterate.
Got no response last time on setsockopt post, so I thought I would reiterate.
From
"Dann Corbit"
Date:
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">These two calls make our remote queries via libpq about twice as fast on average. It seems to me
likeit might be a nice addition to the core product’s libpq (I poked it into the spot where the Nagle algorithm is
turnedoff, but another place would be fine too). Can anyone give me a reason why it is a bad idea to add this in? If
itwere made a parameter with a default of 64K, that would be even better. Then it could be tuned to particular systems
formaximum throughput.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> on = 65535;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> if (setsockopt(conn->sock, SOL_SOCKET, SO_RCVBUF,(char *) &on, sizeof(on)) <
0)</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> {</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> char sebuf[256];</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> printfPQExpBuffer(&conn->errorMessage,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> libpq_gettext("could not set socket SO_RCVBUF window size:
%s\n"),</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> SOCK_STRERROR(SOCK_ERRNO,
sebuf,sizeof(sebuf)));</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> return 0;</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> }</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> on = 65535;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> if (setsockopt(conn->sock, SOL_SOCKET, SO_SNDBUF,(char *) &on, sizeof(on)) <
0)</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> {</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> char sebuf[256];</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> printfPQExpBuffer(&conn->errorMessage,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> libpq_gettext("could not set socket SO_SNDBUF window size:
%s\n"),</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> SOCK_STRERROR(SOCK_ERRNO,
sebuf,sizeof(sebuf)));</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> return 0;</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> }</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font></div>
Re: Got no response last time on setsockopt post, so I thought I would reiterate.
From
Tom Lane
Date:
"Dann Corbit" <DCorbit@connx.com> writes:
> These two calls make our remote queries via libpq about twice as fast on
> average.
And, perhaps, cause even greater factors of degradation in other
scenarios (not to mention the possibility of complete failure on some
platforms). You haven't provided nearly enough evidence that this is
a safe change to make.
regards, tom lane
Re: Got no response last time on setsockopt post, so I thought I would reiterate.
From
"Dann Corbit"
Date:
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Monday, June 11, 2007 3:41 PM > To: Dann Corbit > Cc: pgsql-hackers@postgresql.org; Larry McGhaw > Subject: Re: [HACKERS] Got no response last time on setsockopt post, so I > thought I would reiterate. > > "Dann Corbit" <DCorbit@connx.com> writes: > > These two calls make our remote queries via libpq about twice as fast on > > average. > > And, perhaps, cause even greater factors of degradation in other > scenarios (not to mention the possibility of complete failure on some > platforms). You haven't provided nearly enough evidence that this is > a safe change to make. May I suggest: http://www-didc.lbl.gov/TCP-tuning/setsockopt.html http://www.ncsa.uiuc.edu/People/vwelch/net_perf/tcp_windows.html We test against dozens of operating systems and we have never had a problem (generally, we use our own tcp/ip network objects for communication and we only recently figured out why PostgreSQL was lagging so far behind and patched libPQ ourselves.) Now, it will be about 2 weeks before our full regressions have run against PostgreSQL on all of our platforms, but we do adjust the TCP/IP window on all of our clients and servers and have yet to find one that is unable to either negotiate a decent size or ignore our request at worst. However, I won't twist your arm. I just wanted to be sure that those at the PostgreSQL organization were aware of this simple trick. Our products run on: Aix BeOS Hpux Linux (everywhere, including mainframe zLinux) MVS SunOS Solaris OpenVMS Alpha OpenVMS VAX OpenVMS Itanium Windows And several others
Re: Got no response last time on setsockopt post, so I thought I would reiterate.
From
Greg Smith
Date:
On Mon, 11 Jun 2007, Dann Corbit wrote: > These two calls make our remote queries via libpq about twice as fast on > average. Can you comment a bit on what your remote queries are typically doing? You'll need to provide at least an idea what type of test case you're seeing the improvement on for others to try and replicate it. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Re: Got no response last time on setsockopt post, so I thought I would reiterate.
From
"Dann Corbit"
Date:
> -----Original Message----- > From: Greg Smith [mailto:gsmith@gregsmith.com] > Sent: Monday, June 11, 2007 4:09 PM > To: Dann Corbit > Cc: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Got no response last time on setsockopt post, so I > thought I would reiterate. > > On Mon, 11 Jun 2007, Dann Corbit wrote: > > > These two calls make our remote queries via libpq about twice as fast on > > average. > > Can you comment a bit on what your remote queries are typically doing? > You'll need to provide at least an idea what type of test case you're > seeing the improvement on for others to try and replicate it. We have literally thousands (maybe hundreds of thousands -- I'm not totally sure exactly how many there are because I am in development and not in testing) of queries, that take dozens of machines over a week to run. Our queries include inserts, updates, deletes, joins, views, um... You name it. Our product is database middleware and so we have to test against anything that is a legal SQL query against every sort of database and operating system combination (PostgreSQL is one of many environments that we support). If you have seen the NIST SQL verification suite, that is part of our test suite. We also found the PostgreSQL suite useful (though the PostgreSQL specific things we only run against PostgreSQL). We also have our own collection of regression tests that we have gathered over the past 20 years or so. I can't be specific because we run every sort of query. Most of our hardware is fairly high end (generally 1GB Ethernet, but we do have some machines that only have 100 MB net cards in them). I guess that our usage is atypical for general business use but fairly typical for those companies that produce middleware tool sets. However, many of our regressions came from customer feedback and so we do test lots and lots of valid customer requirements. I have a simple suggestion: Put the setsockopt calls in (with the necessary fluff to make it robust) and then perform the OSDB test. I guess that unless the OSDB houses the clients and the server on the same physical hardware you will see a very large bonus for a very simple change. > -- > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Re: Got no response last time on setsockopt post, so I thought I would reiterate.
From
Andrew Dunstan
Date:
Dann Corbit wrote: > However, I won't twist your arm. I just wanted to be sure that those at > the PostgreSQL organization were aware of this simple trick. Our > products run on: > Aix > BeOS > Hpux > Linux (everywhere, including mainframe zLinux) > MVS > SunOS > Solaris > OpenVMS Alpha > OpenVMS VAX > OpenVMS Itanium > Windows > > And several others > > > > We already set the SNDBUF on Windows for reasons documented in the code. I think if you were to quantify the alleged improvement by platform it might allay suspicion. cheers andrew
Re: Got no response last time on setsockopt post, so I thought I would reiterate.
From
"Dann Corbit"
Date:
> -----Original Message----- > From: Andrew Dunstan [mailto:andrew@dunslane.net] > Sent: Monday, June 11, 2007 4:35 PM > To: Dann Corbit > Cc: Tom Lane; pgsql-hackers@postgresql.org; Larry McGhaw > Subject: Re: [HACKERS] Got no response last time on setsockopt post, so I > thought I would reiterate. > > Dann Corbit wrote: > > However, I won't twist your arm. I just wanted to be sure that those at > > the PostgreSQL organization were aware of this simple trick. Our > > products run on: > > Aix > > BeOS > > Hpux > > Linux (everywhere, including mainframe zLinux) > > MVS > > SunOS > > Solaris > > OpenVMS Alpha > > OpenVMS VAX > > OpenVMS Itanium > > Windows > > > > And several others > > > > > > > > > > We already set the SNDBUF on Windows for reasons documented in the code. The only place I see it is for Windows *only* in PQCOMM.C (to 32K). Did I miss it somewhere else? > I think if you were to quantify the alleged improvement by platform it > might allay suspicion. I do not know if you will see the same results as we do. We support ancient and modern operating systems, on ancient and modern hardware (we have OpenVMS 6.1 running Rdb as old as 4.2, for instance -- 1980's technology). The only way for you to see if your environments have the same sort of benefits that we see is to test it yourselves. The TCP/IP window size is such a well known optimization setting (in fact the dominant one) that I am kind of surprised to be catching anyone unawares.
Re: Got no response last time on setsockopt post, so I thought I would reiterate.
From
Tom Lane
Date:
"Dann Corbit" <DCorbit@connx.com> writes: > May I suggest: > http://www-didc.lbl.gov/TCP-tuning/setsockopt.html > http://www.ncsa.uiuc.edu/People/vwelch/net_perf/tcp_windows.html I poked around on those pages and almost immediately came across http://www.psc.edu/networking/projects/tcptune/ which appears more up-to-date than the other pages, and it specifically recommends *against* setting SO_SNDBUF or SO_RCVBUF on modern Linuxen. So that's one fairly large category where we probably do not want this. You have not even made it clear whether you were increasing the sizes in the server-to-client or client-to-server direction, and your handwaving about the test conditions makes it even harder to know what you are measuring. I would think for instance that local vs remote connections make a big difference and might need different tuning. BTW, if we look at this issue we ought to also look at whether the send/recv quantum in libpq and the backend should be changed. It's been 8K for about ten years now ... regards, tom lane
Re: Got no response last time on setsockopt post, so I thought I would reiterate.
From
"Dann Corbit"
Date:
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Monday, June 11, 2007 5:12 PM > To: Dann Corbit > Cc: pgsql-hackers@postgresql.org; Larry McGhaw > Subject: Re: [HACKERS] Got no response last time on setsockopt post, so I > thought I would reiterate. > > "Dann Corbit" <DCorbit@connx.com> writes: > > May I suggest: > > http://www-didc.lbl.gov/TCP-tuning/setsockopt.html > > http://www.ncsa.uiuc.edu/People/vwelch/net_perf/tcp_windows.html > > I poked around on those pages and almost immediately came across > http://www.psc.edu/networking/projects/tcptune/ > which appears more up-to-date than the other pages, and it specifically > recommends *against* setting SO_SNDBUF or SO_RCVBUF on modern Linuxen. > So that's one fairly large category where we probably do not want this. It can still be a good idea to set it: http://datatag.web.cern.ch/datatag/howto/tcp.html 64K was just an example. Like I said before, it should be configurable. > You have not even made it clear whether you were increasing the sizes in > the server-to-client or client-to-server direction, and your handwaving > about the test conditions makes it even harder to know what you are > measuring. I would think for instance that local vs remote connections > make a big difference and might need different tuning. The configuration is a negotiation between client and server. You may or may not get what you ask for. I suggest that it is simple to implement and worthwhile to test. But it was only a suggestion. > BTW, if we look at this issue we ought to also look at whether the > send/recv quantum in libpq and the backend should be changed. It's been > 8K for about ten years now ... I suspect that TCP/IP packetizing will moderate the affects of changes on this parameter.