Perfomance degradation 9.3 (vs 9.2) for FreeBSD

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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/

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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. :) )

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* 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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers