[rtg] efficent way of deleting data?

[bill fumerola]

On Fri, Jul 11, 2008 at 10:11:56AM -0400, Drew Weaver wrote:
>                 I've been toying around with this for a few days now and I haven't come across a real efficient way of deleting data which is no longer useful in the RTG tables (by efficient I mean, in a way which doesn't take 15 minutes per table per interface.)
> 
> Can anyone else share their strategy for what they do with data when it is no longer useful?
> 
> Do you just store it forever and only select data within the appropriate dtime scope?

i'd suggest using mysql MERGE tables[1] as your backing storage to
seperate the tables that you're likely to delete data from the tables
you need to be inserting into. that way your graphing/reporting SELECTs
and rtgpoll INSERTs won't be blocked by your DELETEs that take a long
time to complete.

you can do this based on a date pattern (insert into tables that have a
month component in their name) or just start a new table when you want
to work on the old data, point rtgpoll at the new table, and point
graphing/reporting at the merge table.

alternatively, if you're deleting data for storage reasons, you can use
merge tables and then myisampack the tables that contain old data.

-- bill