From owner-freebsd-database@FreeBSD.ORG Fri Jul 12 13:55:43 2013 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) by hub.freebsd.org (Postfix) with ESMTP id 635A1F3C for ; Fri, 12 Jul 2013 13:55:43 +0000 (UTC) (envelope-from freebsd-database@m.gmane.org) Received: from plane.gmane.org (plane.gmane.org [80.91.229.3]) by mx1.freebsd.org (Postfix) with ESMTP id 21DB317E4 for ; Fri, 12 Jul 2013 13:55:42 +0000 (UTC) Received: from list by plane.gmane.org with local (Exim 4.69) (envelope-from ) id 1Uxdor-0001z8-9I for freebsd-database@freebsd.org; Fri, 12 Jul 2013 15:55:33 +0200 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 ; Fri, 12 Jul 2013 15:55:33 +0200 Received: from ivoras by lara.cc.fer.hr with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Fri, 12 Jul 2013 15:55:33 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: freebsd-database@freebsd.org From: Ivan Voras Subject: Re: RAID10 stripe size and PostgreSQL performance Date: Fri, 12 Jul 2013 15:55:18 +0200 Lines: 82 Message-ID: References: Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="----enig2KBTORBKKVINQAUTXCBNH" 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:17.0) Gecko/20130322 Thunderbird/17.0.4 In-Reply-To: X-Enigmail-Version: 1.5.1 Cc: freebsd-fs@freebsd.org X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 12 Jul 2013 13:55:43 -0000 This is an OpenPGP/MIME signed message (RFC 4880 and 3156) ------enig2KBTORBKKVINQAUTXCBNH Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On 11/07/2013 23:07, Artem Naluzhnyy wrote: > On Mon, Jul 8, 2013 at 6:16 PM, Ivan Voras wrote: >> On 08/07/2013 14:40, Artem Naluzhnyy wrote: >>> Is this expected behavior with more than twice higher pgbench tps on >>> 1MB stripe size? >> >> No, it is not. >> >> For start, can you please repeat your benchmarks but with restarting t= he >> PostgreSQL server between each pgbench run? >=20 > Fresh OS installation without DB warning, reboot after pgbench DB > initialization (DB size: 26 GB) before benchmarking: >=20 > * 32 KB (half of the UFS bsize) - tps=3D198 >=20 > * 64 KB - tps=3D226 >=20 > * 128 KB (default for the RAID controller) - tps=3D298 >=20 > * 1 MB (max for the RAID controller) - tps=3D347 I just looked at your RAID configuration at http://pastebin.com/F8uZEZdm and you have a mirror of stripes (RAID-01) nor a stripe of mirrors (RAID-10). And apparently, is I parse your configuration correctly, you have a 1M stripe in the MIRROR part of the RAID, and an unknown stripe size in the STRIPE part. Mirroring may halp your read performance, but will not help your write performance. If you are running pgbench with default settings, and with your test database size which can fit in RAM, you probably cache all reads eventually and then writes become the bottleneck. >> Also, you should make sure that the database is located on the same >> location on the disk platters by e.g. creating a small partition which= >> is about 150% larger than your pgbench database (and your pgbench >> database should be at least 2x larger than your RAM, if you are going = to >> benchmark IO and not memory caches), which is located at the same >> position (byte offset) in your RAID10 volume. >=20 > Unfortunately it's not that easy to make a custom partitioning. > However, all tests were done just after the server reinstallation > using exactly the same order of commands. I'm not saying that your production database should be on a custom partition, but your pgbench test database (and the file for the following test) should. Anyway, could you please do one more test: 1) create a large file with "dd if=3D/dev/zero of=3Dfile bs=3D1m count=3D= 48000" 2) install /usr/ports/benchmarks/randomio 3) run "randomio file 8 0.5 1 8192 10 10" =2E.. and report the results. ------enig2KBTORBKKVINQAUTXCBNH 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 Thunderbird - http://www.enigmail.net/ iEYEARECAAYFAlHgCsYACgkQ/QjVBj3/HSyGngCgk4lxgvQD696qqjJs86ozGIzX o/cAoKT64CMmalnxC2PvJsvSxmGG/FCA =PDLg -----END PGP SIGNATURE----- ------enig2KBTORBKKVINQAUTXCBNH--