Re: Questions about delete and optimize
2) Your OPTIMIZE statement does cause mysql to create a temporary table, which eventually replaces your current one. I suggest you try something along the lines of this and compare speed: - LOCK TABLES .. - CREATE TABLE `table_tmp` (..) # identical table - INSERT INTO `table_tmp` SELECT * FROM `table` WHERE date (NOW()) http://dev.mysql.com/doc/refman/5.0/en/insert-select.html - DROP TABLE `table` - RENAME TABLE `table_tmp` TO `table` - UNLOCK TABLES The above may be faster if you have an index on 'date'. Atle On Thu, 1 Feb 2007, Ian Barnes wrote: Hi, We are looking at various methods that we can effectively and efficiently delete lots of rows from a database and then optimize it. Our main concern is disk space - the partition we are working with is only 12gigs small and our database vary in size from 1gig (not a problem) to 11gig. In the example below I will use one whos .MYD is 6.5 Gig and the .MYI is 2.7Gig. There are around 28,900,000 rows in the database. Once a month we run an automated program that deletes rows older than X months and then we attempt the optimize the table in question. The delete query we use is: DELETE FROM table WHERE date(current_date - interval 2 month). Now my questions surrounding this are: 1.) Is it quicker to do a query where we say something like: DELETE FROM table WHERE date = '2006-11-01' instead of where date(current_date)? 2.) Does the current way we do it use a tmp table that is written to disk ? Then, we run the simple optimize command: OPTIMIZE TABLE tablename and that is normally where we come into the problem that mysql tries to create a tmp file while optimizing and it runs out of space, and then corrupts the main table. We need to run the optimize because after deleting all those rows, the space isnt freed up until we run the optimize. So my other question is can we do an optimize a different way, or is there some way that we can insert and delete rows that would require less optimization? Thanks in advance, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Questions about delete and optimize
Re: I can't say that I've tried this, It works like a champ we do every day in Oracle, DB2 and MySQL. It takes less time because you're bypassing logging thus reduce I/O and locking, etc. -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Thursday, February 01, 2007 3:36 PM To: 'Ian Barnes'; mysql@lists.mysql.com Subject: RE: Questions about delete and optimize Another way to do it would be to select the data you want to keep into a table on another file system, truncate the existing table, optimize it, then reload it with the data you saved. I can't say that I've tried this, and have no idea how long it would take or even if it would work. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ian Barnes [mailto:[EMAIL PROTECTED] Sent: Thursday, February 01, 2007 4:23 PM To: mysql@lists.mysql.com Subject: Questions about delete and optimize Hi, We are looking at various methods that we can effectively and efficiently delete lots of rows from a database and then optimize it. Our main concern is disk space - the partition we are working with is only 12gigs small and our database vary in size from 1gig (not a problem) to 11gig. In the example below I will use one whos .MYD is 6.5 Gig and the .MYI is 2.7Gig. There are around 28,900,000 rows in the database. Once a month we run an automated program that deletes rows older than X months and then we attempt the optimize the table in question. The delete query we use is: DELETE FROM table WHERE date(current_date - interval 2 month). Now my questions surrounding this are: 1.) Is it quicker to do a query where we say something like: DELETE FROM table WHERE date = '2006-11-01' instead of where date(current_date)? 2.) Does the current way we do it use a tmp table that is written to disk ? Then, we run the simple optimize command: OPTIMIZE TABLE tablename and that is normally where we come into the problem that mysql tries to create a tmp file while optimizing and it runs out of space, and then corrupts the main table. We need to run the optimize because after deleting all those rows, the space isnt freed up until we run the optimize. So my other question is can we do an optimize a different way, or is there some way that we can insert and delete rows that would require less optimization? Thanks in advance, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions about delete and optimize
Hi, We are looking at various methods that we can effectively and efficiently delete lots of rows from a database and then optimize it. Our main concern is disk space - the partition we are working with is only 12gigs small and our database vary in size from 1gig (not a problem) to 11gig. In the example below I will use one whos .MYD is 6.5 Gig and the .MYI is 2.7Gig. There are around 28,900,000 rows in the database. Once a month we run an automated program that deletes rows older than X months and then we attempt the optimize the table in question. The delete query we use is: DELETE FROM table WHERE date(current_date - interval 2 month). Now my questions surrounding this are: 1.) Is it quicker to do a query where we say something like: DELETE FROM table WHERE date = '2006-11-01' instead of where date(current_date)? 2.) Does the current way we do it use a tmp table that is written to disk ? Then, we run the simple optimize command: OPTIMIZE TABLE tablename and that is normally where we come into the problem that mysql tries to create a tmp file while optimizing and it runs out of space, and then corrupts the main table. We need to run the optimize because after deleting all those rows, the space isnt freed up until we run the optimize. So my other question is can we do an optimize a different way, or is there some way that we can insert and delete rows that would require less optimization? Thanks in advance, Ian
RE: Questions about delete and optimize
Another way to do it would be to select the data you want to keep into a table on another file system, truncate the existing table, optimize it, then reload it with the data you saved. I can't say that I've tried this, and have no idea how long it would take or even if it would work. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ian Barnes [mailto:[EMAIL PROTECTED] Sent: Thursday, February 01, 2007 4:23 PM To: mysql@lists.mysql.com Subject: Questions about delete and optimize Hi, We are looking at various methods that we can effectively and efficiently delete lots of rows from a database and then optimize it. Our main concern is disk space - the partition we are working with is only 12gigs small and our database vary in size from 1gig (not a problem) to 11gig. In the example below I will use one whos .MYD is 6.5 Gig and the .MYI is 2.7Gig. There are around 28,900,000 rows in the database. Once a month we run an automated program that deletes rows older than X months and then we attempt the optimize the table in question. The delete query we use is: DELETE FROM table WHERE date(current_date - interval 2 month). Now my questions surrounding this are: 1.) Is it quicker to do a query where we say something like: DELETE FROM table WHERE date = '2006-11-01' instead of where date(current_date)? 2.) Does the current way we do it use a tmp table that is written to disk ? Then, we run the simple optimize command: OPTIMIZE TABLE tablename and that is normally where we come into the problem that mysql tries to create a tmp file while optimizing and it runs out of space, and then corrupts the main table. We need to run the optimize because after deleting all those rows, the space isnt freed up until we run the optimize. So my other question is can we do an optimize a different way, or is there some way that we can insert and delete rows that would require less optimization? Thanks in advance, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions about delete and optimize
Ian, based on your needs (regularly deleting everything morre than X months old), I recommend you look into using the MERGE engine. Essentially, it is multiple MyISAM tables that appear as one, and lopping off the oldest data is as simple as redfining the MERGE and then dropping the oldest table. http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html They're not perfect but they may do what you want, without the problem you currently have of huge tmp tables filling up your partition. Dan On 2/1/07, Ian Barnes [EMAIL PROTECTED] wrote: Hi, We are looking at various methods that we can effectively and efficiently delete lots of rows from a database and then optimize it. Our main concern is disk space - the partition we are working with is only 12gigs small and our database vary in size from 1gig (not a problem) to 11gig. In the example below I will use one whos .MYD is 6.5 Gig and the .MYI is 2.7Gig. There are around 28,900,000 rows in the database. Once a month we run an automated program that deletes rows older than X months and then we attempt the optimize the table in question. The delete query we use is: DELETE FROM table WHERE date(current_date - interval 2 month). Now my questions surrounding this are: 1.) Is it quicker to do a query where we say something like: DELETE FROM table WHERE date = '2006-11-01' instead of where date(current_date)? 2.) Does the current way we do it use a tmp table that is written to disk ? Then, we run the simple optimize command: OPTIMIZE TABLE tablename and that is normally where we come into the problem that mysql tries to create a tmp file while optimizing and it runs out of space, and then corrupts the main table. We need to run the optimize because after deleting all those rows, the space isnt freed up until we run the optimize. So my other question is can we do an optimize a different way, or is there some way that we can insert and delete rows that would require less optimization? Thanks in advance, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
