Slow Jira startup with PostgreSQL
- Mike Harrison
Problem
Your Jira installation is using a PostgreSQL database. You have JEMH installed and you are seeing longer Jira startup times than expected.
In your atlassian-jira.log file you may see Active Objects taking an unusually long time to initialise for JEMH:
2018-06-19 14:09:12 [c.a.activeobjects.osgi.TenantAwareActiveObjects] bundle [com.javahollic.jira.jemh-ui] creating ActiveObjects
...
2018-06-19 14:19:21 [c.a.activeobjects.osgi.TenantAwareActiveObjects] bundle [com.javahollic.jira.jemh-ui] created ActiveObjectsSolution
One cause of this has been found to be PostgreSQL tables that have a large amount of dead tuples (data that has been deleted or obsoleted). In order to maintain optimum performance, tables should regularly (perhaps nightly) have these removed by use of the VACUUM command. This command reclaims the space taken by this dead data, improving the performance of subsequent SQL operations.
Usage of command
VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ table [ (column [, ...] ) ] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table.
VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an exclusive lock on each table while it is being processed.
For more information on optimising PostgreSQL for use with Atlassian products, see here: https://confluence.atlassian.com/kb/optimize-and-improve-postgresql-performance-with-vacuum-analyze-and-reindex-885239781.html.