Summary

When running Confluence 5.9.4 on MySQL 5.6.x, content indexing takes much longer (anywhere from 6-10 times as much time) compared to when running on MySQL 5.5.x or any other version of MySQL running on a Confluence version 5.8.x

Environment

• Ubuntu 14.04
• Confluence 5.9.4
• MySQL 5.6.27
• MySQL 5.5.46
• MySQL 5.6.21

  Steps to Reproduce

1. Stand up a Confluence 5.9.4
2. Fill it with content
3. Reindex (from scratch or otherwise)
4. Compare the amount it takes for the same content on Confluence 5.8.x or Confluence 5.9.4 with MySQL 5.5

Expected Results

In a Confluence 5.9.4 instance running on MySQL 5.5, indexing from scratch takes about 9 seconds.

Actual Results

With this same amount of content (restored from backup) running on MySQL 5.6 the indexing takes around 1 minute. This is not such a significant change, but this occurs when indexing ~3500 pieces of content. We have multiple tickets with much larger instances in support reporting upwards of 40 hours to complete a re-index.

Workaround

At this point, we have been unable to find a workaround. The following has been attempted to see if it addresses the issue from a mysql bug: https://bugs.mysql.com/bug.php?id=74739

Meanwhile you have two options to avoid this problem: either disable query caching on server (by setting 'query_cache_size=0' or 'query_cache_type=OFF' http://dev.mysql.com/doc/refman/5.5/en/query-cache.html) or stop using cursor fetch in client (connection property 'useCursorFetch=false').

This could be a regression of the following bug found immediately after upgrading to Confluence 5.9.x, but it seems like there is a more general issue with indexing and MySQl in Confluence 5.9: CONF-39746

Other related bugs have also been closed as support requests, but so far support has been unable to find a solution to this issue:
https://jira.atlassian.com/browse/CONF-40134
https://jira.atlassian.com/browse/CONF-40399