Confluence hammers SQL Server with thousands of queries

[Alexander S.]

Hi,

I recently checked SQL Server that houses Confluence DB and was puzzled to find several Confluence v6.4.3 queries that literally run 3-4 thousand times (!) per minute each in our log. Needless to say this results in CPU performance drain.

Here's one such query for example:

select this_.id as id1_30_0_, this_.user_name as user_nam2_30_0_, this_.lower_user_name as lower_us3_30_0_, this_.active as active4_30_0_, this_.created_date as created_5_30_0_, this_.updated_date as updated_6_30_0_, this_.first_name as first_na7_30_0_, this_.lower_first_name as lower_fi8_30_0_, this_.last_name as last_nam9_30_0_, this_.lower_last_name as lower_l10_30_0_, this_.display_name as display11_30_0_, this_.lower_display_name as lower_d12_30_0_, this_.email_address as email_a13_30_0_, this_.lower_email_address as lower_e14_30_0_, this_.external_id as externa15_30_0_, this_.directory_id as directo16_30_0_, this_.credential as credent17_30_0_ from cwd_user this_ where this_.directory_id=@P0 and this_.lower_user_name=@P1

Given we're talking about an instance that's only used from time to time by 5 folks in total, I'm baffled and bugged by this finding.

Any tips and ideas on troubleshooting or at least throttling this crazy Confluence activity down would be highly appreciated.

Thanks!

[Daniel Ebers · Accepted solution]

Hi Alexander,

this will be hard to diagnose from remote without access to the server but in the first step you could dig deeper by, for example, enabling a slow query log and stuff to get a better idea to see what is going on.

In case you are using Apps it would also be worth to have a look at them.

Here you find more general information - please make sure to select the right guide according to the database you have installed:

https://confluence.atlassian.com/kb/database-troubleshooting-and-how-to-guides-744326149.html

Cheers,
Daniel

[Alexander S.]

Hi Daniel,

I've come across this article before, however, there isn't anything relevant to the issue contained in it.

I have however found more details on SQL logging and have enabled it in Confluence ( https://confluence.atlassian.com/doc/enabling-detailed-sql-logging-179030.html ). Can you please advise where is this log located? This isn't mentioned in the article itself.

Thanks!

Alexander 

[Daniel Ebers]

So far, so good!
The log file is in the "logs" directory. This is stored in the Confluence home directory.

https://confluence.atlassian.com/doc/confluence-home-and-other-important-directories-590259707.html