Why the PostgreSQL database is growing faster in size ?

[Florian GARDES]

Since a few months, the available disk space on my linux Atlassian server (Jira+Bitbucket+Confluence) is getting smaller and smaller.
It seems that the PostgreSQL database has grown by 20 GB in two months while we are a small team (about 5 people).
Indeed, the command "df -hk" indicates that the disk space decreases by about 4kb per second while nobody uses the Atlassian suite.
When I stop Jira, the available disk space doesn't decrease anymore.
How can I understand and fix the issue ?

[Thiago Masutti]

Hi Florian.

Have you confirmed the growth comes from DB?

Linux du command might be helpful to identify largest directories.

If it's from the DB, can you check which database and tables are the top consumers?

[Nic Brough -Adaptavist-]

FWIW, the usual culprit of chewing up disk space is attachments.  As @Thiago Masutti says, you need to look at what disk space is being consumed - are you sure it's the databases?

[Florian GARDES]

Hi, 
Hereafter the result of the iotop command:

It confirms write access on the physical disk into the jiradb database..

For information, I have already lost 10Go since the creation of the ticket !

How can I find which tables are concerned ?

[Florian GARDES]

Some additional information:

    name    |   owner    |  size   
------------+------------+---------
 jiradb     | postgres   | 178 GB
 confluence | confluence | 2257 MB
 bitbucket  | bitbucket  | 561 MB
 postgres   | postgres   | 7008 kB
 template1  | postgres   | 6889 kB
 template0  | postgres   | 6889 kB

            relation             | total_size 
---------------------------------+------------
 public.clusteredjob             | 101 GB
 public.rundetails               | 76 GB
 public.entity_property          | 12 MB
 public.cwd_user_attributes      | 10232 kB
 public.changeitem               | 6496 kB
 public.jiraissue                | 6016 kB
 public.changegroup              | 5328 kB
 public.remotelink               | 4168 kB
 public.AO_60DB71_LEXORANK       | 3336 kB
 public.propertyentry            | 2864 kB
 public.sequence_value_item      | 2424 kB
 public.AO_575BF5_PROVIDER_ISSUE | 1800 kB
 public.os_historystep           | 1272 kB
 public.propertystring           | 1008 kB
 public.userassociation          | 960 kB
 public.os_currentstep           | 680 kB
 public.os_wfentry               | 624 kB
 public.customfieldvalue         | 488 kB
 public.os_currentstep_prev      | 488 kB
 public.propertytext             | 472 kB

[Thiago Masutti]

Hi @Florian GARDES 
I hope you are well.

clusteredjob and rundetails DB tables store information about scheduled jobs and it's interesting they are on the GB size (they shouldn't).

If you have an active maintenance license I would suggest opening a ticket to Atlassian Support as they would need to assess the type data stored and why it's still increasing.

Out of curiosity, could you please share the output of the following queries?

select * from clusteredjob order by next_run desc limit 15;

select * from rundetails order by start_time desc limit 15;

Kind regards,
Thiago Masutti

[Florian GARDES]

Hi @Thiago Masutti

Thanks for your reply. I will try to open a ticket to the support. But it seems that it is no longer opened for Jira Server software.

Regards

[Florian GARDES]

jiradb=> select * from rundetails order by start_time desc limit 15;
id | job_id | start_time | run_duration | run_outcome | info_message 
------------+---------------------------------------------------+----------------------------+--------------+-------------+--------------
1235327568 | f397412d-23f1-4c72-b9be-54dbeaf8d12d | 2022-11-22 07:58:13.359+01 | 4 | S | 
1235327567 | 996e1aff-188d-45ba-95e9-9d6f55d853b9 | 2022-11-22 07:58:13.354+01 | 4 | S | 
1235327565 | JiraPluginScheduler:LEXO_RANK_STATS_COLLECTOR_JOB | 2022-11-22 07:58:13.353+01 | 0 | S | 
1235327566 | 83c27ebf-36d2-487a-9e0c-b89aaf7f630c | 2022-11-22 07:58:13.351+01 | 4 | S | 
1235327564 | d6fe3e5b-592c-4c0d-87a7-54df38d320d6 | 2022-11-22 07:58:13.348+01 | 3 | S | 
1235327563 | 6b1cc9d9-4f04-4a2f-9479-61c9609c8c67 | 2022-11-22 07:58:13.343+01 | 3 | S | 
1235327561 | d6e17113-7665-404f-9817-ee84c86829ea | 2022-11-22 07:58:13.341+01 | 3 | S | 
1235327562 | 142e56e8-5668-4a7f-b745-14098df69e2c | 2022-11-22 07:58:13.341+01 | 4 | S | 
1235327560 | 1417f50c-545f-41be-b086-2fcf770924ac | 2022-11-22 07:58:13.335+01 | 4 | S | 
1235327559 | 10c2458a-7f85-45fa-afab-37151e46941c | 2022-11-22 07:58:13.333+01 | 3 | S | 
1235327557 | 13e60e8d-e5f4-4e7c-a4f2-d49ca43d8aac | 2022-11-22 07:58:13.329+01 | 5 | S | 
1235327558 | e81b5a2c-c4c9-486f-b54e-ea4e33d7dede | 2022-11-22 07:58:13.328+01 | 3 | S | 
1235327556 | cdaa26ba-22d9-4471-ab20-83ac149199e8 | 2022-11-22 07:58:13.321+01 | 6 | S | 
1235327555 | c38b257d-f025-44cd-999b-33226e739c92 | 2022-11-22 07:58:13.319+01 | 4 | S | 
1235327553 | 99039030-fcb5-4f36-b198-2d5912750431 | 2022-11-22 07:58:13.317+01 | 4 | S | 
(15 rows)

[Florian GARDES]

jiradb=> select * from clusteredjob order by next_run desc limit 15;
id | job_id | job_runner_key | sched_type | interval_millis | first_run | cron_expression | time_zone | next_run | version | parameters 
--------+---------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------+------------+-----------------+---------------+-----------------+-----------+---------------+---------+----------------------
123304 | jira-migration-user-email-analytics-events-collector-job-id | jira-migration-user-email-analytics-events-collector-job-key | C | | | 0 0 1 ? * SUN * | | 1669507200000 | 32 | 
123305 | jira-migration-analytics-server-analysis-job-id | jira-migration-analytics-server-analysis-job-key | I | 604800000 | 1650502539170 | | | 1669261201430 | 32 | 
146407 | applink-status-analytics-job | com.atlassian.applinks.analytics.ApplinkStatusJob | I | 86400000 | | | | 1669172689776 | 2 | 
10001 | com.atlassian.jira.service.JiraService:10002 | com.atlassian.jira.service.DefaultServiceManager | C | | | 0 0 1 * * ? | | 1669161600000 | 1715 | \xaced000573...
146400 | analytics-collection | com.atlassian.plugins.authentication.impl.analytics.StatisticsCollectionService | C | | | 0 0 23 * * ? | | 1669154400000 | 1 | 
10106 | com.atlassian.jira.service.JiraService:10001 | com.atlassian.jira.service.DefaultServiceManager | C | | | 0 45 5/12 * * ? | | 1669135500000 | 3422 | \xaced000573720...
10204 | LocalPluginLicenseNotificationJob-job | LocalPluginLicenseNotificationJob-runner | I | 86400000 | 1520527955854 | | | 1669112417222 | 1712 | 
10206 | PluginUpdateCheckJob-job | PluginUpdateCheckJob-runner | I | 86400000 | 1520554719637 | | | 1669112416524 | 1711 | 
10207 | InstanceTopologyJob-job | InstanceTopologyJob-runner | I | 86400000 | 1520562339514 | | | 1669112416336 | 1711 | 
146404 | com.atlassian.jira.web.action.issue.DefaultTemporaryWebAttachmentsMonitor | com.atlassian.jira.web.action.issue.DefaultTemporaryWebAttachmentsMonitor | I | 3600000 | 1669085678407 | | | 1669100740806 | 5 | 
146401 | assertionId-cleanup | com.atlassian.plugins.authentication.impl.web.saml.AssertionValidationService | I | 3600000 | 1669085651007 | | | 1669100739808 | 5 | 
146408 | CompatibilityPluginScheduler.JobId.hipchatInstallGlancesJob | CompatibilityPluginScheduler.JobRunnerKey.com.atlassian.jira.plugins.hipchat.service.connect.InstallGlancesJobHandler | I | 3600000 | 1669082333046 | | | 1669100739416 | 5 | 
146410 | CompatibilityPluginScheduler.JobId.hipchatRefreshConnectionStatusJob | CompatibilityPluginScheduler.JobRunnerKey.com.atlassian.jira.plugins.hipchat.service.ping.RefreshConnectionStatusJobHandler | I | 3600000 | 1669082324161 | | | 1669100734401 | 5 | 
146403 | data-provider-cleanup | com.atlassian.jira.plugin.devstatus.provider.DataProviderCleanupJob | I | 1800000 | 1669082057710 | | | 1669100678328 | 9 | 
123306 | jira-migration-analytics-events-dispatcher-job-id | jira-migration-analytics-events-dispatcher-job-key | I | 3600000 | 1650502539221 | | | 1669100677996 | 4741 | 
(15 rows)