Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 09EB979838C for ; Tue, 16 Mar 2010 21:28:46 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 53784-07-6 for ; Wed, 17 Mar 2010 00:28:24 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f221.google.com (mail-bw0-f221.google.com [209.85.218.221]) by mail.postgresql.org (Postfix) with ESMTP id 950EB7983A9 for ; Tue, 16 Mar 2010 20:58:53 -0300 (ADT) Received: by bwz21 with SMTP id 21so583082bwz.35 for ; Tue, 16 Mar 2010 16:58:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:message-id:subject :from:to:cc:content-type; bh=jI5WK8OZug9DGc1RuOMfzV1oX2AVWd9K3V7Oo3KBs50=; b=GAwcnvlT9tNmsVvPQL57lAPWmW/lyWNiffD+9keXcpQd4EDUJ4clIRtU1IpZS3T7yG 9PtN5xjB3bhrpvuybhZ+fw/ZcM8nw/ikrdOUtkhkOUloI9w6doK3lyg8XuShnT5mgg04 Hing9I21fD+ZZ4fX6pKX4B/feuWustFqIbZK8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:cc:content-type; b=Z39a3NL/qktT0RAF76LIKms0nyB+nO6cfOYMR5HxKL0pDD4uw2Bz+gNkKbMW9RSe/H NhVI4akrd3NIq45ulNyV/ZoG5lio3hr7mOwJKrMFHbKQfDbwVJJS8XrQmN7yI4r11gfc YqTSfUwo5vGAQ5yNTup26mjH1NjdX1RkWGYeg= MIME-Version: 1.0 Received: by 10.204.42.6 with SMTP id q6mr383551bke.156.1268783930676; Tue, 16 Mar 2010 16:58:50 -0700 (PDT) Date: Tue, 16 Mar 2010 18:58:50 -0500 Message-ID: Subject: Block at a time ... From: Dave Crooke To: Alvaro Herrera Cc: Tom Lane , Greg Stark , Pierre C , Greg Smith , Paul McGarry , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000325556c927c167b0481f3c723 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.385 tagged_above=-10 required=5 tests=AWL=0.213, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/185 X-Sequence-Number: 38010 --000325556c927c167b0481f3c723 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable I agree with Tom, any reordering attempt is at best second guessing the filesystem and underlying storage. However, having the ability to control the extent size would be a worthwhil= e improvement for systems that walk and chew gum (write to lots of tables) concurrently. I'm thinking of Oracle's AUTOEXTEND settings for tablespace datafiles .... = I think the ideal way to do it for PG would be to make the equivalent configurable in postgresql.conf system wide, and allow specific per-table settings in the SQL metadata, similar to auto-vacuum. An awesomely simple alternative is to just specify the extension as e.g. 5% of the existing table size .... it starts by adding one block at a time for tiny tables, and once your table is over 20GB, it ends up adding a whole 1G= B file and pre-allocating it. Very little wasteage. Cheers Dave On Tue, Mar 16, 2010 at 4:49 PM, Alvaro Herrera wrote: > Tom Lane escribi=F3: > > Alvaro Herrera writes: > > > Maybe it would make more sense to try to reorder the fsync calls > > > instead. > > > > Reorder to what, though? You still have the problem that we don't know > > much about the physical layout on-disk. > > Well, to block numbers as a first step. > > However, this reminds me that sometimes we take the block-at-a-time > extension policy too seriously. We had a customer that had a > performance problem because they were inserting lots of data to TOAST > tables, causing very frequent extensions. I kept wondering whether an > allocation policy that allocated several new blocks at a time could be > useful (but I didn't try it). This would also alleviate fragmentation, > thus helping the physical layout be more similar to logical block > numbers. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > --000325556c927c167b0481f3c723 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable I agree with Tom, any reordering attempt is at best second guessing the fil= esystem and underlying storage.

However, having the ability to contr= ol the extent size would be a worthwhile improvement for systems that walk = and chew gum (write to lots of tables) concurrently.

I'm thinking of Oracle's AUTOEXTEND settings for tablespace dat= afiles .... I think the ideal way to do it for PG would be to make the equi= valent configurable in postgresql.conf system wide, and allow specific per-= table settings in the SQL metadata, similar to auto-vacuum.

An awesomely simple alternative is to just specify the extension as e.g= . 5% of the existing table size .... it starts by adding one block at a tim= e for tiny tables, and once your table is over 20GB, it ends up adding a wh= ole 1GB file and pre-allocating it. Very little wasteage.

Cheers
Dave

On Tue, Mar 16, 2010 a= t 4:49 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
Tom Lane escribi=F3:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Maybe it would make more sense to try to reorder the fsync calls<= br> > > instead.
>
> Reorder to what, though? =A0You still have the problem that we don'= ;t know
> much about the physical layout on-disk.

Well, to block numbers as a first step.

However, this reminds me that sometimes we take the block-at-a-time
extension policy too seriously. =A0We had a customer that had a
performance problem because they were inserting lots of data to TOAST
tables, causing very frequent extensions. =A0I kept wondering whether an allocation policy that allocated several new blocks at a time could be
useful (but I didn't try it). =A0This would also alleviate fragmentatio= n,
thus helping the physical layout be more similar to logical block
numbers.

--
Alvaro Herrera =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0http://www.C= ommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support<= br>

--000325556c927c167b0481f3c723--