Question

I am getting complaints of slow performance on a FogBugz install running on Windows Server 2003 with SQL Server 2008 running on a seperate box. Are there any guidelines for how to troubleshoot this? Anything specific I should be monitoring?

Answer 1

FogBugz receives some amount of performance tuning with every release, so if you're not presently using the latest version of FogBugz (and upgrading is viable), your first step should probably be to get the latest version of FogBugz.

In your specific case:

1. Upgrade ;-)

2. Do a ping from the webhost to the DB server, see what that is. Lower is better, but it's nothing to be overly concerned with unless it's above ~50ms. (>100ms would be bad).

3. If you're at the 1-2 second latency level for viewing a FogBugz page, about half of that is going to be the browser working after it's already received everything from FogBugz. Not much you can do about that outside of your choice of browser, but we're continually working to make that better.

4. For differences between your US and Europe users, that comes down to your web server and the relative latencies between it and the users.

Shawn, one of our SysAdmins, says:

Without knowing more (database size, SQL server specs, server load, other tasks it’s performing, characteristics of slowness) all I can do is speculate. I have two guesses:

• His indexes are fragmented
• He’s let his data and log files autogrow so they’re fragmented

Fixing the latter involves a few steps and significant downtime during the disk defrag.

• Run a DBCC SHRINKFILE operation on his data file with the NOTRUNCATE option set
• Manually set the size of the data file to allow significant growth
• Put the database into simple recovery mode (this will break his transaction log backup chain)
• Run a DBCC SHRINKFILE operation on his log file with the TRUNCATEONLY option set
• If he’s doing transaction log backups, set the size of the log file so that it never has to grow and then revert to Full recovery mode. Otherwise, you’re done with the log file.
• Turn off SQL Server and defrag the disk itself
• Rebuild all indexes in the database

Info on the DBCC SHRINKFILE command: http://msdn.microsoft.com/en-us/library/ms189493.aspx

Good reading on database maintenance (these are oriented toward non-DBAs): http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx http://sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx

Hope that helps. I can get more specific with more information about his setup.

As a very generalized overview of FogBugz performance, there are perhaps 5 main areas:

1. Your Database. The vast majority of FogBugz performance issues will be related to the database running FogBugz. FogBugz spends a lot of time interacting with its database, and if the database is slow, under high load, or there's a high latency between your database box and your FogBugz webhost, FogBugz starts slowing down. Rebuilding your indicies can be a good start to debunking DB slowdown issues- beyond that, there's a suite of database tuning tools available.
   • MySQL: One thing to try is to turn on the slow queries log.
   • SQL Server: The SQL Server Profiler is great and can easily show you slow queries.
     
     
2. Your email server. This is a curious one, in that it has an impact on Heartbeat, which is the underlying service which FogBugz uses to perform a variety of maintenance tasks, from search indexing to, well, email. If the latency in checking a mailbox is large (several minutes), heartbeat will slow down (especially if you have multiple mailboxes.) A slowdown of heartbeat is interesting in that it doesn't really affect end-user perception of speed. What it does affect is the time required for FogBugz to do things like:
   • Add new cases to the search index
   • Send email notifications
   • Respond to incoming email
     
     

3. Full-Text Search. If your database is under ~5gb in size, it's probably pointless for you to consider FTS as a bottleneck. If FTS is being slow, this is most likely caused by a high latency between the webhost and the database server. The FogBugz search index is stored in the server, and some searches require heavy use of the index, which unfortunately entails many webhost-database roundtrips. Full-Text Search performance received a lot of attention in FogBugz 7.1, so if this is what's slowing you down, and you're not presently using FB 7.1 or greater, upgrading is going to be the best solution. Protip: Full-Text Search is fastest if the FogBugz webhost and the FogBugz database are the same machine. FTS performance degenerates linearly with a high coefficient as round-trip time between the database and the webhost increases.

4. Plugins. FogBugz plugins are mighty, but like Spiderman, they mandate responsibility. Sometimes a plugin will hook into basic FogBugz functions in a way that can cause massive slowdown due to a neglected database index, a poor algorithm, etc. This is usually just a case of the plugin author not designing for the use case that you have. Try disabling your plugins and seeing if that solves the performance issue. If it does, try and find the specific plugin which is causing problems, and then notify the plugin author.

5. The FogBugz Webhost. If it's CPU bound by the FogBugz process, there's an issue here. Usually this indicates that the full-text indexer is temporarily choking on something. Otherwise, you're generally ok outside of the standard webserver concerns.

Answer 2

Removing the "Project Backlog" plug-in improved performance dramatically