Question

We experienced some very long running SQL queries on MySQL 5.5 server:

DELETE FROM IndexFilePage WHERE ixIndexFile IN (SELECT ixIndexFile FROM IndexFile WHERE ixLastGeneration < xxxx OR ixLastGeneration < ixFirstGeneration);

DELETE FROM Attachment WHERE ixAttachment IN (SELECT ixAttachment FROM AttachmentReference WHERE ixBugEvent = xxxx);

Due to the use of IN MySQL does a full table scan w/o using an index so this resulted in an overall slow FogBugz system.

Our solution was to modify two procedures in Wasabi files.

CBugEvent.was procedure DeleteExistingAttachments()

 cmd = GetCmd("SELECT ixAttachment FROM AttachmentReference WHERE ixBugEvent = ?", "I")
 cmd.SetParam 1, ixBugEvent
 Dim rs = cmd.ExecuteKeyset

 Dim cmdDelete

 While Not rs.EOF
   ' Delete the actual attachments
   cmdDelete = GetCmd( "DELETE FROM Attachment WHERE ixAttachment = ?", "I" )
   cmdDelete.SetParam 1, rs("ixAttachment")
   cmdDelete.ExecuteNonQuery
   rs.MoveNext
 Wend

 CloseRS rs

CIndexFile.was procedure DeleteOldIndexFiles

 Dim cmd = GetCmd("SELECT ixIndexFile FROM IndexFile WHERE ixLastGeneration < ? OR ixLastGeneration < ixFirstGeneration", "I")
 cmd.SetParam 1, ixOldestGenerationAllowed
 Dim rs = cmd.ExecuteKeyset

 Dim cmdDelete

 While Not rs.EOF
   ' Delete the actual attachments
   cmdDelete = GetCmd( "DELETE FROM IndexFilePage WHERE ixIndexFile = ?", "I" )
   cmdDelete.SetParam 1, rs("ixIndexFile")
   cmdDelete.ExecuteNonQuery
   rs.MoveNext
 Wend

 CloseRS rs

This worked for us in FogBugz 8.4.87 (DB 780, Build 1).

Disclaimer: USE AT YOUR OWN RISK! Please understand that you modify the Wasabi source code of FogBugz and that nobody can be held responsible for any data loss. Please make reliable backups first! I REPEAT: USE AT YOUR OWN RISK!

Case FC2060975

Answer 1

UPDATE: We'll be shipping a fix for these two methods in FogBugz 8.5, tentatively scheduled for mid-July.

First of all: Thank you for posting this here! This is awesome, and you rocked my world.

I've implemented these changes with a few customers now and it resolved the relevant performance problems in all of them. The installations are still running and haven't reported any related problems. Obviously, this isn't proper testing and these aren't "official" Fog Creek changes, but this is at least a good sign.

The key is to make sure you're experiencing these specific issues, otherwise these changes won't help.

If you're experiencing these problems and are comfortable making these changes, it should be safe ON THE CONDITION that you can tolerate a little downtime in case things go south and MAKE A BACKUP BEFORE YOU EVEN THINK ABOUT IT.

(Was that clear enough? ;-) )

If you're not sure how to make source code changes, you should check out this question.