Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YnT5l-0002oA-7u for pgsql-performance@arkaria.postgresql.org; Wed, 29 Apr 2015 14:36:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YnT5k-0004vf-FK for pgsql-performance@arkaria.postgresql.org; Wed, 29 Apr 2015 14:36:00 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YnT5i-0004vW-8P for pgsql-performance@postgresql.org; Wed, 29 Apr 2015 14:35:58 +0000 Received: from proofpoint1.mail.rice.edu ([128.42.201.100] helo=pp1.rice.edu) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1YnT5f-0006J1-8p for pgsql-performance@postgresql.org; Wed, 29 Apr 2015 14:35:56 +0000 Received: from pps.filterd (pp1.rice.edu [127.0.0.1]) by pp1.rice.edu (8.14.5/8.14.5) with SMTP id t3TEWAv6012426; Wed, 29 Apr 2015 09:35:44 -0500 Received: from mh10.mail.rice.edu (mh10.mail.rice.edu [128.42.201.30]) by pp1.rice.edu with ESMTP id 1u2v1yr4be-1; Wed, 29 Apr 2015 09:35:44 -0500 X-Virus-Scanned: by amavis-2.7.0 at mh10.mail.rice.edu, auth channel X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.rice.edu (aart.rice.edu [168.7.56.48]) by mh10.mail.rice.edu (Postfix) with ESMTP id C1E766140A; Wed, 29 Apr 2015 09:35:43 -0500 (CDT) Received: by aart.rice.edu (Postfix, from userid 18612) id B8CC6100832; Wed, 29 Apr 2015 09:35:43 -0500 (CDT) Date: Wed, 29 Apr 2015 09:35:43 -0500 From: "ktm@rice.edu" To: "Joshua D. Drake" Cc: Andres Freund , Jan Gunnar Dyrset , "pgsql-performance@postgresql.org" Subject: Re: PostgreSQL disk fragmentation causes performance problems on Windows Message-ID: <20150429143543.GQ3510@aart.rice.edu> References: <20150429080639.GC14535@awork2.anarazel.de> <20150429080854.GD14535@awork2.anarazel.de> <5540E588.9080507@commandprompt.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <5540E588.9080507@commandprompt.com> User-Agent: Mutt/1.5.20 (2009-12-10) X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 kscore.is_bulkscore=2.13717932240343e-14 kscore.compositescore=0 circleOfTrustscore=0 compositescore=0.283316031490134 suspectscore=0 recipient_domain_to_sender_totalscore=0 phishscore=0 bulkscore=0 kscore.is_spamscore=0 rbsscore=0.283316031490134 recipient_to_sender_totalscore=0 recipient_domain_to_sender_domain_totalscore=0 spamscore=0 recipient_to_sender_domain_totalscore=0 urlsuspectscore=0.0553703525497916 adultscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=7.0.1-1402240000 definitions=main-1504290183 X-Pg-Spam-Score: -4.2 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On Wed, Apr 29, 2015 at 07:07:04AM -0700, Joshua D. Drake wrote: > > On 04/29/2015 01:08 AM, Andres Freund wrote: > > >>Which OS and filesystem is this done on? Because many halfway modern > >>systems, like e.g ext4 and xfs, implement this in the background as > >>'delayed allocation'. > > > >Oh, it's in the subject. Stupid me, sorry for that. I'd consider testing > >how much better this behaves under a different operating system, as a > >shorter term relief. > > This is a known issue on the Windows platform. It is part of the > limitations of that environment. Linux/Solaris/FreeBSD do not suffer > from this issue in nearly the same manner. > > jD > You might consider a CLUSTER or VACUUM FULL to re-write the table with less fragmentation. Regards, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance