List:General Discussion« Previous MessageNext Message »
From:Randall Price Date:March 11 2010 5:09pm
Subject:Very slow delete for Master / Child tables with millions of rows
View as plain text  
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:prajapatikc@stripped]
Sent: Thursday, March 11, 2010 11:02 AM
To: Price, Randall
Cc: mysql@stripped
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.Price@stripped<mailto: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