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 :

> 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 = mm

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

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

[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  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]

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

[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

[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

[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

[Mark Wong]

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

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

Regards,
Mark

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

[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

[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

[Andrew Dunstan]

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



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

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

running Ubuntu or some Redhattish variant or, yes, even FreeBSD.


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


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



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


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


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


cheers

andrew


--
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 wrote:

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

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

Regards,
Mark

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


cheers

andrew




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


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


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

CC'd Tom on this email.

-Alfred


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

[Joshua D. Drake]

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


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


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


JD




cheers

andrew





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


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

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

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

Palle


signature.asc
Description: Message signed with OpenPGP using GPGMail

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


cheers

andrew




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

* 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

[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

[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 
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 *UsedShm

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

[Michael Paquier]

On Tue, Apr 22, 2014 at 9:58 AM, Tatsuo Ishii  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

[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

[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

[Andrew Dunstan]

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

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

[Peter Geoghegan]

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

[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

[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]

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

[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

[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

[Peter Geoghegan]

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

[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

[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

[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

[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

[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  http://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

[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

[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

[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  http://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, 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

[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

[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]

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

[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:
> 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, 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 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: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

[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, 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

[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

[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

[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

[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

[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

[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

[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

[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

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


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

[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

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


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

[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

[Andres Freund]

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

[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

[Tom Lane]

Andres Freund  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

[Alfred Perlstein]

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

Andres Freund  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

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


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

[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

[Andrew Dunstan]

On 04/21/2014 11:39 AM, Magnus Hagander wrote:
On Mon, Apr 21, 2014 at 4:51 PM, Andres Freund > wrote:


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

[Magnus Hagander]

On Mon, Apr 21, 2014 at 4:51 PM, Andres Freund wrote:

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

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

[Tom Lane]

Andres Freund  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

[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

[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

[Palle Girgensohn]

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

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

[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]

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

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

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