pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPre-allocation of space: a business rationale
3+ messages / 3 participants
[nested] [flat]
* Pre-allocation of space: a business rationale
@ 2005-11-03 03:27 Bath, David <dave.bath@unix.net>
0 siblings, 1 reply; 3+ messages in thread
From: Bath, David @ 2005-11-03 03:27 UTC (permalink / raw)
To: pgsql-admin
There has been a discussion about allowing pre-allocation of space,
and want to highlight the issues from a business perspective, rather
than those affectings DBAs and OS admins.
On Thu, 03 Nov 2005 06:00, Robert Treat wrote:
> On Monday 31 October 2005 22:59, Tom Lane wrote:
> > 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.
My real concern is not performance, but the impact on business continuity
in the following scenario (although I can imagine others of similar import).
(Apologies if there is a well-known way of addressing this issue).
A) I have a tablespace holding data for important business processes.
(Lets term this "24x7data")
B) I allow a user to have their own data, perhaps some non-critical summaries
and/or scratchpad/development work. (Lets term this "luser data")
C) I want to avoid the possibility of uncontrolled growth of luser data
blowing disk leading to stoppage of 24x7 data.
As I see it, unless I am able to partition them on different disks, which
might be difficult if I've created one logical disk across a cluster, it
is possible that uncontrolled growth of luser data (they might have an
endless loop in buggy code, or just attempt to pig out on data space)
might chew up all available storage, leading to 24x7 processes crashing
when they need to insert more data.
This could happen even if luser and 24x7 stuff live in different databases.
One theoretical way of avoiding this is to allow a "maximum size" argument
to the CREATE TABLESPACE statement, with a preference for preallocation.
This is probably easier to implement than a quota system on either an
object-owner (e.g. CREATE USER) or object (e.g. CREATE TABLE/INDEX)
basis.
I can't see such parameters for v8.0 statements of these types.
While it might be fine to monitor disk usage, even if you read reports
every day and can get the $ for a new disk quickly, a lazy programmer
or piggy user can blow away your disk in a blink of an eye.
(Aside: I once needed to get error reports from telecoms bearers into
postgresql's uncle (ingres5) and could get about 10,000 errors a
second coming through msgs and sems. Even without an endless loop bug,
and with creation of an expandable circular buffer for non-volatile
caching, I STILL had to worry about crashing the telecoms monitoring
processes if I pigged out on disk.)
Note that I'm not suggesting the cloning of any particular Oracle feature,
I do believe it is important to have a well-known way of addressing
such scenarios.
--
David T. Bath
dave.bath@unix.net
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Pre-allocation of space: a business rationale
@ 2005-11-03 03:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bath, David <dave.bath@unix.net>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2005-11-03 03:48 UTC (permalink / raw)
To: dave.bath@unix.net; +Cc: pgsql-admin
"Bath, David" <dave.bath@unix.net> writes:
> C) I want to avoid the possibility of uncontrolled growth of luser data
> blowing disk leading to stoppage of 24x7 data.
You put the luser data and the critical data into separate tablespaces
that are in separate partitions (filesystems). End of problem ...
(And no, I don't believe in having Postgres reinvent filesystem-level
functionality. If you didn't set up appropriate hard partitions,
consider a loopback filesystem for your tablespace.)
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Pre-allocation of space: a business rationale
@ 2005-11-03 19:21 Jim C. Nasby <jnasby@pervasive.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Jim C. Nasby @ 2005-11-03 19:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: dave.bath@unix.net; pgsql-admin
On Wed, Nov 02, 2005 at 10:48:00PM -0500, Tom Lane wrote:
> "Bath, David" <dave.bath@unix.net> writes:
> > C) I want to avoid the possibility of uncontrolled growth of luser data
> > blowing disk leading to stoppage of 24x7 data.
>
> You put the luser data and the critical data into separate tablespaces
> that are in separate partitions (filesystems). End of problem ...
>
> (And no, I don't believe in having Postgres reinvent filesystem-level
> functionality. If you didn't set up appropriate hard partitions,
> consider a loopback filesystem for your tablespace.)
Does every OS we support have a loopback filesystem? Can they all impose
space limits?
It doesn't seem unreasonable to support a limit on tablespace (or table)
size. It also doesn't seem like it would take that much code to add
support for it. Of course usual disclaimer about 'submit a patch then'
applies, but it sounds like such a patch would get rejected out-of-hand.
--
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-11-03 19:21 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-03 03:27 Pre-allocation of space: a business rationale Bath, David <dave.bath@unix.net>
2005-11-03 03:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-03 19:21 ` Jim C. Nasby <jnasby@pervasive.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox