SonarQube 5.6.7 unable to search for issues (ES error)

I'm able to perform analysis, though this is running very slow. Now I'm facing even more problem with browsing through issues. Our infrastructure is not keeping up with Elastic Search requirements, SECCOMP is not compiled into kernel so I have this option added to sonar.properties sonar.search.javaAdditionalOpts=-Dbootstrap.system_call_filter=false

Also I have supplied crazy amount of memory,

sonar.search.javaOpts=-Xmx2G -Xms1G -Xss1m -Djava.net.preferIPv4Stack=true \
  -XX:+UseParNewGC -XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=75 \
  -XX:+UseCMSInitiatingOccupancyOnly -XX:+HeapDumpOnOutOfMemoryError

System 64-bit, 16 GM RAM and 2 cores; runs RHEL 6.7

And the ES breaks down with following log.

2018.03.01 14:32:34 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1285367970 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:09:43 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1285374084 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:10:04 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1285374084 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:20:15 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1285379720 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:20:47 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1285379720 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:21:30 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1285379720 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:21:39 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1287374978 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:02 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1287374978 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:12 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1279785543 [1.1gb] from field [issueCreatedAt] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:12 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1279798446 [1.1gb] from field [issueCreatedAt] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:12 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1279787446 [1.1gb] from field [issueCreatedAt] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:12 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1279771440 [1.1gb] from field [issueCreatedAt] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:12 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1279800560 [1.1gb] from field [issueCreatedAt] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:13 ERROR web[o.s.s.w.WebServiceEngine] Fail to process request http://172.17.33.86:9091/sonar/api/issues/search?p=1&ps=50&s=FILE_LINE&asc=true&additionalFields=_all&facets=types%2Cresolutions%2CcreatedAt&resolved=false&types=VULNERABILITY&sinceLeakPeriod=true&componentUuids=AWHS0koMv8wI-iczVKXw
java.lang.IllegalStateException: Fail to execute ES search request '{"from":0,"size":50,"query":{"filtered":{"query":{"match_all":{}},"filter":{"bool":{"must":[{"terms":{"modulePath":["AWHS0koMv8wI-iczVKXw"]}},{"missing":{"field":"resolution"}},{"terms":{"type":["VULNERABILITY"]}},{"range":{"issueCreatedAt":{"from":"2018-02-19T19:14:08.456Z","to":null,"include_lower":true,"include_upper":true},"_cache":false}},{"has_parent":{"query":{"filtered":{"query":{"match_all":{}},"filter":{"bool":{"must":{"or":{"filters":[{"term":{"users":"admin"}},{"term":{"groups":"Anyone"}},{"term":{"groups":"sonar-users"}},{"term":{"groups":"sonar-administrators"}}]}},"_cache":true}}}},"parent_type":"authorization"}}]}}}},"sort":[{"project":{"order":"asc","missing":"_first"}},{"filePath":{"order":"asc","missing":"_first"}},{"line":{"order":"asc","missing":"_first"}},{"severityValue":{"order":"desc","missing":"_first"}},{"key":{"order":"asc","missing":"_first"}}],"aggregations":{"types":{"global":{},"aggregations":{"types_filter":{"filter":{"bool":{"must":[{"query":{"match_all":{}}},{"terms":{"modulePath":["AWHS0koMv8wI-iczVKXw"]}},{"missing":{"field":"resolution"}},{"range":{"issueCreatedAt":{"from":"2018-02-19T19:14:08.456Z","to":null,"include_lower":true,"include_upper":true},"_cache":false}},{"has_parent":{"query":{"filtered":{"query":{"match_all":{}},"filter":{"bool":{"must":{"or":{"filters":[{"term":{"users":"admin"}},{"term":{"groups":"Anyone"}},{"term":{"groups":"sonar-users"}},{"term":{"groups":"sonar-administrators"}}]}},"_cache":true}}}},"parent_type":"authorization"}}]}},"aggregations":{"types":{"terms":{"field":"type","size":10,"min_doc_count":1,"order":{"_count":"desc"}}},"types_selected":{"terms":{"field":"type","include":"VULNERABILITY"}}}}}},"resolutions":{"global":{},"aggregations":{"resolutions_filter":{"filter":{"bool":{"must":[{"query":{"match_all":{}}},{"terms":{"modulePath":["AWHS0koMv8wI-iczVKXw"]}},{"terms":{"type":["VULNERABILITY"]}},{"range":{"issueCreatedAt":{"from":"2018-02-19T19:14:08.456Z","to":null,"include_lower":true,"include_upper":true},"_cache":false}},{"has_parent":{"query":{"filtered":{"query":{"match_all":{}},"filter":{"bool":{"must":{"or":{"filters":[{"term":{"users":"admin"}},{"term":{"groups":"Anyone"}},{"term":{"groups":"sonar-users"}},{"term":{"groups":"sonar-administrators"}}]}},"_cache":true}}}},"parent_type":"authorization"}}]}},"aggregations":{"resolutions":{"terms":{"field":"resolution","size":15,"min_doc_count":1,"order":{"_count":"desc"}}},"resolutions_missing":{"missing":{"field":"resolution"}}}}}},"createdAt":{"date_histogram":{"field":"issueCreatedAt","interval":"1d","min_doc_count":0,"pre_zone":"GMT","offset":"-3600s","format":"yyyy-MM-dd'T'HH:mm:ssZ","extended_bounds":{"min":1519067648456,"max":1519914131788}}}}}' on indices '[issues]' on types '[issue]'
    at org.sonar.server.es.request.ProxySearchRequestBuilder.get(ProxySearchRequestBuilder.java:47) ~[sonar-server-5.6.7.jar:na]
    at org.sonar.server.es.request.ProxySearchRequestBuilder.get(ProxySearchRequestBuilder.java:35) ~[sonar-server-5.6.7.jar:na]
    at org.sonar.server.issue.index.IssueIndex.search(IssueIndex.java:235) ~[sonar-server-5.6.7.jar:na]
    at org.sonar.server.issue.ws.SearchAction.doHandle(SearchAction.java:301) ~[sonar-server-5.6.7.jar:na]
    at org.sonar.server.issue.ws.SearchAction.handle(SearchAction.java:288) ~[sonar-server-5.6.7.jar:na]
    at org.sonar.server.ws.WebServiceEngine.execute(WebServiceEngine.java:107) ~[sonar-server-5.6.7.jar:na]
    at sun.reflect.GeneratedMethodAccessor228.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_91]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_91]
    at org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(JavaMethod.java:425) [jruby-complete-1.7.9.jar:na]
    at org.jruby.javasupport.JavaMethod.invokeDirect(JavaMethod.java:292) [jruby-complete-1.7.9.jar:na]
    at org.jruby.java.invokers.InstanceMethodInvoker.call(InstanceMethodInvoker.java:44) [jruby-complete-1.7.9.jar:na]

Yes it does plead for sometime before breaking down and refuses to show any issue on UI. Please help, all set to upgrade to SQ 5.6.7 so later we can upgrade to SQ 6.7.1 but now we are still running SQ 4.5.4 in Production (and it is able to handle such large amount of data)

How large is our DB? Our database has close to 27 million LOC, with 5.6 million closed/open issues on Database.

Solution

Proper Solution

Upgrade to SonarQube 6.1 LTS
if you are Oracle DB, make sure tables are analysed. i.e., keep indexing up-to-date
If you don't have the room to upgrade and indexes are up-to-date yet you encounter slowness, set minimal weeks for Database Cleaner, even less than what is recommended by SonarQube and run analysis.

When SonarQube removes old Snapshots all related old issues will be properly removed, only when new analysis is ran db cleaner is executed on the component. If you have ghost projects (not in SCM anymore) create empty projects and use same key to clear the DB.

Stupid Hack

For long and hard, I tried to change the code of SonarQube as recommended in the issue SONAR-8067 (linked in comments by Ann). But I was not able to do that, the description was too high level and I have almost no time at hand to reverse engineer like mad, so I picked the brute forced.

Special mention: I believe we already have a pretty crushing policy for closed issues, but they remained stubborn.

Thanks to scanning of minified JS, everyday our DB is exploding exponentially, right now it has 6.8 million rows in Issues table, and with no choice I executed the following command.

delete from issues where status = 'CLOSED' or status = 'RESOLVED'

This sql statement took 14 minutes and cleared 4.5 million rows from the table. Later from SonarQube home directory removed data, temp directories and restarted SonarQube, now ES is breathing. But the story is not over yet. Now the dashboard is unstable and the 6.8million rows are back in the table! (I have no idea how this can even happen, some rogue code in SonarQube I suspect).

Issues are still browsable from Issues, Measures and Code Dashboards. Like us if you have a custom Dashboard add Issues Widget,

Note to moderators: the stupid hack might be enough sometimes.