Very slow delete for Master / Child tables with millions of rows
am 11.03.2010 18:09:08 von Randall.Price --_000_9269AB049ED62741A28FBF28706E31D32597516E27fangornccw2 kv_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Here is my.ini file - and I am using all Innodb
[client]
port=3D3306
[mysql]
default-character-set=3Dlatin1
[mysqld]
port=3D3306
skip-name-resolve
basedir=3D"W:/Applications/MySQL/MySQL Server 5.0/"
datadir=3D"W:/Applications/MySQL/MySQL Server 5.0/Data/"
default-character-set=3Dlatin1
default-storage-engine=3DINNODB
sql-mode=3D"STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGIN E_SUBSTITUTION"
max_connections=3D100
query_cache_size=3D50M
table_cache=3D512M
tmp_table_size=3D103M
thread_cache_size=3D8
#*** MyISAM Specific options
myisam_max_sort_file_size=3D100G
myisam_max_extra_sort_file_size=3D100G
myisam_sort_buffer_size=3D205M
key_buffer_size=3D512M
read_buffer_size=3D64K
read_rnd_buffer_size=3D256K
sort_buffer_size=3D2M
#*** INNODB Specific options ***
innodb_additional_mem_pool_size=3D7M
innodb_flush_log_at_trx_commit=3D1
innodb_log_buffer_size=3D3498K
innodb_buffer_pool_size=3D512M
innodb_log_file_size=3D170M
innodb_thread_concurrency=3D10
query_cache_type=3D1
long_query_time=3D2
log-slow-queries=3DSlow.log
innodb_file_per_table
innodb_lock_wait_timeout=3D500
From: Krishna Chandra Prajapati [mailto:prajapatikc@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 ro=
ws
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
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 `tb=
lwsusservers` (`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`) REFERENCES=
`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`) RE=
FERENCES `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCA=
DE
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1
--_000_9269AB049ED62741A28FBF28706E31D32597516E27fangornccw2 kv_--