Thread: Pre-allocate space in advance

Pre-allocate space in advance

From
"Tomeh, Husam"
Date:
I apologize for the wrong subject in a previous post. Here it is again:
 
 
  My understanding that the current release of Postgres does not allow the DBA to pre-allocated space for the data files or tablespace in advance (as Oracle or SQL Server does). Am I correct on that ? If that's still the case, is this item on the TODO list or the white board? Pre-allocating space will prevent extending the datafile during loading massive data (batch processing) and would improve the overall batch write performance.
 
Thanks in advance.

--
 Husam

---------------------------(end of broadcast)---------------------------

TIP 2: Don't 'kill -9' the postmaster


**********************************************************************

This message contains confidential information intended only for the use of the addressee(s)

named above and may contain information that is legally privileged.  If you are not the

addressee, or the person responsible for delivering it to the addressee, you are hereby
notified that reading, disseminating, distributing or copying this message is strictly prohibited. 

If you have received this message by mistake, please immediately notify us by replying to the

message and delete the original message immediately thereafter.

 

Thank you.                                                                                                       FADLD Tag
**********************************************************************

Re: Pre-allocate space in advance

From
"Jim C. Nasby"
Date:
There was recently discussion about changing how allocation happens in
the future. I'm pretty sure it was started by Bruce Momjian on -hackers,
but I can't find it in the archives now...

On Mon, Oct 31, 2005 at 03:14:13PM -0800, Tomeh, Husam wrote:
> I apologize for the wrong subject in a previous post. Here it is again:
>
>
>   My understanding that the current release of Postgres does not allow
> the DBA to pre-allocated space for the data files or tablespace in
> advance (as Oracle or SQL Server does). Am I correct on that ? If that's
> still the case, is this item on the TODO list or the white board?
> Pre-allocating space will prevent extending the datafile during loading
> massive data (batch processing) and would improve the overall batch
> write performance.
>
> Thanks in advance.
>
> --
>  Husam
>
> ---------------------------(end of broadcast)---------------------------
>
> TIP 2: Don't 'kill -9' the postmaster
>
>
>
>
>
> **********************************************************************
> This message contains confidential information intended only for the
> use of the addressee(s) named above and may contain information that
> is legally privileged.  If you are not the addressee, or the person
> responsible for delivering it to the addressee, you are hereby
> notified that reading, disseminating, distributing or copying this
> message is strictly prohibited.  If you have received this message by
> mistake, please immediately notify us by replying to the message and
> delete the original message immediately thereafter.
>
> Thank you.                                       FADLD Tag
> **********************************************************************

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Pre-allocate space in advance

From
Bruce Momjian
Date:
Jim C. Nasby wrote:
> There was recently discussion about changing how allocation happens in
> the future. I'm pretty sure it was started by Bruce Momjian on -hackers,
> but I can't find it in the archives now...

I don't think is was me.  If it was, it would be on the TODO list.

---------------------------------------------------------------------------


>
> On Mon, Oct 31, 2005 at 03:14:13PM -0800, Tomeh, Husam wrote:
> > I apologize for the wrong subject in a previous post. Here it is again:
> >
> >
> >   My understanding that the current release of Postgres does not allow
> > the DBA to pre-allocated space for the data files or tablespace in
> > advance (as Oracle or SQL Server does). Am I correct on that ? If that's
> > still the case, is this item on the TODO list or the white board?
> > Pre-allocating space will prevent extending the datafile during loading
> > massive data (batch processing) and would improve the overall batch
> > write performance.
> >
> > Thanks in advance.
> >
> > --
> >  Husam
> >
> > ---------------------------(end of broadcast)---------------------------
> >
> > TIP 2: Don't 'kill -9' the postmaster
> >
> >
> >
> >
> >
> > **********************************************************************
> > This message contains confidential information intended only for the
> > use of the addressee(s) named above and may contain information that
> > is legally privileged.  If you are not the addressee, or the person
> > responsible for delivering it to the addressee, you are hereby
> > notified that reading, disseminating, distributing or copying this
> > message is strictly prohibited.  If you have received this message by
> > mistake, please immediately notify us by replying to the message and
> > delete the original message immediately thereafter.
> >
> > Thank you.                                       FADLD Tag
> > **********************************************************************
>
> --
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Pre-allocate space in advance

From
"Jim C. Nasby"
Date:
On Mon, Oct 31, 2005 at 08:37:22PM -0500, Bruce Momjian wrote:
> Jim C. Nasby wrote:
> > There was recently discussion about changing how allocation happens in
> > the future. I'm pretty sure it was started by Bruce Momjian on -hackers,
> > but I can't find it in the archives now...
>
> I don't think is was me.  If it was, it would be on the TODO list.

They say the mind is the first thing to go, and I don't remember the
second...

Someone posted about changes to FSM filling among other things; stuff
that would be at the high-end of performance tuning. If not you maybe it
was Simon?

Ahh, finally:
http://archives.postgresql.org/pgsql-hackers/2005-10/msg01312.php
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Pre-allocate space in advance

From
"Tomeh, Husam"
Date:
I don't have any numbers; and it may be an misconception as you pointed
out that's out there among Oracle DBAs that need to be re-visited again.

Tom, could you also explain what you meant in a previous post I found
during my research on this issue, which I may have misunderstood it: "I
actually wish that Postgres would preallocate a lot of blocks in advance
(ie. 100 x 8KB). That would probably improve performance, as it would
not have to keep re-extending the file."

http://archives.postgresql.org/pgsql-interfaces/2001-01/msg00153.php

Thanks,

--
 Husam


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, October 31, 2005 7:59 PM
To: Scott Marlowe
Cc: Tomeh, Husam; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Vacuum Verbose output

Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Mon, 2005-10-31 at 16:34, Tomeh, Husam wrote:
>> Pre-allocating space will prevent extending the datafile during
>> loading massive data (batch processing) and would improve the overall
>> batch write performance.

> Have you got any file system benchmarks that back up this assertion?
I
> would love to see something that shows one way or the other if that
> really makes any difference.

Barring some pretty solid evidence, you're unlikely to attract any
enthusiasm among pghackers for this sort of thing.  We are generally
disinclined to reinvent functionality that properly belongs to the
kernel or filesystem layer.  "Oracle does it" cuts no ice in this
connection, because Oracle is designed around a twenty-year-old
assumption that the database is smarter than the kernel, and the world
has changed a lot since then.

In short: show us some numbers that prove this is worth our attention.

            regards, tom lane

**********************************************************************
This message contains confidential information intended only for the
use of the addressee(s) named above and may contain information that
is legally privileged.  If you are not the addressee, or the person
responsible for delivering it to the addressee, you are hereby
notified that reading, disseminating, distributing or copying this
message is strictly prohibited.  If you have received this message by
mistake, please immediately notify us by replying to the message and
delete the original message immediately thereafter.

Thank you.                                       FADLD Tag
**********************************************************************


Re: Pre-allocate space in advance

From
Scott Marlowe
Date:
On Tue, 2005-11-01 at 11:17, Tomeh, Husam wrote:
>  I don't have any numbers; and it may be an misconception as you pointed
> out that's out there among Oracle DBAs that need to be re-visited again.
>
> Tom, could you also explain what you meant in a previous post I found
> during my research on this issue, which I may have misunderstood it: "I
> actually wish that Postgres would preallocate a lot of blocks in advance
> (ie. 100 x 8KB). That would probably improve performance, as it would
> not have to keep re-extending the file."
>
> http://archives.postgresql.org/pgsql-interfaces/2001-01/msg00153.php
>
> Thanks,

Ummm, that was a different Tom, wasn't it?

Re: Pre-allocate space in advance

From
"Tomeh, Husam"
Date:
Sorry about that.


--
 Husam

-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Tuesday, November 01, 2005 9:25 AM
To: Tomeh, Husam
Cc: Tom Lane; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Pre-allocate space in advance

On Tue, 2005-11-01 at 11:17, Tomeh, Husam wrote:
>  I don't have any numbers; and it may be an misconception as you
pointed
> out that's out there among Oracle DBAs that need to be re-visited
again.
>
> Tom, could you also explain what you meant in a previous post I found
> during my research on this issue, which I may have misunderstood it:
"I
> actually wish that Postgres would preallocate a lot of blocks in
advance
> (ie. 100 x 8KB). That would probably improve performance, as it would
> not have to keep re-extending the file."
>
> http://archives.postgresql.org/pgsql-interfaces/2001-01/msg00153.php
>
> Thanks,

Ummm, that was a different Tom, wasn't it?

**********************************************************************
This message contains confidential information intended only for the
use of the addressee(s) named above and may contain information that
is legally privileged.  If you are not the addressee, or the person
responsible for delivering it to the addressee, you are hereby
notified that reading, disseminating, distributing or copying this
message is strictly prohibited.  If you have received this message by
mistake, please immediately notify us by replying to the message and
delete the original message immediately thereafter.

Thank you.                                       FADLD Tag
**********************************************************************


Re: Pre-allocate space in advance

From
"Jim C. Nasby"
Date:
On Tue, Nov 01, 2005 at 09:17:46AM -0800, Tomeh, Husam wrote:
>
> I don't have any numbers; and it may be an misconception as you pointed
> out that's out there among Oracle DBAs that need to be re-visited again.
>
> Tom, could you also explain what you meant in a previous post I found
> during my research on this issue, which I may have misunderstood it: "I
> actually wish that Postgres would preallocate a lot of blocks in advance
> (ie. 100 x 8KB). That would probably improve performance, as it would
> not have to keep re-extending the file."
>
> http://archives.postgresql.org/pgsql-interfaces/2001-01/msg00153.php

Wrong Tom.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461