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 > -----Original Message----- > From: Scott H [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 12, 2003 3:17 PM > To: Michael McTernan; Dan Greene > Cc: MySQL Mailing List > Subject: RE: maintaining size of a db > > > 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.) > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
