We were experiencing sever slowdown whenever someone clicked on the dashboard. It effected everyone else who was using confluence. After some investigation. The confluence guys came up with the below: (It definitly works and is a good idea to apply)
Thanks for the profiling data, it's really helped us track down where Confluence is spending too much time. The reason the dashboard is loading so slowly is that in order to decide whether to display "view mail" and "view blog posts" links for each space, it needs to query for the existence of mail and blog posts. The simplest (and usually most efficient) way to do this using SQL's count() function. Unfortunately, without indexes on the Content table, the database ends up doing a full table scan for each one of these queries, for every visible space, which leads to the dashboard taking a long time to load.
I'm attaching some correspondance we had with the DBA at another of our customers who is running a Confluence deployment against Oracle. If you pass this information on to your DBA, you should see a significant performance improvement almost immediately. Note also the recommendation to have Oracle trigger an ANALYSE, so that it makes best use of these indexes once they're created.
The original conversation these messages are pulled from is online here: http://jira.atlassian.com/browse/CONF-2780
1:
=======
I have created indexes for some of the suggested columns based upon my own observations and there seems to be an improvement. Note that is on Oracle 9i.
The columns that I have indexed are :-
CONTENT.SPACEID
CONTENT.MAILSPACEID
CONTENT.BLOGSPACEID
CONTENT.CONTENTTYPE ( Bitmap index on Oracle )
CONTENT.PARENTID
CONTENT.TITLE
LINKS.CONTENTID
LINKS.DESTSPACEKEY
Previously I had also gained improvements by analysing the entire database - Oracle was not using some indexes as there were no statistics.
Regards
Michael Thorpe
2:
========
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 );