Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-2780

Missing database indexes and slow performance

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Fixed
    • Icon: High High
    • 1.4.4
    • 1.3.5, 1.3.6, 1.4, 1.4.1, 1.4.2, 1.4.3, 2.0
    • None

      Not sure whats going on - but sometimes hitting a space homepage takes over 10 sec to respond...
      The thing is its not consistantly this slow. It seems to be related to inactivity - ie if its been inactive for a while, its very slow to respond for the first time. Smells like a caching thing.

      Let me know what I should look at so we can narrow it down.

      -Nick

            [CONFSERVER-2780] Missing database indexes and slow performance

            jens added a comment -

            btw, I have removed the comment I made since it seems to confuse.

            jens added a comment - btw, I have removed the comment I made since it seems to confuse.

            jens added a comment -

            Nick,

            The indexes will only be set when you do a fresh install of Confluence. There is currently no upgrade task to create the indexes and therefore you won't have any problems. Apologies for the confusion.

            Cheers,
            Jens

            jens added a comment - Nick, The indexes will only be set when you do a fresh install of Confluence. There is currently no upgrade task to create the indexes and therefore you won't have any problems. Apologies for the confusion. Cheers, Jens

            Nick Sieger added a comment -

            I noticed Jen's comment on the Database Indexing page about recommending to "not install the indexes manually but wait for the 1.4.4 release". If you already have them installed, will the 1.4.4 upgrade fail? I think it should not fail, or if possible give the option to disable the installation of indexes.

            Also, our DBAs renamed the indexes slightly to conform with local conventions. Therefore logic that checks for the index name will not be sufficient.

            Nick Sieger added a comment - I noticed Jen's comment on the Database Indexing page about recommending to "not install the indexes manually but wait for the 1.4.4 release". If you already have them installed, will the 1.4.4 upgrade fail? I think it should not fail, or if possible give the option to disable the installation of indexes. Also, our DBAs renamed the indexes slightly to conform with local conventions. Therefore logic that checks for the index name will not be sufficient.

            dave (Inactive) added a comment -

            Index for USERNAME column added.

            Thanks,
            Dave

            dave (Inactive) added a comment - Index for USERNAME column added. Thanks, Dave

            Nick Minutello added a comment -

            Should we re-open this until its fixed again?

            Nick Minutello added a comment - Should we re-open this until its fixed again?

            Nick Minutello added a comment -

            Another index needed:

            CREATE INDEX CONTENT_IDX10 ON CONTENT (USERNAME)

            Nick Minutello added a comment - Another index needed: CREATE INDEX CONTENT_IDX10 ON CONTENT (USERNAME)

            Nick Minutello added a comment -

            Eggcellent!

            Nick Minutello added a comment - Eggcellent!

            Charles Miller (Inactive) added a comment -

            These indexes were folded into the hibernate configuration in 1.4

            Charles Miller (Inactive) added a comment - These indexes were folded into the hibernate configuration in 1.4

            Michael Thorpe added a comment -

            And yet more indexes ( These reduced physical IO by 80% on our database ) :-

            CREATE INDEX CONTENT_IDX7 ON CONTENT (PREVVER)
            PARALLEL ( DEGREE 4 INSTANCES 1 );

            CREATE BITMAP INDEX CONTENT_IDX9 ON CONTENT (CONTENT_STATUS);

            CREATE INDEX CONTENT_IDX8 ON CONTENT (PAGEID)
            PARALLEL ( DEGREE 4 INSTANCES 1 );

            CREATE INDEX EXTRNLNKS_IDX1 ON EXTRNLNKS (CONTENTID)
            PARALLEL ( DEGREE 4 INSTANCES 1 );

            CREATE INDEX ATTACHMENTS_IDX1 ON ATTACHMENTS (PAGEID)
            PARALLEL ( DEGREE 4 INSTANCES 1 );

            CREATE INDEX SPACEPERMISSIONS_IDX1 ON SPACEPERMISSIONS (SPACEID)
            PARALLEL ( DEGREE 2 INSTANCES 1 );

            Michael Thorpe added a comment - And yet more indexes ( These reduced physical IO by 80% on our database ) :- CREATE INDEX CONTENT_IDX7 ON CONTENT (PREVVER) PARALLEL ( DEGREE 4 INSTANCES 1 ); CREATE BITMAP INDEX CONTENT_IDX9 ON CONTENT (CONTENT_STATUS); CREATE INDEX CONTENT_IDX8 ON CONTENT (PAGEID) PARALLEL ( DEGREE 4 INSTANCES 1 ); CREATE INDEX EXTRNLNKS_IDX1 ON EXTRNLNKS (CONTENTID) PARALLEL ( DEGREE 4 INSTANCES 1 ); CREATE INDEX ATTACHMENTS_IDX1 ON ATTACHMENTS (PAGEID) PARALLEL ( DEGREE 4 INSTANCES 1 ); CREATE INDEX SPACEPERMISSIONS_IDX1 ON SPACEPERMISSIONS (SPACEID) PARALLEL ( DEGREE 2 INSTANCES 1 );

            Nick Faiz [OLD] (Inactive) added a comment -

            Thanks Michael,

            This information will be helpful for CONF-2981

            Cheers,
            Nick

            Nick Faiz [OLD] (Inactive) added a comment - Thanks Michael, This information will be helpful for CONF-2981 Cheers, Nick

              Unassigned Unassigned
              3b1ae0ec93c9 Nick Minutello
              Affected customers:
              2 This affects my team
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: