Thread: Pre-allocate space in advance
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
**********************************************************************
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
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
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
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 **********************************************************************
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?
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 **********************************************************************
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