High CPU Usage on Confluence Database When Accessing Analytics Page

XMLWordPrintable

    • 1
    • Severity 3 - Minor

      Issue Summary

      When accessing the Analytics page in Confluence (tested with version 9.2.13), the customer experiences a significant spike in CPU usage on their database (RDS instance).

      Investigation revealed that specific SQL queries generated by Confluence Analytics are responsible for the high CPU load. The issue is reproducible by simply loading the Analytics page, which causes the page to load indefinitely and aligns with the observed CPU spike.

      Steps to Reproduce

      1. Creating a large Confluence instance.
      2. Bulk-creating pages and blogs with events so that AO_7B47A5_EVENT has millions of rows and realistic activity patterns.
      3. Navigate to the Analytics page in Confluence (v9.2.13).
      4. Observe that the page continues loading indefinitely.
      5. Monitor the database (RDS instance) CPU usage, which spikes during this time.

      Expected Results

      The analytics page should load quickly without any CPU spikes. 

      Actual Results

      • Database CPU usage reaches maximum (100%) for several minutes.
      • Multiple, complex SQL queries are executed, contributing to the high CPU load.

      Problematic sample SQL Query:

      select `AO_7B47A5_EVENT`.`USER_KEY`, count(case when `AO_7B47A5_EVENT`.`NAME` in ('page_viewed', 'blog_viewed') then 1 else null end), count(case when `AO_7B47A5_EVENT`.`NAME` in ('page_created', 'blog_created') then 1 else null end), count(case when `AO_7B47A5_EVENT`.`NAME` in ('page_updated', 'blog_updated') then 1 else null end), count(case when `AO_7B47A5_EVENT`.`NAME` = 'comment_created' then 1 else null end), count(case when `AO_7B47A5_EVENT`.`NAME` in ('page_created', 'blog_created', 'page_updated', 'blog_updated', 'comment_created') then 1 else null end) from `AO_7B47A5_EVENT` inner join `SPACES` on `AO_7B47A5_EVENT`.`SPACE_KEY` = `SPACES`.`SPACEKEY` where `AO_7B47A5_EVENT`.`NAME` in ('page_viewed', 'blog_viewed', 'page_created', 'blog_created', 'page_updated', 'blog_updated', 'comment_created') and (`AO_7B47A5_EVENT`.`EVENT_AT` >= 1760918400000 and `AO_7B47A5_EVENT`.`EVENT_AT` < 1769990400000) and `AO_7B47A5_EVENT`.`ID` <= 35115357 and (`SPACES`.`SPACESTATUS` = 'CURRENT' and `SPACES`.`SPACETYPE` = 'global') group by `AO_7B47A5_EVENT`.`USER_KEY` order by count(case when `AO_7B47A5_EVENT`.`NAME` in ('page_viewed', 'blog_viewed') then 1 else null end) desc, `AO_7B47A5_EVENT`.`USER_KEY` asc limit 500 offset 0

       

      Workaround

      Currently there is no known workaround for this behavior. A workaround will be added here when available

              Assignee:
              Unassigned
              Reporter:
              Vishnu Prasad P S
              Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated: