Re: Deleting from one table blocks other tables?
Hi. On Sun 2003-01-12 at 22:01:37 -0500, [EMAIL PROTECTED] wrote: On Sun, Jan 12, 2003 at 08:12:35PM -0700, Rodney Broom wrote: I'm trying to delete 5 million rows... [...] If I was deleting things regularly, I'd have to delete maybe a couple hundred thousand rows every day. [...] When I say blocked, I mean e.g. another process tries to SELECT from another table in the database, but it takes way too long. If you are absolutely sure that the other queries don't related to the deleting query at all, it means they are slow, because your disks are too stressed. Any suggestions on how I can delete those rows without causing a lot of downtime? As http://www.mysql.com/doc/en/DELETE.html suggests, you can use LIMIT with DELETE in order to restrict the time a DELETE needs by running it in batches. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting from one table blocks other tables?
Set up a loop where you delete with a LIMIT, pause a few milliseconds to give others a chance to get in, and loop until no records are deleted. Philip Mak wrote: I have a table in a database. I'm trying to delete 5 million rows from it. But whenever I try to do this, while the delete command is executing access to the other tables in the database are blocked (which freezes up a website that runs off that database, even though that website doesn't use the table I'm deleting from)! Any suggestions on how I can delete those rows without causing a lot of downtime? I'm using 3.23.47. Would it help if I upgraded to 4.x? sql (stupid filter...) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting from one table blocks other tables?
From: Philip Mak [EMAIL PROTECTED] I'm trying to delete 5 million rows... Do you have to do this often? ...access to the other tables in the database are blocked... How do you mean blocked? Do you mean that you're getting an error message someplace, or that other operations take way too long, or something else? Any suggestions on how I can delete those rows without causing a lot of downtime? Do the big operations at a time when your site isn't busy. Like at night. sql (stupid filter...) Yes, but at least they're trying. --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting from one table blocks other tables?
On Sun, Jan 12, 2003 at 08:12:35PM -0700, Rodney Broom wrote: I'm trying to delete 5 million rows... Do you have to do this often? Sort of. I have a process that logs to MySQL, and I want to delete old log entries. I haven't been deleting old entries at all recently because it will freeze up everything, though. If I was deleting things regularly, I'd have to delete maybe a couple hundred thousand rows every day. ...access to the other tables in the database are blocked... How do you mean blocked? Do you mean that you're getting an error message someplace, or that other operations take way too long, or something else? When I say blocked, I mean e.g. another process tries to SELECT from another table in the database, but it takes way too long. Any suggestions on how I can delete those rows without causing a lot of downtime? Do the big operations at a time when your site isn't busy. Like at night. My site is always busy. People don't use it as much at night, but there's still many people using it. sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting from one table blocks other tables?
I'm trying to delete 5 million rows... Can you drop the table, then re-create it for further use? Anything besides the 5,000,000 you want to keep? Do you have to do this often? Sort of. I have a process that logs to MySQL, and I want to delete old log entries. I haven't been deleting old entries at all recently because it will freeze up everything, though. How long do you need to keep any of these log entries? Are you using any of the entries for anything useful, or are they just accumulating? If I was deleting things regularly, I'd have to delete maybe a couple hundred thousand rows every day. ...access to the other tables in the database are blocked... How do you mean blocked? Do you mean that you're getting an error message someplace, or that other operations take way too long, or something else? When I say blocked, I mean e.g. another process tries to SELECT from another table in the database, but it takes way too long. Do these log entries have to be stored in the same database? Any suggestions on how I can delete those rows without causing a lot of downtime? Do the big operations at a time when your site isn't busy. Like at night. My site is always busy. People don't use it as much at night, but there's still many people using it. Consider using the drop method or writing these entries to a different database - maybe even a different machine altogether. This seems to be a problem amenable to separating busy databse functions from each other. If there really are some records in that log table you need to keep, then you might consider dumping the records to a flat file for later procesing and then just drop that table and recreate it for further logging. Should be a lot faster than trying to do a massive delete from a production server. Personally, I would never write those logging entries to the same database server relied upon for responding to user requests. Are the log entries being produced by the same script that is serving users? I bet they are - if true, then rewrite the script to send those log records somewhere else besides the critical database/server. hope this helps - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
