From owner-freebsd-performance@FreeBSD.ORG Wed May 21 16:50:54 2014 Return-Path: Delivered-To: freebsd-performance@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) (using TLSv1 with cipher ADH-AES256-SHA (256/256 bits)) (No client certificate requested) by hub.freebsd.org (Postfix) with ESMTPS id 7FAE56DE; Wed, 21 May 2014 16:50:54 +0000 (UTC) Received: from melon.pingpong.net (melon.pingpong.net [79.136.116.200]) by mx1.freebsd.org (Postfix) with ESMTP id 2866A28DD; Wed, 21 May 2014 16:50:53 +0000 (UTC) Received: from [10.19.39.26] (c-5eeaaaa2-74736162.cust.telenor.se [94.234.170.162]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by melon.pingpong.net (Postfix) with ESMTPSA id 9EA7F378E0; Wed, 21 May 2014 18:50:51 +0200 (CEST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (1.0) Subject: Re: FreeBSD 10 and PostgreSQL 9.3 scalability issues From: Palle Girgensohn X-Mailer: iPhone Mail (11D201) In-Reply-To: Date: Wed, 21 May 2014 18:50:50 +0200 Content-Transfer-Encoding: quoted-printable Message-Id: <1473AF7C-B190-4CD4-B611-BA4090A081CB@pingpong.net> References: <5327B9B7.3050103@gmail.com> <2610F490C952470C9D15999550F67068@multiplay.co.uk> <532A192A.1070509@gmail.com> <572540F9-13E4-4BA9-88AE-5F47FB19450A@pingpong.net> <1BC3D447-2044-4AB8-B183-B83957BC9112@pingpong.net> To: =?utf-8?Q?"Gezeala_M._Bacu=C3=B1o_II"?= Cc: Adrian Chadd , "bsdmailinglist@googlegroups.com" , Petr Janda , Steven Hartland , FreeBSD Mailing Lists , Sean Chittenden X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 21 May 2014 16:50:54 -0000 I did some tests with zfs, and results where appallingly bad, but that was w= ith db size > ram.=20 I think the model used by PostgreSQL, as most databases, are very disk block= centric. Using zfs makes it hard to get good performance. But this was some= time ago, maybe things have improved.=20 Palle > 21 maj 2014 kl. 18:33 skrev Gezeala M. Bacu=C3=B1o II := >=20 > Gotcha. >=20 > I've been testing using pgbench on FreeBSD 9.0 release + ZFS + pg 9.3.. I > can reach the freebsd 10 stats on the pdf files if the dataset gets way lower if the dataset >RAM. Test was done on pools without L2ARC > and with/without compression. I also remember increasing a vm.pmap sysctl.= > I'm out of the office right now sick so I can't provide the stats but yes,= > with mmap it is pretty bad.. >=20 > Keep us in the loop. I'd like to help on getting the performance data they= > need. >> On May 20, 2014 11:16 PM, "Palle Girgensohn" wrote:= >>=20 >> I got no response about how to grab performance data. >>=20 >> The PostgreSQL team is also making an effort by setting up machines >> dedicated to performance measuring and tuning. >>=20 >> And freebsd guys and PostgreSQL guys are apparently meeting at pgcon this= >> week. >>=20 >> We'll see where that leads. >>=20 >> In the mean time, if I for some pointers on how to grab performance data,= >> I could do some more tests. >>=20 >> Palle >>=20 >> 21 maj 2014 kl. 02:13 skrev Gezeala M. Bacu=C3=B1o II = : >>=20 >>=20 >> Do you guys have any updates on this? >>=20 >> -- >>=20 >> regards >>=20 >> gezeala bacu=C3=B1o II >>=20 >>=20 >> On Tue, Apr 22, 2014 at 11:52 PM, Palle Girgensohn w= rote: >>=20 >>>=20 >>>=20 >>>> 23 apr 2014 kl. 01:04 skrev Adrian Chadd : >>>>=20 >>>> Hi, >>>>=20 >>>> Are you able to repeat these tests (for both 9.2 and 9.3) whilst >>>> grabbing some performance data from lock profiling and hwpmc? >>>=20 >>> I sure can, but I'd love some pointers as to how this is done. Please? := -) >>>=20 >>>>=20 >>>> 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. >>>=20 >>>=20 >>> 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. >>>=20 >>> The initial post in *this* thread does however indicate that freebsd >>> performs poorer than Linux and dragonflybsd, but does not really compare= >>> PostgreSQL versions. >>>=20 >>> Just so we're not pursuing the wrong problem here, let's be open minded >>> about the definition of the problem. :-) >>>=20 >>>>=20 >>>> What about with more clients? 64? 128? 256? >>>=20 >>> My test went to 80. I can go higher as well, though other sources say 50= >>> is a reasonable limit for PostgreSQL. >>>=20 >>> Palle >>>=20 >>>=20 >>>>=20 >>>>=20 >>>> Thanks! >>>>=20 >>>>=20 >>>>=20 >>>> -a >>>>=20 >>>>=20 >>>>> On 21 April 2014 14:11, Palle Girgensohn wrote: >>>>>=20 >>>>>=20 >>>>>> Den torsdagen den 20:e mars 2014 kl. 00:33:10 UTC+1 skrev Sean >>> Chittenden: >>>>>>=20 >>>>>>> As far as I know, the test was done on both UFS2 and ZFS and the >>>>>>> difference was marginal. >>>>>>=20 >>>>>> 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 >>>>>>=20 >>>>>> http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN115039 >>>>>>=20 >>>>>>=20 >>>>>>>>> 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/4250b= 961/attachment-0001.pdf >>>>>>>>=20 >>>>>>>>=20 >>>>>>>> Do you have the ability to test with FreeBSD 8.x and 9.x to see if >>> this >>>>>> is >>>>>>>> regression? >>>>>>>>=20 >>>>>>>> Also you don't mention the FS used in each case, so I'm wondering i= f >>>>>> you >>>>>>>> used a ZFS install of FreeBSD which could help to explain things. >>>>>>=20 >>>>>>=20 >>>>>> -- >>>>>> Sean Chittenden >>>>>> se...@chittenden.org >>>>> Hi, >>>>>=20 >>>>> There is a fresh thread about this in postgresql-hackers [1]. >>>>>=20 >>>>> 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." >>>>>=20 >>>>> 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? >>>>>=20 >>>>> Palle >>>>>=20 >>>>> [1] >>> http://www.postgresql.org/message-id/2AE143D2-87D3-4AD1-AC78-CE2258230C0= 5@FreeBSD.org >>>>> _______________________________________________ >>>>> freebsd-performance@freebsd.org mailing list >>>>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance >>>>> To unsubscribe, send any mail to " >>> freebsd-performance-unsubscribe@freebsd.org" >>> _______________________________________________ >>> freebsd-performance@freebsd.org mailing list >>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance >>> To unsubscribe, send any mail to " >>> freebsd-performance-unsubscribe@freebsd.org" > _______________________________________________ > freebsd-performance@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-performance > To unsubscribe, send any mail to "freebsd-performance-unsubscribe@freebsd.= org"