Cool idea, but I don't think you can really do it. When I try, mysql just gives me back the syntax stuff. <sigh> I'm a bit perplexed - I would have thought this would be a semi-common issue in db maintenance, but no one seems to have set this up. I'm still trying, please send any other ideas/suggestions my way... thx!!
--- Dan Greene wrote: > you may be able to put both statements to a > text file, let's call it deleteold.sql > > then your cron job would be : > > mysql (put your connect stuff here) < > deleteold.sql > > > From: Scott H > > Well, it sort of helps. But that section is > > about future enhancements intended for mysql. > > I need to set something up now, with the > current > > stable version. One thing I read (can't find > it > > now) indicated that the current version (I'm > > actually running 4.0.15a) has "limited" > support for subqueries - but I don't know > >exactly how far that goes. > > > > So let me set the stage a bit more - I'll > assume > > for now there is no reasonably simple way to > work > > with the actual size of the database on disk, > and > > instead will go with the idea that I can > expect > > the size of any one record to be of some > average. > > So, according to Dan's suggestion, if I do a > > little math, and control the number of > records, I > > can control the size of the db -- > approximately. > > That's fine. Thus if have "seq" as an > > autoincrement field, and I wanted to stay > around > > say 1000 records, deleting the oldest > records, I > > would need to run a cron job that would > somehow > > nest or relate these 2 sql statements: > > > > select (@aa:=seq) as low_seq from logtable > order > > by seq limit 1000,1 > > > > delete from logtable where seq < @aa > > > > I've tried putting this into a subquery > format > > but no luck. But I'm a noob, so I keep > trying, > > thinking I might hit on the right syntax. > Or, is > > there some way to pull the value of "low_seq" > > into an environment variable and use it in a > > script file to run the 2nd statement? Other > > ideas? > > > > thanks, scott > > > > --- Michael McTernan wrote: > > > >From the manual: > > > > > > 1.8.4.1 Subqueries > > > > > > Subqueries are supported in MySQL version > 4.1. > > > See section 1.6.1 Features > > > Available in MySQL 4.1. > > > > > > Hope that helps, > > > > > > Mike > > > > > > > From: Scott H > > > > OK, I *THINK* I follow you here. Couple > of > > > > questions. I'm reading an online > tutorial > > > trying > > > > to figure this out, and I am led to > believe > > > mysql > > > > can't do nested queries, aka sub-queries. > But > > > you > > > > say it can? Is this recent? And I don't > have > > > a > > > > timestamp field, I have an autoincrement > > > field, > > > > but what do you mean by the "(@aa:=id)" > > > thing? I > > > > don't follow that. thanks. > > > > > > > > --- Dan Greene > > > <[EMAIL PROTECTED]> > > > > wrote: > > > > > What I would do is a classical > > > guesstimate.... > > > > > > > > > > find the average size per record (data > file > > > > > size + index file(s) size / # records > in > > > table) > > > > > > > > > > using that, find the data used per day > > > > > > > > > > using that, figure out how many days, > on > > > > > average it takes to hit 20GB > > > > > > > > > > let's say it's 89 days. > > > > > > > > > > right off the top, take 10% off for > safety, > > > now > > > > > we're at 80 days > > > > > > > > > > presuming your table has a timestamp > field: > > > > > > > > > > delete from log_table WHERE > TO_DAYS(NOW()) > > > - > > > > > TO_DAYS(date_col) > 80 > > > > > > > > > > if you don't have a timestamp field, > but > > > you do > > > > > have an autoincrement id field: > > > > > > > > > > figure out number of records on average > = > > > 20gb > > > > > (say it's 2M) > > > > > again, use 10% for safety (1.8M) > > > > > > > > > > select (@aa:=id) as low_id from > logtable > > > order > > > > > by id limit 18000000,1 > > > > > delete from logtable where id < @aa > > > > > > > > > > (do subqueries work with a limit > clause?) > > > > > > > > > > > > > > > > -----Original Message----- > > > > > > From: Scott H > > > > > [mailto:[EMAIL PROTECTED] > > > > > > Sent: Wednesday, November 12, 2003 > 11:19 > > > AM > > > > > > To: Dan Greene; MySQL Mailing List > > > > > > Subject: RE: maintaining size of a db > > > > > > > > > > > > > > > > > > Yes sir, exactly. It's just that's > what > > > I'm > > > > > > looking for, and can't figure out. I > can > > > set > > > > > up > > > > > > a cron job, but what exactly would > the > > > SQL > > > > > delete > > > > > > statement be that would allow me to > > > delete > > > > > old > > > > > > records in such a way that the db > > > maintains > > > > > an > > > > > > approximately constant size on disk? > > > > > (Failing > > > > > > that perhaps a delete statement that > > > would > > > > > just > > > > > > have it maintain a constant # of > records? > > > > > > ...maybe this would be much simpler?) > > > > > > > > > > > > --- Dan Greene wrote: > > > > > > > cronjob a sql script that runs a > delete > > > > > > > statement for old jobs daily > > > > > > > > > > > > > > > --- Egor Egorov wrote: > > > > > > > > > Scott H wrote: > > > > > > > > >> Can't seem to find this one in > the > > > > > manual > > > > > > > or > > > > > > > > >> archives - how do I control a > db > > > to > > > > > > > maintain > > > > > > > > >> its size to an arbitrary > value, > > > say 20 > > > > > GB? > > > > > > > I > > > > > > > > >> want to just rotate records, > > > deleting > > > > > > > those > > > > > > > > >> that are oldest. > > > > > > > > > > > > > > > > > > You can't restrict size of the > > > database > > > > > > > only > > > > > > > > > with MySQL, use disk quotas. > > > > > > > > > > > > > > > > No! That would just stop mysql > right > > > in > > > > > its > > > > > > > > tracks (so to speak...) when it > got > > > too > > > > > > > large. > > > > > > > > But I want old records sloughed > off > > > and > > > > > the > > > > > > > db to > > > > > > > > continue running. (This is for a > > > central > > > > > > > syslog > > > > > > > > box.) __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
