Database spikes & locks - can't find the root cause.

Błażej O_

Hello.

Lately we are experiencing problems with database connection spikes. There seem to be locks on the database.

We will be very thankful for any hints where to look for the cause.

We have tried to find a root cause to the problem, but after days of cleaning logs, and digging for hints we are left only with countless scattered symptoms, which we are even unsure if those are connected (damn, feels like in House episode).

Problems started approximately when we have connected one of our departments to Jira, thus doubling issue count and active users count. We are also strongly dependant on Kepler Romino's Database and SIL custom fields. SIL and database fields are HEAVILY used by the new department. No more than week later we have upgraded our instance from 5.2.5 to 6.1.7 as well as all (~40) plugins to the newest available versions. Now we can't tell what problems are caused by the heavy load, which by the upgrade of Jira, and which by some plugins.

We looked in atlassian-jira.log but there is nothing that would appear during or before each spike.

We have enabled SQL logging, but the amount of data logged to the file is so insane (20MB each 3-4 minutes) that it is simply impossible to find any hint out there...

We have sat and waited for the spikes to appear to peek the database connections. We have found out that spikes are caused by the locks appearing on the database, but there is a complete randomness in the command that begins the lock avalanche. Sometimes selects, sometimes updates, sometimes inserts. When we manually kill the locking connection on the database on time, spike is ended immediately. But we still don't know what sparked the locks.

We will be glad for any help or directions where too look for the cause of the problem.

Nic Brough -Adaptavist-

Three things I'd look at:

1. Your spikes of activity are mostly suspiciously evenly spaced. I'd do a bit more monitoring and see if the spikes are forming patterns of usage. Like 11:20am Mon-Fri.

2. You say "the amount of data logged to the file is so insane (20MB each 3-4 minutes) that it is simply impossible to find any hint out there" - have you tried some basic analysis? Simple numbers can tell you a lot - for example "95% of our SQL log is Select X from Y where Z" (i.e. always the same query), that could tell you a lot on its own.

3. One quick trick to rule something out - I worked at a place where the users were effectively DoS attacking Jira with their "clever" scripts (i.e. hideously inefficient and they could have done almost all of it inside Jira if they'd bothered to use their brain). Try Admin -> General settings -> Allow remote calls = off. If there's no effect on the database and it still spikes, you've ruled out accidental DoS. If the datbase looks a lot happier, you can then go digging through the remote call logging...

4. Oops. And what Florin said too. :-)