List:General Discussion« Previous MessageNext Message »
From:pan Date:January 13 2003 3:33am
Subject:Re: Deleting from one table blocks other tables?
View as plain text  
> > > 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
> 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

Deleting from one table blocks other tables?Philip Mak12 Jan
  • Re: Deleting from one table blocks other tables?Rodney Broom13 Jan
    • Re: Deleting from one table blocks other tables?Philip Mak13 Jan
      • Re: Deleting from one table blocks other tables?Benjamin Pflugmann13 Jan
  • Re: Deleting from one table blocks other tables?pan13 Jan
  • Re: Deleting from one table blocks other tables?gerald_clark13 Jan