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 t...@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=arch...@jab.org
RE: Database Quotas
i guess my thinking is more along the lines of implementing a lustre interface http://en.wikipedia.org/wiki/Lustre_%28file_system%29#Architecture 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 target's LVM / RAID device) Both MDS and OSS can implement either ext3 or ZFS/DMU Storage algos..in either 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 it..if the requested target cannot complete the request 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 alert or log but the OSS will hunt for the next target that can fulfill the write request of the OST this is my (albeit cursory) interpretation of Object Oriented Disk Architectures does this conform to your understanding? Martin Gainty __ Please do not alter/modify or disrupt this transmission. Thank You > Date: Fri, 21 May 2010 07:21:22 -0700 > From: t...@soe.ucsc.edu > To: mgai...@hotmail.com > CC: mysql@lists.mysql.com > Subject: 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-error-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 > t...@soe.ucsc.edu > 831-459-5354 _ Hotmail is redefining busy with tools for the New Busy. Get more from your inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2
Re: Database Quotas
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-error-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 > t...@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=joh...@pixelated.net > > -- - Johnny Withers 601.209.4985 joh...@pixelated.net
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-error-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 t...@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=arch...@jab.org
RE: Database Quotas
Tim- 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-error-codes.html does this help? Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > Date: Thu, 20 May 2010 18:09:42 -0700 > From: t...@soe.ucsc.edu > To: noel.but...@ausics.net > CC: mysql@lists.mysql.com > Subject: 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 > t...@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=mgai...@hotmail.com > _ The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail. http://www.windowslive.com/campaign/thenewbusy?tile=multicalendar&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5
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 t...@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=arch...@jab.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 t...@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.html 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=arch...@jab.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 > t...@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=arch...@jab.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 t...@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=arch...@jab.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=arch...@jab.org
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 t...@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=arch...@jab.org
Re: Database Quotas
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.
Re: Database quotas?
Another way I was thinking about by now is to periodically calculate the database's size and, if over quota, revoke the user's INSERT, UPDATE rights and so on. It's not real-time, too, but maybe a little more friendly since I don't know how MySQL behaves when running against fs quota writing to a file... And it's easily configurable for any MySQL user account. And this cron script wouldn't actually need system root privileges, just sufficient privileges on that MySQL server. -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) On Thursday, September 25, 2003 12:32 AM CET, Steven Adams wrote: > Its not, i ment to say mysql user. > > A cron will work, but it wont be up-to-date.. hmm, need to research > it a bit more. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database quotas?
Its not, i ment to say mysql user. A cron will work, but it wont be up-to-date.. hmm, need to research it a bit more. - Original Message - From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, September 25, 2003 3:11 AM Subject: Re: Database quotas? > On Wed, Sep 24, 2003 at 03:18:07PM +1000, [EMAIL PROTECTED] wrote: > > Yeah but when they make a nwe table, it will be owned by root.root wont it? > > First, don't run MySQL as root. > > Second, yes. It will be owned by whoever creates it. But a periodic cron > job to chorwn them appropriately would be a reasonable solution, I suspect. > > Jeremy > -- > Jereme D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ > > MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 388,649,043 queries (420/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database quotas?
On Wed, Sep 24, 2003 at 03:18:07PM +1000, [EMAIL PROTECTED] wrote: > Yeah but when they make a nwe table, it will be owned by root.root wont it? First, don't run MySQL as root. Second, yes. It will be owned by whoever creates it. But a periodic cron job to chorwn them appropriately would be a reasonable solution, I suspect. Jeremy -- Jereme D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 388,649,043 queries (420/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database quotas?
Yeah but when they make a nwe table, it will be owned by root.root wont it? > On Wed, Sep 24, 2003 at 01:38:43PM +1000, [EMAIL PROTECTED] wrote: >> Hey guys, >> >> is it possible to bind some databases to a userid so u can do disk >> quotas on them, or limit how big a db can be? > > chown the files. Just make sure they're group-wrtiable so that MySQL > can write to 'em. > -- > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ > > MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 353,756,882 queries > (401/sec. avg) > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database quotas?
On Wed, Sep 24, 2003 at 01:38:43PM +1000, [EMAIL PROTECTED] wrote: > Hey guys, > > is it possible to bind some databases to a userid so u can do disk quotas > on them, or limit how big a db can be? chown the files. Just make sure they're group-wrtiable so that MySQL can write to 'em. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 353,756,882 queries (401/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
