Improving Database Performance

Diagnosis

Use native database tools to assess the impact of your database. If you'd like to check what Confluence is doing from it's side, you can enable SQL logging. If you analyze thread dumps, as this is done in general Troubleshooting Confluence hanging or crashing guide, you may find the kinds of threads like this:

"http-8080-Processor150" daemon prio=1 tid=0x08543368 nid=0x11aa in Object.wait() [0x665a4000..0x665a51b0]
at java.lang.Object.wait(Native Method)
- waiting on <0x83140488> (a com.mchange.v2.resourcepool.BasicResourcePool)
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAcquire(BasicResourcePool.java:968)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:208)
- locked <0x83140488> (a com.mchange.v2.resourcepool.BasicResourcePool)

These threads are waiting for a database connection. It could be that the database is not performing optimally, or it may just need tuning for allowing more connection threads. Both are discussed below.

Upgrade your Database and Drivers

SQL Server 2000, Oracle 9i, and MySQL with 3.1 drivers are among some of the issues with database performance. Ensure you are using updated versions of databases and their drivers.

Upgrade your hardware

Atlassian does not offer specific recommendations on hardware for database performance. Use good judgment and native OS and database tools for your assessment.

Ensure you have the Latest Database Indices

Confluence has improved database performance over time. You'll want to make sure you have all the latest, if you're getting hung threads waiting for db connections.

Confluence 2.10 or Manual .ddl Indices

With 2.10 and later, Confluence includes database indices bundled. Confluence 2.10 automatically creates the necessary database indexes when you upgrade. If you are not on 2.10, you may have run the ddl manually during the upgrade process. To check, you can look against these.

Additional Indices not Included in 2.10
  • One import db index is the lower case page title index. Prior to Confluence 3.0, querying for a page by title and space key can take a long time due to table scans necessary on a lowercase where clause. On most databases it is possible to add a lowercase index on these columns that helps with performance. See Creating a Lowercase Page Title Index for instructions on how to do this. Prior to 2.10, apply lowercase title indexes (all Confluence versions).
  • The compound database index for the ATTACHMENTDATA table is described in CONF-13819.
  • A composite index on some of the columns in SpacePermissions table is described in CONF-14488.

Tuning the Database Connection Pool

This is described in the knowledge base article Confluence Slows and Times out During Periods of High Load due to DB Connection Pool.

Configure a Database Query Timeout

If a database is getting overloaded, you can prevent it from crashing Confluence by Configuring a database query timeout.

Troubleshooting Database Issues.

Was this helpful?

Thanks for your feedback!

Why was this unhelpful?

Have a question about this article?

See questions about this article

Powered by Confluence and Scroll Viewport