FreeBSD 10 and PostgreSQL 9.3 scalability issues

[Palle Girgensohn]

> 23 apr 2014 kl. 01:04 skrev Adrian Chadd <adrian at freebsd.org>:
> 
> Hi,
> 
> Are you able to repeat these tests (for both 9.2 and 9.3) whilst
> grabbing some performance data from lock profiling and hwpmc?

I sure can, but I'd love some pointers as to how this is done. Please? :-)

> 
> The benchmarking is great but it doesn't tell us enough information as
> to "why" things behave poorly compared to Linux and why the mmap drop
> isn't so great.


As per the discussion on postresql-hackers, the regression between pg9.2 and pg9.3, which includes the sysv->mmap shift, *might* also exist, at least partly, on Linux as well.

The initial post in *this* thread does however indicate that freebsd performs poorer than Linux and dragonflybsd, but does not really compare PostgreSQL versions.

Just so we're not pursuing the wrong problem here, let's be open minded about the definition of the problem. :-)

> 
> What about with more clients? 64? 128? 256?

My test went to 80. I can go higher as well, though other sources say 50 is a reasonable limit for PostgreSQL. 

Palle 


> 
> 
> Thanks!
> 
> 
> 
> -a
> 
> 
>> On 21 April 2014 14:11, Palle Girgensohn <girgen at pingpong.net> wrote:
>> 
>> 
>>> Den torsdagen den 20:e mars 2014 kl. 00:33:10 UTC+1 skrev Sean Chittenden:
>>> 
>>>> As far as I know, the test was done on both UFS2 and ZFS and the
>>>> difference was marginal.
>>> 
>>> As Adrian pointed out, there is an mmap(2) mutex in the way. Starting in
>>> PostgreSQL 9.3, shared buffers are allocated out of mmap(2) instead of shm.
>>> shm is only used to notify the PostgreSQL postmaster that a child process
>>> exited/crashed (when a pid detaches from a shm segment, there is a kernel
>>> event, but there is no kernel event when detaching from an mmap(2) region).
>>> -sc
>>> 
>>> http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN115039
>>> 
>>> 
>>>>>> Just want to share these pgbench results done by DragonFlyBSD, and
>>> would
>>>>>> like some input on why these numbers look so bad and what can be done
>>> to
>>>>>> improve (ie. kernel tunables etc) the performance.
>>> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf
>>>>> 
>>>>> 
>>>>> Do you have the ability to test with FreeBSD 8.x and 9.x to see if this
>>> is
>>>>> regression?
>>>>> 
>>>>> Also you don't mention the FS used in each case, so I'm wondering if
>>> you
>>>>> used a ZFS install of FreeBSD which could help to explain things.
>>> 
>>> 
>>> --
>>> Sean Chittenden
>>> se... at chittenden.org <javascript:>
>> Hi,
>> 
>> There is a fresh thread about this in postgresql-hackers [1].
>> 
>> There are two parallel approaches suggested there, where one is to have an
>> option to continue using the old SYSV shared memory in PostgreSQL, and the
>> other is the suggestion that "somebody needs to hold the FreeBSD folks'
>> feet to the fire about when we can expect to see a fix from their side."
>> 
>> Looking at the original post in this thread, it seems to me that FreeBSD
>> has scalability problems beyond what the SYSV vs mmap change in PostgreSQL
>> introduces? Check my test of PostgreSQL 9.2 vs 9.3 on FreeBSD 10.0 at [1].
>> The difference between PG92 and PG93 is not huge, ~17%. The difference
>> between FreeBSD and the other OS:es in this thread's original post's
>> performance chart seems to be about a lot more?
>> 
>> Palle
>> 
>> [1]
>> http://www.postgresql.org/message-id/2AE143D2-87D3-4AD1-AC78-CE2258230C05@FreeBSD.org
>> _______________________________________________
>> freebsd-performance at freebsd.org mailing list
>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>> To unsubscribe, send any mail to "freebsd-performance-unsubscribe at freebsd.org"