Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id BC96E6330B8 for ; Wed, 28 Jul 2010 12:17:06 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 29426-04 for ; Wed, 28 Jul 2010 15:16:55 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f46.google.com (mail-ew0-f46.google.com [209.85.215.46]) by mail.postgresql.org (Postfix) with ESMTP id 2B08E632E10 for ; Wed, 28 Jul 2010 12:16:54 -0300 (ADT) Received: by ewy23 with SMTP id 23so1795534ewy.19 for ; Wed, 28 Jul 2010 08:16:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:references:in-reply-to :content-type:content-transfer-encoding; bh=NxVBb0Zp9CYe/bN8AKbN2RpS3EnGeuT4JyW8MX44Hsg=; b=vnTIa7gpHJZWWO0gcgoFkoFG0I54ItSPEl8I0haK1PnGsuKBg0mJRgwhb+9WNJLbBs pJ/GNcGhSZwhSsuEIIFpSXbz4FqyyIfW8BrrPVipy6S8roDC21yKOUacOCd7pYXTZLn4 /AAHzWNSBIQ5uPCX/aFtSM/ueZOxjHHbkQLw4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type:content-transfer-encoding; b=G2KT8KEKZ7Y5kdzbhhhZUkNB96mAlX2WHQQUM6Bgt82Zw/8ohkw/bqejPFvIscLcsz +pdrKSmWhMTgWOGh0TUf4pqig6JQhASLFCCPhdNwVFIrqWAXj1doi2x1CA7SauFbdVuW 7yhcGQfpFlSROU1LRpzxYVflmzQ2wY7VXH9aw= Received: by 10.14.37.78 with SMTP id x54mr2303918eea.8.1280330212650; Wed, 28 Jul 2010 08:16:52 -0700 (PDT) Received: from [0.0.0.0] (5357DA75.cable.casema.nl [83.87.218.117]) by mx.google.com with ESMTPS id a48sm9663333eei.19.2010.07.28.08.16.51 (version=TLSv1/SSLv3 cipher=RC4-MD5); Wed, 28 Jul 2010 08:16:51 -0700 (PDT) Message-ID: <4C504A43.3000304@gmail.com> Date: Wed, 28 Jul 2010 17:18:27 +0200 From: Yeb Havinga User-Agent: Thunderbird 2.0.0.24 (Windows/20100228) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Testing Sandforce SSD References: <4C4A9452.9070100@gmail.com> <4C4B1F84.5030306@2ndquadrant.com> <4C4C83DF.3010703@gmail.com> <4C4C8CCC.7080406@gmail.com> <4C4D6396.5020707@gmail.com> <4C4DAAE8.3060501@2ndquadrant.com> <4C4DE50B.1060102@gmail.com> <4C4DE5C9.8090004@2ndquadrant.com> <4C503473.5080105@gmail.com> In-Reply-To: <4C503473.5080105@gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8 X-Spam-Level: X-Archive-Number: 201007/435 X-Sequence-Number: 39824 Yeb Havinga wrote: > Michael Stone wrote: >> On Mon, Jul 26, 2010 at 03:23:20PM -0600, Greg Spiegelberg wrote: >>> I know I'm talking development now but is there a case for a pg_xlog >>> block >>> device to remove the file system overhead and guaranteeing your data is >>> written sequentially every time? >> >> If you dedicate a partition to xlog, you already get that in practice >> with no extra devlopment. > Due to the LBA remapping of the SSD, I'm not sure of putting files > that are sequentially written in a different partition (together with > e.g. tables) would make a difference: in the end the SSD will have a > set new blocks in it's buffer and somehow arrange them into sets of > 128KB of 256KB writes for the flash chips. See also > http://www.anandtech.com/show/2899/2 > > But I ran out of ideas to test, so I'm going to test it anyway. Same machine config as mentioned before, with data and xlog on separate partitions, ext3 with barrier off (save on this SSD). pgbench -c 10 -M prepared -T 3600 -l test starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 300 query mode: prepared number of clients: 10 duration: 3600 s number of transactions actually processed: 10856359 tps = 3015.560252 (including connections establishing) tps = 3015.575739 (excluding connections establishing) This is about 25% faster than data and xlog combined on the same filesystem. Below is output from iostat -xk 1 -p /dev/sda, which shows each second per partition statistics. sda2 is data, sda3 is xlog In the third second a checkpoint seems to start. avg-cpu: %user %nice %system %iowait %steal %idle 63.50 0.00 30.50 2.50 0.00 3.50 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 6518.00 36.00 2211.00 148.00 35524.00 31.75 0.28 0.12 0.11 25.00 sda1 0.00 2.00 0.00 5.00 0.00 636.00 254.40 0.03 6.00 2.00 1.00 sda2 0.00 218.00 36.00 40.00 148.00 1032.00 31.05 0.00 0.00 0.00 0.00 sda3 0.00 6298.00 0.00 2166.00 0.00 33856.00 31.26 0.25 0.12 0.12 25.00 avg-cpu: %user %nice %system %iowait %steal %idle 60.50 0.00 37.50 0.50 0.00 1.50 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 6514.00 33.00 2283.00 140.00 35188.00 30.51 0.32 0.14 0.13 29.00 sda1 0.00 0.00 0.00 3.00 0.00 12.00 8.00 0.00 0.00 0.00 0.00 sda2 0.00 0.00 33.00 2.00 140.00 8.00 8.46 0.03 0.86 0.29 1.00 sda3 0.00 6514.00 0.00 2278.00 0.00 35168.00 30.88 0.29 0.13 0.13 29.00 avg-cpu: %user %nice %system %iowait %steal %idle 33.00 0.00 34.00 18.00 0.00 15.00 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 3782.00 7.00 7235.00 28.00 44068.00 12.18 69.52 9.46 0.09 62.00 sda1 0.00 0.00 0.00 1.00 0.00 4.00 8.00 0.00 0.00 0.00 0.00 sda2 0.00 322.00 7.00 6018.00 28.00 25360.00 8.43 69.22 11.33 0.08 47.00 sda3 0.00 3460.00 0.00 1222.00 0.00 18728.00 30.65 0.30 0.25 0.25 30.00 avg-cpu: %user %nice %system %iowait %steal %idle 9.00 0.00 36.00 22.50 0.00 32.50 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 1079.00 3.00 11110.00 12.00 49060.00 8.83 120.64 10.95 0.08 86.00 sda1 0.00 2.00 0.00 2.00 0.00 320.00 320.00 0.12 60.00 35.00 7.00 sda2 0.00 30.00 3.00 10739.00 12.00 43076.00 8.02 120.49 11.30 0.08 83.00 sda3 0.00 1047.00 0.00 363.00 0.00 5640.00 31.07 0.03 0.08 0.08 3.00 avg-cpu: %user %nice %system %iowait %steal %idle 62.00 0.00 31.00 2.00 0.00 5.00 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 6267.00 51.00 2493.00 208.00 35040.00 27.71 1.80 0.71 0.12 31.00 sda1 0.00 0.00 0.00 3.00 0.00 12.00 8.00 0.00 0.00 0.00 0.00 sda2 0.00 123.00 51.00 344.00 208.00 1868.00 10.51 1.50 3.80 0.10 4.00 sda3 0.00 6144.00 0.00 2146.00 0.00 33160.00 30.90 0.30 0.14 0.14 30.00