 | In this set of pages on Investigating A Slow Confluence Installation - Version 3.3.1, I document the steps I took to try and identify slow running operations on my copy of Confluence 3.3.1. I recently added about 10K pages and labels for an EGW topical index, roughly doubling the number of pages (not not total size of content). This should account for the recent slowdowns and non-responsive Confluence that I have seen.
In this set of topics I discuss how I examined the database, stack dumps, dealt with Confluence hangs, logged bugs with the vendor, examined robot activity, and took other defensive measures to keep my copy of Confluence running. Please let me know if these topics helped you run your copy of Conflunece. You can use the "Add comment" on any of these pages to let me know. Please include your email addres in your comment, I will keep a copy of your email so we can correspond about Confluence, but quickly remove it from the comment you place here. |
Saturday afternoon....
About 24 hours after beginning on this journey of investigating a slow performing Confluence installation, I now have a Confluence watchdog for linux which can detect when my Confluence has hosed itself, and it can automatically take care of getting thread dumps, gathering logs, killing confluence, and restarting confluence. Pretty nifty tool.
Unfortunately, it seems like it will be doing more restarts than should ever be needed. This Confluence is spinning out of control in only a few hours after each restart. There is a lot of traffic from robots trying index nonsensical combinations of related labels, and I need to look into a method for [Using apache modrewrite to filter junk urls away from Confluence]. But it is only about 100 hits a minute, which doesn't seem like all that much. Its just MSNbot and yandex.
But just before the Confluence Watchdog was going to restart my Confluence for the umpteenth time today, I decided to start looking at the SQL Server 2005 database that I am using. The activity monitor had maybe 30 open jtds connections, which doesn't seem like a lot, and they all seemed idle, none of them runable. Taskmgr didn't show SQL server chewing up any great number of CPU cycles either, all the activity seemed to be coming from VMWARE as the guest OS was being overwhelmed with whatever Confluence was doing instead of responding to web queries.
Only one of the jtds sessions seemed to have a pending query. The Confluence SQL queries are parameterized (probably by the hibernate layer) and I took the query, and after a little bit of editing to supply some values for the params, it ran quickly. The execution plan for it looked like it had the appropriate indexes too.
I found this article on Quick and Dirty SQL Server Slow Query Log. He mentioned this article and this query:
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC
I ran it and received this SQL Server 2005 report of most read intensive queries for Confluence. Hmmm... First attempt to save it got the dreaded proxy timeout error. It might be too big, I may need to trip it down.
Looking at SQL Server 2005 report of most read intensive queries for Confluence, I was able to find a few queries that benefited from having additional indexes created. I noticed some queries using the "lower" operator on titles in the WHERE clause. I don't know if a lowered version of titles can be indexed, but I would look into adding a second copy of the the title that was lower case, and making an index on that field. I am concerned that a dumb SQL query planner would do a table scan instead of using an index. And database tuning advisor did recommend additional indexes for the queries I found that were doing this, but I wasn't able to really figure out if they were addressing this issue or not.
For the other queries I looked at, the database tuning advisor could not recommend any improvements.
The next thing I did was rebuild all the database indexes to get rid of fragmentation. Still no big improvement in speed. I've already given attention to things like memory and JVM optimization, and the only thing I could do better is run Confluence in it's own dedicated machine, instead of a VM on the same machine the database server is running on. The vendor likes to blaim the database for problems with the app, and the database could definitely be a source of problems for all kinds of reasons, probably the chief of which might be connection pooling not being able to make enough database connections, lack of indexing in the database, etc. But I've already paid attention to all those things.
As often happens with this app, the bad performance is coming from within the app itself. I noticed that suddenly the performance of Confluence increased, and the load on the CPU went way down, so I did an Analysis of thread dumps to figure out why Confluence CPU load suddenly decreased, and also Examing Confluence Page Profiling, and discovered there is often an http processing thread stuck in a long running operation.