List:General Discussion« Previous MessageNext Message »
From:Krishna Chandra Prajapati Date:March 11 2010 4:02pm
Subject:Re: Very slow delete for Master / Child tables with millions of rows
View as plain text  
Hi Randall,

How much memory is allocated to innodb_buffer_pool_size.

Please send your mysql configuration file (my.cnf)

Thanks,
Krishna

On Thu, Mar 11, 2010 at 8:57 PM, Price, Randall <Randall.Price@stripped>wrote:

> I am experiencing very slow deletes when I delete a record from a master
> table and have cascading deletes on two detail tables.
>
> I have an application that looks for records in the master table that are
> older than "X" days and delete them.  The cascasing deletes then handles
> deleting all the child records in the other tables.  However, this process
> is very slow.  Depending on how many records are found to delete, this
> process takes anywhere from 30-40 minutes to several hours.
>
> Due to the nature of my application, I must loop through the records to
> delete, do some stuff for each record, then delete it.  I suspect at this
> point, each tables' indexes need to be rebuilt.  There are several indexes
> and the ones for the tables with 4,000,000+ rows probably takes a while.
>
> My question is:  What is the best way to handle deleting master/detail
> records in this scenario?
>
> I have a brief diagram of my tables and the CREATE TABLE statements follow.
>
> Thanks,
>
> Randall Price
>
>
>
>                           +-------------------+
>                           | tblwsusclientinfo |
> +-----------------+        +-------------------+
> | tblwsusclients  |        | ID                |
> +-----------------+        | UpdateGUID        |
> | SusClientId     |<-----oo| SusClientId       |
> | ...             |   |    | ...               |
> +-----------------+   |    +-------------------+
>  ( ~ 3,000  rows)     |     (~ 4,000,000 rows )
>                      |
>                      |
>                      |    +-------------------------+
>                      |    | tblwsusevents           |
>                      |    +-------------------------|
>                      |    | EventGUID               |
>                      |    | ...                     |
>                      +--oo| EventAssociatedComputer |
>                           | ...                     |
>                           +-------------------------|
>                            (~ 4,300,000 rows )
>
>
> CREATE TABLE `tblwsusclients` (
>  `SusClientId` varchar(36) NOT NULL default '',
>  `DNSName` varchar(256) NOT NULL default '',
>  `ServerGUID` varchar(36) NOT NULL default '',
>  `IPAddress` varchar(15) NOT NULL default '',
>  `LastReportTime` datetime NOT NULL default '0000-00-00 00:00:00',
>  `LastSyncTime` datetime NOT NULL default '0000-00-00 00:00:00',
>  `DetectionResult` varchar(256) default NULL,
>  `ResponsiblePerson` varchar(16) default NULL,
>  `TargetGroup` varchar(45) default NULL,
>  `Affiliation` varchar(45) default NULL,
>  `AddedDate` datetime default NULL,
>  `IsActive` tinyint(1) default NULL,
>  `UnRegisteredDate` datetime default NULL,
>  `SCVersion` double default NULL,
>  `BiosName` varchar(256) default NULL,
>  `BiosVersion` varchar(45) default NULL,
>  `OSVersion` varchar(45) default NULL,
>  `SPVersion` varchar(45) default NULL,
>  `Make` varchar(256) default NULL,
>  `Model` varchar(256) default NULL,
>  `ProcArchitecture` varchar(45) default NULL,
>  `OSLongName` varchar(256) default NULL,
>  `TimedOutDate` datetime default NULL,
>  PRIMARY KEY  (`SusClientId`),
>  KEY `FK_tblwsusclients_1` (`ServerGUID`),
>  KEY `IX_DNSName` (`DNSName`),
>  KEY `IX_IsActive` (`IsActive`),
>  CONSTRAINT `FK_tblwsusclients_1` FOREIGN KEY (`ServerGUID`) REFERENCES
> `tblwsusservers` (`ServerGUID`) ON DELETE CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
>
>
> CREATE TABLE `tblwsusclientinfo` (
>  `ID` bigint(20) unsigned NOT NULL auto_increment,
>  `UpdateGUID` varchar(36) NOT NULL default '',
>  `SusClientId` varchar(36) NOT NULL default '',
>  `UpdateState` varchar(256) NOT NULL default '',
>  `LastTimeChanged` datetime default NULL,
>  PRIMARY KEY  (`ID`),
>  KEY `IX_UpdateState` (`UpdateState`),
>  KEY `IX_SusClientId_UpdateState` (`SusClientId`,`UpdateState`),
>  KEY `FK_tblwsusclientinfo_1` (`UpdateGUID`),
>  KEY `FK_tblwsusclientinfo_2` (`SusClientId`),
>  CONSTRAINT `FK_tblwsusclientinfo_1` FOREIGN KEY (`UpdateGUID`) REFERENCES
> `tblupdateinformation` (`UpdateGUID`) ON DELETE CASCADE ON UPDATE CASCADE,
>  CONSTRAINT `FK_tblwsusclientinfo_2` FOREIGN KEY (`SusClientId`) REFERENCES
> `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
>
>
> CREATE TABLE `tblwsusevents` (
>  `EventGUID` varchar(36) NOT NULL default '',
>  `EventCreationDate` datetime NOT NULL default '0000-00-00 00:00:00',
>  `EventMessage` text NOT NULL,
>  `EventId` varchar(256) NOT NULL default '',
>  `EventSource` varchar(45) NOT NULL default '',
>  `EventErrorCode` varchar(45) default NULL,
>  `EventIsError` tinyint(1) NOT NULL default '0',
>  `EventAssociatedUpdate` varchar(36) default NULL,
>  `EventAssociatedComputer` varchar(36) default NULL,
>  `EventAssociatedWSUSServer` varchar(36) NOT NULL default '',
>  PRIMARY KEY  (`EventGUID`),
>  KEY `IX_EventId` (`EventId`),
>  KEY `IX_EventCreationDate` (`EventCreationDate`),
>  KEY `FK_tblwsusevents_1` (`EventAssociatedComputer`),
>  CONSTRAINT `FK_tblwsusevents_1` FOREIGN KEY (`EventAssociatedComputer`)
> REFERENCES `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE
> CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
>

Thread
Very slow delete for Master / Child tables with millions of rowsRandall Price11 Mar
  • Re: Very slow delete for Master / Child tables with millions of rowsJohan De Meersman11 Mar
    • RE: Very slow delete for Master / Child tables with millions of rowsDaevid Vincent11 Mar
  • Re: Very slow delete for Master / Child tables with millions of rowsKrishna Chandra Prajapati11 Mar