RE: Delete questions and speed/safety issues
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 '-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
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 '-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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete questions and speed/safety issues
Jack Coxen wrote: I had originally planned to use syntax similar to: DELETE * FROM table_name WHERE `dtime` [90 days ago] delete * from table_name where to_days(now())-to_days(dtime) 90 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. why myisamchk? to compact the tables? you don't need myisamchk in normal use. 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. if you don't use myisamchk you can lock the table before doing the delete, which ensures that nobody else will be using it. keeps the apps from reading inconsistent data and lets your query run with full speed. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 '-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
RE: Delete questions and speed/safety issues
If the database cannot grow unbounded, and you have to prune the database from time to time (no matter how much disk space you may have) you will have to perform regular table defragmentation if you want to minimize performance degradation. So, you'll need to schedule periodic outages to do that, or alternatively, you can establish two database instances and swap between them, performing the defragmentation on the inactive database. --Michael -Original Message- From: Jack Coxen [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 11:43 AM To: 'Michael S. Fischer'; Jack Coxen; 'MySQL List (E-mail)' Subject: RE: Delete questions and speed/safety issues 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 '-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
