List:General Discussion« Previous MessageNext Message »
From:Vivek Khera Date:June 15 1999 6:54pm
Subject:how to make deletes not block inserts into same table?
View as plain text  
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!
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