List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:June 15 1999 1:07pm
Subject:Re: how to make deletes not block inserts into same table?
View as plain text  
Vivek Khera wrote:
> 
> I've got a table with 9,451,483 records in it.  I'm running a delete
> which should get rid of about 480,000 of those rows.  The delete has
> gone one for over 10 minutes, while all other inserts into that table
> have blocked.  This quickly filled up my connection table as the web
> site posting the data is quite busy...
> 
> Anyhow, what can I do to make the delete either 1) go faster (a select
> on the same data takes only about 2 minutes) or 2) not block the
> inserts?
> 
> The table is structured like this (non-relevent and non-key fields
> deleted):
> 
>   user_id int(10) unsigned DEFAULT '0' NOT NULL,
>   game_name varchar(6) DEFAULT '' NOT NULL,
>   game_serial mediumint(8) unsigned DEFAULT '0' NOT NULL,
>   play_validation varchar(44) DEFAULT '' NOT NULL,
>   play_id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
>   play_time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
>   PRIMARY KEY (play_id),
>   KEY game_name (game_name,game_serial),
>   KEY play_time (play_time),
>   UNIQUE play_validation (play_validation),
>   KEY user_id (user_id)
> 
> The delete in question looks like this:
> 
>  DELETE FROM mytable WHERE game_name='abcdef' AND game_serial=5
> 
> I'm running 3.22.14-gamma, for sun-solaris2.6 on sparc (binary
> distribution) as installed by the hosting company on our dedicated
> box.
> 
> Unfortunately, the server monitor program noticed that mysql was full
> of connections and killed the server before I could kill the thread,
> so I think I lost some data.  Bummer.  The table index is corrupted as
> well, so isamchk is fixing it now...
> 
> Any pointers to getting this delete to finish reasonably fast would
> help greatly.  Thanks!
> 

Here is a rather crude solution, maybe you can refine it a little bit:

-Do a select into a temporary table inserting all the ids that need to
be deleted
- Then proceed with an incremental delete: pick up a group of ids to
delete, delete them, make a record of deletion, then sleep for a little
bit, then do another batch until you are finished.


-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
Thread
how to make deletes not block inserts into same table?Vivek Khera15 Jun
  • Re: how to make deletes not block inserts into same table?Sasha Pachev15 Jun
  • Re: how to make deletes not block inserts into same table?Michael R. Gile15 Jun
    • Re: how to make deletes not block inserts into same table?Vivek Khera15 Jun
      • Re: how to make deletes not block inserts into same table?Michael Widenius25 Jun