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.pr...@vt.edu>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 > >
