Uploaded image for project: 'Confluence Server'
  1. Confluence Server
  2. CONFSERVER-58065

Reduce number and cost of permission queries to render create buttons

    XMLWordPrintable

    Details

      Description

      Problem

      There are 2 types of create buttons in Confluence: Quick create (to create a page) and Blueprint create (will bring up a Create dialog with blueprint options). In order to decide if these buttons should be visible or not, for every page load, Confluence has to execute several DB queries to scan all SPACES and SPACEPERMISSIONS tables to find any spaces in which current user has permission to create page/blogpost. You can find a sample query below:

      SELECT DISTINCT
          space1_.SPACEID       AS SPACEID1_59_,
          space1_.SPACENAME     AS SPACENAM2_59_,
          space1_.SPACEKEY      AS SPACEKEY3_59_,
          space1_.LOWERSPACEKEY AS LOWERSPA4_59_,
          space1_.SPACEDESCID   AS SPACEDES5_59_,
          space1_.HOMEPAGE      AS HOMEPAGE6_59_,
          space1_.CREATOR       AS CREATOR7_59_,
          space1_.CREATIONDATE  AS CREATION8_59_,
          space1_.LASTMODIFIER  AS LASTMODI9_59_,
          space1_.LASTMODDATE   AS LASTMOD10_59_,
          space1_.SPACETYPE     AS SPACETY11_59_,
          space1_.SPACESTATUS   AS SPACEST12_59_
      FROM
          SPACEPERMISSIONS spacepermi0_
      INNER JOIN
          SPACES space1_
      ON
          spacepermi0_.SPACEID=space1_.SPACEID
      WHERE
          (
              spacepermi0_.PERMUSERNAME='user-key-1'
          OR  spacepermi0_.PERMGROUPNAME IN ('group-1' ,
                                             'group-2' ,
                                             'group-3' ,
                                             'group-4')
          OR  spacepermi0_.PERMALLUSERSSUBJECT='authenticated-users'
          OR  (
                  spacepermi0_.PERMUSERNAME IS NULL)
          AND (
                  spacepermi0_.PERMGROUPNAME IS NULL)
          AND (
                  spacepermi0_.PERMALLUSERSSUBJECT IS NULL))
      AND spacepermi0_.PERMTYPE='EDITSPACE'
      AND (
              space1_.SPACESTATUS IN ('CURRENT'))
      ORDER BY
          space1_.SPACENAME,
          space1_.SPACEKEY limit 1
      

      These permission queries don't scale well, especially in MySQL. Below you can find average query time for a dataset of about 6,000 spaces and 300,000 records in SPACEPERMISSIONS table:

      DBMS Avg query time (ms)
      Postgres 40
      MySQL 300
      SQL Server 20
      Oracle 50

      Not only expensive, the number of these queries is also unnecessary high. See below for count for each page type and type of Create buttons:

        Dashboard Page/Blogpost View
      Quick Create 2 0
      Blueprint Create 2 2

      Under very high load, these queries can put enormous pressure on DB and may even cause outages.

      Root cause

      The code to render Create buttons doesn't scale well because it involves too many queries which can be costly in big instances. This ticket aims to alleviate some problems in CONFSERVER-57535 while the proper fix is being considered.

       

      Workaround

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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nhdang Nhan Dang
              Reporter:
              nhdang Nhan Dang
              Votes:
              1 Vote for this issue
              Watchers:
              11 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: