MySQL Bugs: #38542: data collection cascading deletes causes scalability problems

Bug #38542	data collection cascading deletes causes scalability problems
Submitted:	4 Aug 2008 15:16	Modified:	7 May 2009 15:13
Reporter:	Sloan Childers	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Server	Severity:	S3 (Non-critical)
Version:	1.3.3	OS:	Any
Assigned to:	Sloan Childers	CPU Architecture:	Any

Description:
Customers that delete a server with long data collection purge times frequently find the MEM server non-responsive and start killing processes.  Then InnoDB starts a massive rollback and things go down hill from there.

How to repeat:
Delete a managed instance that has a lot of data collection attached to it either from the UI or directly from the MEM database.

Suggested fix:
Investigate simply dropping the PK/FK relationship between data collection and the schedule table.  Possibly allow the data collection purge to remove orphan data collections on it's schedule but we'll have to make sure that it handles orphans and doesn't check schedules.

Here's the syntax to drop the FK that kills us on cascading deletes...

ALTER TABLE dc_integer DROP FOREIGN KEY dc_integer_ibfk_1;
ALTER TABLE dc_float DROP FOREIGN KEY dc_float_ibfk_1;
ALTER TABLE dc_varchar DROP FOREIGN KEY dc_varchar_ibfk_1;

Currently testing to see how long this takes with large data sets...  It doesn't look promising.

The data collection schema was re-architected in 2.x.  We are currently investigating better data compression and faster data purge algorithms in 2.1.x. This will not be fixed in the 1.x code branch.