Thread: [RFC] Change the default of update_process_title to off
Hello, I'd like to propose changing the default value of update_process_title to off, at least on Windows. I'll submit a patchif we see no big problem. PROBLEM ======================================== Our customer is trying to certify PostgreSQL with their packaged software product. Currently, the product supports a famousDBMS (let me call it DBMS-X hereafter). They evaluated the performance of PostgreSQL and DBMS-X. The performance of PostgreSQL was very bad on Windows. The result was as follows (the unit is some request throughput). These were measured on the same machine. * DBMS-X on Windows: 750 * PostgreSQL on Windows: 250 * PostgreSQL on Linux: 870 The performance on Windows was considered unacceptable. Using pgbench, we could see similar result -- the performance onLinux is about three times higher than on Windows. CAUSE ======================================== The CreateEvent() and CloseHandle() Win32 API calls from postgres.exe was consuming much CPU time. While stressing the systemby running the select-only mode of pgbench, Windows performance monitor showed 50% User Time, 40% Privileged Time,and 10% Idle Time. Windows Performance Toolkit, which corresponds to perf on Linux, revealed that half of the privilegedtime was used by CreateEvent() and CloseHandle() called from set_ps_display(). Those calls are performed whenupdate_process_title is on. With update_process_title off, the performance became much closer to Linux as follows. The scaling factoris 300. The pgbenchclient was run on a different Windows machine with 12 CPU cores. The effect was minimal on Linux. C:\> pgbench -h <db_server> -T 30 -c #clients -j 12 -S benchdb [Windows] #clients on off 12 29793 38169 24 31587 87237 48 32588 83335 96 34261 67668 [Linux] #clients on off 12 52823 52976 24 90712 91955 48 108653 108762 96 107167 107140 PROPOSAL AND CONSIDERATIONS ======================================== I think we should change the default of update_process_title to off on Windows because: 1. The performance gain is huge. 2. It's almost useless because we can only see the postgres command line with Process Explorer, which the user must downloadfrom Microsoft and install. 3. I don't see the benefit of update_process_title=on at the expense of performance. 4. The default setting of PostgreSQL parameters should be friendly. I'm afraid many users cannot track the cause of poorperformance to update_process_title. I heard that MySQL's popularity was partly because it ran smoothly on Windows inthe early days. PostgreSQL should be, too. The question is, do we want to change the default to off on other OSes? Is the command line really useful? If useful, doesit need to be on by default? Regards Takayuki Tsunakawa
On Thu, Aug 4, 2016 at 4:41 PM, Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com> wrote: > 1. The performance gain is huge. > 2. It's almost useless because we can only see the postgres command line with Process Explorer, which the user must downloadfrom Microsoft and install. > 3. I don't see the benefit of update_process_title=on at the expense of performance. > 4. The default setting of PostgreSQL parameters should be friendly. I'm afraid many users cannot track the cause of poorperformance to update_process_title. I heard that MySQL's popularity was partly because it ran smoothly on Windows inthe early days. PostgreSQL should be, too. > > The question is, do we want to change the default to off on other OSes? I don't think so. > Is the command line really useful? > If useful, does it need to be on by default? I'd vote for keeping it on by default, because this information with ps is really useful for any kind of deployments, testing, etc. Here is a different proposal: documenting instead that disabling that parameter on Windows can improve performance, at the cost of losing information verbosity for processes. -- Michael
On 2016-08-04 16:48:11 +0900, Michael Paquier wrote: > Here is a different proposal: documenting instead that disabling that > parameter on Windows can improve performance, at the cost of losing > information verbosity for processes. The benefit on windows seems pretty marginal, given the way it has to be viewed. People installing processexplorer et. al. to view a handle that have to know about, will be able to change the config.
Andres Freund <andres@anarazel.de> writes:
> On 2016-08-04 16:48:11 +0900, Michael Paquier wrote:
>> Here is a different proposal: documenting instead that disabling that
>> parameter on Windows can improve performance, at the cost of losing
>> information verbosity for processes.
> The benefit on windows seems pretty marginal, given the way it has to be
> viewed. People installing processexplorer et. al. to view a handle that
> have to know about, will be able to change the config.
Yeah, I think I agree. It would be bad to disable it by default on
Unix, because ps(1) is a very standard tool there, but the same argument
doesn't hold for Windows.
Another route to a solution would be to find a cheaper way to update
the process title on Windows ... has anyone looked for alternatives?
regards, tom lane
On Thu, Aug 4, 2016 at 3:52 AM, Andres Freund <andres@anarazel.de> wrote: > On 2016-08-04 16:48:11 +0900, Michael Paquier wrote: >> Here is a different proposal: documenting instead that disabling that >> parameter on Windows can improve performance, at the cost of losing >> information verbosity for processes. > > The benefit on windows seems pretty marginal, given the way it has to be > viewed. People installing processexplorer et. al. to view a handle that > have to know about, will be able to change the config. I agree. I think it would be fine to disable this on Windows, but I wouldn't want to do the same thing on other platforms. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Yeah, I think I agree. It would be bad to disable it by default on Unix, > because ps(1) is a very standard tool there, but the same argument doesn't > hold for Windows. It seems that we could reach a consensus. The patch is attached. I'll add this to the next CommitFest. > Another route to a solution would be to find a cheaper way to update the > process title on Windows ... has anyone looked for alternatives? I couldn't find an alternative solution after asking some Windows support staff. Regards Takayuki Tsunakawa
Attachment
On 5 August 2016 at 12:25, Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com> wrote: > It seems that we could reach a consensus. The patch is attached. I'll add this to the next CommitFest. + The default is off on Windows + because the overhead is significant, and on on other platforms. "than on other platforms" But perhaps it's better written like: + This value defaults to "off" on Windows platforms due to the platform's significant overhead for updating the process title. +1 for this patch from me. I'd hate to think someone would quickly dismiss Postgres on windows due to some low TPS caused by updating the process title. -- David Rowley http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Fri, Aug 5, 2016 at 01:12:39PM +0200, David Rowley wrote: > On 5 August 2016 at 12:25, Tsunakawa, Takayuki > <tsunakawa.takay@jp.fujitsu.com> wrote: > > It seems that we could reach a consensus. The patch is attached. I'll add this to the next CommitFest. > > > + The default is off on Windows > + because the overhead is significant, and on on other platforms. I think "on on" is odd. I think you want "and on for other platforms." -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
On Fri, Aug 5, 2016 at 3:25 AM, Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com> wrote: >> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] >> Yeah, I think I agree. It would be bad to disable it by default on Unix, >> because ps(1) is a very standard tool there, but the same argument doesn't >> hold for Windows. > > It seems that we could reach a consensus. The patch is attached. I'll add this to the next CommitFest. > >> Another route to a solution would be to find a cheaper way to update the >> process title on Windows ... has anyone looked for alternatives? > > I couldn't find an alternative solution after asking some Windows support staff. > > Regards > Takayuki Tsunakawa Shouldn't we change the code which initdb uses to create the example postgresql.conf, so that it shows the commented out value as being 'off', when initdb is run on Windows? Cheers, Jeff
On Fri, Aug 5, 2016 at 12:19 PM, Jeff Janes <jeff.janes@gmail.com> wrote: > On Fri, Aug 5, 2016 at 3:25 AM, Tsunakawa, Takayuki > <tsunakawa.takay@jp.fujitsu.com> wrote: >>> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] >>> Yeah, I think I agree. It would be bad to disable it by default on Unix, >>> because ps(1) is a very standard tool there, but the same argument doesn't >>> hold for Windows. >> >> It seems that we could reach a consensus. The patch is attached. I'll add this to the next CommitFest. >> >>> Another route to a solution would be to find a cheaper way to update the >>> process title on Windows ... has anyone looked for alternatives? >> >> I couldn't find an alternative solution after asking some Windows support staff. >> >> Regards >> Takayuki Tsunakawa > > Shouldn't we change the code which initdb uses to create the example > postgresql.conf, so that it shows the commented out value as being > 'off', when initdb is run on Windows? +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
From: David Rowley [mailto:david.rowley@2ndquadrant.com] > But perhaps it's better written like: > > + This value defaults to "off" on Windows platforms due to the > platform's significant overhead for updating the process title. Thank you, I copied this. But I changed "off" to off because other places in config.sgml don't enclose on/off with quotes. > From: Robert Haas [mailto:robertmhaas@gmail.com] > On Fri, Aug 5, 2016 at 12:19 PM, Jeff Janes <jeff.janes@gmail.com> wrote: > > Shouldn't we change the code which initdb uses to create the example > > postgresql.conf, so that it shows the commented out value as being > > 'off', when initdb is run on Windows? > > +1. Good idea. Done. Regards Takayuki Tsunakawa
Attachment
On Fri, Aug 5, 2016 at 12:25 PM, Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com> wrote:
-- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Yeah, I think I agree. It would be bad to disable it by default on Unix,
> because ps(1) is a very standard tool there, but the same argument doesn't
> hold for Windows.
It seems that we could reach a consensus. The patch is attached. I'll add this to the next CommitFest.
> Another route to a solution would be to find a cheaper way to update the
> process title on Windows ... has anyone looked for alternatives?
I couldn't find an alternative solution after asking some Windows support staff.
FWIW, I remember this from back in the days when this was written. We kind of expected this would be slow already back then, but couldn't find a better way at the time either. And back then, I guess there were just enough *other* things that were slow with pg-on-windows that it didn't become as obvious.
So - +1 for changing the defaults as suggested.
What's our take on backpatching such changes? Should this be 9.6 only, or back further?
On Tue, Aug 16, 2016 at 5:53 AM, Magnus Hagander <magnus@hagander.net> wrote: > What's our take on backpatching such changes? Should this be 9.6 only, or > back further? I would have thought this was a master-only change, although back-patching it to 9.6 would be OK if it gets done RSN. I don't think changing GUC defaults in released branches is a good idea. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Aug 16, 2016 at 5:53 AM, Magnus Hagander <magnus@hagander.net> wrote:
>> What's our take on backpatching such changes? Should this be 9.6 only, or
>> back further?
> I would have thought this was a master-only change, although
> back-patching it to 9.6 would be OK if it gets done RSN. I don't
> think changing GUC defaults in released branches is a good idea.
I agree with fixing 9.6, but not further back.
regards, tom lane
On Tue, Aug 16, 2016 at 3:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
-- Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Aug 16, 2016 at 5:53 AM, Magnus Hagander <magnus@hagander.net> wrote:
>> What's our take on backpatching such changes? Should this be 9.6 only, or
>> back further?
> I would have thought this was a master-only change, although
> back-patching it to 9.6 would be OK if it gets done RSN. I don't
> think changing GUC defaults in released branches is a good idea.
I agree with fixing 9.6, but not further back.
Good, that pretty much aligns with what I was thinking.
Applied and backpatched to 9.6.
From: Magnus Hagander [mailto:magnus@hagander.net]
Applied and backpatched to 9.6.
Thank you very much. I didn’t expect 9.6 to be patched, so I’m very happy.
Regards
Takayuki Tsunakawa
On Tue, Aug 16, 2016 at 11:53:25AM +0200, Magnus Hagander wrote: > On Fri, Aug 5, 2016 at 12:25 PM, Tsunakawa, Takayuki < > tsunakawa.takay@jp.fujitsu.com> wrote: > > > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > Yeah, I think I agree. It would be bad to disable it by default on Unix, > > because ps(1) is a very standard tool there, but the same argument > doesn't > > hold for Windows. > > It seems that we could reach a consensus. The patch is attached. I'll add > this to the next CommitFest. > > > Another route to a solution would be to find a cheaper way to update the > > process title on Windows ... has anyone looked for alternatives? > > I couldn't find an alternative solution after asking some Windows support > staff. > > > FWIW, I remember this from back in the days when this was written. We kind of > expected this would be slow already back then, but couldn't find a better way > at the time either. And back then, I guess there were just enough *other* > things that were slow with pg-on-windows that it didn't become as obvious. > > So - +1 for changing the defaults as suggested. I am shocked at how much a speedup we get by turning off the process title on Windows: > From Tsunakawa, Takayuki > > C:\> pgbench -h <db_server> -T 30 -c #clients -j 12 -S benchdb > > [Windows] > #clients on off > 12 29793 38169 > 24 31587 87237 > 48 32588 83335 > 96 34261 67668 This ranges from a 28% to a 97% speed improvement on Windows! Those are not typos! This is a game-changer for use of Postgres on Windows for certain workloads! -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
On Tue, Aug 23, 2016 at 1:44 PM, Bruce Momjian <bruce@momjian.us> wrote: >> [Windows] >> #clients on off >> 12 29793 38169 >> 24 31587 87237 >> 48 32588 83335 >> 96 34261 67668 > > This ranges from a 28% to a 97% speed improvement on Windows! Those are > not typos! This is a game-changer for use of Postgres on Windows for > certain workloads! While I don't care all that much about performance on windows, it is a little sad that it took this long to fix something so simple. Consider this exchange, as a further example of our lack of concern here: https://www.postgresql.org/message-id/30619.1428157653@sss.pgh.pa.us ISTM that we don't even care about Windows performance to a minimal degree. Hopefully, the ICU stuff Peter Eisentraut is working on will level the playing field here a little bit, if only as an accidental side-effect. -- Peter Geoghegan
On Tue, Aug 23, 2016 at 01:58:02PM -0700, Peter Geoghegan wrote: > On Tue, Aug 23, 2016 at 1:44 PM, Bruce Momjian <bruce@momjian.us> wrote: > >> [Windows] > >> #clients on off > >> 12 29793 38169 > >> 24 31587 87237 > >> 48 32588 83335 > >> 96 34261 67668 > > > > This ranges from a 28% to a 97% speed improvement on Windows! Those are > > not typos! This is a game-changer for use of Postgres on Windows for > > certain workloads! > > While I don't care all that much about performance on windows, it is a > little sad that it took this long to fix something so simple. Consider > this exchange, as a further example of our lack of concern here: Agreed, but we really can only react to the research we are given. If no Windows users are digging in to find problems, those problems will not be found. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
From: Peter Geoghegan [mailto:pg@heroku.com] > On Tue, Aug 23, 2016 at 1:44 PM, Bruce Momjian <bruce@momjian.us> wrote: > >> [Windows] > >> #clients on off > >> 12 29793 38169 > >> 24 31587 87237 > >> 48 32588 83335 > >> 96 34261 67668 > > > > This ranges from a 28% to a 97% speed improvement on Windows! Those > > are not typos! This is a game-changer for use of Postgres on Windows > > for certain workloads! > > While I don't care all that much about performance on windows, it is a little > sad that it took this long to fix something so simple. Consider this exchange, > as a further example of our lack of concern here: > > https://www.postgresql.org/message-id/30619.1428157653@sss.pgh.pa.us Probably, the useful Windows Performance Toolkit, which is a counterpart of perf on Linux, was not available before. Maybewe can dig deeper into performance problems with it now. As a similar topic, I wonder whether the following still holds true, after many improvements on shared buffer lock contention. https://www.postgresql.org/docs/devel/static/runtime-config-resource.html "The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB." Regards Takayuki Tsunakawa
On Wed, Aug 24, 2016 at 4:35 AM, Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com> wrote:
From: Peter Geoghegan [mailto:pg@heroku.com]
> On Tue, Aug 23, 2016 at 1:44 PM, Bruce Momjian <bruce@momjian.us> wrote:
> >> [Windows]
> >> #clients on off
> >> 12 29793 38169
> >> 24 31587 87237
> >> 48 32588 83335
> >> 96 34261 67668
> >
> > This ranges from a 28% to a 97% speed improvement on Windows! Those
> > are not typos! This is a game-changer for use of Postgres on Windows
> > for certain workloads!
>
> While I don't care all that much about performance on windows, it is a little
> sad that it took this long to fix something so simple. Consider this exchange,
> as a further example of our lack of concern here:
>
> https://www.postgresql.org/message-id/30619.1428157653@ sss.pgh.pa.us
Probably, the useful Windows Performance Toolkit, which is a counterpart of perf on Linux, was not available before. Maybe we can dig deeper into performance problems with it now.
As a similar topic, I wonder whether the following still holds true, after many improvements on shared buffer lock contention.
https://www.postgresql.org/docs/devel/static/runtime- config-resource.html
"The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB."
Yes, that may very much be out of date as well. A good set of benchmarks around that would definitely be welcome.
On Wed, Aug 24, 2016 at 2:35 PM, Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com> wrote: > From: Peter Geoghegan [mailto:pg@heroku.com] >> On Tue, Aug 23, 2016 at 1:44 PM, Bruce Momjian <bruce@momjian.us> wrote: >> >> [Windows] >> >> #clients on off >> >> 12 29793 38169 >> >> 24 31587 87237 >> >> 48 32588 83335 >> >> 96 34261 67668 >> > >> > This ranges from a 28% to a 97% speed improvement on Windows! Those >> > are not typos! This is a game-changer for use of Postgres on Windows >> > for certain workloads! >> >> While I don't care all that much about performance on windows, it is a little >> sad that it took this long to fix something so simple. Consider this exchange, >> as a further example of our lack of concern here: >> >> https://www.postgresql.org/message-id/30619.1428157653@sss.pgh.pa.us > > Probably, the useful Windows Performance Toolkit, which is a counterpart of perf on Linux, was not available before. Maybewe can dig deeper into performance problems with it now. > > As a similar topic, I wonder whether the following still holds true, after many improvements on shared buffer lock contention. > > https://www.postgresql.org/docs/devel/static/runtime-config-resource.html > > "The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB." I don't use Windows, but I have heard recently that this is still true from someone who was testing with pgbench. He reported a dip in the curve above 512MB. Another database vendor recommends granting SeLockMemoryPrivilege so that it can use large pages on Windows when using several GB of buffer pool. I wonder if that might help Postgres on Windows. This could be useful as a starting point to test that theory: https://www.postgresql.org/message-id/CAEepm%3D075-bgHi_VDt4SCAmt%2Bo_%2B1XaRap2zh7XwfZvT294oHA%40mail.gmail.com -- Thomas Munro http://www.enterprisedb.com
> From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Thomas Munro > Another database vendor recommends granting SeLockMemoryPrivilege so that > it can use large pages on Windows when using several GB of buffer pool. > I wonder if that might help Postgres on Windows. This could be useful as > a starting point to test that theory: > > https://www.postgresql.org/message-id/CAEepm%3D075-bgHi_VDt4SCAmt%2Bo_ > %2B1XaRap2zh7XwfZvT294oHA%40mail.gmail.com Sorry for my late reply, and thank you. I've created a patch based on yours, and I'll submit it shortly in a separate thread. Regards Takayuki Tsunakawa