Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id D20BB1648186 for ; Fri, 30 Mar 2012 22:32:45 -0300 (ADT) Received: from [2001:1528:137::f1] (helo=mail.gransy.com) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SDnBL-0005go-Nn for pgsql-performance@postgresql.org; Sat, 31 Mar 2012 01:32:44 +0000 Received: from antispam.gransy.com (antispam.gransy.com [77.78.104.124]) by mail.gransy.com (Postfix) with ESMTP id 85CBD508652 for ; Sat, 31 Mar 2012 03:32:31 +0200 (CEST) Received: from mail.gransy.com ([82.208.29.194]) by antispam.gransy.com (antispam.gransy.com [77.78.104.124]) (amavisd-new, port 10024) with ESMTP id r1NwPn+X2H1d for ; Sat, 31 Mar 2012 03:32:31 +0200 (CEST) Received: by mail.gransy.com (Postfix, from userid 5000) id 7125F508675; Sat, 31 Mar 2012 03:32:31 +0200 (CEST) Received: from [192.168.1.237] (ip-78-45-133-255.net.upcbroadband.cz [78.45.133.255]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.gransy.com (Postfix) with ESMTPSA id 4A7CD508652 for ; Sat, 31 Mar 2012 03:32:31 +0200 (CEST) Message-ID: <4F765EAF.3080406@fuzzy.cz> Date: Sat, 31 Mar 2012 03:32:31 +0200 From: Tomas Vondra User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:11.0) Gecko/20120312 Thunderbird/11.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Tablespaces on a raid configuration References: <20120330145348.GS2533@aart.rice.edu> In-Reply-To: <20120330145348.GS2533@aart.rice.edu> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Host-Lookup-Failed: Reverse DNS lookup failed for 2001:1528:137::f1 (failed) X-Pg-Spam-Score: -1.1 (-) X-Archive-Number: 201203/194 X-Sequence-Number: 46644 On 30.3.2012 16:53, ktm@rice.edu wrote: > On Fri, Mar 30, 2012 at 02:45:36PM +0000, Campbell, Lance wrote: >> PostgreSQL 9.0.x >> When PostgreSQL storage is using a relatively large raid 5 or 6 array is there any value in having your tables distributed across multiple tablespaces if those tablespaces will exists on the same raid array? I understand the value if you were to have the tablespaces on different raid arrays. But what about on the same one? >> >> >> Thanks, >> >> Lance Campbell >> Software Architect >> Web Services at Public Affairs >> 217-333-0382 >> > > I have seen previous discussions about using different filesystems versus > a single filesystem and one advantage that multiple tablespaces have is > that an fsync on one table/tablespace would not block or be blocked by > an fsync on a different table/tablespace at the OS level. No. What matters is a physical device. If you have a drive that can do just 120 seeks/fsyncs per second (or more, depends on the speed), then even if you divide that into multiple filesystems you're still stuck with the total of 120 seeks. I.e. splitting that into 10 partitions won't give you 1200 seeks ... OP mentions he's using RAID-5 or 6 - that's pretty bad because it effectively creates one huge device. Splitting this into filesystem will behave rather bad, because all the drives are rather tightly coupled because of to the parity. If you can create the filesystems on different devices, then you're golden and this can really help. And it's not just about fsync operations - WAL is written in sequential manner. By placing it on the same device as data files you're effectively forcing it to be written randomly, because the the database has to write a WAL record, seeks somewhere else to read something, etc. Tomas