Switching to another database isn't really an option. I didn't write the package and I'm not good enough to port it to another database or to rewrite it for a multiple machine architecture.
Probably the only non-RAID option I have (assuming I want to keep more than 3 months worth of data) would be to add another couple of drives to the server and then split the tables among them with links back to the original database directory - sort of 'poor man's RAID'. Drives I can get...it's the RAID controller and enclosure that is the sticking point. Jack -----Original Message----- From: Michael S. Fischer [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 1:43 PM To: 'Jack Coxen'; 'MySQL List (E-mail)' Subject: RE: Delete questions and speed/safety issues It's quite possible you're using the wrong tool for the job. Since this is a write-intensive environment, you may get better performance by using another database such as PostgreSQL or Oracle. Alternatively, consider the option of re-architecting the application to distribute the writes across multiple machines, each with its own small disk and running its own instance of MySQL. You need not necessarily have a big RAID array to scale effectively, and sometimes the "small soldiers" approach is more cost-effective. In order to safely run myisamchk on a table, mysqld must be shut down, or, alternatively, you must find some way to guarantee that the table is not presently open by mysqld and that mysqld will not try to open the files corresponding to the table while the check is in progress. --Michael > -----Original Message----- > From: Jack Coxen [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 20, 2003 6:07 AM > To: MySQL List (E-mail) > Subject: Delete questions and speed/safety issues > > > 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 > >
