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]
