Here is my.ini file - and I am using all Innodb [client] port=3306
[mysql] default-character-set=latin1 [mysqld] port=3306 skip-name-resolve basedir="W:/Applications/MySQL/MySQL Server 5.0/" datadir="W:/Applications/MySQL/MySQL Server 5.0/Data/" default-character-set=latin1 default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=100 query_cache_size=50M table_cache=512M tmp_table_size=103M thread_cache_size=8 #*** MyISAM Specific options myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G myisam_sort_buffer_size=205M key_buffer_size=512M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=2M #*** INNODB Specific options *** innodb_additional_mem_pool_size=7M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=3498K innodb_buffer_pool_size=512M innodb_log_file_size=170M innodb_thread_concurrency=10 query_cache_type=1 long_query_time=2 log-slow-queries=Slow.log innodb_file_per_table innodb_lock_wait_timeout=500 From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: Thursday, March 11, 2010 11:02 AM To: Price, Randall Cc: mysql@lists.mysql.com Subject: Re: Very slow delete for Master / Child tables with millions of rows 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<mailto: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
