two last gotchas I thought of... 1- the routine will erase all the old records, but the day's logs will increment between executions, so you may want to give it a day's worth of 'padding' if the 20GB is a hard limit (disk size). No worries if it's flexible
2- until you have filled to your size limit, you may want to run it manually as you won't have your 250,000 records in the table yet, so your initial query will return null (I think), which is very likely to mess up the delete statement following it.... > -----Original Message----- > From: Scott H [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 13, 2003 12:30 PM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: maintaining size of a db > > > Got it Harald, thanks. OK, I've got this working > now, so I'll do a quick overview of what I've > learned... for the archives: > > I am setting up mysql with msyslog to be a > centralized logging server. My servers (Windows > & Red Hat) will send their logs to this mysql > box. To keep the mysql db from growing beyond a > certain size, I first estimate the size of my > average record and divide by the total byte size > I want to allow on disk, to determine about how > many records I want as a maximum (of course, one > must look at and consider the size of any indexes > for your db, also, and leave some extra room for > error). For this example, I'll say I've figured > out that I can allow a maximum of 250,000 > records, and I have: > - a db named "msyslog" > - and a table within it named "syslogTB" > > syslogTB has an autoincrement field "seq". What I > do is set up a cron job to run a scan of the > database periodically, and yank out all records > beyond 250,000. The cron job runs as <sql-user> > with password <PASSWORD> and calls a plain text > file "/root/delete_old.sql" for it's input. The > cron job will thus run this as its command: > > /<path>/mysql -u <sql-user> --password=<PASSWORD> > msyslog < /root/delete_old.sql > > ...and in /root/delete_old.sql, there is only > this text (2 lines): > > select (@aa:=seq) as low_seq from syslogTB order > by seq DESC limit 250000,1; > delete from syslogTB where seq < @aa; > > Thanks everyone for your help!! > > Scott > > --- Harald Fuchs wrote: > > Scott H wrote: > > > 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? > > > > That's exactly what the two statements above > > do, except that MySQL has > > "user variables" (the @aa shown above) instead > > of "environment variables". > > > > ===== > -- > > To announce that there must be no criticism of the President, > or that we are to stand by the President, right or wrong, is > not only unpatriotic and servile, but is morally treasonable > to the American public. > -- Theodore Roosevelt, 1918 > > > > > > > . > > __________________________________ > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
