From owner-freebsd-hackers@FreeBSD.ORG Tue Mar 5 17:27:42 2013 Return-Path: Delivered-To: freebsd-hackers@freebsd.org Received: from mx1.freebsd.org (mx1.FreeBSD.org [8.8.178.115]) by hub.freebsd.org (Postfix) with ESMTP id DF4CED34 for ; Tue, 5 Mar 2013 17:27:42 +0000 (UTC) (envelope-from freebsd-hackers@m.gmane.org) Received: from plane.gmane.org (plane.gmane.org [80.91.229.3]) by mx1.freebsd.org (Postfix) with ESMTP id 73A35149 for ; Tue, 5 Mar 2013 17:27:42 +0000 (UTC) Received: from list by plane.gmane.org with local (Exim 4.69) (envelope-from ) id 1UCvQ3-00070i-Gs for freebsd-hackers@freebsd.org; Tue, 05 Mar 2013 18:12:51 +0100 Received: from lara.cc.fer.hr ([161.53.72.113]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 05 Mar 2013 18:12:51 +0100 Received: from ivoras by lara.cc.fer.hr with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 05 Mar 2013 18:12:51 +0100 X-Injected-Via-Gmane: http://gmane.org/ To: freebsd-hackers@freebsd.org From: Ivan Voras Subject: Re: MySQL Data_free = 0 Slowness (appending to files slow?) Date: Tue, 05 Mar 2013 18:12:19 +0100 Lines: 117 Message-ID: References: <20130304155020.77e470da@pleiades.nextvenue.com> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig90C1B9441DFB393EA253B2B0" X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: lara.cc.fer.hr User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:14.0) Gecko/20120812 Thunderbird/14.0 In-Reply-To: <20130304155020.77e470da@pleiades.nextvenue.com> X-Enigmail-Version: 1.4.3 X-BeenThere: freebsd-hackers@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list List-Id: Technical Discussions relating to FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 05 Mar 2013 17:27:42 -0000 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig90C1B9441DFB393EA253B2B0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On 04/03/2013 21:50, Nick Evans wrote: > iostat (slow case): >=20 > tty mfid0 mfid1 cd0 = cpu > tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy = in id > 0 479 0.00 0 0.00 80.00 2 0.16 0.00 0 0.00 11 0 1 = 0 88 > 0 600 0.00 0 0.00 32.00 3 0.09 0.00 0 0.00 13 0 1 = 0 86 > 0 553 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 13 0 1 = 0 85 > 0 1631 0.00 0 0.00 38.40 5 0.19 0.00 0 0.00 14 0 1 = 0 85 > 0 665 0.00 0 0.00 32.00 4 0.12 0.00 0 0.00 14 0 1 = 0 85 > 0 737 0.00 0 0.00 32.00 1 0.03 0.00 0 0.00 15 0 1 = 0 84 > 0 485 32.00 2 0.06 127.05 770 95.56 0.00 0 0.00 13 0 1= 0 85 > 0 624 0.00 0 0.00 126.95 61 7.55 0.00 0 0.00 13 0 1= 0 85 > 0 1586 0.00 0 0.00 32.00 1 0.03 0.00 0 0.00 14 0 1 = 0 85 > 0 565 0.00 0 0.00 32.00 1 0.03 0.00 0 0.00 14 0 1 = 0 85 > 0 488 0.00 0 0.00 32.00 2 0.06 0.00 0 0.00 14 0 1 = 0 85 > 0 515 0.00 0 0.00 32.00 1 0.03 0.00 0 0.00 13 0 1 = 0 85 > iostat (fast case): >=20 > tty mfid0 mfid1 cd0 = cpu > tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy = in id > 0 542 0.00 0 0.00 32.00 300 9.37 0.00 0 0.00 14 0 6 = 1 79 > 0 540 32.00 10 0.31 32.00 318 9.93 0.00 0 0.00 14 0 5 = 1 79 > 0 688 0.00 0 0.00 32.00 331 10.33 0.00 0 0.00 14 0 5 = 1 79 > 0 1863 8.00 2 0.02 32.00 300 9.37 0.00 0 0.00 14 0 6 = 1 79 > 0 720 0.00 0 0.00 32.00 306 9.55 0.00 0 0.00 14 0 5 = 1 80 > 0 565 0.00 0 0.00 32.00 299 9.33 0.00 0 0.00 14 0 6 = 0 79 > 0 1470 0.00 0 0.00 116.51 3413 388.27 0.00 0 0.00 10 0 = 5 1 83 > 0 553 0.00 0 0.00 32.00 303 9.46 0.00 0 0.00 13 0 7 = 1 79 > 0 1730 0.00 0 0.00 32.00 337 10.52 0.00 0 0.00 14 0 6 = 0 80 > 0 649 0.00 0 0.00 32.00 342 10.68 0.00 0 0.00 14 0 6 = 1 79 > 0 901 0.00 0 0.00 32.00 317 9.90 0.00 0 0.00 14 0 6 = 1 80 > 0 1654 0.00 0 0.00 32.00 328 10.24 0.00 0 0.00 15 0 5 = 1 79 I don't know if you've noticed, but: 1) In your slow case, the load is almost entirely in userspace (USR), i.e. mysql, while in the fast case, the kernel load (SYS) is 5x-6x greater. Unless the first case actually excercises your disks more (unlikely since you have almost no IO load at all) it means that it's mysql that is slow, not the kernel. 2) Ordinary iostat doesn't show the disk busy-time estimation. You need to use "-x" (e.g. "iostat -x 1") to see the service time and busy percentage stats. 3) you seem to have bursts of traffic, probably writing, which may be caused by the file system IFF mysql is not doing anything to fsync the data sooner (i.e. you have everything in a single transaction), but the burst in the "fast" case is 388 MB while the burst in the "slow" case is 100 MB 4) your benchmark seems to be single-threaded 5) You have a lot of 32 KB transactions, which probably means you have created the file system in 9.x with the default options, leading to 32 KB blocks. This is probably not the source of your current problems, but in the future you might want to match the block size to innodb's block size (16 KiB). You should also do that on the RAID array level and match the block alignments. 6) Are you using innodb_file_per_table? You probably should. --------------enig90C1B9441DFB393EA253B2B0 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.19 (FreeBSD) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAlE2J3QACgkQ/QjVBj3/HSwF6ACgmOuvaaIfoX44ZWWvfyLbUugd qVEAn1CmbceGx7OfDdLpZfNGFmd9G9+D =D2ru -----END PGP SIGNATURE----- --------------enig90C1B9441DFB393EA253B2B0--