Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-40544

Overall slowness in indexing with Confluence 5.9.x and MySQL 5.6



      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


      • 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.


      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:

        1. 5.5 after change.png
          5.5 after change.png
          43 kB
        2. 5.5 before change.png
          5.5 before change.png
          42 kB
        3. 5.6 after change.png
          5.6 after change.png
          44 kB
        4. 5.6 before change.png
          5.6 before change.png
          43 kB

            rading Ran
            dponzio Daniel Ponzio
            15 Vote for this issue
            45 Start watching this issue