469,957 Members | 2,537 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,957 developers. It's quick & easy.

Delete questions and speed/safety issues

Content-Type: text/plain;

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`)

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.



Jack Coxen
IP Network Engineer
712 North Main Street
Coudersport, PA 16915
Jul 19 '05 #1
0 946

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

25 posts views Thread by Brian Patterson | last post: by
reply views Thread by Jack Coxen | last post: by
reply views Thread by Creigh Shank | last post: by
2 posts views Thread by Howard Swope | last post: by
3 posts views Thread by silver360 | last post: by
4 posts views Thread by Dean Craig | last post: by
4 posts views Thread by AzizMandar | last post: by
4 posts views Thread by shamirza | last post: by
11 posts views Thread by Ed Dror | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.