Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add shared_memory_type GUC.

  1. Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Palle Girgensohn <girgen@freebsd.org> — 2014-04-20T09:24:38Z

    Hi!

I see performance degradation with PostgreSQL 9.3 vs 9.2 on FreeBSD, and I'm wondering who to poke to mitigate the problem. In reference to this thread [1], who where the FreeBSD people that Francois mentioned? If mmap needs to perform well in the kernel, I'd like to know of someone with FreeBSD kernel knowledge who is interested in working with mmap perfocmance. If mmap is indeed the cuplrit, I've just tested 9.2.8 vs 9.3.4, I nevere isolated the mmap patch, although I believe Francois did just that with similar results.

Enclosed is a report from a simple pgbench check.

Palle

[1] http://www.postgresql.org/message-id/20130126120024.GA21101@sekishi.zefyris.com

  2. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Francois Tigeot <ftigeot@wolfpond.org> — 2014-04-20T10:19:55Z

    Hi,

On Sun, Apr 20, 2014 at 11:24:38AM +0200, Palle Girgensohn wrote:
> 
> I see performance degradation with PostgreSQL 9.3 vs 9.2 on FreeBSD, and I'm wondering who to poke to mitigate the problem. In reference to this thread [1], who where the FreeBSD people that Francois mentioned?

At least one FreeBSD hacker came to discuss it on the #dragonflybsd irc
channel and tried to run the benchmark on a 80-core machine.

I didn't keep logs and don't remember his/their name(s) but there was
definitely some FreeBSD effort at the time to investigate and fix things.

> If mmap needs to perform well in the kernel, I'd like to know of someone with FreeBSD kernel knowledge who is interested in working with mmap perfocmance. If mmap is indeed the cuplrit, I've just tested 9.2.8 vs 9.3.4, I nevere isolated the mmap patch, although I believe Francois did just that with similar results.

I did test the 9.3 -devel branch before and after the SysV shared memory =>
mmap commit. The performance degradation was visible.

I recently ran a few benchmarks of PostgreSQL 9.3 with different operating systems
including DragonFly 3.6 and FreeBSD 10. You may be interested in the results:

http://lists.dragonflybsd.org/pipermail/users/2014-March/128216.html

-- 
Francois Tigeot

  3. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Palle Girgensohn <girgen@pingpong.net> — 2014-04-20T14:07:25Z

    > 20 apr 2014 kl. 12:19 skrev Francois Tigeot <ftigeot@wolfpond.org>:
> 
> Hi,
> 
>> On Sun, Apr 20, 2014 at 11:24:38AM +0200, Palle Girgensohn wrote:
>> 
>> I see performance degradation with PostgreSQL 9.3 vs 9.2 on FreeBSD, and I'm wondering who to poke to mitigate the problem. In reference to this thread [1], who where the FreeBSD people that Francois mentioned?
> 
> At least one FreeBSD hacker came to discuss it on the #dragonflybsd irc
> channel and tried to run the benchmark on a 80-core machine.
> 
> I didn't keep logs and don't remember his/their name(s) but there was
> definitely some FreeBSD effort at the time to investigate and fix things.
> 
>> If mmap needs to perform well in the kernel, I'd like to know of someone with FreeBSD kernel knowledge who is interested in working with mmap perfocmance. If mmap is indeed the cuplrit, I've just tested 9.2.8 vs 9.3.4, I nevere isolated the mmap patch, although I believe Francois did just that with similar results.
> 
> I did test the 9.3 -devel branch before and after the SysV shared memory =>
> mmap commit. The performance degradation was visible.
> 
> I recently ran a few benchmarks of PostgreSQL 9.3 with different operating systems
> including DragonFly 3.6 and FreeBSD 10. You may be interested in the results:
> 
> http://lists.dragonflybsd.org/pipermail/users/2014-March/128216.html
> 

Interesting, indeed. The fixes to dragonfly where made quite recently, in 3.2, right? Was it an isolated patch that could perhaps be used as inspiration for a similar fix on freebsd, or is it the major rewrite of the scheduler mentioned in [http://m.slashdot.org/story/177299]?

Palle


> -- 
> Francois Tigeot

  4. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Francois Tigeot <ftigeot@wolfpond.org> — 2014-04-21T09:26:43Z

    On Sun, Apr 20, 2014 at 04:07:25PM +0200, Palle Girgensohn wrote:
> 
> >> If mmap needs to perform well in the kernel, I'd like to know of someone with FreeBSD kernel knowledge who is interested in working with mmap perfocmance. If mmap is indeed the cuplrit, I've just tested 9.2.8 vs 9.3.4, I nevere isolated the mmap patch, although I believe Francois did just that with similar results.
> > 
> > I did test the 9.3 -devel branch before and after the SysV shared memory =>
> > mmap commit. The performance degradation was visible.
> > 
> > I recently ran a few benchmarks of PostgreSQL 9.3 with different operating systems
> > including DragonFly 3.6 and FreeBSD 10. You may be interested in the results:
> > 
> > http://lists.dragonflybsd.org/pipermail/users/2014-March/128216.html
> > 
> 
> Interesting, indeed. The fixes to dragonfly where made quite recently, in 3.2, right?

The most important fixes occured in the 3.1 development version, around
September 2012.

There was definitely more than an isolated patch; the new scheduler was only
part of the performance improvements.
I'm afraid none of the commits would be applicable as-is to FreeBSD 10.x; the
DragonFly kernel is vastly different in locking, threading and VM management.

The FreeBSD folks should know what to do though; I collected performance
counter data during the last benchmark run and sent it to adrian@.
It was also discussed on freebsd-performance; the thread begins here:
http://lists.freebsd.org/pipermail/freebsd-performance/2014-March/004770.html

-- 
Francois Tigeot

  5. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Palle Girgensohn <girgen@pingpong.net> — 2014-04-21T10:58:01Z

    
> 21 apr 2014 kl. 11:26 skrev Francois Tigeot <ftigeot@wolfpond.org>:
> 
>> On Sun, Apr 20, 2014 at 04:07:25PM +0200, Palle Girgensohn wrote:
>> 
>>>> If mmap needs to perform well in the kernel, I'd like to know of someone with FreeBSD kernel knowledge who is interested in working with mmap perfocmance. If mmap is indeed the cuplrit, I've just tested 9.2.8 vs 9.3.4, I nevere isolated the mmap patch, although I believe Francois did just that with similar results.
>>> 
>>> I did test the 9.3 -devel branch before and after the SysV shared memory =>
>>> mmap commit. The performance degradation was visible.
>>> 
>>> I recently ran a few benchmarks of PostgreSQL 9.3 with different operating systems
>>> including DragonFly 3.6 and FreeBSD 10. You may be interested in the results:
>>> 
>>> http://lists.dragonflybsd.org/pipermail/users/2014-March/128216.html
>> 
>> Interesting, indeed. The fixes to dragonfly where made quite recently, in 3.2, right?
> 
> The most important fixes occured in the 3.1 development version, around
> September 2012.
> 
> There was definitely more than an isolated patch; the new scheduler was only
> part of the performance improvements.
> I'm afraid none of the commits would be applicable as-is to FreeBSD 10.x; the
> DragonFly kernel is vastly different in locking, threading and VM management.
> 
> The FreeBSD folks should know what to do though; I collected performance
> counter data during the last benchmark run and sent it to adrian@.
> It was also discussed on freebsd-performance; the thread begins here:
> http://lists.freebsd.org/pipermail/freebsd-performance/2014-March/004770.html
> 

Great, thanks for the pointers!

Palle

  6. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andres Freund <andres@2ndquadrant.com> — 2014-04-21T11:10:01Z

    Hi,

On 2014-04-20 11:24:38 +0200, Palle Girgensohn wrote:
> I see performance degradation with PostgreSQL 9.3 vs 9.2 on FreeBSD, and I'm wondering who to poke to mitigate the problem. In reference to this thread [1], who where the FreeBSD people that Francois mentioned? If mmap needs to perform well in the kernel, I'd like to know of someone with FreeBSD kernel knowledge who is interested in working with mmap perfocmance. If mmap is indeed the cuplrit, I've just tested 9.2.8 vs 9.3.4, I nevere isolated the mmap patch, although I believe Francois did just that with similar results.

If there are indeed such large regressions on FreeBSD we need to treat
them as postgres regressions. It's nicer not to add config options for
things that don't need it, but apparently that's not the case here.

Imo this means we need to add GUC to control wether anon mmap() or sysv
shmem is to be used. In 9.3.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  7. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <bright@mu.org> — 2014-04-21T14:31:55Z

    On 4/21/14 4:10 AM, Andres Freund wrote:
> Hi,
>
> On 2014-04-20 11:24:38 +0200, Palle Girgensohn wrote:
>> I see performance degradation with PostgreSQL 9.3 vs 9.2 on FreeBSD, and I'm wondering who to poke to mitigate the problem. In reference to this thread [1], who where the FreeBSD people that Francois mentioned? If mmap needs to perform well in the kernel, I'd like to know of someone with FreeBSD kernel knowledge who is interested in working with mmap perfocmance. If mmap is indeed the cuplrit, I've just tested 9.2.8 vs 9.3.4, I nevere isolated the mmap patch, although I believe Francois did just that with similar results.
> If there are indeed such large regressions on FreeBSD we need to treat
> them as postgres regressions. It's nicer not to add config options for
> things that don't need it, but apparently that's not the case here.
>
> Imo this means we need to add GUC to control wether anon mmap() or sysv
> shmem is to be used. In 9.3.
>
> Greetings,
>
> Andres Freund
>
Andres, thank you.  Speaking as a FreeBSD developer that would be a good 
idea.

-- 
Alfred Perlstein

  8. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T14:34:13Z

    On 4/21/14 4:10 AM, Andres Freund wrote:
> Hi,
>
> On 2014-04-20 11:24:38 +0200, Palle Girgensohn wrote:
>> I see performance degradation with PostgreSQL 9.3 vs 9.2 on FreeBSD, and I'm wondering who to poke to mitigate the problem. In reference to this thread [1], who where the FreeBSD people that Francois mentioned? If mmap needs to perform well in the kernel, I'd like to know of someone with FreeBSD kernel knowledge who is interested in working with mmap perfocmance. If mmap is indeed the cuplrit, I've just tested 9.2.8 vs 9.3.4, I nevere isolated the mmap patch, although I believe Francois did just that with similar results.
> If there are indeed such large regressions on FreeBSD we need to treat
> them as postgres regressions. It's nicer not to add config options for
> things that don't need it, but apparently that's not the case here.
>
> Imo this means we need to add GUC to control wether anon mmap() or sysv
> shmem is to be used. In 9.3.
>
> Greetings,
>
> Andres Freund
>
Andres, thank you.  Speaking as a FreeBSD developer that would be a good 
idea.

-Alfred

  9. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Tom Lane <tgl@sss.pgh.pa.us> — 2014-04-21T14:45:24Z

    Andres Freund <andres@2ndquadrant.com> writes:
> If there are indeed such large regressions on FreeBSD we need to treat
> them as postgres regressions. It's nicer not to add config options for
> things that don't need it, but apparently that's not the case here.

> Imo this means we need to add GUC to control wether anon mmap() or sysv
> shmem is to be used. In 9.3.

I will resist this mightily.  One of the main reasons to switch to mmap
was so we would no longer have to explain about SysV shm configuration.
Are we going to still have to explain that, but only for FreeBSD?
No thanks.  It will certainly not be the *first* resort.  Instead,
somebody needs to hold the FreeBSD folks' feet to the fire about when
we can expect to see a fix from their side.

			regards, tom lane

  10. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andres Freund <andres@2ndquadrant.com> — 2014-04-21T14:51:00Z

    On 2014-04-21 10:45:24 -0400, Tom Lane wrote:
> Andres Freund <andres@2ndquadrant.com> writes:
> > If there are indeed such large regressions on FreeBSD we need to treat
> > them as postgres regressions. It's nicer not to add config options for
> > things that don't need it, but apparently that's not the case here.
> 
> > Imo this means we need to add GUC to control wether anon mmap() or sysv
> > shmem is to be used. In 9.3.
> 
> I will resist this mightily.  One of the main reasons to switch to mmap
> was so we would no longer have to explain about SysV shm configuration.

It's still explained in the docs and one of the dynshm implementations
is based on sysv shmem. So I don't see this as a convincing reason.

Regressing installed OSs by 15-20% just to save a couple of lines of
docs and code seems rather unconvincing to me.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  11. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Magnus Hagander <magnus@hagander.net> — 2014-04-21T15:39:39Z

    On Mon, Apr 21, 2014 at 4:51 PM, Andres Freund <andres@2ndquadrant.com>wrote:

> On 2014-04-21 10:45:24 -0400, Tom Lane wrote:
> > Andres Freund <andres@2ndquadrant.com> writes:
> > > If there are indeed such large regressions on FreeBSD we need to treat
> > > them as postgres regressions. It's nicer not to add config options for
> > > things that don't need it, but apparently that's not the case here.
> >
> > > Imo this means we need to add GUC to control wether anon mmap() or sysv
> > > shmem is to be used. In 9.3.
> >
> > I will resist this mightily.  One of the main reasons to switch to mmap
> > was so we would no longer have to explain about SysV shm configuration.
>
> It's still explained in the docs and one of the dynshm implementations
> is based on sysv shmem. So I don't see this as a convincing reason.
>
> Regressing installed OSs by 15-20% just to save a couple of lines of
> docs and code seems rather unconvincing to me.
>
>
There's also the fact that even if it's changed in FreeBSD, that might be
somethign that takes years to trickle out to whatever stable release people
are actually using.

But do we really want a *guc* for it though? Isn't it enough (and in fact
better) with a configure switch to pick the implementation when multiple
are available, that could then be set by default for example by the freebsd
ports build? That's a lot less "overhead" to keep dragging around...


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

  12. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andres Freund <andres@2ndquadrant.com> — 2014-04-21T15:43:46Z

    Hi,

On 2014-04-21 17:39:39 +0200, Magnus Hagander wrote:
> But do we really want a *guc* for it though? Isn't it enough (and in fact
> better) with a configure switch to pick the implementation when multiple
> are available, that could then be set by default for example by the freebsd
> ports build? That's a lot less "overhead" to keep dragging around...

Well, we don't know at all it's just freebsd that's affected. In fact, I
would be surprised if there aren't other platforms that regressed due to
this.
I think a configure switch actually ends up being more code than the GUC...

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  13. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andrew Dunstan <andrew@dunslane.net> — 2014-04-21T15:45:49Z

    On 04/21/2014 11:39 AM, Magnus Hagander wrote:
> On Mon, Apr 21, 2014 at 4:51 PM, Andres Freund <andres@2ndquadrant.com 
> <mailto:andres@2ndquadrant.com>> wrote:
>
>     On 2014-04-21 10:45:24 -0400, Tom Lane wrote:
>     > Andres Freund <andres@2ndquadrant.com
>     <mailto:andres@2ndquadrant.com>> writes:
>     > > If there are indeed such large regressions on FreeBSD we need
>     to treat
>     > > them as postgres regressions. It's nicer not to add config
>     options for
>     > > things that don't need it, but apparently that's not the case
>     here.
>     >
>     > > Imo this means we need to add GUC to control wether anon
>     mmap() or sysv
>     > > shmem is to be used. In 9.3.
>     >
>     > I will resist this mightily.  One of the main reasons to switch
>     to mmap
>     > was so we would no longer have to explain about SysV shm
>     configuration.
>
>     It's still explained in the docs and one of the dynshm implementations
>     is based on sysv shmem. So I don't see this as a convincing reason.
>
>     Regressing installed OSs by 15-20% just to save a couple of lines of
>     docs and code seems rather unconvincing to me.
>
>
> There's also the fact that even if it's changed in FreeBSD, that might 
> be somethign that takes years to trickle out to whatever stable 
> release people are actually using.
>
> But do we really want a *guc* for it though? Isn't it enough (and in 
> fact better) with a configure switch to pick the implementation when 
> multiple are available, that could then be set by default for example 
> by the freebsd ports build? That's a lot less "overhead" to keep 
> dragging around...
>
>

That seems to make more sense. I can't imagine why this would be a 
runtime parameter as opposed to build time.

cheers

andrew

  14. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andres Freund <andres@2ndquadrant.com> — 2014-04-21T15:49:01Z

    Hi,

On 2014-04-21 11:45:49 -0400, Andrew Dunstan wrote:
> That seems to make more sense. I can't imagine why this would be a runtime
> parameter as opposed to build time.

Because that implies that packagers and porters need to make that
decision. If it's a GUC people can benchmark it and decide.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  15. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Tom Lane <tgl@sss.pgh.pa.us> — 2014-04-21T15:58:10Z

    Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-04-21 11:45:49 -0400, Andrew Dunstan wrote:
>> That seems to make more sense. I can't imagine why this would be a runtime
>> parameter as opposed to build time.

> Because that implies that packagers and porters need to make that
> decision. If it's a GUC people can benchmark it and decide.

As against that, the packager would be more likely to get it right
(or even to know that there's an issue).

			regards, tom lane

  16. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T15:59:42Z

    On 4/21/14 8:45 AM, Andrew Dunstan wrote:
>
> On 04/21/2014 11:39 AM, Magnus Hagander wrote:
>> On Mon, Apr 21, 2014 at 4:51 PM, Andres Freund 
>> <andres@2ndquadrant.com <mailto:andres@2ndquadrant.com>> wrote:
>>
>>     On 2014-04-21 10:45:24 -0400, Tom Lane wrote:
>>     > Andres Freund <andres@2ndquadrant.com
>>     <mailto:andres@2ndquadrant.com>> writes:
>>     > > If there are indeed such large regressions on FreeBSD we need
>>     to treat
>>     > > them as postgres regressions. It's nicer not to add config
>>     options for
>>     > > things that don't need it, but apparently that's not the case
>>     here.
>>     >
>>     > > Imo this means we need to add GUC to control wether anon
>>     mmap() or sysv
>>     > > shmem is to be used. In 9.3.
>>     >
>>     > I will resist this mightily.  One of the main reasons to switch
>>     to mmap
>>     > was so we would no longer have to explain about SysV shm
>>     configuration.
>>
>>     It's still explained in the docs and one of the dynshm 
>> implementations
>>     is based on sysv shmem. So I don't see this as a convincing reason.
>>
>>     Regressing installed OSs by 15-20% just to save a couple of lines of
>>     docs and code seems rather unconvincing to me.
>>
>>
>> There's also the fact that even if it's changed in FreeBSD, that 
>> might be somethign that takes years to trickle out to whatever stable 
>> release people are actually using.
>>
>> But do we really want a *guc* for it though? Isn't it enough (and in 
>> fact better) with a configure switch to pick the implementation when 
>> multiple are available, that could then be set by default for example 
>> by the freebsd ports build? That's a lot less "overhead" to keep 
>> dragging around...
>>
>>
>
> That seems to make more sense. I can't imagine why this would be a 
> runtime parameter as opposed to build time.

I am unsure of the true overhead of making this a runtime tunable so 
pardon if I'm asking for "a lot".  From the perspective of both an OS 
developer and postgresql user (I am both) it really makes more sense to 
have it a runtime tunable for the following reasons:

 From an OS developer making this a runtime allows us to much more 
easily do the testing (instead of needing two compiled versions).
 From a sysadmin perspective it makes switching to/from a LOT easier in 
case the new mmap code exposes a stability or performance bug.

-Alfred

  17. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T16:01:41Z

    On 4/21/14 8:58 AM, Tom Lane wrote:
> Andres Freund <andres@2ndquadrant.com> writes:
>> On 2014-04-21 11:45:49 -0400, Andrew Dunstan wrote:
>>> That seems to make more sense. I can't imagine why this would be a runtime
>>> parameter as opposed to build time.
>> Because that implies that packagers and porters need to make that
>> decision. If it's a GUC people can benchmark it and decide.
> As against that, the packager would be more likely to get it right
> (or even to know that there's an issue).

Can the package builder not set the default for the runtime tunable?

Honestly we're about to select a db platform for another FreeBSD based 
system we are building, I strongly hoping that we can get back to 
sysvshm easily otherwise we may have to select another store.

-Alfred (who still remembers back when Tom had a login on our primary db 
to help us. :) )

  18. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andres Freund <andres@2ndquadrant.com> — 2014-04-21T16:11:51Z

    On 2014-04-21 11:58:10 -0400, Tom Lane wrote:
> Andres Freund <andres@2ndquadrant.com> writes:
> > On 2014-04-21 11:45:49 -0400, Andrew Dunstan wrote:
> >> That seems to make more sense. I can't imagine why this would be a runtime
> >> parameter as opposed to build time.
> 
> > Because that implies that packagers and porters need to make that
> > decision. If it's a GUC people can benchmark it and decide.
> 
> As against that, the packager would be more likely to get it right
> (or even to know that there's an issue).

I sure hope that FreeBSD is going to fix this at some point (it's surely
affecting more than just postgres). But since we (and probably the
packagers) don't know which platforms it's going to affect the only
thing we could do would be to add a configure switch. To test people
would need to recompile postgres.
I don't understand what the problem with a GUC here is. It's a pretty
simple patch and that codepath is entered only once, so performance
surely can't be an argument.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  19. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Stephen Frost <sfrost@snowman.net> — 2014-04-21T16:13:38Z

    * Alfred Perlstein (alfred@freebsd.org) wrote:
> Can the package builder not set the default for the runtime tunable?

Yeah, I was thinking about that also, but at least in this case it seems
pretty clear that the 'right' answer is known at build time.

> Honestly we're about to select a db platform for another FreeBSD
> based system we are building, I strongly hoping that we can get back
> to sysvshm easily otherwise we may have to select another store.

Is there no hope of this getting fixed in FreeBSD..?  PG wouldn't be the
only application impacted by this, I'm sure...

	Thanks,

		Stephen

  20. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T16:20:28Z

    On 4/21/14 9:13 AM, Stephen Frost wrote:
> * Alfred Perlstein (alfred@freebsd.org) wrote:
>> Can the package builder not set the default for the runtime tunable?
> Yeah, I was thinking about that also, but at least in this case it seems
> pretty clear that the 'right' answer is known at build time.
>
>> Honestly we're about to select a db platform for another FreeBSD
>> based system we are building, I strongly hoping that we can get back
>> to sysvshm easily otherwise we may have to select another store.
> Is there no hope of this getting fixed in FreeBSD..?  PG wouldn't be the
> only application impacted by this, I'm sure...
There is definitely hope, however changes to the FreeBSD vm are taken as 
seriously as changes to core changes to Postresql's store. In addition 
changes to vm is somewhat in the realm of complexity of Postgresql store 
as well so it may not be coming in the next few days/weeks, but rather a 
month or two.  I am not sure if an easy fix is available in FreeBSD but 
we will see in short order.

I need to do some research.  I work with Adrian (FreeBSD kernel dev 
mentioned earlier in the thread), I'll grab him today and discuss what 
the issue may be.

-Alfred

  21. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andrew Dunstan <andrew@dunslane.net> — 2014-04-21T16:24:06Z

    On 04/21/2014 11:59 AM, Alfred Perlstein wrote:
> On 4/21/14 8:45 AM, Andrew Dunstan wrote:
>>
>> On 04/21/2014 11:39 AM, Magnus Hagander wrote:
>>> On Mon, Apr 21, 2014 at 4:51 PM, Andres Freund 
>>> <andres@2ndquadrant.com <mailto:andres@2ndquadrant.com>> wrote:
>>>
>>>     On 2014-04-21 10:45:24 -0400, Tom Lane wrote:
>>>     > Andres Freund <andres@2ndquadrant.com
>>>     <mailto:andres@2ndquadrant.com>> writes:
>>>     > > If there are indeed such large regressions on FreeBSD we need
>>>     to treat
>>>     > > them as postgres regressions. It's nicer not to add config
>>>     options for
>>>     > > things that don't need it, but apparently that's not the case
>>>     here.
>>>     >
>>>     > > Imo this means we need to add GUC to control wether anon
>>>     mmap() or sysv
>>>     > > shmem is to be used. In 9.3.
>>>     >
>>>     > I will resist this mightily.  One of the main reasons to switch
>>>     to mmap
>>>     > was so we would no longer have to explain about SysV shm
>>>     configuration.
>>>
>>>     It's still explained in the docs and one of the dynshm 
>>> implementations
>>>     is based on sysv shmem. So I don't see this as a convincing reason.
>>>
>>>     Regressing installed OSs by 15-20% just to save a couple of 
>>> lines of
>>>     docs and code seems rather unconvincing to me.
>>>
>>>
>>> There's also the fact that even if it's changed in FreeBSD, that 
>>> might be somethign that takes years to trickle out to whatever 
>>> stable release people are actually using.
>>>
>>> But do we really want a *guc* for it though? Isn't it enough (and in 
>>> fact better) with a configure switch to pick the implementation when 
>>> multiple are available, that could then be set by default for 
>>> example by the freebsd ports build? That's a lot less "overhead" to 
>>> keep dragging around...
>>>
>>>
>>
>> That seems to make more sense. I can't imagine why this would be a 
>> runtime parameter as opposed to build time.
>
> I am unsure of the true overhead of making this a runtime tunable so 
> pardon if I'm asking for "a lot".  From the perspective of both an OS 
> developer and postgresql user (I am both) it really makes more sense 
> to have it a runtime tunable for the following reasons:
>
> From an OS developer making this a runtime allows us to much more 
> easily do the testing (instead of needing two compiled versions).
> From a sysadmin perspective it makes switching to/from a LOT easier in 
> case the new mmap code exposes a stability or performance bug.
>
>

1. OS developers are not the target audience for GUCs. If the OS 
developers want to test and can't be botherrd with building with a 
couple of different parameters then I'm not very impressed.

2. We should be trying to get rid of GUCs where possible, and only add 
them when we must. The more there are the more we confuse users. If a 
packager can pick a default surely they can pick build options too.

cheers

andrew

  22. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Francois Tigeot <ftigeot@wolfpond.org> — 2014-04-21T16:24:43Z

    On Mon, Apr 21, 2014 at 05:43:46PM +0200, Andres Freund wrote:
> 
> On 2014-04-21 17:39:39 +0200, Magnus Hagander wrote:
> > But do we really want a *guc* for it though? Isn't it enough (and in fact
> > better) with a configure switch to pick the implementation when multiple
> > are available, that could then be set by default for example by the freebsd
> > ports build? That's a lot less "overhead" to keep dragging around...
> 
> Well, we don't know at all it's just freebsd that's affected. In fact, I
> would be surprised if there aren't other platforms that regressed due to
> this.

The only platforms affected are the BSDs.

I ran (or tried to run) Pgbench on the four major ones during the last two
years. My experience so far:

- FreeBSD: has trouble scaling; there's some interest to improve things but
  nobody has done anything about it yet

- NetBSD: crashes under load; this could have been fixed but when I ran the
  benchmarks in 2012 none of the developers seemed to care.

- OpenBSD: couldn't run the benchmark; for some reason this operating system
  is unable to mmap() 32GB of memory on a recent PC with 128GB of RAM.

- DragonFly: scales better than everything else out there even with mmap()

Given these results I doubt reintroducing SysV shm memory use on PostgreSQL
is worthwile; most platforms where it has a performance impact have much
bigger issues to fix first.

-- 
Francois Tigeot

  23. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T16:25:50Z

    On 4/21/14 9:24 AM, Andrew Dunstan wrote:
>
> On 04/21/2014 11:59 AM, Alfred Perlstein wrote:
>> On 4/21/14 8:45 AM, Andrew Dunstan wrote:
>>>
>>> On 04/21/2014 11:39 AM, Magnus Hagander wrote:
>>>> On Mon, Apr 21, 2014 at 4:51 PM, Andres Freund 
>>>> <andres@2ndquadrant.com <mailto:andres@2ndquadrant.com>> wrote:
>>>>
>>>>     On 2014-04-21 10:45:24 -0400, Tom Lane wrote:
>>>>     > Andres Freund <andres@2ndquadrant.com
>>>>     <mailto:andres@2ndquadrant.com>> writes:
>>>>     > > If there are indeed such large regressions on FreeBSD we need
>>>>     to treat
>>>>     > > them as postgres regressions. It's nicer not to add config
>>>>     options for
>>>>     > > things that don't need it, but apparently that's not the case
>>>>     here.
>>>>     >
>>>>     > > Imo this means we need to add GUC to control wether anon
>>>>     mmap() or sysv
>>>>     > > shmem is to be used. In 9.3.
>>>>     >
>>>>     > I will resist this mightily.  One of the main reasons to switch
>>>>     to mmap
>>>>     > was so we would no longer have to explain about SysV shm
>>>>     configuration.
>>>>
>>>>     It's still explained in the docs and one of the dynshm 
>>>> implementations
>>>>     is based on sysv shmem. So I don't see this as a convincing 
>>>> reason.
>>>>
>>>>     Regressing installed OSs by 15-20% just to save a couple of 
>>>> lines of
>>>>     docs and code seems rather unconvincing to me.
>>>>
>>>>
>>>> There's also the fact that even if it's changed in FreeBSD, that 
>>>> might be somethign that takes years to trickle out to whatever 
>>>> stable release people are actually using.
>>>>
>>>> But do we really want a *guc* for it though? Isn't it enough (and 
>>>> in fact better) with a configure switch to pick the implementation 
>>>> when multiple are available, that could then be set by default for 
>>>> example by the freebsd ports build? That's a lot less "overhead" to 
>>>> keep dragging around...
>>>>
>>>>
>>>
>>> That seems to make more sense. I can't imagine why this would be a 
>>> runtime parameter as opposed to build time.
>>
>> I am unsure of the true overhead of making this a runtime tunable so 
>> pardon if I'm asking for "a lot".  From the perspective of both an OS 
>> developer and postgresql user (I am both) it really makes more sense 
>> to have it a runtime tunable for the following reasons:
>>
>> From an OS developer making this a runtime allows us to much more 
>> easily do the testing (instead of needing two compiled versions).
>> From a sysadmin perspective it makes switching to/from a LOT easier 
>> in case the new mmap code exposes a stability or performance bug.
>>
>>
>
> 1. OS developers are not the target audience for GUCs. If the OS 
> developers want to test and can't be botherrd with building with a 
> couple of different parameters then I'm not very impressed.
>
> 2. We should be trying to get rid of GUCs where possible, and only add 
> them when we must. The more there are the more we confuse users. If a 
> packager can pick a default surely they can pick build options too.
Thank you for the lecture Andrew!  Really pleasant way to treat a user 
and a fan of the system. :)



-Alfred

  24. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Stephen Frost <sfrost@snowman.net> — 2014-04-21T16:34:07Z

    * Alfred Perlstein (alfred@freebsd.org) wrote:
> There is definitely hope, however changes to the FreeBSD vm are
> taken as seriously as changes to core changes to Postresql's store.
> In addition changes to vm is somewhat in the realm of complexity of
> Postgresql store as well so it may not be coming in the next few
> days/weeks, but rather a month or two.  I am not sure if an easy fix
> is available in FreeBSD but we will see in short order.

This has been known for over a year.. :(

> I need to do some research.  I work with Adrian (FreeBSD kernel dev
> mentioned earlier in the thread), I'll grab him today and discuss
> what the issue may be.

Hopefully that'll get things moving in the right direction, finally..

	Thanks,

		Stephen

  25. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andrew Dunstan <andrew@dunslane.net> — 2014-04-21T16:38:59Z

    On 04/21/2014 12:25 PM, Alfred Perlstein wrote:
>
>>>
>>
>> 1. OS developers are not the target audience for GUCs. If the OS 
>> developers want to test and can't be botherrd with building with a 
>> couple of different parameters then I'm not very impressed.
>>
>> 2. We should be trying to get rid of GUCs where possible, and only 
>> add them when we must. The more there are the more we confuse users. 
>> If a packager can pick a default surely they can pick build options too.
> Thank you for the lecture Andrew!  Really pleasant way to treat a user 
> and a fan of the system. :)
>
>

I confess to being mightily confused.

cheers

andrew

  26. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T16:42:06Z

    On 4/21/14 9:34 AM, Stephen Frost wrote:
> * Alfred Perlstein (alfred@freebsd.org) wrote:
>> There is definitely hope, however changes to the FreeBSD vm are
>> taken as seriously as changes to core changes to Postresql's store.
>> In addition changes to vm is somewhat in the realm of complexity of
>> Postgresql store as well so it may not be coming in the next few
>> days/weeks, but rather a month or two.  I am not sure if an easy fix
>> is available in FreeBSD but we will see in short order.
> This has been known for over a year.. :(
I know!  I remember warning y'all about it back at pgcon last year. :)
>
>> I need to do some research.  I work with Adrian (FreeBSD kernel dev
>> mentioned earlier in the thread), I'll grab him today and discuss
>> what the issue may be.
> Hopefully that'll get things moving in the right direction, finally..
Sure, to be fair, we are under the gun here for a product, it may just 
mean that the end result of that conversation is "mysql".

I'm hoping we can use Postgresql as I've been a huge fan since 1999.  I 
based my first successful project on it and had a LOT of help from the 
pgsql community, Tom, Bruce and we even contracted Vadim for some work 
on incremental vacuums!

-Alfred

  27. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T16:44:09Z

    On 4/21/14 9:38 AM, Andrew Dunstan wrote:
>
> On 04/21/2014 12:25 PM, Alfred Perlstein wrote:
>>
>>>>
>>>
>>> 1. OS developers are not the target audience for GUCs. If the OS 
>>> developers want to test and can't be botherrd with building with a 
>>> couple of different parameters then I'm not very impressed.
>>>
>>> 2. We should be trying to get rid of GUCs where possible, and only 
>>> add them when we must. The more there are the more we confuse users. 
>>> If a packager can pick a default surely they can pick build options 
>>> too.
>> Thank you for the lecture Andrew!  Really pleasant way to treat a 
>> user and a fan of the system. :)
>>
>>
>
> I confess to being mightily confused.

Sure, to clarify:

Andrew, you just told someone who in a db stack sits both below (as a 
pgsql user 15 years) and above (as a FreeBSD kernel dev 15 years) your 
software what they "really need".

-Alfred

  28. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andrew Dunstan <andrew@dunslane.net> — 2014-04-21T16:51:32Z

    On 04/21/2014 12:44 PM, Alfred Perlstein wrote:
> On 4/21/14 9:38 AM, Andrew Dunstan wrote:
>>
>> On 04/21/2014 12:25 PM, Alfred Perlstein wrote:
>>>
>>>>>
>>>>
>>>> 1. OS developers are not the target audience for GUCs. If the OS 
>>>> developers want to test and can't be botherrd with building with a 
>>>> couple of different parameters then I'm not very impressed.
>>>>
>>>> 2. We should be trying to get rid of GUCs where possible, and only 
>>>> add them when we must. The more there are the more we confuse 
>>>> users. If a packager can pick a default surely they can pick build 
>>>> options too.
>>> Thank you for the lecture Andrew!  Really pleasant way to treat a 
>>> user and a fan of the system. :)
>>>
>>>
>>
>> I confess to being mightily confused.
>
> Sure, to clarify:
>
> Andrew, you just told someone who in a db stack sits both below (as a 
> pgsql user 15 years) and above (as a FreeBSD kernel dev 15 years) your 
> software what they "really need".
>
>


I told you what *we* (i.e. the PostgreSQL community) need, IMNSHO (and 
speaking as a Postgres developer and consultant of 10 or so years standing).

cheers

andrew

  29. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andres Freund <andres@2ndquadrant.com> — 2014-04-21T16:51:50Z

    On 2014-04-21 09:42:06 -0700, Alfred Perlstein wrote:
> Sure, to be fair, we are under the gun here for a product, it may just mean
> that the end result of that conversation is "mysql".

Personally arguments in that vain are removing just about any incentive
I have to work on the problem.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  30. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2014-04-21T16:52:35Z

    Alfred Perlstein wrote:

> I am unsure of the true overhead of making this a runtime tunable so
> pardon if I'm asking for "a lot".  From the perspective of both an
> OS developer and postgresql user (I am both) it really makes more
> sense to have it a runtime tunable for the following reasons:
> 
> From an OS developer making this a runtime allows us to much more
> easily do the testing (instead of needing two compiled versions).
> From a sysadmin perspective it makes switching to/from a LOT easier
> in case the new mmap code exposes a stability or performance bug.

In this case, AFAICS the only overhead of a runtime option (what we call
a GUC) is the added potential for user confusion, and the necessary
documentation.  If we instead go for a compile-time option, both items
become smaller.

In any case, I don't see that there's much need for a runtime option,
really; you already know that the mmap code path is slower in FreeBSD.
You only need to benchmark both options once the FreeBSD vm code itself
is fixed, right?

In fact, it might not even need to be a configure option; I would
suggest a pg_config_manual.h setting instead, and perhaps tweaks to the
src/template/freebsd file to enable it automatically on the "broken"
FreeBSD releases.  We could then, in the future, have the template
itself turn the option off for the future FreeBSD release that fixes the
problem.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

  31. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T17:33:27Z

    On 4/21/14, 9:51 AM, Andrew Dunstan wrote:
>
> On 04/21/2014 12:44 PM, Alfred Perlstein wrote:
>> On 4/21/14 9:38 AM, Andrew Dunstan wrote:
>>>
>>> On 04/21/2014 12:25 PM, Alfred Perlstein wrote:
>>>>
>>>>>>
>>>>>
>>>>> 1. OS developers are not the target audience for GUCs. If the OS 
>>>>> developers want to test and can't be botherrd with building with a 
>>>>> couple of different parameters then I'm not very impressed.
>>>>>
>>>>> 2. We should be trying to get rid of GUCs where possible, and only 
>>>>> add them when we must. The more there are the more we confuse 
>>>>> users. If a packager can pick a default surely they can pick build 
>>>>> options too.
>>>> Thank you for the lecture Andrew!  Really pleasant way to treat a 
>>>> user and a fan of the system. :)
>>>>
>>>>
>>>
>>> I confess to being mightily confused.
>>
>> Sure, to clarify:
>>
>> Andrew, you just told someone who in a db stack sits both below (as a 
>> pgsql user 15 years) and above (as a FreeBSD kernel dev 15 years) 
>> your software what they "really need".
>>
>>
>
>
> I told you what *we* (i.e. the PostgreSQL community) need, IMNSHO (and 
> speaking as a Postgres developer and consultant of 10 or so years 
> standing).

How high on the hierarchy of PostgreSQL's "needs" is making a single 
option a tunable versus compile time thing?  I mean seriously you mean 
to stick on this one point when one of your users are asking you about 
this?   That is pretty concerning to me.

-Alfred

  32. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2014-04-21T17:37:09Z

    Alfred Perlstein wrote:

> How high on the hierarchy of PostgreSQL's "needs" is making a single
> option a tunable versus compile time thing?  I mean seriously you
> mean to stick on this one point when one of your users are asking
> you about this?   That is pretty concerning to me.

I think the sticking point here is that the problem affects a single
platform, and it can easily be construed as a platform bug.  For
problems that affect PostgreSQL as a whole for everybody, we hesitate a
lot less when it comes to creating new runtime options.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

  33. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T17:39:12Z

    On 4/21/14, 9:51 AM, Andres Freund wrote:
> On 2014-04-21 09:42:06 -0700, Alfred Perlstein wrote:
>> Sure, to be fair, we are under the gun here for a product, it may just mean
>> that the end result of that conversation is "mysql".
> Personally arguments in that vain are removing just about any incentive
> I have to work on the problem.

I was just explaining that we have a timeline over here and while that 
may disincentive you for providing what we need it would be very unfair.

In that I mean sometimes the reality of a situation can be inconvenient 
and for that I do apologize.

What I am seeing here is unfortunately a very strong departure from 
FreeBSD support by the community from several of the developers.  In 
fact over drinks at pgcon last year there were a TON of jokes making fun 
of FreeBSD users and developers which I took in stride as professional 
joking with alcohol involved.  I thought it was pretty funny.  However a 
year later and I realize that there appears to be a real problem with 
FreeBSD in the pgsql community.

There are other Linux centric dbs to pick from.  If pgsql is just 
another Linux centric DB then that is unfortunate but something I can 
deal with.

-Alfred

  34. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T17:41:25Z

    On 4/21/14, 9:52 AM, Alvaro Herrera wrote:
> Alfred Perlstein wrote:
>
>> I am unsure of the true overhead of making this a runtime tunable so
>> pardon if I'm asking for "a lot".  From the perspective of both an
>> OS developer and postgresql user (I am both) it really makes more
>> sense to have it a runtime tunable for the following reasons:
>>
>>  From an OS developer making this a runtime allows us to much more
>> easily do the testing (instead of needing two compiled versions).
>>  From a sysadmin perspective it makes switching to/from a LOT easier
>> in case the new mmap code exposes a stability or performance bug.
> In this case, AFAICS the only overhead of a runtime option (what we call
> a GUC) is the added potential for user confusion, and the necessary
> documentation.  If we instead go for a compile-time option, both items
> become smaller.
>
> In any case, I don't see that there's much need for a runtime option,
> really; you already know that the mmap code path is slower in FreeBSD.
> You only need to benchmark both options once the FreeBSD vm code itself
> is fixed, right?
>
> In fact, it might not even need to be a configure option; I would
> suggest a pg_config_manual.h setting instead, and perhaps tweaks to the
> src/template/freebsd file to enable it automatically on the "broken"
> FreeBSD releases.  We could then, in the future, have the template
> itself turn the option off for the future FreeBSD release that fixes the
> problem.
>
That is correct, until you're in prod and suddenly one option becomes 
unstable, or you want to try a quick kernel patch without rebooting.

Look, this is an argument I've lost time and time again in open source 
software communities, the idea of a software option as opposed to 
compile time really seems to hit people the wrong way.

I think from now on it just makes sense to sit back and let whatever 
happens happen.

-Alfred

  35. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Stephen Frost <sfrost@snowman.net> — 2014-04-21T17:42:09Z

    * Alfred Perlstein (alfred@freebsd.org) wrote:
> How high on the hierarchy of PostgreSQL's "needs" is making a single
> option a tunable versus compile time thing?  I mean seriously you
> mean to stick on this one point when one of your users are asking
> you about this?   That is pretty concerning to me.

Seriously, we do care that the system is easy to use for both admins and
end users and part of how we do that is by minimizing the number of
tunable options because they add to confusion and increase the
difficulty to manage the system- look at certain other $expensive
RDBMS's if you're unsure about that.

Far better is to work out the *correct* solution to a given problem
rather than punt'ing on it and asking the (almost uniformly
under-informed user) to tell us what to do.

And, yes, while we're interested in our user's input, we do not add new
configuration variables because one user asked us to.

	Thanks,

		Stephen

  36. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Stephen Frost <sfrost@snowman.net> — 2014-04-21T18:14:45Z

    Alfred,

* Alfred Perlstein (alfred@freebsd.org) wrote:
> On 4/21/14, 9:51 AM, Andres Freund wrote:
> >On 2014-04-21 09:42:06 -0700, Alfred Perlstein wrote:
> >>Sure, to be fair, we are under the gun here for a product, it may just mean
> >>that the end result of that conversation is "mysql".
> >Personally arguments in that vain are removing just about any incentive
> >I have to work on the problem.
> 
> I was just explaining that we have a timeline over here and while
> that may disincentive you for providing what we need it would be
> very unfair.

I'm pretty sure Andres was referring to the part where there's a
'threat' to move to some other platform due to a modest performance
degredation, as if it's the only factor involved in making a decision
among the various RDBMS options.  If that's really your deciding
criteria instead of the myriad of other factors, I daresay you have your
priorities mixed up.

> There are other Linux centric dbs to pick from.  If pgsql is just
> another Linux centric DB then that is unfortunate but something I
> can deal with.

These attacks really aren't going to get you anywhere.  We're talking
about a specific performance issue that FreeBSD has and how much PG
(surely not the only application impacted by this issue) should bend
to address it, even though the FreeBSD folks were made aware of the
issue over year ago and have done nothing to address it.

Moreover, you'd like to also define the way we deal with the issue as
being to make it runtime configurable rather than as a compile-time
option, even though 90% of the users out there won't understand the
difference nor would know how to correctly set it (and, in many cases,
may end up making the wrong decision because it's the default for
other platforms, unless we add more code to address this at initdb
time).

Basically, it doesn't sound like you're terribly concerned with the
majority of our user base, even on FreeBSD, and would prefer to try
and browbeat us into doing what you've decided is the correct solution
because it'd work better for you.

I've been guiltly of the same in the past and it's not fun having to
back off from a proposal when it's pointed out that there's a better
option, particularly when it doesn't seem like the alternative is
better for me, but that's just part of working in any large project.

	Thanks,

		Stephen

  37. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T19:27:44Z

    On 4/21/14, 11:14 AM, Stephen Frost wrote:
> Alfred,
>
> * Alfred Perlstein (alfred@freebsd.org) wrote:
>> On 4/21/14, 9:51 AM, Andres Freund wrote:
>>> On 2014-04-21 09:42:06 -0700, Alfred Perlstein wrote:
>>>> Sure, to be fair, we are under the gun here for a product, it may just mean
>>>> that the end result of that conversation is "mysql".
>>> Personally arguments in that vain are removing just about any incentive
>>> I have to work on the problem.
>> I was just explaining that we have a timeline over here and while
>> that may disincentive you for providing what we need it would be
>> very unfair.
> I'm pretty sure Andres was referring to the part where there's a
> 'threat' to move to some other platform due to a modest performance
> degredation, as if it's the only factor involved in making a decision
> among the various RDBMS options.  If that's really your deciding
> criteria instead of the myriad of other factors, I daresay you have your
> priorities mixed up.
>
>> There are other Linux centric dbs to pick from.  If pgsql is just
>> another Linux centric DB then that is unfortunate but something I
>> can deal with.
> These attacks really aren't going to get you anywhere.  We're talking
> about a specific performance issue that FreeBSD has and how much PG
> (surely not the only application impacted by this issue) should bend
> to address it, even though the FreeBSD folks were made aware of the
> issue over year ago and have done nothing to address it.
>
> Moreover, you'd like to also define the way we deal with the issue as
> being to make it runtime configurable rather than as a compile-time
> option, even though 90% of the users out there won't understand the
> difference nor would know how to correctly set it (and, in many cases,
> may end up making the wrong decision because it's the default for
> other platforms, unless we add more code to address this at initdb
> time).
>
> Basically, it doesn't sound like you're terribly concerned with the
> majority of our user base, even on FreeBSD, and would prefer to try
> and browbeat us into doing what you've decided is the correct solution
> because it'd work better for you.
>
> I've been guiltly of the same in the past and it's not fun having to
> back off from a proposal when it's pointed out that there's a better
> option, particularly when it doesn't seem like the alternative is
> better for me, but that's just part of working in any large project.
>
Stephen, please calm down on the hyperbole, seriously, picking another 
db is not an attack.

I was simply asking for a feature that would make my life easier as both 
an admin deploying postgresql and a kernel dev attempting to fix a 
problem.  I'm one guy, probably the only guy right now asking.  Honestly 
the thought of needing to compile two versions of postgresql to do sysv 
vs mmap performance would take me more time than I would like to devote 
to the issue when my time is already limited.

Again, it was an ask, you are free to do what you like, the same way you 
were free to ignore my advice at pgcon about mmap being less efficient.  
It does not make what I'm saying an "attack".  Just like when 
interviewing people choosing a different candidate for a job is not an 
attack on the other candidates!

-Alfred

  38. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Stephen Frost <sfrost@snowman.net> — 2014-04-21T19:47:31Z

    Alfred,

* Alfred Perlstein (alfred@freebsd.org) wrote:
> Stephen, please calm down on the hyperbole, seriously, picking
> another db is not an attack.

Perhaps it wasn't intended by you, but to those of us reading it, your
comments came across clearly as "if you don't fix this, then we
won't use PG".  Email certainly isn't perfect but I hope you can
understand our confusion- why else bring up MySQL or any other database
if not to try and push us to do what you want?  It's hard for me to see
how bringing up other databases or making comments about us being
"Linux-only" are anything other than attempts to persude by threating
that we might lose a user or users.

> I was simply asking for a feature that would make my life easier as
> both an admin deploying postgresql and a kernel dev attempting to
> fix a problem.  I'm one guy, probably the only guy right now asking.
> Honestly the thought of needing to compile two versions of
> postgresql to do sysv vs mmap performance would take me more time
> than I would like to devote to the issue when my time is already
> limited.

That's certainly unfortunate.  For my 2c, I'd recommend that you write a
minimal implementation that allows you to test just the sysv-vs-mmap
case (which could certainly take an option, to avoid having to
recompile during testing), or even ask if anyone here already has; I
wouldn't be at all surprised if both Robert and Francois did exactly
that already, nor would I be surprised if someone volunteered to write
such a small C utility for you, if it meant that this issue would be
fixed in FreeBSD that much sooner.

Asking for help to address the FreeBSD performance would have been much
better received.

	Thanks,

		Stephen

  39. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andres Freund <andres@2ndquadrant.com> — 2014-04-21T19:50:59Z

    On 2014-04-21 15:47:31 -0400, Stephen Frost wrote:
> That's certainly unfortunate.  For my 2c, I'd recommend that you write a
> minimal implementation that allows you to test just the sysv-vs-mmap
> case (which could certainly take an option, to avoid having to
> recompile during testing), or even ask if anyone here already has;

I don't think that's something all that easily testable in
isolation. The behaviour here is heavily related to concurrency.

> I
> wouldn't be at all surprised if both Robert and Francois did exactly
> that already, nor would I be surprised if someone volunteered to write
> such a small C utility for you, if it meant that this issue would be
> fixed in FreeBSD that much sooner.

I don't know, but the patch for a guc would be < 10 lines. I think I'd
start with that.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  40. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Stephen Frost <sfrost@snowman.net> — 2014-04-21T19:56:15Z

    * Andres Freund (andres@2ndquadrant.com) wrote:
> On 2014-04-21 15:47:31 -0400, Stephen Frost wrote:
> > That's certainly unfortunate.  For my 2c, I'd recommend that you write a
> > minimal implementation that allows you to test just the sysv-vs-mmap
> > case (which could certainly take an option, to avoid having to
> > recompile during testing), or even ask if anyone here already has;
> 
> I don't think that's something all that easily testable in
> isolation. The behaviour here is heavily related to concurrency.

Concurrency is not terribly hard to generate in a simulated case; I
still feel that doing this independently of PG would probably be better
than involving all the rest of the PG code in testing something this
low-level.

> > I
> > wouldn't be at all surprised if both Robert and Francois did exactly
> > that already, nor would I be surprised if someone volunteered to write
> > such a small C utility for you, if it meant that this issue would be
> > fixed in FreeBSD that much sooner.
> 
> I don't know, but the patch for a guc would be < 10 lines. I think I'd
> start with that.

Certainly running a minimally patched PG wouldn't be hard for a kernel
dev either, of course.

	Thanks,

		Stephen

  41. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T20:52:48Z

    On 4/21/14, 12:47 PM, Stephen Frost wrote:
>   Asking for help to address the FreeBSD performance would have been 
> much better received. Thanks, Stephen 

That is exactly what I did, I asked for a version of postgresql that was 
easy to switch at runtime between two behaviors.

That would make it a LOT easier to run a few scripts and make sure I got 
the correct binary without having to munge PREFIX and a bunch of PATH 
and other tools to get my test harness to DTRT.

-Alfred

  42. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Bruce Momjian <bruce@momjian.us> — 2014-04-21T20:59:48Z

    On Mon, Apr 21, 2014 at 01:52:48PM -0700, Alfred Perlstein wrote:
> 
> On 4/21/14, 12:47 PM, Stephen Frost wrote:
> >  Asking for help to address the FreeBSD performance would have
> >been much better received. Thanks, Stephen
> 
> That is exactly what I did, I asked for a version of postgresql that
> was easy to switch at runtime between two behaviors.
> 
> That would make it a LOT easier to run a few scripts and make sure I
> got the correct binary without having to munge PREFIX and a bunch of
> PATH and other tools to get my test harness to DTRT.

I think the big point is that you must realize that we are dealing with
thousands of users, so making a suggestion without considering its
impact on those thousands of people is not helpful.

We have clearly stated the need to consider those thousands of users,
and you are still saying the same thing --- this would make it easy for
"me".  This is not helpful to the discussion, and you must realize that
at some level.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

  43. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Joshua D. Drake <jd@commandprompt.com> — 2014-04-21T21:08:51Z

    On 04/21/2014 10:39 AM, Alfred Perlstein wrote:

> What I am seeing here is unfortunately a very strong departure from
> FreeBSD support by the community from several of the developers.  In
> fact over drinks at pgcon last year there were a TON of jokes making fun
> of FreeBSD users and developers which I took in stride as professional
> joking with alcohol involved.  I thought it was pretty funny.  However a
> year later and I realize that there appears to be a real problem with
> FreeBSD in the pgsql community.

The reality is, FreeBSD is like Saab (before it died, and no I am not 
suggesting that FreeBSD is dying). Saab was a niche, very cool 
automobile that offered a lot of unique features that others didn't. 
However, they didn't sell very well in the states but had a very devoted 
fan base (myself included).

FreeBSD is awesome. There is no question about that. It certainly has a 
better license than Linux and has offered things for years that Linux 
has never really gotten right (jails/zones).

That said, FreeBSD is niche and Linux is, not. Linux is the king of the 
jungle in this world. Whether we want it to be or not and what that 
means is: that is where the resources go.

If the community had more *BSD presence I think it would be great but it 
isn't all that viable at this point. I do know however that no-one in 
this community would turn down a team of FreeBSD advocates helping us 
make PostgreSQL awesome for PostgreSQL.

>
> There are other Linux centric dbs to pick from.  If pgsql is just

No, there is about 1 and its derivatives thereof. If you want the type 
of features pgsql offers, then pgsql is all you have got.

Sincerely,

JD



-- 
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.

  44. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Bruce Momjian <bruce@momjian.us> — 2014-04-21T21:21:20Z

    On Mon, Apr 21, 2014 at 02:08:51PM -0700, Joshua Drake wrote:
> If the community had more *BSD presence I think it would be great
> but it isn't all that viable at this point. I do know however that
> no-one in this community would turn down a team of FreeBSD advocates
> helping us make PostgreSQL awesome for PostgreSQL.

I don't think we would even implement a run-time control for Linux or
Windows for this, so I don't even think it is a FreeBSD issue.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

  45. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Stephen Frost <sfrost@snowman.net> — 2014-04-21T21:23:15Z

    Alfred,

* Alfred Perlstein (alfred@freebsd.org) wrote:
> On 4/21/14, 12:47 PM, Stephen Frost wrote:
> >  Asking for help to address the FreeBSD performance would have
> >been much better received. Thanks, Stephen
> 
> That is exactly what I did, I asked for a version of postgresql that
> was easy to switch at runtime between two behaviors.
> 
> That would make it a LOT easier to run a few scripts and make sure I
> got the correct binary without having to munge PREFIX and a bunch of
> PATH and other tools to get my test harness to DTRT.

I'm sure one of the hackers would be happy to provide you with a patch
to help you with your testing.

That's quite a different thing from asking for a GUC to be provided and
then supported over the next 5 years as part of the core release, which
is what I believe we all thought you were asking for.

	Thanks,

		Stephen

  46. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-21T21:25:31Z

    On 4/21/14, 2:23 PM, Stephen Frost wrote:
> Alfred,
>
> * Alfred Perlstein (alfred@freebsd.org) wrote:
>> On 4/21/14, 12:47 PM, Stephen Frost wrote:
>>>   Asking for help to address the FreeBSD performance would have
>>> been much better received. Thanks, Stephen
>> That is exactly what I did, I asked for a version of postgresql that
>> was easy to switch at runtime between two behaviors.
>>
>> That would make it a LOT easier to run a few scripts and make sure I
>> got the correct binary without having to munge PREFIX and a bunch of
>> PATH and other tools to get my test harness to DTRT.
> I'm sure one of the hackers would be happy to provide you with a patch
> to help you with your testing.
That would be fine.
> That's quite a different thing from asking for a GUC to be provided and
> then supported over the next 5 years as part of the core release, which
> is what I believe we all thought you were asking for.
I did not know that GUCs were not classified into 
"experimental/non-experimental".  The fact that a single GUC would need 
to be supported for 5 years is definitely something to consider.  Now I 
understand the push back a little more.

-Alfred

  47. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andres Freund <andres@2ndquadrant.com> — 2014-04-21T21:25:35Z

    On 2014-04-21 17:21:20 -0400, Bruce Momjian wrote:
> On Mon, Apr 21, 2014 at 02:08:51PM -0700, Joshua Drake wrote:
> > If the community had more *BSD presence I think it would be great
> > but it isn't all that viable at this point. I do know however that
> > no-one in this community would turn down a team of FreeBSD advocates
> > helping us make PostgreSQL awesome for PostgreSQL.
> 
> I don't think we would even implement a run-time control for Linux or
> Windows for this, so I don't even think it is a FreeBSD issue.

I think some of the arguments in this thread are pretty damn absurd. We
have just introduced dynamic_shared_memory_type.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  48. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Jim Nasby <jim@nasby.net> — 2014-04-21T22:08:45Z

    On 4/21/14, 4:08 PM, Joshua D. Drake wrote:
> If the community had more *BSD presence I think it would be great but it isn't all that viable at this point. I do know however that no-one in this community would turn down a team of FreeBSD advocates helping us make PostgreSQL awesome for PostgreSQL.

I assume you meant FreeBSD awesome for PostgreSQL? :)

I'm also a big fan of *BSD but the reality is it's MUCH harder to get *BSD into a corporation than linux. Now, if FreeBSD had a bunch of stuff that made PostgreSQL run like 4x faster on *BSD than Linux that would be a different story.
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

  49. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    David G. Johnston <david.g.johnston@gmail.com> — 2014-04-21T23:54:55Z

    Stephen Frost wrote
> * Alfred Perlstein (

> alfred@

> ) wrote:
>> On 4/21/14, 12:47 PM, Stephen Frost wrote:
>> >  Asking for help to address the FreeBSD performance would have
>> >been much better received. Thanks, Stephen
>> 
>> That is exactly what I did, I asked for a version of postgresql that
>> was easy to switch at runtime between two behaviors.
>> 
>> That would make it a LOT easier to run a few scripts and make sure I
>> got the correct binary without having to munge PREFIX and a bunch of
>> PATH and other tools to get my test harness to DTRT.
> 
> I'm sure one of the hackers would be happy to provide you with a patch
> to help you with your testing.
> 
> That's quite a different thing from asking for a GUC to be provided and
> then supported over the next 5 years as part of the core release, which
> is what I believe we all thought you were asking for.

Alfred,

Are you willing and use a custom 9.3 installed from source or are you asking
for something to actually be released to the wild before you go and test it
- your comments are unclear on this point?

The technical consensus is that the more desirable approach is to have the
determination done at compile-time since - besides testing - no obvious
reason exists that a user, once they have determined the correct option for
their platform, would find reason to change it.  Yes, it adds another player
to the game (unless you install from source), but the community is already
structured to rely upon packagers to do the right thing for their platform
so that the amount of customization presented to the user can be minimized.

In short, the goal is to have GUCs limited to work-mix, not platform,
configuration; and definitely not for platform testing purposes.  If you are
going to be testing platform performance it seems to be expected that you
have the ability to compile and alter source code.  This may indeed limit
the potential number of testers but it does add efficiency to the process
because the testers can make patches.

David J.






--
View this message in context: http://postgresql.1045698.n5.nabble.com/Perfomance-degradation-9-3-vs-9-2-for-FreeBSD-tp5800835p5800993.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

  50. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Tatsuo Ishii <ishii@postgresql.org> — 2014-04-22T00:34:58Z

    > I see performance degradation with PostgreSQL 9.3 vs 9.2 on FreeBSD, and I'm wondering who to poke to mitigate the problem. In reference to this thread [1], who where the FreeBSD people that Francois mentioned? If mmap needs to perform well in the kernel, I'd like to know of someone with FreeBSD kernel knowledge who is interested in working with mmap perfocmance. If mmap is indeed the cuplrit, I've just tested 9.2.8 vs 9.3.4, I nevere isolated the mmap patch, although I believe Francois did just that with similar results.

I observe performance degradation with PostgreSQL 9.3 vs 9.2 on Linux
as well.  The hardware is HP DL980G7, 80 cores, 2TB mem, RHEL 6,
pgbench is used (read only query), scale factor is 1,000 (DB size
15GB).

Included is the graph (from PostgreSQL Enterprise Consortium's 2014
report page 13: https://www.pgecons.org/downloads/43). I see up to 14%
degration (at 128 concurrent users) comparing with 9.2.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

  51. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Peter Geoghegan <pg@heroku.com> — 2014-04-22T00:36:19Z

    On Mon, Apr 21, 2014 at 4:10 AM, Andres Freund <andres@2ndquadrant.com> wrote:
> If there are indeed such large regressions on FreeBSD we need to treat
> them as postgres regressions. It's nicer not to add config options for
> things that don't need it, but apparently that's not the case here.

+1, but I think this is something for packagers to get right, not users.

I really don't like the idea of playing chicken with the FreeBSD
people, especially since we're going to use System V shared memory
into the foreseeable future anyway. It's probably *far* easier for us
to fix it than it is for the FreeBSD people to fix it.

-- 
Peter Geoghegan

  52. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Joshua D. Drake <jd@commandprompt.com> — 2014-04-22T00:44:21Z

    On 04/21/2014 03:08 PM, Jim Nasby wrote:
>
> On 4/21/14, 4:08 PM, Joshua D. Drake wrote:
>> If the community had more *BSD presence I think it would be great but
>> it isn't all that viable at this point. I do know however that no-one
>> in this community would turn down a team of FreeBSD advocates helping
>> us make PostgreSQL awesome for PostgreSQL.
>
> I assume you meant FreeBSD awesome for PostgreSQL? :)

Yes. Ty for the correction.

>
> I'm also a big fan of *BSD but the reality is it's MUCH harder to get
> *BSD into a corporation than linux. Now, if FreeBSD had a bunch of stuff
> that made PostgreSQL run like 4x faster on *BSD than Linux that would be
> a different story.

Exactly.

JD


-- 
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.

  53. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Stephen Frost <sfrost@snowman.net> — 2014-04-22T00:49:18Z

    * Tatsuo Ishii (ishii@postgresql.org) wrote:
> I observe performance degradation with PostgreSQL 9.3 vs 9.2 on Linux
> as well.  The hardware is HP DL980G7, 80 cores, 2TB mem, RHEL 6,
> pgbench is used (read only query), scale factor is 1,000 (DB size
> 15GB).

Can you isolate the sysv-vs-mmap patch and see what happens with just
that change..?

> Included is the graph (from PostgreSQL Enterprise Consortium's 2014
> report page 13: https://www.pgecons.org/downloads/43). I see up to 14%
> degration (at 128 concurrent users) comparing with 9.2.

That URL returns 'Forbidden'...

	Thanks,

		Stephen

  54. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Tatsuo Ishii <ishii@postgresql.org> — 2014-04-22T00:58:06Z

    > * Tatsuo Ishii (ishii@postgresql.org) wrote:
>> I observe performance degradation with PostgreSQL 9.3 vs 9.2 on Linux
>> as well.  The hardware is HP DL980G7, 80 cores, 2TB mem, RHEL 6,
>> pgbench is used (read only query), scale factor is 1,000 (DB size
>> 15GB).
> 
> Can you isolate the sysv-vs-mmap patch and see what happens with just
> that change..?

Unfortunately I don't have an access to the machine at this moment.

>> Included is the graph (from PostgreSQL Enterprise Consortium's 2014
>> report page 13: https://www.pgecons.org/downloads/43). I see up to 14%
>> degration (at 128 concurrent users) comparing with 9.2.
> 
> That URL returns 'Forbidden'...

Sorry for this. I sent a problem report to the person in charge.  In
the mean time, please go to:
https://www.pgecons.org/download/works_2013/ then click the link "2013
年度WG1活動報告" (sorry for not English). You should be able to
download a report (PDF).

Also the report is written in Japanese. I hope you can read at leat
the graph in page 13 and the table in page 14.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

  55. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andrew Dunstan <andrew@dunslane.net> — 2014-04-22T01:06:35Z

    On 04/21/2014 08:49 PM, Stephen Frost wrote:
> * Tatsuo Ishii (ishii@postgresql.org) wrote:
>> I observe performance degradation with PostgreSQL 9.3 vs 9.2 on Linux
>> as well.  The hardware is HP DL980G7, 80 cores, 2TB mem, RHEL 6,
>> pgbench is used (read only query), scale factor is 1,000 (DB size
>> 15GB).
> Can you isolate the sysv-vs-mmap patch and see what happens with just
> that change..?


This is exactly why we need a benchfarm.

I actually have a client working based on Greg Smith's pgbench tools.

What we would need is a way to graph the results - that's something 
beyond my very rudimentary expertise in web programming. If anyone feels 
like collaborating I'd be glad to hear from them (The web site is 
programmed in perl + TemplateToolkit, but even that's not immutable. I'm 
open to using, say, node.js plus one of its templating engines.


cheers

andrew

  56. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Tatsuo Ishii <ishii@postgresql.org> — 2014-04-22T01:08:45Z

    > This is exactly why we need a benchfarm.
> 
> I actually have a client working based on Greg Smith's pgbench tools.
> 
> What we would need is a way to graph the results - that's something
> beyond my very rudimentary expertise in web programming. If anyone
> feels like collaborating I'd be glad to hear from them (The web site
> is programmed in perl + TemplateToolkit, but even that's not
> immutable. I'm open to using, say, node.js plus one of its templating
> engines.

gnuplot? (the graph I attached was created by gnuplt).

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

  57. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Peter Geoghegan <pg@heroku.com> — 2014-04-22T01:16:59Z

    On Mon, Apr 21, 2014 at 6:08 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:
>> What we would need is a way to graph the results - that's something
>> beyond my very rudimentary expertise in web programming. If anyone
>> feels like collaborating I'd be glad to hear from them (The web site
>> is programmed in perl + TemplateToolkit, but even that's not
>> immutable. I'm open to using, say, node.js plus one of its templating
>> engines.
>
> gnuplot? (the graph I attached was created by gnuplt).

That's all pgbench-tools itself uses.

The problem with a performance farm is that it's relatively hard to
donate a performance farm member. It more or less requires expensive
hardware, and a large amount of rigor in testing and normalizing
various aspects of the environment that might otherwise add noise.
Then again, it might only take 2 or 3 servers to make a huge
difference. There are a number of different things that would be
immediately compelling to target with that kind of thing, so the first
step is non-obvious too.

-- 
Peter Geoghegan

  58. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andrew Dunstan <andrew@dunslane.net> — 2014-04-22T01:19:22Z

    On 04/21/2014 09:16 PM, Peter Geoghegan wrote:
> On Mon, Apr 21, 2014 at 6:08 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:
>>> What we would need is a way to graph the results - that's something
>>> beyond my very rudimentary expertise in web programming. If anyone
>>> feels like collaborating I'd be glad to hear from them (The web site
>>> is programmed in perl + TemplateToolkit, but even that's not
>>> immutable. I'm open to using, say, node.js plus one of its templating
>>> engines.
>> gnuplot? (the graph I attached was created by gnuplt).
> That's all pgbench-tools itself uses.
>
> The problem with a performance farm is that it's relatively hard to
> donate a performance farm member. It more or less requires expensive
> hardware, and a large amount of rigor in testing and normalizing
> various aspects of the environment that might otherwise add noise.
> Then again, it might only take 2 or 3 servers to make a huge
> difference. There are a number of different things that would be
> immediately compelling to target with that kind of thing, so the first
> step is non-obvious too.
>


If we never start we'll never get there.

I can think of several organizations that might be approached to donate 
hardware.

cheers

andrew

  59. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Joshua D. Drake <jd@commandprompt.com> — 2014-04-22T05:36:26Z

    On 04/21/2014 06:19 PM, Andrew Dunstan wrote:

>
> If we never start we'll never get there.
>
> I can think of several organizations that might be approached to donate
> hardware.

Like .Org?

We have a hardware farm, a rack full of hardware and spindles. It isn't 
the most current but it is there.

Sincerely,

JD



-- 
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.

  60. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Tatsuo Ishii <ishii@postgresql.org> — 2014-04-22T05:47:28Z

    >> Can you isolate the sysv-vs-mmap patch and see what happens with just
>> that change..?
> 
> Unfortunately I don't have an access to the machine at this moment.

Where is the patch? I would like to test it on a smaller machine for
now.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

  61. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Michael Paquier <michael.paquier@gmail.com> — 2014-04-22T06:16:44Z

    On Tue, Apr 22, 2014 at 9:58 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:

> > * Tatsuo Ishii (ishii@postgresql.org) wrote:
> >> I observe performance degradation with PostgreSQL 9.3 vs 9.2 on Linux
> >> as well.  The hardware is HP DL980G7, 80 cores, 2TB mem, RHEL 6,
> >> pgbench is used (read only query), scale factor is 1,000 (DB size
> >> 15GB).
> >
> > Can you isolate the sysv-vs-mmap patch and see what happens with just
> > that change..?
>
> Unfortunately I don't have an access to the machine at this moment.
>
> >> Included is the graph (from PostgreSQL Enterprise Consortium's 2014
> >> report page 13: https://www.pgecons.org/downloads/43). I see up to 14%
> >> degration (at 128 concurrent users) comparing with 9.2.
> >
> > That URL returns 'Forbidden'...
>
> Sorry for this. I sent a problem report to the person in charge.  In
> the mean time, please go to:
> https://www.pgecons.org/download/works_2013/ then click the link "2013
> 年度WG1活動報告" (sorry for not English). You should be able to
> download a report (PDF).
>
> Also the report is written in Japanese. I hope you can read at leat
> the graph in page 13 and the table in page 14.
>
Is pgecons planning to do a translation of that at some point? It looks
like good material, and the audience able to understand it is rather
limited now :)
-- 
Michael

  62. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Mark Kirkwood <mark.kirkwood@catalyst.net.nz> — 2014-04-22T06:26:12Z

    On 22/04/14 09:25, Andres Freund wrote:
> On 2014-04-21 17:21:20 -0400, Bruce Momjian wrote:
>> On Mon, Apr 21, 2014 at 02:08:51PM -0700, Joshua Drake wrote:
>>> If the community had more *BSD presence I think it would be great
>>> but it isn't all that viable at this point. I do know however that
>>> no-one in this community would turn down a team of FreeBSD advocates
>>> helping us make PostgreSQL awesome for PostgreSQL.
>>
>> I don't think we would even implement a run-time control for Linux or
>> Windows for this, so I don't even think it is a FreeBSD issue.
>
> I think some of the arguments in this thread are pretty damn absurd. We
> have just introduced dynamic_shared_memory_type.
>

+1

I was just thinking the same thing...

  63. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andres Freund <andres@2ndquadrant.com> — 2014-04-22T12:19:21Z

    Hi,

Attached you can find a short (compile tested only ) patch implementing
a 'shared_memory_type' GUC, akin to 'dynamic_shared_memory_type'. Will
only apply to 9.4, not 9.3, but it should be easy to convert for it.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  64. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Magnus Hagander <magnus@hagander.net> — 2014-04-22T12:22:05Z

    On Tue, Apr 22, 2014 at 8:26 AM, Mark Kirkwood <
mark.kirkwood@catalyst.net.nz> wrote:

> On 22/04/14 09:25, Andres Freund wrote:
>
>> On 2014-04-21 17:21:20 -0400, Bruce Momjian wrote:
>>
>>> On Mon, Apr 21, 2014 at 02:08:51PM -0700, Joshua Drake wrote:
>>>
>>>> If the community had more *BSD presence I think it would be great
>>>> but it isn't all that viable at this point. I do know however that
>>>> no-one in this community would turn down a team of FreeBSD advocates
>>>> helping us make PostgreSQL awesome for PostgreSQL.
>>>>
>>>
>>> I don't think we would even implement a run-time control for Linux or
>>> Windows for this, so I don't even think it is a FreeBSD issue.
>>>
>>
>> I think some of the arguments in this thread are pretty damn absurd. We
>> have just introduced dynamic_shared_memory_type.
>>
>>
> +1
>
> I was just thinking the same thing...
>
>
I didn't realize we had a guc for dynamic shared memory, must've missed
that in the discussion about that one. I agree that if we have that, it
makes perfect sense to have the same setting available for the main shared
memory segment.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

  65. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Stephen Frost <sfrost@snowman.net> — 2014-04-22T12:55:08Z

    * Magnus Hagander (magnus@hagander.net) wrote:
> I didn't realize we had a guc for dynamic shared memory, must've missed
> that in the discussion about that one. I agree that if we have that, it
> makes perfect sense to have the same setting available for the main shared
> memory segment.

I recall the back-and-forth about the issues with dynamic shared memory
but I had hoped they would result in a solution that didn't involve
having to support both..

	Thanks,

		Stephen

  66. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andrew Dunstan <andrew@dunslane.net> — 2014-04-22T15:26:38Z

    On 04/22/2014 01:36 AM, Joshua D. Drake wrote:
>
> On 04/21/2014 06:19 PM, Andrew Dunstan wrote:
>
>>
>> If we never start we'll never get there.
>>
>> I can think of several organizations that might be approached to donate
>> hardware.
>
> Like .Org?
>
> We have a hardware farm, a rack full of hardware and spindles. It 
> isn't the most current but it is there.
>
>


I'm going away tomorrow for a few days R&R. when I'm back next week I 
will set up a demo client running this module. If you can have a machine 
prepped for this purpose by then so much the better, otherwise I will 
have to drag out a box I recently rescued and have been waiting for 
something to use it with. It's more important that it's stable (i.e. 
nothing else running on it) than that it's very powerful. It could be 
running Ubuntu or some Redhattish variant or, yes, even FreeBSD.

cheers

andrew

  67. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Palle Girgensohn <girgen@freebsd.org> — 2014-04-22T15:33:09Z

    22 apr 2014 kl. 17:26 skrev Andrew Dunstan <andrew@dunslane.net>:

> 
> On 04/22/2014 01:36 AM, Joshua D. Drake wrote:
>> 
>> On 04/21/2014 06:19 PM, Andrew Dunstan wrote:
>> 
>>> 
>>> If we never start we'll never get there.
>>> 
>>> I can think of several organizations that might be approached to donate
>>> hardware.
>> 
>> Like .Org?
>> 
>> We have a hardware farm, a rack full of hardware and spindles. It isn't the most current but it is there.
>> 
>> 
> 
> 
> I'm going away tomorrow for a few days R&R. when I'm back next week I will set up a demo client running this module. If you can have a machine prepped for this purpose by then so much the better, otherwise I will have to drag out a box I recently rescued and have been waiting for something to use it with. It's more important that it's stable (i.e. nothing else running on it) than that it's very powerful. It could be running Ubuntu or some Redhattish variant or, yes, even FreeBSD.

If you need help with the FreeBSD setup, I'm at you service.

Palle

  68. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Joshua D. Drake <jd@commandprompt.com> — 2014-04-22T17:06:20Z

    On 04/22/2014 08:26 AM, Andrew Dunstan wrote:

> I'm going away tomorrow for a few days R&R. when I'm back next week I
> will set up a demo client running this module. If you can have a machine
> prepped for this purpose by then so much the better, otherwise I will
> have to drag out a box I recently rescued and have been waiting for
> something to use it with. It's more important that it's stable (i.e.
> nothing else running on it) than that it's very powerful. It could be
> running Ubuntu or some Redhattish variant or, yes, even FreeBSD.

This is best handled by Mark. Mark can you help Andrew with this? I 
assume we would use the DL385 with the MS70?

JD


>
> cheers
>
> andrew
>
>


-- 
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.

  69. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Merlin Moncure <mmoncure@gmail.com> — 2014-04-22T17:54:01Z

    On Mon, Apr 21, 2014 at 8:06 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
> On 04/21/2014 08:49 PM, Stephen Frost wrote:
>>
>> * Tatsuo Ishii (ishii@postgresql.org) wrote:
>>>
>>> I observe performance degradation with PostgreSQL 9.3 vs 9.2 on Linux
>>> as well.  The hardware is HP DL980G7, 80 cores, 2TB mem, RHEL 6,
>>> pgbench is used (read only query), scale factor is 1,000 (DB size
>>> 15GB).
>>
>> Can you isolate the sysv-vs-mmap patch and see what happens with just
>> that change..?
>
>
>
> This is exactly why we need a benchfarm.
>
> I actually have a client working based on Greg Smith's pgbench tools.
>
> What we would need is a way to graph the results - that's something beyond
> my very rudimentary expertise in web programming. If anyone feels like
> collaborating I'd be glad to hear from them (The web site is programmed in
> perl + TemplateToolkit, but even that's not immutable. I'm open to using,
> say, node.js plus one of its templating engines.

Hm, you got me interested.  Is the data you want to visualize stored
in a database?  Got some example data? (this is pretty off topic, feel
free to contact off-list or on a new thread etc).

merlin

  70. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-22T22:01:50Z

    On 4/22/14, 8:26 AM, Andrew Dunstan wrote:
>
> On 04/22/2014 01:36 AM, Joshua D. Drake wrote:
>>
>> On 04/21/2014 06:19 PM, Andrew Dunstan wrote:
>>
>>>
>>> If we never start we'll never get there.
>>>
>>> I can think of several organizations that might be approached to donate
>>> hardware.
>>
>> Like .Org?
>>
>> We have a hardware farm, a rack full of hardware and spindles. It 
>> isn't the most current but it is there.
>>
>>
>
>
> I'm going away tomorrow for a few days R&R. when I'm back next week I 
> will set up a demo client running this module. If you can have a 
> machine prepped for this purpose by then so much the better, otherwise 
> I will have to drag out a box I recently rescued and have been waiting 
> for something to use it with. It's more important that it's stable 
> (i.e. nothing else running on it) than that it's very powerful. It 
> could be running Ubuntu or some Redhattish variant or, yes, even FreeBSD.
>
> cheers
>
> andrew
>
>

Hey folks, I just spoke with our director of netops Tom Sparks here at 
Norse and we have a vested interest in Postgresql.  We can throw 
together a cluster of 4 machines with specs approximately in the range 
of dual quad core westmere with ~64GB of ram running FreeBSD 10 or 11.  
We can also do an Ubungu install as well or other Linux distro.  Please 
let me know if that this would be a something that the project could 
make use of please.

We also have colo space and power, etc.  So this would be the whole 
deal.  The cluster would be up for as long as needed.

Are the machine specs sufficient?  Any other things we should look for?

CC'd Tom on this email.

-Alfred

  71. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Mark Wong <markwkm@gmail.com> — 2014-04-22T22:43:37Z

    On Tue, Apr 22, 2014 at 10:06 AM, Joshua D. Drake <jd@commandprompt.com>wrote:

>
> On 04/22/2014 08:26 AM, Andrew Dunstan wrote:
>
>  I'm going away tomorrow for a few days R&R. when I'm back next week I
>> will set up a demo client running this module. If you can have a machine
>> prepped for this purpose by then so much the better, otherwise I will
>> have to drag out a box I recently rescued and have been waiting for
>> something to use it with. It's more important that it's stable (i.e.
>> nothing else running on it) than that it's very powerful. It could be
>> running Ubuntu or some Redhattish variant or, yes, even FreeBSD.
>>
>
> This is best handled by Mark. Mark can you help Andrew with this? I assume
> we would use the DL385 with the MS70?
>

Yeah, I can help.  But let me know if Alfred's offer is preferred.

Regards,
Mark

  72. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Andrew Dunstan <andrew@dunslane.net> — 2014-04-23T00:07:21Z

    On 04/22/2014 06:43 PM, Mark Wong wrote:
> On Tue, Apr 22, 2014 at 10:06 AM, Joshua D. Drake 
> <jd@commandprompt.com <mailto:jd@commandprompt.com>> wrote:
>
>
>     On 04/22/2014 08:26 AM, Andrew Dunstan wrote:
>
>         I'm going away tomorrow for a few days R&R. when I'm back next
>         week I
>         will set up a demo client running this module. If you can have
>         a machine
>         prepped for this purpose by then so much the better, otherwise
>         I will
>         have to drag out a box I recently rescued and have been
>         waiting for
>         something to use it with. It's more important that it's stable
>         (i.e.
>         nothing else running on it) than that it's very powerful. It
>         could be
>
>         running Ubuntu or some Redhattish variant or, yes, even FreeBSD.
>
>
>     This is best handled by Mark. Mark can you help Andrew with this?
>     I assume we would use the DL385 with the MS70?
>
>
> Yeah, I can help.  But let me know if Alfred's offer is preferred.


I don't think they are mutually exclusive, but I'd rather start off with 
one machine. I would find it easiest if it were on something like 
CentOS6.5.

When we have that running and reporting like we want it we can add a 
FreeBSD server.

The idea is that these machines would be available for a long time, 
ideally quite a few years. We want to have them with a stable time 
series of performance data so that when something disturbs the 
performance it sticks out like a sore thumb.

cheers

andrew

  73. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Mark Kirkwood <mark.kirkwood@catalyst.net.nz> — 2014-04-23T01:01:14Z

    On 23/04/14 00:19, Andres Freund wrote:
> Hi,
>
> Attached you can find a short (compile tested only ) patch implementing
> a 'shared_memory_type' GUC, akin to 'dynamic_shared_memory_type'. Will
> only apply to 9.4, not 9.3, but it should be easy to convert for it.
>

Have just tried this out (on Ubuntu 14.04 rather than Freebsd, as it is 
what I happened to be running), certainly works for me (big shared 
memory segment when I set it to 'sysv', only a tiny one when I use 'mmap').

The regression tests pass in both cases.

regards

Mark

  74. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    YAMAMOTO Takashi <yamt@netbsd.org> — 2014-04-23T04:17:48Z

    > - NetBSD: crashes under load; this could have been fixed but when I ran the
>   benchmarks in 2012 none of the developers seemed to care.

do you mean this?
https://mail-index.netbsd.org/tech-kern/2012/08/29/msg013918.html

YAMAMOTO Takashi

  75. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Tatsuo Ishii <ishii@postgresql.org> — 2014-04-24T00:26:25Z

    >> >> Included is the graph (from PostgreSQL Enterprise Consortium's 2014
>> >> report page 13: https://www.pgecons.org/downloads/43). I see up to 14%
>> >> degration (at 128 concurrent users) comparing with 9.2.
>> >
>> > That URL returns 'Forbidden'...
>>
>> Sorry for this. I sent a problem report to the person in charge.  In
>> the mean time, please go to:
>> https://www.pgecons.org/download/works_2013/ then click the link "2013
>> 年度WG1活動報告" (sorry for not English). You should be able to
>> download a report (PDF).
>>
>> Also the report is written in Japanese. I hope you can read at leat
>> the graph in page 13 and the table in page 14.
>>
> Is pgecons planning to do a translation of that at some point? It looks
> like good material, and the audience able to understand it is rather
> limited now :)

Yeah, once I proposed a translation of the documents by professional
translators to the organization. Their decision was "no". The main
reason was cost. The document is huge and the translation work could
cost tremendously. So unless someone comes up for volunteering the
translation work, the document would not be translated.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

  76. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Mark Wong <markwkm@gmail.com> — 2014-04-24T01:57:24Z

    > On Apr 22, 2014, at 5:07 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> 
> 
>> On 04/22/2014 06:43 PM, Mark Wong wrote:
>> On Tue, Apr 22, 2014 at 10:06 AM, Joshua D. Drake <jd@commandprompt.com <mailto:jd@commandprompt.com>> wrote:
>> 
>> 
>>    On 04/22/2014 08:26 AM, Andrew Dunstan wrote:
>> 
>>        I'm going away tomorrow for a few days R&R. when I'm back next
>>        week I
>>        will set up a demo client running this module. If you can have
>>        a machine
>>        prepped for this purpose by then so much the better, otherwise
>>        I will
>>        have to drag out a box I recently rescued and have been
>>        waiting for
>>        something to use it with. It's more important that it's stable
>>        (i.e.
>>        nothing else running on it) than that it's very powerful. It
>>        could be
>> 
>>        running Ubuntu or some Redhattish variant or, yes, even FreeBSD.
>> 
>> 
>>    This is best handled by Mark. Mark can you help Andrew with this?
>>    I assume we would use the DL385 with the MS70?
>> 
>> 
>> Yeah, I can help.  But let me know if Alfred's offer is preferred.
> 
> 
> I don't think they are mutually exclusive, but I'd rather start off with one machine. I would find it easiest if it were on something like CentOS6.5.
> 
> When we have that running and reporting like we want it we can add a FreeBSD server.
> 
> The idea is that these machines would be available for a long time, ideally quite a few years. We want to have them with a stable time series of performance data so that when something disturbs the performance it sticks out like a sore thumb.

Ok, centos 6.4 is on there now, I'll try to get that upgraded within a few days or so. I'll keep you posted.

Regards,
Mark

  77. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Noah Misch <noah@leadboat.com> — 2014-04-24T02:14:37Z

    On Mon, Apr 21, 2014 at 11:25:35PM +0200, Andres Freund wrote:
> On 2014-04-21 17:21:20 -0400, Bruce Momjian wrote:
> > On Mon, Apr 21, 2014 at 02:08:51PM -0700, Joshua Drake wrote:
> > > If the community had more *BSD presence I think it would be great
> > > but it isn't all that viable at this point. I do know however that
> > > no-one in this community would turn down a team of FreeBSD advocates
> > > helping us make PostgreSQL awesome for PostgreSQL.
> > 
> > I don't think we would even implement a run-time control for Linux or
> > Windows for this, so I don't even think it is a FreeBSD issue.
> 
> I think some of the arguments in this thread are pretty damn absurd. We
> have just introduced dynamic_shared_memory_type.

I agree.  The ideal is nobody wishing for an option, but I'd rather have the
option if a non-theoretical set of users is feeling the pain of its absence.

-- 
Noah Misch
EnterpriseDB                                 http://www.enterprisedb.com

  78. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Ian Barwick <ian@2ndquadrant.com> — 2014-04-24T08:24:01Z

    On 24/04/14 09:26, Tatsuo Ishii wrote:
>>>>> Included is the graph (from PostgreSQL Enterprise Consortium's 2014
>>>>> report page 13: https://www.pgecons.org/downloads/43). I see up to 14%
>>>>> degration (at 128 concurrent users) comparing with 9.2.
>>>>
>>>> That URL returns 'Forbidden'...
>>>
>>> Sorry for this. I sent a problem report to the person in charge.  In
>>> the mean time, please go to:
>>> https://www.pgecons.org/download/works_2013/ then click the link "2013
>>> 年度WG1活動報告" (sorry for not English). You should be able to
>>> download a report (PDF).
>>>
>>> Also the report is written in Japanese. I hope you can read at leat
>>> the graph in page 13 and the table in page 14.
>>>
>> Is pgecons planning to do a translation of that at some point? It looks
>> like good material, and the audience able to understand it is rather
>> limited now :)
> 
> Yeah, once I proposed a translation of the documents by professional
> translators to the organization. Their decision was "no". The main
> reason was cost. The document is huge and the translation work could
> cost tremendously. So unless someone comes up for volunteering the
> translation work, the document would not be translated.

I actually started translating one of those reports on the way home
from last year's PgCon (PgEcons made a presentation there:
http://www.pgcon.org/2013/schedule/events/556.en.html ) - it was a long flight - but
didn't have any
particular incentive to finish it.

It might make a nice JPUG project for members who want to practise their
English.


Regards

Ian Barwick

-- 
 Ian Barwick                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  79. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Jim Nasby <jim@nasby.net> — 2014-04-26T19:32:58Z

    On 4/22/14, 5:01 PM, Alfred Perlstein wrote:
>
> Hey folks, I just spoke with our director of netops Tom Sparks here at Norse and we have a vested interest in Postgresql.  We can throw together a cluster of 4 machines with specs approximately in the range of dual quad core westmere with ~64GB of ram running FreeBSD 10 or 11. We can also do an Ubungu install as well or other Linux distro.  Please let me know if that this would be a something that the project could make use of please.
>
> We also have colo space and power, etc.  So this would be the whole deal.  The cluster would be up for as long as needed.
>
> Are the machine specs sufficient?  Any other things we should look for?
>
> CC'd Tom on this email.

Did anyone respond to this off-list?

Would these machines be more useful as dedicated performance test servers for the community or generic BenchFarm members?
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

  80. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Stephen Frost <sfrost@snowman.net> — 2014-04-27T01:15:22Z

    Jim,

* Jim Nasby (jim@nasby.net) wrote:
> On 4/22/14, 5:01 PM, Alfred Perlstein wrote:
> >We also have colo space and power, etc.  So this would be the whole deal.  The cluster would be up for as long as needed.
> >
> >Are the machine specs sufficient?  Any other things we should look for?
> >
> >CC'd Tom on this email.
> 
> Did anyone respond to this off-list?

Yes, I did follow-up with Tom.  I'll do so again, as the discussion had
died down.

> Would these machines be more useful as dedicated performance test servers for the community or generic BenchFarm members?

I don't believe they would be terribly useful as buildfarm systems; we
could set up similar systems with VMs to just run the regression tests.
Where I see these systems being particularly valuable would be as the
start of our performance farm, and perhaps one of the systems as a PG
infrastructure server.

	Thanks!

		Stephen

  81. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Alfred Perlstein <alfred@freebsd.org> — 2014-04-27T01:25:19Z

    JFYI we have 3 or 4 machines racked for the pgsql project in our DC. 

Tom informed me he would be lighting them up this week time permitting.  

Sent from my iPhone

> On Apr 26, 2014, at 6:15 PM, Stephen Frost <sfrost@snowman.net> wrote:
> 
> Jim,
> 
> * Jim Nasby (jim@nasby.net) wrote:
>>> On 4/22/14, 5:01 PM, Alfred Perlstein wrote:
>>> We also have colo space and power, etc.  So this would be the whole deal.  The cluster would be up for as long as needed.
>>> 
>>> Are the machine specs sufficient?  Any other things we should look for?
>>> 
>>> CC'd Tom on this email.
>> 
>> Did anyone respond to this off-list?
> 
> Yes, I did follow-up with Tom.  I'll do so again, as the discussion had
> died down.
> 
>> Would these machines be more useful as dedicated performance test servers for the community or generic BenchFarm members?
> 
> I don't believe they would be terribly useful as buildfarm systems; we
> could set up similar systems with VMs to just run the regression tests.
> Where I see these systems being particularly valuable would be as the
> start of our performance farm, and perhaps one of the systems as a PG
> infrastructure server.
> 
>    Thanks!
> 
>        Stephen

  82. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Stephen Frost <sfrost@snowman.net> — 2014-04-27T01:36:28Z

    Alfred,

* Alfred Perlstein (alfred@freebsd.org) wrote:
> JFYI we have 3 or 4 machines racked for the pgsql project in our DC. 

Oh, great!

> Tom informed me he would be lighting them up this week time permitting.  

Excellent, many thanks!

	Stephen

  83. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Tatsuo Ishii <ishii@postgresql.org> — 2014-07-03T10:15:32Z

    Hi,

> Hi,
> 
> Attached you can find a short (compile tested only ) patch implementing
> a 'shared_memory_type' GUC, akin to 'dynamic_shared_memory_type'. Will
> only apply to 9.4, not 9.3, but it should be easy to convert for it.
> 
> Greetings,
> 
> Andres Freund

I have rebased Andres's patch against 9.3-STABLE tree. Please take a
look at attached patches and let me know if you find anything strange.

I am going to test the patch on a huge HP machine: DL980G7/64
cores/2TB mem.  With the patch I would be able to report back if using
SysV shared mem fixes the 9.3 performance problem.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  84. Re: Perfomance degradation 9.3 (vs 9.2) for FreeBSD

    Palle Girgensohn <girgen@freebsd.org> — 2014-10-20T13:09:00Z

    Hello,

How did this testing turn out?

Palle

3 jul 2014 kl. 12:15 skrev Tatsuo Ishii <ishii@postgresql.org>:

> Hi,
> 
>> Hi,
>> 
>> Attached you can find a short (compile tested only ) patch implementing
>> a 'shared_memory_type' GUC, akin to 'dynamic_shared_memory_type'. Will
>> only apply to 9.4, not 9.3, but it should be easy to convert for it.
>> 
>> Greetings,
>> 
>> Andres Freund
> 
> I have rebased Andres's patch against 9.3-STABLE tree. Please take a
> look at attached patches and let me know if you find anything strange.
> 
> I am going to test the patch on a huge HP machine: DL980G7/64
> cores/2TB mem.  With the patch I would be able to report back if using
> SysV shared mem fixes the 9.3 performance problem.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> diff --git a/src/backend/port/sysv_shmem.c b/src/backend/port/sysv_shmem.c
> index f746c81..e82054a 100644
> --- a/src/backend/port/sysv_shmem.c
> +++ b/src/backend/port/sysv_shmem.c
> @@ -72,6 +72,7 @@ static void IpcMemoryDetach(int status, Datum shmaddr);
> static void IpcMemoryDelete(int status, Datum shmId);
> static PGShmemHeader *PGSharedMemoryAttach(IpcMemoryKey key,
> 					 IpcMemoryId *shmid);
> +static void *CreateAnonymousSegment(Size *size);
> 
> 
> /*
> @@ -389,49 +390,19 @@ PGSharedMemoryCreate(Size size, bool makePrivate, int port)
> 	 * developer use, this shouldn't be a big problem.
> 	 */
> #ifndef EXEC_BACKEND
> +	if (shared_memory_type == SHMEM_TYPE_MMAP)
> 	{
> -		long		pagesize = sysconf(_SC_PAGE_SIZE);
> -
> -		/*
> -		 * Ensure request size is a multiple of pagesize.
> -		 *
> -		 * pagesize will, for practical purposes, always be a power of two.
> -		 * But just in case it isn't, we do it this way instead of using
> -		 * TYPEALIGN().
> -		 */
> -		if (pagesize > 0 && size % pagesize != 0)
> -			size += pagesize - (size % pagesize);
> -
> -		/*
> -		 * We assume that no one will attempt to run PostgreSQL 9.3 or later
> -		 * on systems that are ancient enough that anonymous shared memory is
> -		 * not supported, such as pre-2.4 versions of Linux.  If that turns
> -		 * out to be false, we might need to add a run-time test here and do
> -		 * this only if the running kernel supports it.
> -		 */
> -		AnonymousShmem = mmap(NULL, size, PROT_READ | PROT_WRITE, PG_MMAP_FLAGS,
> -							  -1, 0);
> -		if (AnonymousShmem == MAP_FAILED)
> -		{
> -			int		saved_errno = errno;
> -
> -			ereport(FATAL,
> -					(errmsg("could not map anonymous shared memory: %m"),
> -					 (saved_errno == ENOMEM) ?
> -				errhint("This error usually means that PostgreSQL's request "
> -					 "for a shared memory segment exceeded available memory "
> -					  "or swap space. To reduce the request size (currently "
> -					  "%lu bytes), reduce PostgreSQL's shared memory usage, "
> -						"perhaps by reducing shared_buffers or "
> -						"max_connections.",
> -						(unsigned long) size) : 0));
> -		}
> +		AnonymousShmem = CreateAnonymousSegment(&size);
> 		AnonymousShmemSize = size;
> -
> 		/* Now we need only allocate a minimal-sized SysV shmem block. */
> 		sysvsize = sizeof(PGShmemHeader);
> 	}
> +	else
> #endif
> +	{
> +		Assert(shared_memory_type == SHMEM_TYPE_SYSV);
> +		sysvsize = size;
> +	}
> 
> 	/* Make sure PGSharedMemoryAttach doesn't fail without need */
> 	UsedShmemSegAddr = NULL;
> @@ -631,3 +602,47 @@ PGSharedMemoryAttach(IpcMemoryKey key, IpcMemoryId *shmid)
> 
> 	return hdr;
> }
> +
> +/*
> + * Creates an anonymous mmap()ed shared memory segment.
> + *
> + * Pass the requested size in *size.  This function will modify *size to the
> + * actual size of the allocation, if it ends up allocating a segment that is
> + * larger than requested.
> + */
> +#ifndef EXEC_BACKEND
> +static void *
> +CreateAnonymousSegment(Size *size)
> +{
> +	Size		allocsize = *size;
> +	void	   *ptr = MAP_FAILED;
> +	int			mmap_errno = 0;
> +
> +	/*
> +	 * use the original size, not the rounded up value, when falling back
> +	 * to non-huge pages.
> +	 */
> +	allocsize = *size;
> +	ptr = mmap(NULL, allocsize, PROT_READ | PROT_WRITE,
> +			   PG_MMAP_FLAGS, -1, 0);
> +	mmap_errno = errno;
> +
> +	if (ptr == MAP_FAILED)
> +	{
> +		errno = mmap_errno;
> +		ereport(FATAL,
> +				(errmsg("could not map anonymous shared memory: %m"),
> +				 (mmap_errno == ENOMEM) ?
> +				 errhint("This error usually means that PostgreSQL's request "
> +					"for a shared memory segment exceeded available memory, "
> +					  "swap space or huge pages. To reduce the request size "
> +						 "(currently %zu bytes), reduce PostgreSQL's shared "
> +					   "memory usage, perhaps by reducing shared_buffers or "
> +						 "max_connections.",
> +						 *size) : 0));
> +	}
> +
> +	*size = allocsize;
> +	return ptr;
> +}
> +#endif
> diff --git a/src/backend/storage/ipc/ipci.c b/src/backend/storage/ipc/ipci.c
> index 918ac51..51dccdc 100644
> --- a/src/backend/storage/ipc/ipci.c
> +++ b/src/backend/storage/ipc/ipci.c
> @@ -39,6 +39,8 @@
> #include "storage/sinvaladt.h"
> #include "storage/spin.h"
> 
> +/* GUCs */
> +int			shared_memory_type = DEFAULT_SHARED_MEMORY_TYPE;
> 
> shmem_startup_hook_type shmem_startup_hook = NULL;
> 
> diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
> index 2b6527f..2945a68 100644
> --- a/src/backend/utils/misc/guc.c
> +++ b/src/backend/utils/misc/guc.c
> @@ -61,6 +61,7 @@
> #include "replication/walreceiver.h"
> #include "replication/walsender.h"
> #include "storage/bufmgr.h"
> +#include "storage/pg_shmem.h"
> #include "storage/standby.h"
> #include "storage/fd.h"
> #include "storage/proc.h"
> @@ -378,6 +379,19 @@ static const struct config_enum_entry synchronous_commit_options[] = {
> 	{NULL, 0, false}
> };
> 
> +static struct config_enum_entry shared_memory_options[] = {
> +#ifndef WIN32
> +	{ "sysv", SHMEM_TYPE_SYSV, false},
> +#endif
> +#ifndef EXEC_BACKEND
> +	{ "mmap", SHMEM_TYPE_MMAP, false},
> +#endif
> +#ifdef WIN32
> +	{ "windows", SHMEM_TYPE_WINDOWS, false},
> +#endif
> +	{NULL, 0, false}
> +};
> +
> /*
>  * Options for enum values stored in other modules
>  */
> @@ -3328,6 +3342,16 @@ static struct config_enum ConfigureNamesEnum[] =
> 	},
> 
> 	{
> +		{"shared_memory_type", PGC_POSTMASTER, RESOURCES_MEM,
> +			gettext_noop("Selects the shared memory implementation used."),
> +			NULL
> +		},
> +		&shared_memory_type,
> +		DEFAULT_SHARED_MEMORY_TYPE, shared_memory_options,
> +		NULL, NULL, NULL
> +	},
> +
> +	{
> 		{"wal_sync_method", PGC_SIGHUP, WAL_SETTINGS,
> 			gettext_noop("Selects the method used for forcing WAL updates to disk."),
> 			NULL
> diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
> index 18196f8..022cf4d 100644
> --- a/src/backend/utils/misc/postgresql.conf.sample
> +++ b/src/backend/utils/misc/postgresql.conf.sample
> @@ -124,6 +124,13 @@
> #maintenance_work_mem = 16MB		# min 1MB
> #max_stack_depth = 2MB			# min 100kB
> 
> +#shared_memory_type = mmap		# the default is the first option
> +					# supported by the operating system:
> +					#   mmap
> +					#   sysv
> +					#   windows
> +#dynamic_shared_memory_type = posix	# the default is the first option
> +
> # - Disk -
> 
> #temp_file_limit = -1			# limits per-session temp file space
> diff --git a/src/include/storage/pg_shmem.h b/src/include/storage/pg_shmem.h
> index 6ece82b..9c3b6d9 100644
> --- a/src/include/storage/pg_shmem.h
> +++ b/src/include/storage/pg_shmem.h
> @@ -38,6 +38,27 @@ typedef struct PGShmemHeader	/* standard header for all Postgres shmem */
> #endif
> } PGShmemHeader;
> 
> +/* GUC variable */
> +extern int huge_pages;
> +/* Possible values for shared_memory_type */
> +typedef enum
> +{
> +	SHMEM_TYPE_WINDOWS,
> +	SHMEM_TYPE_SYSV,
> +	SHMEM_TYPE_MMAP
> +} PGShmemType;
> +
> +#if !defined(WIN32) && !defined(EXEC_BACKEND)
> +#define DEFAULT_SHARED_MEMORY_TYPE SHMEM_TYPE_MMAP
> +#elif !defined(WIN32)
> +#define DEFAULT_SHARED_MEMORY_TYPE SHMEM_TYPE_SYSV
> +#else
> +#define DEFAULT_SHARED_MEMORY_TYPE SHMEM_TYPE_WINDOWS
> +#endif
> +
> +/* GUC variables */
> +extern int shared_memory_type;
> +extern int huge_pages;
> 
> #ifdef EXEC_BACKEND
> #ifndef WIN32