From owner-freebsd-performance@FreeBSD.ORG Sat Aug 4 20:44:48 2007 Return-Path: Delivered-To: performance@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 20C2B16A419 for ; Sat, 4 Aug 2007 20:44:48 +0000 (UTC) (envelope-from almarrie@gmail.com) Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.241]) by mx1.freebsd.org (Postfix) with ESMTP id CF1AC13C468 for ; Sat, 4 Aug 2007 20:44:47 +0000 (UTC) (envelope-from almarrie@gmail.com) Received: by an-out-0708.google.com with SMTP id c14so260668anc for ; Sat, 04 Aug 2007 13:44:47 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=AsqBLMkKarPCF4w4noo3JRiKG6gOt9WLhPHQUs5rc/vdCnmUn1Q/YQwWP7ZxbtAYnxO5BRET29/ZRuoK4NrWCTlwKjqFs4ufR8lbASYoLRZ4kP4K4ECDabCPUZIYoLd33tTkdD9GRLMGBK343rtTRAAhwUZ279t2QS1Uhl3oADI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=cNUSNTe9VsI+LASPQOokydb5SmjxGxEfeubbSwQjMsaXTcTx1PsbmCwrTumLpOCpO8NWNHVbbGmxVvEVG78bHrACKlwMopxRIhGsN7Tp2Yk6xLWRNF2fZypZgvFFTR/Zuu8yow17zJ3f73y577aYjEmUAF7EjeoPtKCGkcgxEiU= Received: by 10.100.168.13 with SMTP id q13mr2369906ane.1186258840847; Sat, 04 Aug 2007 13:20:40 -0700 (PDT) Received: by 10.100.9.14 with HTTP; Sat, 4 Aug 2007 13:20:40 -0700 (PDT) Message-ID: <499c70c0708041320r1f51cb3qe6f05376cfb8a470@mail.gmail.com> Date: Sat, 4 Aug 2007 23:20:40 +0300 From: "Abdullah Ibn Hamad Al-Marri" To: "Kris Kennaway" In-Reply-To: <20070804080535.GA3952@rot26.obsecurity.org> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20070804080535.GA3952@rot26.obsecurity.org> Cc: Greg 'groggy' Lehey , arch@freebsd.org, performance@freebsd.org Subject: Re: read-write SQL performance X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sat, 04 Aug 2007 20:44:48 -0000 On 8/4/07, Kris Kennaway wrote: > I did some benchmarking of sysbench in read-write mode (previous tests > have focused on read-only mode). The reason for this is that the disk > hardware in my 8-core test system is slow (single disk) and is too > easily saturated. In fact mysql and pgsql have identical performance > when writing to disk. In other words, I seem to be mostly > benchmarking the disk performance and not database or kernel > performance. > > Faster disk hardware is necessary to explore database performance > differences or kernel bottlenecks. An upper bound on possible > read-write performance comes from using a memory disk instead of > physical disk hardware. I replicated the databases onto a suitably > large (2gb) tmpfs and reran the tests together with some mutex > profiling. > > Results are here: > > http://obsecurity.dyndns.org/sysbench-write.png > > There are a couple of interesting features. > > mysql has better peak performance than pgsql, but then quickly falls > in the toilet. Profiling indicates that at peak there is some > contention on lockmgr locks and the proc lock, but most of the > contention is in userland (i.e. within mysql itself). At higher loads > the bottleneck is overwhelmingly within mysql (and the system is > actually 90-100% idle). This seems to be a serious scaling problem > within mysql. > > Peak pgsql performance is lower than mysql, but there is comparatively > little degradation at higher loads. Profiling shows that the dominant > bottleneck at all workloads is lockmgr. > > Fortunately there is a lockmgr rewrite in progress by Attilio for SoC, > so there is great scope for performance improvements to pgsql. > Significant mysql performance improvements may require fundamental > architectural work by the mysql developers. > > Kris Maybe Greg would be interested in the MySQL issues? -- Regards, -Abdullah Ibn Hamad Al-Marri Arab Portal http://www.WeArab.Net/