Database Quotas

Hi,

I'm not sure if this is already an open issue or not - a Google search resulted in various discussions but I didn't find any open support/feature request.

It would be really handy if during the "create database" statement, one could specify something like:

CREATE DATABASE foo QUOTA=10G;

to limit the entire database being created to no more than 10GB (in this example).

I've found various other schemes about using ZFS and other disk partitioning systems to just limit available space in the mySQL database folders, but I've read commentary about how that can corrupt the database if the disk becomes full.

So, is this a feature that seems useful to other people? It would certainly be useful to me.

Thanks for a great product!

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@soe.ucsc.edu
831-459-5354


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Database Quotas

--=-dCWZBYcEV9G6jeppVLRz
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Thu, 2010-05-20 at 16:27 -0700, Tim Gustafson wrote:

> Hi,
>
> I'm not sure if this is already an open issue or not - a Google search resulted in various discussions but I didn't find any open support/feature request.
>
> It would be really handy if during the "create database" statement, one could specify something like:
>
> CREATE DATABASE foo QUOTA=10G;
>
> to limit the entire database being created to no more than 10GB (in this example).
>


Yes it would be nice. But the best current way is to assign a user to
the database and use system quotas.


> limit available space in the mySQL database folders, but I've read commentary about how that can corrupt the database if the disk becomes full.
>


Ummm, you're going to have the same problem either way when the limit is
reached, be it a MySQL quota or system quota, if its full, its full.




--=-dCWZBYcEV9G6jeppVLRz--

Re: Database Quotas

> Ummm, you're going to have the same problem either way when
> the limit is reached, be it a MySQL quota or system quota,
> if its full, its full.

Yes, but mySQL could return a more friendly "you're out of space" message and not corrupt the data files if a given statement would cause the database to exceed its quota (and it could do so in a way that doesn't corrupt the database files)

Or it could work the opposite way: check the current usage against the quota before executing a statement, and if the database is already over quota, don't even attempt it.

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@soe.ucsc.edu
831-459-5354

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Database Quotas

On May 20, 2010 08:32:56 pm Noel Butler wrote:
> On Thu, 2010-05-20 at 16:27 -0700, Tim Gustafson wrote:
> > Hi,
> >
> > I'm not sure if this is already an open issue or not - a Google search
> > resulted in various discussions but I didn't find any open
> > support/feature request.
> >
> > It would be really handy if during the "create database" statement, one
> > could specify something like:
> >
> > CREATE DATABASE foo QUOTA=10G;
> >
> > to limit the entire database being created to no more than 10GB (in this
> > example).
>
> Yes it would be nice. But the best current way is to assign a user to
> the database and use system quotas.
>
> > limit available space in the mySQL database folders, but I've read
> > commentary about how that can corrupt the database if the disk becomes
> > full.
>
> Ummm, you're going to have the same problem either way when the limit is
> reached, be it a MySQL quota or system quota, if its full, its full.

Use postgres, you can assign tablespaces to a partition of the size you want. When it gets full,
writes are refused. I'm not sure how nicely that is handled ( in terms of error output ) but the
advantage is that Pg is ACID compliant, so you won't lose data.

Colin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Database Quotas

> Use postgres, you can assign tablespaces to a partition
> of the size you want. When it gets full, writes are
> refused. I'm not sure how nicely that is handled ( in
> terms of error output ) but the advantage is that Pg is
> ACID compliant, so you won't lose data.

Wow, that's the first time I've read a message on a support list that seriously said "use another program" in response to a (IMHO) reasonable feature request. :)

But then again, your domain name is "obviouslymalicious.com", so...

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@soe.ucsc.edu
831-459-5354

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Database Quotas

On May 20, 2010 09:55:41 pm Tim Gustafson wrote:
> > Use postgres, you can assign tablespaces to a partition
> > of the size you want. When it gets full, writes are
> > refused. I'm not sure how nicely that is handled ( in
> > terms of error output ) but the advantage is that Pg is
> > ACID compliant, so you won't lose data.
>
> Wow, that's the first time I've read a message on a support list that
> seriously said "use another program" in response to a (IMHO) reasonable
> feature request. :)
>
> But then again, your domain name is "obviouslymalicious.com", so...
>
> Tim Gustafson
> Baskin School of Engineering
> UC Santa Cruz
> tjg@soe.ucsc.edu
> 831-459-5354

Lol, yeah, it did seem like more of a trolling attempt than what I was going for. My point was two-
fold. First, generally speaking, putting a quota on an entire database means you are probably doing
it wrong. In a perfect world, it seems to be that building a database which can maintain a size
without constant mothering would be best, this doesn't always happen for one reason or another, but
of all the ways to maintain a constant database size, quotas are one of the worst. In mysql, there
don't seem to be defined ways to handle this sort of error reliably, at least not from the
perspective of the data I've lost in this way on mysql.

Second, I mention Postgres in this context first because of tablespaces, which allow you to handle
this from the filesystem level and second because it is ACID compliant even when running up against
space boundaries. This is probably important if you like your data intact.

I'm not a mysql developer, neither do I want to be, and neither am I an expert on databases. What I
know comes from the administration I have to do at work, I would venture that many other people on
the list have a better understanding of this issue than I do... just my .02

Colin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Database Quotas

Tim Gustafson wrote:
> Hi,
>
> I'm not sure if this is already an open issue or not - a Google search resulted in various discussions but I didn't find any open support/feature request.
>
> It would be really handy if during the "create database" statement, one could specify something like:
>
> CREATE DATABASE foo QUOTA=10G;
>
> to limit the entire database being created to no more than 10GB (in this example).
>
> I've found various other schemes about using ZFS and other disk partitioning systems to just limit available space in the mySQL database folders, but I've read commentary about how that can corrupt the database if the disk becomes full.
>
> So, is this a feature that seems useful to other people? It would certainly be useful to me.
>
> Thanks for a great product!
>
> Tim Gustafson
> Baskin School of Engineering
> UC Santa Cruz
> tjg@soe.ucsc.edu
> 831-459-5354
>
>

We encourage you to add your comments to the existing feature request:
http://bugs.mysql.com/bug.php?id=21038

Also, you can configure the common InnoDB tablespace to have a fixed
maximum size. But that is not for a single table or database but for the
total of all data stored within InnoDB.
http://dev.mysql.com/doc/refman/5.1/en/innodb-init.html
http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.h tml

While it is similar in concept to what you proposed, it fails to meet
your needs by being global rather than specific.

--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Database Quotas

> First, generally speaking, putting a quota on an entire database means
> you are probably doing it wrong. In a perfect world, it seems to be
> that building a database which can maintain a size without constant
> mothering would be best, this doesn't always happen for one reason
> or another, but of all the ways to maintain a constant database size,
> quotas are one of the worst.

We have a shared mySQL server that is used by web sites, research projects, admin staff and graduate students. None of those types of people are sensitive to database storage restrictions, and what we're trying to accomplish is to prevent any one of those users from going out of control (either by accident, due to a bug, or on purpose) and clobbering the entire mySQL server. Better to have one "dead" database than a whole server.

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@soe.ucsc.edu
831-459-5354

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Database Quotas

--_913165d2-f836-449c-b2db-afa1d58046d4_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Tim-

if MYSQL attempts to insert more bytes than what is available on disk you w=
ill get 28 ENOSPC No space left on device

http://dev.mysql.com/doc/refman/5.0/en/operating-system-erro r-codes.html


does this help?
Martin Gainty=20
______________________________________________=20
Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit=
=E9

=20
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng=
er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter=
leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l=
ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin=
dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w=
ir keine Haftung fuer den Inhalt uebernehmen.Ce message est confidentiel et=
peut =EAtre privil=E9gi=E9. Si vous n'=EAtes pas le destinataire pr=E9vu=
=2C nous te demandons avec bont=E9 que pour satisfaire informez l'exp=E9dit=
eur. N'importe quelle diffusion non autoris=E9e ou la copie de ceci est int=
erdite. Ce message sert =E0 l'information seulement et n'aura pas n'importe=
quel effet l=E9galement obligatoire. =C9tant donn=E9 que les email peuvent=
facilement =EAtre sujets =E0 la manipulation=2C nous ne pouvons accepter a=
ucune responsabilit=E9 pour le contenu fourni.



=20

> Date: Thu=2C 20 May 2010 18:09:42 -0700
> From: tjg@soe.ucsc.edu
> To: noel.butler@ausics.net
> CC: mysql@lists.mysql.com
> Subject: Re: Database Quotas
>=20
> > Ummm=2C you're going to have the same problem either way when
> > the limit is reached=2C be it a MySQL quota or system quota=2C
> > if its full=2C its full.
>=20
> Yes=2C but mySQL could return a more friendly "you're out of space" messa=
ge and not corrupt the data files if a given statement would cause the data=
base to exceed its quota (and it could do so in a way that doesn't corrupt =
the database files)
>=20
> Or it could work the opposite way: check the current usage against the qu=
ota before executing a statement=2C and if the database is already over quo=
ta=2C don't even attempt it.
>=20
> Tim Gustafson
> Baskin School of Engineering
> UC Santa Cruz
> tjg@soe.ucsc.edu
> 831-459-5354
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.com
>=20
=20
____________________________________________________________ _____
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with H=
otmail.=20
http://www.windowslive.com/campaign/thenewbusy?tile=3Dmultic alendar&ocid=3D=
PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5=

--_913165d2-f836-449c-b2db-afa1d58046d4_--

Re: Database Quotas

> if MYSQL attempts to insert more bytes than what is available
> on disk you will get 28 ENOSPC No space left on device
> http://dev.mysql.com/doc/refman/5.0/en/operating-system-erro r-codes.html

Does it figured that out before it tries to write a record? So, if I have 2KB left on the device and I write a 4KB record, does the first 2KB get written and then the error occurs, or does the error occur before the write is attempted?

I guess what I'm asking is will the tables be marked as "crashed" when an ENOSPC happens, or will the tables still be in good health?

If they're still in good health, then I suppose that I could use ZFS file systems to allocate space for databases...it just seems that this ought to be a feature of the database. :)

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@soe.ucsc.edu
831-459-5354

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Database Quotas

--0016e65b4116382bd904871bedbd
Content-Type: text/plain; charset=ISO-8859-1

I ran out of space on a large, busy production database just a few weeks
ago. All tables are InnoDB and I experienced zero data loss.

It was actually running out of space for almost 2 weeks after a review of
the log file. As temp files were deleted transactions were able to continue
until all but zero bytes of the disk were available.

I think MySQL did a fantastic job handling the problem.

JW

On Fri, May 21, 2010 at 9:21 AM, Tim Gustafson wrote:

> > if MYSQL attempts to insert more bytes than what is available
> > on disk you will get 28 ENOSPC No space left on device
> > http://dev.mysql.com/doc/refman/5.0/en/operating-system-erro r-codes.html
>
> Does it figured that out before it tries to write a record? So, if I have
> 2KB left on the device and I write a 4KB record, does the first 2KB get
> written and then the error occurs, or does the error occur before the write
> is attempted?
>
> I guess what I'm asking is will the tables be marked as "crashed" when an
> ENOSPC happens, or will the tables still be in good health?
>
> If they're still in good health, then I suppose that I could use ZFS file
> systems to allocate space for databases...it just seems that this ought to
> be a feature of the database. :)
>
> Tim Gustafson
> Baskin School of Engineering
> UC Santa Cruz
> tjg@soe.ucsc.edu
> 831-459-5354
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e65b4116382bd904871bedbd--

RE: Database Quotas

--_6e30c2ae-b66c-4feb-abb1-5a1d97def949_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


i guess my thinking is more along the lines of implementing a lustre interf=
ace

http://en.wikipedia.org/wiki/Lustre_%28file_system%29#Archit ecture

=20

where the chronology *may* follow:
the File's Metadata attributes are written
the particulars of where the data is written would be handled by OSS which =
delegates to 1..n target nodes (which then passes the information to that t=
arget's LVM / RAID device)


Both MDS and OSS can implement either ext3 or ZFS/DMU Storage algos..in eit=
her case the metadata(MDT) metadata object record is written by the MDS ..
the data will be written to one or more LOV formatted ext3 or ZFS/DMU nodes=
specifying byte-offset and size..
When the requesting client issues a write request for the OST
the governing OSS issues the write request to the target that can fulfill i=
t..if the requested target cannot complete the request=20
that target passes ENOSPC back to OSS which then looks up the next target
The admin of the target node will be notified of the failed attempt by aler=
t or log
but the OSS will hunt for the next target that can fulfill the write reques=
t of the OST

=20

this is my (albeit cursory) interpretation of Object Oriented Disk Architec=
tures

does this conform to your understanding?
Martin Gainty=20
______________________________________________=20
Please do not alter/modify or disrupt this transmission. Thank You



=20


> Date: Fri=2C 21 May 2010 07:21:22 -0700
> From: tjg@soe.ucsc.edu
> To: mgainty@hotmail.com
> CC: mysql@lists.mysql.com
> Subject: Re: Database Quotas
>=20
> > if MYSQL attempts to insert more bytes than what is available
> > on disk you will get 28 ENOSPC No space left on device
> > http://dev.mysql.com/doc/refman/5.0/en/operating-system-erro r-codes.htm=
l=20
>=20
> Does it figured that out before it tries to write a record? So=2C if I ha=
ve 2KB left on the device and I write a 4KB record=2C does the first 2KB ge=
t written and then the error occurs=2C or does the error occur before the w=
rite is attempted?
>=20
> I guess what I'm asking is will the tables be marked as "crashed" when an=
ENOSPC happens=2C or will the tables still be in good health?
>=20
> If they're still in good health=2C then I suppose that I could use ZFS fi=
le systems to allocate space for databases...it just seems that this ought =
to be a feature of the database. :)
>=20
> Tim Gustafson
> Baskin School of Engineering
> UC Santa Cruz
> tjg@soe.ucsc.edu
> 831-459-5354
=20
____________________________________________________________ _____
Hotmail is redefining busy with tools for the New Busy. Get more from your =
inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID283 26::T:WLMTAGL:O=
N:WL:en-US:WM_HMP:042010_2=

--_6e30c2ae-b66c-4feb-abb1-5a1d97def949_--

Re: Database Quotas

> i guess my thinking is more along the lines of implementing
> a lustre interface

I'm sure that I'm vastly over-simplifying this, but I was thinking something along the lines of:

1. Assemble the data being written, calculate its length

2. Check for any free pages in the database file, and use them if there's enough

3. If there aren't enough free pages, check the file system's available space and make sure it exceeds the size of the write being performed

4. If there's enough free disk space, write the record. Otherwise, return an error

I'm thinking that this would be implemented at the storage engine level, and probably just for MyISAM tables.

Just my $0.02. Based on the reply from Johnny Withers, I'm thinking that just using ZFS file system quotas will work for me, but it does seem that this would be a handy feature to have built in to mySQL for people who don't have ZFS, or who can't use it for some reason.

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@soe.ucsc.edu
831-459-5354

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org