Delete questions and speed/safety issues

[Jack Coxen]

------_=_NextPart_001_01C3671C.01DAD650
Content-Type: text/plain;
charset="iso-8859-1"

I'm in the process of writing a Perl script to delete records out of a
database when the records are more than 90 days old. I'm running MySQL
version 4.0.12-standard. The db consists of around 620 MyISAM tables with
the following structure:

CREATE TABLE `ifInErrors_2` (
`id` int(11) NOT NULL default '0',
`dtime` datetime NOT NULL default '0000-00-00 00:00:00',
`counter` bigint(20) NOT NULL default '0',
KEY `ifInErrors_2_idx` (`dtime`)
) TYPE=MyISAM;

The tables range in size from 1-2 rows upwards to over 9,200,000 with an
average of around 570,000 rows. Updates to this database run constantly (it
records information from a program that monitors the routers and switches on
my company's WAN) at a rate of around 2600 inserts/minute.

I had originally planned to use syntax similar to:

DELETE * FROM table_name WHERE `dtime` < [90 days ago]

After the DELETE runs, I plan on running MYISAMCHK on the affected table.
Then I'll repeat both steps for all of the other tables in turn.

Does anyone have any suggestions for alternatives or is there anything I'm
missing here? I'm new to this stuff and may be way off base here. If so,
please tell me.

One last thing. I'm running out of Drive space and am I/O bound - I'm
writing this script in case I can't get the RAID array I'm hoping for.
Because of the I/O problem, execution time can sometimes be a factor in what
I do. Execution speed is a primary concern. If this takes a day or two to
run but the application can be running at the same time then there's no
problem. But if I have to shut down my application for any appreciable
length I time, then I have to find another way of doing this.

Thanks,

Jack

Jack Coxen
IP Network Engineer
TelCove
712 North Main Street
Coudersport, PA 16915
814-260-2705
------_=_NextPart_001_01C3671C.01DAD650--