From owner-freebsd-performance@FreeBSD.ORG Wed May 21 17:17:48 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 AA70C108; Wed, 21 May 2014 17:17:48 +0000 (UTC) Received: from mail01.lax1.stackjet.com (mon01.lax1.stackjet.com [174.136.104.178]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by mx1.freebsd.org (Postfix) with ESMTPS id 7D24D2B4F; Wed, 21 May 2014 17:17:47 +0000 (UTC) Received: from hormesis.group.on (localhost [127.0.0.1]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) (Authenticated sender: sean@chittenden.org) by mail01.lax1.stackjet.com (Postfix) with ESMTPSA id A9D1C3E8DDB; Wed, 21 May 2014 10:17:38 -0700 (PDT) Received: from hormesis.group.on ([64.125.69.70] helo=hormesis.group.on) by ASSP.nospam with SMTPS(ECDHE-RSA-AES256-SHA) (2.4.2); 21 May 2014 10:17:35 -0700 Content-Type: text/plain; charset=iso-8859-1 Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.2\)) Subject: Re: FreeBSD 10 and PostgreSQL 9.3 scalability issues From: Sean Chittenden In-Reply-To: <1473AF7C-B190-4CD4-B611-BA4090A081CB@pingpong.net> Date: Wed, 21 May 2014 10:17:37 -0700 Content-Transfer-Encoding: quoted-printable Message-Id: 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> <1473AF7C-B190-4CD4-B611-BA4090A081CB@pingpong.net> To: Palle Girgensohn X-Mailer: Apple Mail (2.1878.2) X-Assp-Version: 2.4.2(14097) on ASSP.nospam X-Assp-ID: ASSP.nospam m1-92658-01683 X-Assp-Session: 8437F81F8 (mail 1) X-Assp-Envelope-From: sean@chittenden.org X-Assp-Intended-For: girgen@pingpong.net X-Assp-Intended-For: gezeala@gmail.com X-Assp-Intended-For: adrian@freebsd.org X-Assp-Intended-For: bsdmailinglist@googlegroups.com X-Assp-Intended-For: janda.petr@gmail.com X-Assp-Intended-For: killing@multiplay.co.uk X-Assp-Intended-For: freebsd-performance@freebsd.org X-Assp-Client-TLS: yes X-Assp-Server-TLS: yes Cc: Adrian Chadd , "bsdmailinglist@googlegroups.com" , Steven Hartland , FreeBSD Mailing Lists , Petr Janda 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 17:17:48 -0000 > I did some tests with zfs, and results where appallingly bad, but that = was with db size > ram.=20 >=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 I have some hardware that I ran with last week wherein I was *not* able = to reproduce any performance difference between ZFS and UFS2. On both = UFS2 and ZFS I was seeing the same performance when using a a RAID10 / = set of mirrors. I talked with the Dragonfly folk who originally = performed these tests and they also saw the same thing: no real = performance difference between ZFS and UFS. I ran my tests on a host = with 16 drive, 10K SAS, 192GB RAM. I also created a kernel profiling = image and ran the 20 concurrent user test under kgprof(1), dtrace, and = pmcstat and have the results available: http://people.freebsd.org/~seanc/pg9.3-fbsd10-profiling/ There are some investigations that are ongoing as a result of these = findings. The dfly methodology was observed when generating these = results. Stay tuned. -sc -- Sean Chittenden sean@chittenden.org >=20 > Palle >=20 >> 21 maj 2014 kl. 18:33 skrev Gezeala M. Bacu=F1o 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=F1o II = : >>>=20 >>>=20 >>> Do you guys have any updates on this? >>>=20 >>> -- >>>=20 >>> regards >>>=20 >>> gezeala bacu=F1o II >>>=20 >>>=20 >>> On Tue, Apr 22, 2014 at 11:52 PM, Palle Girgensohn = wrote: >>>=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/4250b96= 1/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 if >>>>>>> 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-CE2258230C05@= 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" > _______________________________________________ > 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"