How to improve viewpage.action performance?

[5140883]

'viewpage.action' takes 3~4 seconds to load a page, due to a slow query related to 2 large tables

'CONTENT' table: over 11,000,000 rows, include 5680000 attachments and 3020000 pages

'USERCONTENT_RELATION' tables: over 3,000,000 rows

SQL analysis tells me 'CONTENT' is the KEY problem.

I cannot do anything with SQL query, I just can delete some useless data.

I can delete about 1000000 previous version pages to reduce 'CONTENT' size, but the improve is limited, and the size of 'CONTENT' will keep growing.

Is there any way to solve this issue permanently? 

Like database sharding, or Data Center Confluence, or other resolution?

Thank you!

viewpage.action speed:

Slow query like this:

SELECT
rt. currentId as contentId, rt. draftprevver as latestVersionid, rt. RELATIONID as relationId, CASE WHEN lt. LASTMODDATE < rt. touchdate THEN rt. CONTENT_STATUS ELSE lt. CONTENT_STATUS END as contentStatus, rt. touchdate as lastModifiedDate
FROM
CONTENT lt
JOIN (
SELECT
ucr. LASTMODDATE as touchdate, ucr. RELATIONID, c. LASTMODDATE, c. CONTENTID, c. TITLE, c. CONTENT_STATUS, COALESCE( c. PREVVER, c. CONTENTID) as currentId, c. PREVVER as draftprevver
FROM
CONTENT c
LEFT JOIN USERCONTENT_RELATION ucr ON c. CONTENTID = ucr. TARGETCONTENTID
WHERE
ucr. RELATIONNAME = 'touched'
AND ucr. LASTMODIFIER = '8a4892f0760a770901761c86b7440047'
AND c. CONTENTTYPE in (
'PAGE', 'BLOGPOST')
AND c. CONTENT_STATUS in (
'current', 'draft')
) rt on lt. CONTENTID = rt. currentId
WHERE
lt. CONTENTID in (798650507)

[Sloan]

Hi there

It really depends on a few parameters.

First make sure the database itself has no perfomance issues. Check the connection to the database, make sure that enough database connections are set and they allign with the number of http threads. Look at Confluence slows and times out during periods of high load due to database connection pool

Be aware that confluence also works with several cache mechanics. You might be able to improve the caches. See Cache Statistics

For general perfomance tweaking this guide can help you, Performance Tuning

Moving to Data Center and running it in cluster mode can greatly improve perfomance if you have many users. But it does not just help when you switch from Server to Data Center single node.

⚠ DON'T manually delete content from the database via SQL. You could destroy the index and make a negative impact on the integrity of contents in Confluence.

Cheers
Sloan

[5140883]

Thank you!