From owner-freebsd-arch@FreeBSD.ORG Sat Aug 4 08:05:36 2007 Return-Path: Delivered-To: arch@FreeBSD.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 9C79416A421; Sat, 4 Aug 2007 08:05:36 +0000 (UTC) (envelope-from kris@obsecurity.org) Received: from elvis.mu.org (elvis.mu.org [192.203.228.196]) by mx1.freebsd.org (Postfix) with ESMTP id A585613C46C; Sat, 4 Aug 2007 08:05:36 +0000 (UTC) (envelope-from kris@obsecurity.org) Received: from rot26.obsecurity.org (elvis.mu.org [192.203.228.196]) by elvis.mu.org (Postfix) with ESMTP id 8349F1A4D7E; Sat, 4 Aug 2007 01:04:57 -0700 (PDT) Received: by rot26.obsecurity.org (Postfix, from userid 1001) id 52514C0F6; Sat, 4 Aug 2007 04:05:35 -0400 (EDT) Date: Sat, 4 Aug 2007 04:05:35 -0400 From: Kris Kennaway To: performance@FreeBSD.org Message-ID: <20070804080535.GA3952@rot26.obsecurity.org> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="W/nzBZO5zC0uMSeA" Content-Disposition: inline User-Agent: Mutt/1.4.2.3i Cc: arch@FreeBSD.org Subject: read-write SQL performance X-BeenThere: freebsd-arch@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Discussion related to FreeBSD architecture List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sat, 04 Aug 2007 08:05:36 -0000 --W/nzBZO5zC0uMSeA Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 --W/nzBZO5zC0uMSeA Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.4 (FreeBSD) iD8DBQFGtDNPWry0BWjoQKURArsaAJ0TBneLAFm0JZl16qo/wpeCbxJ7NgCgwHcg NIrOTxurHgldrKsD9BdiPLY= =xNb8 -----END PGP SIGNATURE----- --W/nzBZO5zC0uMSeA--