-
Bug
-
Resolution: Fixed
-
Medium
-
5.9.1, 5.9.2, 5.9.4
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
- Stand up a Confluence 5.9.4
- Fill it with content
- Reindex (from scratch or otherwise)
- 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
- causes
-
-
- Closed
-
- is caused by
-
-
- Closed
-
- is related to
-
-
- Closed
-
- relates to
-
CONFSERVER-41318 Documentation request: Add note of a known performance issue for MySQL 5.5
- Closed
- included in
-
![[JIRA]](/images/icons/generic_link_16.png "[JIRA]")
CPU-282
Confluence 6.0.0-OD-2016.06.1-0007
-
CPU-283
Confluence 6.0.0-OD-2016.06.1-0008
-
CPU-287
Confluence 6.0.0-OD-2016.06.1-0009
- mentioned in
-
Page Loading...
-
-
-
-
-
[CONFSERVER-40544] Overall slowness in indexing with Confluence 5.9.x and MySQL 5.6
Ląd Maciej - PZU
added a comment - Hi,
In 5.9.9 I have this same problem. Altassian customer support indicated that they would open one. I just asked again on my support thread.
Currently we are attempting to migrate to MS SQL due to this indexing performance issue, to make matters worse, the XML import doesn't work, so we are hand converting the mysql db to mssql objects. Lovely.
R
Was a new issue opened? This has definitely not been fixed for us. We first noticed the issue at 5.9.3 and had hoped the fix in 5.9.5 would take care of it, but we just upgraded our 5.9.3 instance to 5.9.6 and it is not any better. We are running MySQL 5.5.22. In our 5.8.18 test instance we rebuilt 32,155 objects in (Elapsed Time: 00:15:32), on a 5.9.6 instance with nearly identical content, we are rebuilding 32,158 objects and it is at 20% in (Elapsed Time: 01:08:54). I would say there is still a pretty big issue. We would love to upgrade to 5.9.x, but we cannot do it until this is resolved. It finally finished in (Elapsed Time: 03:54:03)
Hi, yes I will raise a new issue. This was the one that the support engineer asked me to watch.
We can't move to 5.6 yet via debian wheezy packages. We are pretty strict on on running debian.
It's listed as unstable/testing: https://packages.debian.org/search?keywords=mysql-server-5.6
R
This issue is intended to address the index slowness related to MySQL 5.6
Here are the test results for each version with 8390 objects:
MySQL 5.6
5.9.4 1m15s, 1m12s, 1m08s
5.9.5 15s, 12s, 11s
MySQL 5.5
5.9.4 2m17s, 2m20s, 2m20s
5.9.5 1m57s, 1m41s, 1m44s
You can see there are improvements for both MySQL 5.6 and 5.5 which indicated this issue has been fixed.
May I suggest you to raise another issue for your problem specifically as your issue is not related to MySQL version (comparing 5.9.x to 5.8.x with MySQL 5.5)?
Further more I would recommend upgrading MySQL to 5.6 which has significant improvement on sub-query optimisations.
Hi, we are testing this release now and the perf issue for reindex doesn't appear to be fixed for us.
We run on debian wheezy with debian mysql ( Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.2 )
Before upgrading to 5.9.4 from 5.8.1.4 a full reindex took around an hour.
A full index on 5.9.4 took nearly 3 days to complete on our production host. The daily queue regularly gets backed up now.
I have started a reindex on 5.9.5, currently we are at 5% after 1.5 hours, so maybe it will take 30 hours to complete. So maybe there is an improvement here but we are no where close to 1 to 2 hours that it used to take.
Hello, i have the same problem on Confluence version 5.9.9 with installed MySQL 5.6.32.
156000 were processed in time 23:03:42