Decline in speed due to total no. of attachments

[DanHerbertB]

Good afternoon Mantis Forums!

(This is not a support request, rather a possible solution to a problem )

We've been using the Mantis BT internally for a long while now and it has performed fantastically until recently. We use 1.1.1 with some customizations. Some pages have been slowing down more than others; my_view_page.php was the worst offender. We've traced the majority of speed impact down to file_bug_attachment_count() in core/file_api.php. We don't have many attachments in the system (total count is 1,766) but the files are stored in the database (MySQL) instead of the file system. Not that that is entirely relevant...

file_bug_attachment_count() calls a query which counts all the attachments/bug and has the returned results cached. An example of the time based on file_bug_attachment_count()'s query (wrapped as a sub-query):

Code: Select all

mysql> select count(bug_id) from (SELECT bug_id, COUNT(bug_id) AS attachments FROM mantis_bug_file_table GROUP BY bug_id) as tmp limit 1;
+---------------+
| count(bug_id) |
+---------------+
|           684 |
+---------------+
1 row in set (12.66 sec)

An EXPLAIN tells us that no indexes are being used, which is expected due to the GROUP BY.

We did disable this function for a while (via return 0;), but this caused some usability problems for our clients such as not seeing the paper-clip icon for issues with files. A better solution for us was to limit the query to the relevant bug_id(s) by adding a where clause.
core/file_api: (approx line 113, addition is 2nd to last line)

Code: Select all

                # Otherwise build the cache and return the attachment count
                #   for the given bug (if any).
                $query = "SELECT bug_id, COUNT(bug_id) AS attachments
                                FROM $t_bug_file_table
                                WHERE bug_id=$p_bug_id
                                GROUP BY bug_id";

I understand that file_bug_attachment_count() utilizes a caching system to prevent multiple calls for this purpose. We never were in a position to see all bugs with all attachment-counts at a single given time so we decided that slightly altering this behavior wouldn't impact anything else. We had to make the following change in the same function to keep displayed results accurate.
core/file_api.php: (approx line 107, 2 lines commented out)

Code: Select all

                # If there is no cache hit, check if there is anything in
                #   the cache. If the cache isn't empty and we didn't have
                #   a hit, then there are not attachments for this bug.
                #if ( count( $g_cache_file_count ) > 0 )
                #       return 0;

With this in mind we were able to greatly speed up pages like my_view_page.php whilst maintaining full functionality. We saw a load time of about 15 seconds for my_view_page.php cut down to about 1 second.

Lastly, I understand that this issue may have been resolved in a version of Mantis after 1.1.1. It is for this reason (in addition to not having a SourceForge account, etc) that I haven't made this contribution to the source itself and am writing it here in case some people find it relevant.

Cheers,
Dan