Very slow delete for Master / Child tables with millions of rows

--_000_9269AB049ED62741A28FBF28706E31D32597516D9Ffangornccw2 kv_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I am experiencing very slow deletes when I delete a record from a master ta=
ble and have cascading deletes on two detail tables.

I have an application that looks for records in the master table that are o=
lder than "X" days and delete them. The cascasing deletes then handles del=
eting 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 del=
ete, do some stuff for each record, then delete it. I suspect at this poin=
t, 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 reco=
rds 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 `t=
blwsusservers` (`ServerGUID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1



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`) REFERENCE=
S `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1



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`) R=
EFERENCES `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASC=
ADE
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1


--_000_9269AB049ED62741A28FBF28706E31D32597516D9Ffangornccw2 kv_--

Re: Very slow delete for Master / Child tables with millions of rows

--0016367d6ff4900b8e04818844b5
Content-Type: text/plain; charset=ISO-8859-1

If you really have to loop through the entire set deleting record by record,
I'm not surprised it's slow. Could you change your application to loop
through the records doing "stuff" without deleting (maybe even "do stuff" en
masse), and afterwards do a mass delete ?

I also have a nagging suspicion (unfounded by any actual experience, though)
that it might be faster to cut the explicit relations and do the child
deletes as a separate single-block execute, too. Easy enough to test, I
suppose.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0016367d6ff4900b8e04818844b5--

Re: Very slow delete for Master / Child tables with millions of rows

--001636d33c7cf6744c0481888873
Content-Type: text/plain; charset=ISO-8859-1

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 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
>
>

--001636d33c7cf6744c0481888873--

RE: Very slow delete for Master / Child tables with millions of rows

This isn't surprising, especially if you have foreign keys or indexes, as
each DELETE will cascade and require a rebuild of the indexes (just as an
INSERT does).

Make sure that for each DELETE you are using LIMIT 1; if it's in a loop
(and you're not deleting via PK, but it's a good habit to get into and
won't hurt if using a PK).

A trick I've done, is create another column called "delete_me" or
something, then you loop through and set a flag to 1 for all records you
want deleted. Then at the end (or during the night via crontab or
something) you simply:

DELETE FROM foo WHERE delete_me = 1;

(you may need to adjust other code to ignore any records that are set for
deletion in SELECTs)

Depending on your schema you might also be able to do something like this,
if you want to manage the foreign key deletes yourself.

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM foo WHERE delete_me = 1;
DELETE FROM bar WHERE delete_me = 1;
SET FOREIGN_KEY_CHECKS=1;

I thought there was a way to turn of indexes as well in a similar way.


http://dev.mysql.com/doc/refman/5.0/en/delete.html

Also look at the LOW_PRIORITY if you're using MYISAM tables and QUICK.

Try the trick of INSERTing into a NEW table and using RENAME instead of
DELETE all together.

Store the ID's you want to delete in another table (maybe even a
HEAP/MEMORY one), then:
DELETE FROM LargeTable USING LargeTable INNER JOIN TemporarySmallTable ON
LargeTable.ID = TemporarySmallTable.ID;

Another idea I just had that may work is to use a VIEW as your SELECT table
(where 'delete_me <> 1') then all your code points at the VIEW, and you can
delete from the real main table whenever you like, or just keep it for
archival purposes.

Consider OPTIMIZE TABLE to reclaim unused space and reduce file sizes when
done too.

> -----Original Message-----
> From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On
> Behalf Of Johan De Meersman
> Sent: Thursday, March 11, 2010 7:43 AM
> To: Price, Randall
> Cc: mysql@lists.mysql.com
> Subject: Re: Very slow delete for Master / Child tables with
> millions of rows
>
> If you really have to loop through the entire set deleting
> record by record,
> I'm not surprised it's slow. Could you change your application to loop
> through the records doing "stuff" without deleting (maybe
> even "do stuff" en
> masse), and afterwards do a mass delete ?
>
> I also have a nagging suspicion (unfounded by any actual
> experience, though)
> that it might be faster to cut the explicit relations and do the child
> deletes as a separate single-block execute, too. Easy enough
> to test, I
> suppose.
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org