Uploaded image for project: 'Confluence Server and Data Center'
  1. Confluence Server and Data Center
  2. CONFSERVER-59804

Using the Spaces List macro in a Confluence Page may overload the database

    XMLWordPrintable

    Details

      Description

      Issue Summary

      The Spaces List Macro creates a panel the page with a list of Spaces available in the Confluence instance.
      The user adding this macro can choose from the following options on which Spaces will be listed:

      • (blank) - All spaces in your site, with tabs.
      • all – All spaces in your Confluence site.
      • category – Spaces grouped according to space categories.
      • favorite – Spaces which you have added to My Spaces.
      • new – spaces created within the last 7 days.

      The options all and category (which is the default when the option is left on blank) will fetch all the Spaces from the Confluence instance and show that as an output within the page.

      For Enterprise-sized instances, having thousands of Spaces is a common situation.
      Depending on how the cache is, Hibernate will need to retrieve this heavy data from the database, since it won't paginate the request.
      If the page containing the macro is requested by multiple users at the same time, then it may create an event on which the database server is overloaded, cascading the issue to any other operation that may require information from the database.

      Steps to Reproduce

      1. Install a vanilla instance of Confluence.
      2. Create a Page in a sample Space and add a Spaces List Macro with the default options.
      3. Create 4,000 Spaces.
        • You may use the following script to create them.
          ADMIN_USRNAME=admin
          ADMIN_PWD=admin
          CONFLUENCE_BASE_URL=http://127.0.0.1:8090/confluence
           
          SPACE_KEY=SPC
          SPACE_NAME=SPACE_
          SPACE_DESC=TEST_SPACE
           
          for i in $(seq 1 4000); do
           curl -u $ADMIN_USRNAME:$ADMIN_PWD -X POST -H 'content-type: application/json' ${CONFLUENCE_BASE_URL}/rest/api/space -d '{ "key": "'${SPACE_KEY}${i}'", "name": "'${SPACE_NAME}${i}'", "description": { "plain": { "value": "'${SPACE_DESC}'", "representation": "plain" } }, "metadata": {} }' >/dev/null 2>&1
          done
          
      4. Go to Cog icon > General Configuration > Cache Management and flush all caches.
        • This will force Confluence to go the database on subsequent requests.
      5. Run 10 simultaneous requests to display the page with the Spaces List Macro.
        • You may use a script similar to the below to run the 10 requests.
          USER_USRNAME=user
          USER_PWD=user
          CONFLUENCE_BASE_URL=http://127.0.0.1:8090/confluence
          SPACE_KEY=SPACE123
          PAGE_WITH_MACRO=Page+with+Spaces+List+Macro
          
          for i in $(seq 1 10); do
          nohup curl -u ${USER_USRNAME}:${USER_PWD} ${CONFLUENCE_BASE_URL}'/display/'${SPACE_KEY}'/'${PAGE_WITH_MACRO} >/dev/null &
          done
          

      Expected Results

      All requests are completed in a timely manner without overloading Confluence nor the database.
      If the number of Spaces are too high, the resulting list will be paginated, similar to the presentation seen in the Space Directory.

      Actual Results

      A single request takes a long time to complete and the simultaneous requests may overload the database if the information can't be retrieved from the cache.

      The results below were taken from a test instance when requesting the target page once from the browser depending on how many Spaces are in the Confluence instance.

      Number of Spaces Processing Time HAR image
      100 0.5s
      500 1.13s
      1,000 2.33s
      2,000 4.71s
      4,000 12.74s

      Monitoring the server resources shows an increase on the CPU usage for the database server.

      If thread dumps are taken during the period of high load, we can see threads with stack traces similar to the below:

      "http-nio-21310-exec-25" #767 daemon prio=5 os_prio=31 tid=0x00007fb7bad83800 nid=0x1b603 runnable [0x00007000257c7000]
         java.lang.Thread.State: RUNNABLE
      	at java.net.SocketInputStream.socketRead0(Native Method)
      	at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
      	at java.net.SocketInputStream.read(SocketInputStream.java:171)
      	at java.net.SocketInputStream.read(SocketInputStream.java:141)
      	at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:140)
      	at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:109)
      	at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:67)
      	at org.postgresql.core.PGStream.receiveChar(PGStream.java:288)
      	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1962)
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
      	- locked <0x00000006d7227c30> (a org.postgresql.core.v3.QueryExecutorImpl)
      	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
      	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
      	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
      	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:117)
      	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
      	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
      	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:71)
      	at org.hibernate.loader.Loader.getResultSet(Loader.java:2123)
      	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1911)
      	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887)
      	at org.hibernate.loader.Loader.doQuery(Loader.java:932)
      	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
      	at org.hibernate.loader.Loader.doList(Loader.java:2615)
      	at org.hibernate.loader.Loader.listUsingQueryCache(Loader.java:2460)
      	at org.hibernate.loader.Loader.list(Loader.java:2422)
      	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
      	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:370)
      	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
      	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1481)
      	at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1441)
      	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1410)
      	at com.atlassian.confluence.spaces.persistence.dao.hibernate.HibernateSpaceDao.lambda$getSpaces$4(HibernateSpaceDao.java:293)
      	at com.atlassian.confluence.spaces.persistence.dao.hibernate.HibernateSpaceDao$$Lambda$1259/1162413186.doInHibernate(Unknown Source)
      	at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:361)
      	at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:315)
      	at com.atlassian.confluence.spaces.persistence.dao.hibernate.HibernateSpaceDao.getSpaces(HibernateSpaceDao.java:285)
      	at com.atlassian.confluence.spaces.DefaultSpaceManager$3.getElements(DefaultSpaceManager.java:654)
      	at com.atlassian.confluence.core.DefaultListBuilder.getPage(DefaultListBuilder.java:44)
      	at com.atlassian.confluence.plugins.macros.dashboard.DashboardMacroSupport.getLabelsForPermittedSpaces(DashboardMacroSupport.java:194)
      	at com.atlassian.confluence.plugins.macros.dashboard.DashboardMacroSupport.getViewableTeamLabels(DashboardMacroSupport.java:180)
      	at com.atlassian.confluence.plugins.macros.dashboard.SpacesListMacro.execute(SpacesListMacro.java:154)
      	at com.atlassian.renderer.v2.macro.ResourceAwareMacroDecorator.execute(ResourceAwareMacroDecorator.java:51)
      	at com.atlassian.confluence.macro.V2CompatibilityMacro.execute(V2CompatibilityMacro.java:36)
      	at com.atlassian.confluence.macro.LazyLoadingMacroWrapper.execute(LazyLoadingMacroWrapper.java:24)
      	at com.atlassian.confluence.content.render.xhtml.view.macro.ViewMacroMarshaller.executeMacro(ViewMacroMarshaller.java:255)
      	at com.atlassian.confluence.content.render.xhtml.view.macro.ViewMacroMarshaller.marshalInternal(ViewMacroMarshaller.java:170)
      	at com.atlassian.confluence.content.render.xhtml.view.macro.ViewMacroMarshaller.lambda$marshal$0(ViewMacroMarshaller.java:125)
      	at com.atlassian.confluence.content.render.xhtml.view.macro.ViewMacroMarshaller$$Lambda$1673/1757127376.marshal(Unknown Source)
      	at com.atlassian.confluence.impl.content.render.xhtml.analytics.MetricsCollectingMarshaller.marshal(MetricsCollectingMarshaller.java:52)
      	at com.atlassian.confluence.content.render.xhtml.view.macro.ViewMacroMarshaller.marshal(ViewMacroMarshaller.java:132)
      	at com.atlassian.confluence.content.render.xhtml.view.macro.ViewMacroMarshaller.marshal(ViewMacroMarshaller.java:59)
      	at com.atlassian.confluence.content.render.xhtml.UnmarshalMarshalFragmentTransformer.transform(UnmarshalMarshalFragmentTransformer.java:29)
      	at com.atlassian.confluence.content.render.xhtml.transformers.DefaultFragmentTransformer.transformFragment(DefaultFragmentTransformer.java:141)
      	at com.atlassian.confluence.content.render.xhtml.transformers.DefaultFragmentTransformer.transform(DefaultFragmentTransformer.java:123)
      	at com.atlassian.confluence.content.render.xhtml.storage.StorageXhtmlTransformer.transform(StorageXhtmlTransformer.java:41)
      	at com.atlassian.confluence.content.render.xhtml.TransformerChain.transform(TransformerChain.java:33)
      	at com.atlassian.confluence.content.render.xhtml.TransformerChain.transform(TransformerChain.java:33)
      	at com.atlassian.confluence.content.render.xhtml.PluggableTransformerChain.transform(PluggableTransformerChain.java:39)
      	at com.atlassian.confluence.content.render.xhtml.DefaultRenderer.renderWithoutMetrics(DefaultRenderer.java:194)
      	at com.atlassian.confluence.content.render.xhtml.DefaultRenderer.renderWithResult(DefaultRenderer.java:153)
      	at com.atlassian.confluence.content.render.xhtml.DefaultRenderer.render(DefaultRenderer.java:134)
      	at com.atlassian.confluence.content.render.xhtml.DefaultRenderer.render(DefaultRenderer.java:116)
      	at com.atlassian.confluence.content.render.xhtml.DefaultRenderer.render(DefaultRenderer.java:110)
      	at com.atlassian.confluence.content.render.xhtml.DeviceTypeAwareRenderer.render(DeviceTypeAwareRenderer.java:43)
      	at com.atlassian.confluence.pages.actions.ViewPageAction.execute(ViewPageAction.java:270)
      (...)
      	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
      	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
      	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:853)
      	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1587)
      	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
      	- locked <0x00000006d86bb918> (a org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper)
      	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
      	at java.lang.Thread.run(Thread.java:748)
      

      Depending on how many requests were sent to the target page, the administrator may need to restart Confluence to drop the ongoing requests.

      Workaround

      Currently there's no workaround to prevent the behavior described above when the Spaces List Macro is used in a page.
      Confluence administrators may choose to completely disable this macro to prevent issues affecting the whole instance:

      1. Go to Cog icon > Manage Apps.
      2. Search for Dashboard Macros under System Apps.
      3. Expand the Dashboard Macros' modules.
      4. Disable the spaces (spaces) module.

        Attachments

        1. har-img-001.png
          har-img-001.png
          62 kB
        2. har-img-002.png
          har-img-002.png
          62 kB
        3. har-img-003.png
          har-img-003.png
          62 kB
        4. har-img-004.png
          har-img-004.png
          63 kB
        5. har-img-005.png
          har-img-005.png
          62 kB

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              tmasutti Thiago Masutti
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated: