Skip site navigation (1) Skip section navigation (2)

Re: Block at a time ...

From: Scott Carey <scott(at)richrelevance(dot)com>
To: Craig James <craig_james(at)emolecules(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Block at a time ...
Date: 2010-03-22 18:47:43
Message-ID: D254FE0F-9B3A-432D-AA38-C8B526A7ECEB@richrelevance.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mar 17, 2010, at 9:41 AM, Craig James wrote:

> On 3/17/10 2:52 AM, Greg Stark wrote:
>> On Wed, Mar 17, 2010 at 7:32 AM, Pierre C<lists(at)peufeu(dot)com>  wrote:
>>>> I was thinking in something like that, except that the factor I'd use
>>>> would be something like 50% or 100% of current size, capped at (say) 1 GB.
>> 
>> This turns out to be a bad idea. One of the first thing Oracle DBAs
>> are told to do is change this default setting to allocate some
>> reasonably large fixed size rather than scaling upwards.
>> 
>> This might be mostly due to Oracle's extent-based space management but
>> I'm not so sure. Recall that the filesystem is probably doing some
>> rounding itself. If you allocate 120kB it's probably allocating 128kB
>> itself anyways. Having two layers rounding up will result in odd
>> behaviour.
>> 
>> In any case I was planning on doing this a while back. Then I ran some
>> experiments and couldn't actually demonstrate any problem. ext2 seems
>> to do a perfectly reasonable job of avoiding this problem. All the
>> files were mostly large contiguous blocks after running some tests --
>> IIRC running pgbench.
> 
> This is one of the more-or-less solved problems in Unix/Linux.  Ext* file systems have a "reserve" usually of 10% of the disk space that nobody except root can use.  It's not for root, it's because with 10% of the disk free, you can almost always do a decent job of allocating contiguous blocks and get good performance.  Unless Postgres has some weird problem that Linux has never seen before (and that wouldn't be unprecedented...), there's probably no need to fool with file-allocation strategies.
> 
> Craig
> 

Its fairly easy to break.  Just do a parallel import with say, 16 concurrent tables being written to at once.  Result?  Fragmented tables.

> -- 
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


In response to

Responses

pgsql-performance by date

Next:From: Christian BrinkDate: 2010-03-22 19:09:11
Subject: PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster
Previous:From: Dave CrookeDate: 2010-03-22 15:32:01
Subject: Re: mysql to postgresql, performance questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group