This page last changed on Jun 26, 2006 by david.soul@atlassian.com.
This document is out of date

This documentation applies to Confluence 2.1.x or older. If you have Confluence 2.2.x or newer, please refer to Database Setup for Oracle}

Please note, we strongly recommend you install Confluence on a schema in a brand new database on your Oracle server. If this cannot be accommodated, be aware that Confluence may not install or operate properly. For more details on this limitation please see the issue filed here.

This installation assumes you are using the standalone version of Confluence and Oracle 9i. We have not tested against older versions of Oracle, but have been told that it should work against Oracle 8i provided you use the latest 9i drivers.

Note that some users have found that 9.0.1.0 does not work, while 9.0.2.6 does.

If you are using 10g you should use the 10.1.0.4.0 drivers – the 10.1.0.3.0 drivers don't work when using a Tomcat 5.5 datasource, as the connection returned is a T4CConnection instead of the OracleConnection which Spring is expecting.

1. Adding a datasource to Tomcat

Versions before 5.5
  • Edit the conf/server.xml file in your Tomcat installation
  • Find the following lines:
    <Context path="" docBase="../confluence" debug="0" reloadable="true">
    <Logger className="org.apache.catalina.logger.FileLogger" prefix="atlassian-confluence."
    suffix=".log" timestamp="true"/>
  • Directly after the <Logger.../> line, (before the next </Context> line), insert the following:
    <Resource name="jdbc/Confluence" auth="Container" type="javax.sql.DataSource"/>
    <ResourceParams name="jdbc/Confluence">
    <parameter>
    <name>factory</name>
    <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
    </parameter>
    <parameter>
    <name>maxActive</name>
    <value>20</value>
    </parameter>
    <parameter>
    <name>maxIdle</name>
    <value>10</value>
    </parameter>
    <parameter>
    <name>maxWait</name>
    <value>10000</value>
    </parameter>
    <parameter>
    <name>username</name>
    <value>yourusername</value>
    </parameter>
    <parameter>
    <name>password</name>
    <value>yourpassword</value>
    </parameter>
    <parameter>
    <name>driverClassName</name>
    <value>oracle.jdbc.driver.OracleDriver</value>
    </parameter>
    <parameter>
    <name>url</name>
    <value>jdbc:oracle:thin:@hostname:port:oracle-sid</value>
    </parameter>
    </ResourceParams>
    • You may want to choose different maxActive and maxIdle values: these are how many total database connections will be allowed at one time, and how many will be kept open even when there is no database activity
    • Replace the username and password parameters with the correct values for your database
    • In the url parameter, replace the word 'oracle-sid' with the name of the database your confluence data will be stored in. Obviously also change the hostname and port.
Versions 5.5 and later

In Tomcat 5.5 the configuration format has changed. The data source resource should be defined like this:

<Resource
name="jdbc/Confluence"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@<hostname>:<port>:<sid>"
username="<username>"
password="<password>"
connectionProperties="SetBigStringTryClob=true"
maxActive="25"
maxIdle="5"
maxWait="10000"
/>

2. Configure the Confluence to be aware of this datasource

  • Edit confluence/WEB-INF/web.xml in your confluence installation
  • Go to the end of the file and just before </web-app>, insert the following:
    <resource-ref>
    <description>Connection Pool</description>
    <res-ref-name>jdbc/confluence</res-ref-name>
    <res-type>javax.sql.Datasource</res-type>
    <res-auth>Container</res-auth>
    </resource-ref>
  • Copy the ojdbc14.jar to the common/lib directory (this jar is the oracle jdbc thin driver for JDK 1.4. It comes bundled with your Oracle 9i database). We recommend using the thin drivers only.

3. Confluence setup wizard

  • Startup confluence using bin/startup.bat or startup.sh
  • Run through the setup process and when asked to choose a database type: choose Datasource Connection
  • Enter in java:comp/env/jdbc/confluence for the name of the datasource and select the oracle 9 dialect.
  • Complete the setup and that's it!

Note that the <Context .../> element will not yet exist in your server.xml unless you have explicitly put it there. This is true on Tomcat at least, if you have followed the Confluence instructions for (auto)deploying the application by simply copying the confluence directory into tomcat/webapps/.
Also, it is probably advisable to set the "reloadable" attribute of the <Context> element to "false" since hot reloads are not officially supported and leaving it true will cause exceptions to be thrown if you edit properties files and so on while Confluence is running.

Posted by scanny at Jun 29, 2005 01:13

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 );

Posted by karenc at Aug 22, 2005 01:54
Document generated by Confluence on Mar 22, 2007 20:58