Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Palle Girgensohn]

Hello,

How did this testing turn out?

Palle

3 jul 2014 kl. 12:15 skrev Tatsuo Ishii is...@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)
   {
 - longpagesize = 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)
 +{
 + Sizeallocsize = *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 == 

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Tatsuo Ishii]

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 

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Jim Nasby]

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   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Stephen Frost]

Jim,

* Jim Nasby (j...@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


signature.asc
Description: Digital signature

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

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 sfr...@snowman.net wrote:
 
 Jim,
 
 * Jim Nasby (j...@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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Stephen Frost]

Alfred,

* Alfred Perlstein (alf...@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


signature.asc
Description: Digital signature

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Ian Barwick]

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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Tatsuo Ishii]

  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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Mark Wong]

 On Apr 22, 2014, at 5:07 PM, Andrew Dunstan and...@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 j...@commandprompt.com 
 mailto:j...@commandprompt.com wrote:
 
 
On 04/22/2014 08:26 AM, Andrew Dunstan wrote:
 
I'm going away tomorrow for a few days RR. 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

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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Noah Misch]

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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Michael Paquier]

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

  * Tatsuo Ishii (is...@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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Mark Kirkwood]

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



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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andres Freund]

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
From e090326d8b5933a162e8e503ccec714bde3a49b7 Mon Sep 17 00:00:00 2001
From: Andres Freund and...@anarazel.de
Date: Tue, 22 Apr 2014 14:17:34 +0200
Subject: [PATCH] Add shared_memory_type GUC.

---
 src/backend/port/sysv_shmem.c | 18 --
 src/backend/storage/ipc/ipci.c|  2 ++
 src/backend/utils/misc/guc.c  | 23 +++
 src/backend/utils/misc/postgresql.conf.sample |  7 ++-
 src/include/storage/pg_shmem.h| 21 +++--
 5 files changed, 62 insertions(+), 9 deletions(-)

diff --git a/src/backend/port/sysv_shmem.c b/src/backend/port/sysv_shmem.c
index 5e3850b..f6343d3 100644
--- a/src/backend/port/sysv_shmem.c
+++ b/src/backend/port/sysv_shmem.c
@@ -465,14 +465,20 @@ PGSharedMemoryCreate(Size size, bool makePrivate, int port,
 	 * developer use, this shouldn't be a big problem.
 	 */
 #ifndef EXEC_BACKEND
-	AnonymousShmem = CreateAnonymousSegment(size);
-	AnonymousShmemSize = size;
+	if (shared_memory_type == SHMEM_TYPE_MMAP)
+	{
+		AnonymousShmem = CreateAnonymousSegment(size);
+		AnonymousShmemSize = size;
 
-	/* Now we need only allocate a minimal-sized SysV shmem block. */
-	sysvsize = sizeof(PGShmemHeader);
-#else
-	sysvsize = 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;
diff --git a/src/backend/storage/ipc/ipci.c b/src/backend/storage/ipc/ipci.c
index 4290d2d..c58f171 100644
--- a/src/backend/storage/ipc/ipci.c
+++ b/src/backend/storage/ipc/ipci.c
@@ -42,6 +42,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 ea54d16..f6d9dc4 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -403,6 +403,19 @@ static const struct config_enum_entry huge_pages_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
  */
@@ -3453,6 +3466,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 70e5a51..1c5f02a 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -127,7 +127,12 @@
 #maintenance_work_mem = 64MB		# min 1MB
 #autovacuum_work_mem = -1		# min 1MB, or -1 to use maintenance_work_mem
 #max_stack_depth = 2MB			# min 100kB
-#dynamic_shared_memory_type = posix # the default is the first option
+#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
 	# supported by the operating system:
 	#   posix
 	#   sysv
diff --git a/src/include/storage/pg_shmem.h b/src/include/storage/pg_shmem.h
index ab28ebe..53ea43a 100644
--- a/src/include/storage/pg_shmem.h
+++ b/src/include/storage/pg_shmem.h
@@ -41,8 +41,13 @@ 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;
 
 /* Possible values for huge_pages */
 typedef enum
@@ -52,6 +57,10 @@ typedef enum
 	HUGE_PAGES_TRY
 } HugePagesType;
 
+/* GUC variables */
+extern int shared_memory_type;
+extern int huge_pages;
+
 #ifndef WIN32
 extern unsigned long UsedShmemSegID;
 #else
@@ -59,6 +68,14 @@ extern HANDLE UsedShmemSegID;
 #endif
 extern void 

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Magnus Hagander]

On Tue, Apr 22, 2014 at 8:26 AM, Mark Kirkwood 
mark.kirkw...@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/

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Stephen Frost]

* Magnus Hagander (mag...@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


signature.asc
Description: Digital signature

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andrew Dunstan]

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 RR. 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




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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Palle Girgensohn]

22 apr 2014 kl. 17:26 skrev Andrew Dunstan and...@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 RR. 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


signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Joshua D. Drake]

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


I'm going away tomorrow for a few days RR. 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.


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Merlin Moncure]

On Mon, Apr 21, 2014 at 8:06 PM, Andrew Dunstan and...@dunslane.net wrote:

 On 04/21/2014 08:49 PM, Stephen Frost wrote:

 * Tatsuo Ishii (is...@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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

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 RR. 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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Mark Wong]

On Tue, Apr 22, 2014 at 10:06 AM, Joshua D. Drake j...@commandprompt.comwrote:


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

  I'm going away tomorrow for a few days RR. 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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andrew Dunstan]

On 04/22/2014 06:43 PM, Mark Wong wrote:
On Tue, Apr 22, 2014 at 10:06 AM, Joshua D. Drake 
j...@commandprompt.com mailto:j...@commandprompt.com wrote:



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

I'm going away tomorrow for a few days RR. 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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Mark Kirkwood]

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



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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[YAMAMOTO Takashi]

 - 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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Francois Tigeot]

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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Palle Girgensohn]

 21 apr 2014 kl. 11:26 skrev Francois Tigeot ftig...@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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andres Freund]

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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Tom Lane]

Andres Freund and...@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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andres Freund]

On 2014-04-21 10:45:24 -0400, Tom Lane wrote:
 Andres Freund and...@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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Magnus Hagander]

On Mon, Apr 21, 2014 at 4:51 PM, Andres Freund and...@2ndquadrant.comwrote:

 On 2014-04-21 10:45:24 -0400, Tom Lane wrote:
  Andres Freund and...@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/

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andres Freund]

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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andrew Dunstan]

On 04/21/2014 11:39 AM, Magnus Hagander wrote:
On Mon, Apr 21, 2014 at 4:51 PM, Andres Freund and...@2ndquadrant.com 
mailto:and...@2ndquadrant.com wrote:


On 2014-04-21 10:45:24 -0400, Tom Lane wrote:
 Andres Freund and...@2ndquadrant.com
mailto:and...@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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andres Freund]

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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

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 
and...@2ndquadrant.com mailto:and...@2ndquadrant.com wrote:


On 2014-04-21 10:45:24 -0400, Tom Lane wrote:
 Andres Freund and...@2ndquadrant.com
mailto:and...@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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

On 4/21/14 8:58 AM, Tom Lane wrote:

Andres Freund and...@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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Tom Lane]

Andres Freund and...@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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Stephen Frost]

* Alfred Perlstein (alf...@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


signature.asc
Description: Digital signature

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andres Freund]

On 2014-04-21 11:58:10 -0400, Tom Lane wrote:
 Andres Freund and...@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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

On 4/21/14 9:13 AM, Stephen Frost wrote:

* Alfred Perlstein (alf...@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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andrew Dunstan]

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 
and...@2ndquadrant.com mailto:and...@2ndquadrant.com wrote:


On 2014-04-21 10:45:24 -0400, Tom Lane wrote:
 Andres Freund and...@2ndquadrant.com
mailto:and...@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




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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Francois Tigeot]

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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

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 
and...@2ndquadrant.com mailto:and...@2ndquadrant.com wrote:


On 2014-04-21 10:45:24 -0400, Tom Lane wrote:
 Andres Freund and...@2ndquadrant.com
mailto:and...@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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Stephen Frost]

* Alfred Perlstein (alf...@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


signature.asc
Description: Digital signature

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andrew Dunstan]

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



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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

On 4/21/14 9:34 AM, Stephen Frost wrote:

* Alfred Perlstein (alf...@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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andrew Dunstan]

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



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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andres Freund]

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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alvaro Herrera]

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 Herrerahttp://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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

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





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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alvaro Herrera]

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 Herrerahttp://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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

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



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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Stephen Frost]

* Alfred Perlstein (alf...@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


signature.asc
Description: Digital signature

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Stephen Frost]

Alfred,

* Alfred Perlstein (alf...@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


signature.asc
Description: Digital signature

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

On 4/21/14, 11:14 AM, Stephen Frost wrote:

Alfred,

* Alfred Perlstein (alf...@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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Stephen Frost]

Alfred,

* Alfred Perlstein (alf...@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


signature.asc
Description: Digital signature

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andres Freund]

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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Stephen Frost]

* Andres Freund (and...@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


signature.asc
Description: Digital signature

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

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



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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Bruce Momjian]

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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Joshua D. Drake]

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.


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Stephen Frost]

Alfred,

* Alfred Perlstein (alf...@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


signature.asc
Description: Digital signature

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Bruce Momjian]

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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Alfred Perlstein]

On 4/21/14, 2:23 PM, Stephen Frost wrote:

Alfred,

* Alfred Perlstein (alf...@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



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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andres Freund]

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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Jim Nasby]

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   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[David G Johnston]

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.


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Tatsuo Ishii]

 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

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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Peter Geoghegan]

On Mon, Apr 21, 2014 at 4:10 AM, Andres Freund and...@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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Joshua D. Drake]

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.


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Stephen Frost]

* Tatsuo Ishii (is...@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


signature.asc
Description: Digital signature

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Tatsuo Ishii]

 * Tatsuo Ishii (is...@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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andrew Dunstan]

On 04/21/2014 08:49 PM, Stephen Frost wrote:

* Tatsuo Ishii (is...@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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Tatsuo Ishii]

 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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Peter Geoghegan]

On Mon, Apr 21, 2014 at 6:08 PM, Tatsuo Ishii is...@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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Andrew Dunstan]

On 04/21/2014 09:16 PM, Peter Geoghegan wrote:

On Mon, Apr 21, 2014 at 6:08 PM, Tatsuo Ishii is...@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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Joshua D. Drake]

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.


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Tatsuo Ishii]

 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


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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Francois Tigeot]

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

Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

[Palle Girgensohn]

 20 apr 2014 kl. 12:19 skrev Francois Tigeot ftig...@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