Freshly installed confluence database size grows without much actual activity on the node

[PF]

Confluence database size grows without much activity on this node. Please see the image attachedSteep drop on Feb 22 happened when I did FULL VACUUM on both databases and cleared confluence 'scheduler_run_details'  table. Nonetheless it is still growing. What's wrong here? Manually running sheduled tasks from Admin page in confluence does not clear the database.

[PF · Accepted solution]

Further conversation with Atlassian support clarified that by default event logs in 'scheduler_run_details' table are preserved for 90 days, therefore it is expected that this table grows for 90 days after fresh installation, then it stops growing as older entries are removed, leaving free space for new ones.

There are however environment setting, which go to 'setenv.sh' to redefine this behaviour if there is desire to it. They are:

jobs.limit.per.purge,

all.jobs.ttl.hours,

unsuccessful.jobs.ttl.hours

It is possible to define these settings to 24 hours like so:

-Djobs.limit.per.purge=50000
-Dall.jobs.ttl.hours=24
-Dunsuccessful.jobs.ttl.hours=24

[AnnWorley]

Thanks for the follow-up, It could come in handy for the next administrator concerned about the size of the 'scheduler_run_details' table.

[AnnWorley]

Hi,

Content is stored in the database so even if you don't see a lot of activity as far as page hits, you may have users adding a lot of content.

To get a better idea of what is causing the database to grow, please identify which tables are largest or growing. That should give us a hint as to what is contributing to the db size.

Users and groups are also stored in the database so if you are synchronizing a large external directory that might add bulk to your db as well.

Installing plugins/add-ons can also take up space. Those are generally stored in the Active Objects (AO) tables.

I look forward to hearing which tables are taking up so much space in your database.

Thanks,

Ann

[PF]

Hi, Ann

Thanks for your reply

By "activity" I mean "every kind" of activity, thus I'm not counting "page hits" as the only type of activity possible. I thought it is obvius, so I didn't clarify.

I have already identified 'scheduler_run_details' as biggest, clearing of which gave the result you can see on the image attached.

To be more exact, at the moment 'scheduler_run_details' occupies nearly 13 MB, 'EVENTS' -- 2.6 MB. So, for  'scheduler_run_details' it is plus 13 MB since Feb 22.

The next table by size is 'AO_6384AB_FEATURE_METADATA_AO', which occupies 0.6 MB approx.

While I was writing this reply, the only table that grew is 'scheduler_run_details'. It grew from 13484032 bytes to 13516800 bytes.

[AnnWorley]

Please let me know what version of Confluence you are seeing this in as Confluence 6.4.3 was supposed to fix this issue: (A Scheduled Job called 'Purge Old Job Run Details' has been introduced to purge scheduled job history)

Please see Frequent deadlocks on the scheduler_run_details table can cause an outage

Here is another concern to watch out for - if the host runs out of resources the scheduled job will not run to delete old records. How to resolve "DELETE from scheduler_run_details" Confluence Query locking up SQL database?

[Justin W_]

Hi PF,

This is actually caused by a bug, due to the logic in the job scheduler and the lack of auto-grooming on this table: https://jira.atlassian.com/browse/CONFSERVER-53381

I've listed a few bandaids in the bug linked above, but to fix this issue 100%, you'll need to upgrade to a fixed version, of 6.4.3 or greater. 

In the fixed versions, A Scheduled Job called 'Purge Old Job Run Details' has been introduced to purge scheduled job history.

Best Regards,
Justin

[PF]

Confluence version on this node is "Atlassian Confluence 6.7.0".

As I have mentioned in my question, manually running tasks does not make any difference.

[AnnWorley]

Perhaps there is a resource shortage causing the Purge Old Job Run Details job not to run as in How to resolve "DELETE from scheduler_run_details" Confluence Query locking up SQL database?:

Joshua Cook Aug 07, 2017
It looks like the problem did end up being related to a lack of server resources (memory). 

[PF]

I don't observe "sporadic problem in which a specific query ..... seems to lock up all of the databases on our server". No database is being locked at all. Neither there is memory shortage on that node. It still has 600 MB of free memory.

[PF]

Besides the database itself is on a separate server.

[AnnWorley]

I opened a support ticket on your behalf so a support engineer can check your logs and configuration files for variables that could contribute to the database growing unexpectedly. We can circle back to this thread after we find out what the resolution is.

You should see an email from our support portal shortly.

[PF]

Thank you very much!