Storage & Performance

Read about internal performance optimizations, search tables and how the app stores data.

Introduction

Since app version 2.0.0 the app's data storage is optimized for Jira Data Center to work in a clustered environment. We make use of Scheduled Jobs, Caches and Database Search Tables to achieve best performance in a clustered environment during high load and with a lot of data. Data is stored in two different ways:

  • Maintenance Data such as internal app settings are stored via PluginSettingsFactory.

  • Permissions and search tables are stored via ActiveObjects in database tables.

Storing Lightweight App Data

App settings are stored via the Shared Access Layer (PluginSettingsFactory) which is a Jira API providing simple save and load methods for storing small chunks of data. This is useful for internal App Settings. This data should usually not be accessed via a database query tool.

The data is stored by Jira in a table called propertyentry with foreignkey references to the table propertystring. The data can be accessed by using property keys.

DataMigrationTask Information

jiracustomfieldeditorplugin.<migrationTaskId>.migrationtasks

DataMigrationTask Information stores a date and the migration-task id per migration task. The format is a timestamp in seconds. If the migrationtask did not run, there is no entry.

Example entries look like this:

> SELECT * FROM "propertyentry" WHERE property_key = "jiracustomfieldeditorplugin.200001.migrationtasks" OR property_key = "jiracustomfieldeditorplugin.121001.migrationtasks"
| id    | entity_name     | entity_id | property_key                                       | propertytype |
|-------|-----------------|-----------|----------------------------------------------------|--------------|
| 10521 | jira.properties |         1 | jiracustomfieldeditorplugin.200001.migrationtasks  |            5 |
| 10515 | jira.properties |         1 | jiracustomfieldeditorplugin.121001.migrationtasks  |            5 |
> SELECT * FROM "propertystring" WHERE id = 10523
| id    | propertyvalue |
|-------|---------------|
| 10515 | 1556833017635 |
| 10521 | 1556833169626 |

GlobalSettings

jiracustomfieldeditorplugin.admin.settings

GlobalSettings are stored as stringified json. The format is shown in the example below.

Example entries look like this:

> SELECT * FROM "propertyentry" WHERE property_key = "jiracustomfieldeditorplugin.admin.settings"
| id    | entity_name     | entity_id | property_key                                | propertytype |
|-------|-----------------|-----------|---------------------------------------------|--------------|
| 10523 | jira.properties |         1 | jiracustomfieldeditorplugin.admin.settings  |            5 |
> SELECT * FROM "propertystring" WHERE id = 10523
| id    | propertyvalue                                    |
|-------|--------------------------------------------------|
| 10523 | {"hideAdminLinksFromNonAdminUsers":false}        |

Storing Complex App Data

App Permissions and Search Tables are stored via ActiveObjects which is a Jira API providing real database access to database tables. This is useful for complex data that needs to be queried by SQL Statements. This data can be accessed via a database query tool.

Search Table: AO_4811F4_CUSTOM_FIELDS

This table stores customfields to be able to provide filtering and pagination. The table contains customfield id, name, description and has multiple indexes on the columns, to provide fast sql queries during filtering.

ID
INTEGER

The primary key of the table with auto_increment.

FIELD_ID
INTEGER

The customfield id.

FIELD_ID_AS_STRING
VARCHAR(255)

The customfield id for fulltext search.

NAME
VARCHAR(255)

The customfield name cut off at 255 characters.

NAME_LOWER
VARCHAR(255)

The customfield name cut off at 255 characters and lower-cased for fulltext search.

DESCRIPTION
VARCHAR(255)

The customfield description cut off at 255 characters.

DESCRIPTION_LOWER
VARCHAR(255)

The customfield description cut off at 255 characters and lower-cased for fulltext search.

FIELD_TYPE
VARCHAR(255)

The customfield type in short form such as select, cascadingselect, multicheckboxes, radiobuttons, multiselect.

An example entry looks like this:

> SELECT * FROM "AO_4811F4_CUSTOM_FIELDS"
| ID | FIELD_ID | FIELD_ID_AS_STRING | NAME             | NAME_LOWER       | DESCRIPTION                               | DESCRIPTION_LOWER                         | FIELD_TYPE      |
|----|----------|--------------------|------------------|------------------|-------------------------------------------|-------------------------------------------|-----------------|
|  2 |    10201 | 10201              | Coffee varieties | coffee varieties | Coffee varieties according to its origins | coffee varieties according to its origins | multicheckboxes |

App Permissions: AO_4811F4_PERMISSIONS

This table stores the app permissions used to enforce authorization on customfields and contexts. The table contains customfield id, context id, permission type, userkey and groupname and has multiple indexes on the columns, to provide fast sql queries.

ID
INTEGER

The primary key of the table with auto_increment.

FIELD_ID
INTEGER

The customfield id.

CONTEXT_ID
INTEGER

The customfield's context id or null if global permission type.

TYPE
INTEGER

1 = global permission, 2 = context permission.

USER
VARCHAR(255)

The userKey of the user granted permission to. Note that userKey is not userName.

GROUP
VARCHAR(255)

The groupname of the group granted permission to.

Note: Starting with v2.0.0 the user permissions are stored as userKeys (not userNames). You can read this GDPR BlogPost to see why.

Example entries look like this:

> SELECT * FROM "AO_4811F4_PERMISSIONS"
| ID  | FIELD_ID | CONTEXT_ID | TYPE | USER     | GROUP               |
|-----|----------|------------|------|----------|---------------------|
| 316 |    10203 |      10406 |    2 | admin    |                     |
| 317 |    10203 |      10406 |    2 |          | jira-user           |
| 318 |    10203 |            |    1 | john     |                     |
| 319 |    10203 |            |    1 |          | jira-administrators |

App Settings: AO_4811F4_JOB

This table stores information about internal app jobs. The table contains job id, name, status, scheduled time, finished time and has multiple indexes on the columns, to provide fast sql queries.

ID
INTEGER

The primary key of the table with auto_increment.

NAME
VARCHAR(255)

The job name.

JOB_STATUS
INTEGER

The job status as: 1 = SCHEDULED, 2 = RUNNING, 3 = SUCCESS, 4 = FAILED.

SCHEDULED_TIME
DATE_TIME

The UTC timestamp when the job was scheduled for execution.

FINISHED_TIME
DATE_TIME

The UTC timestamp when the job finished its execution.

Example entries look like this:

> SELECT * FROM "AO_4811F4_JOB"
| ID | NAME                           | JOB_STATUS | SCHEDULED_TIME          | FINISHED_TIME           |
|----|--------------------------------|------------|-------------------------|-------------------------|
|  1 | JobRunDataMigrationTasks       |          3 | 2019-05-16 13:03:27.106 | 2019-05-16 13:03:27.113 |
|  2 | JobIndexCustomFieldSearchTable |          3 | 2019-05-16 13:03:27.106 | 2019-05-16 13:03:27.16  |

App Settings: AO_4811F4_EVENT_LOG

This table stores information about internal app events. The table contains job id, name, status, scheduled time, finished time and has multiple indexes on the columns, to provide fast sql queries.

ID
INTEGER

The primary key of the table with auto_increment.

NAME
VARCHAR(255)

The event name.

NAME_LOWER
VARCHAR(255)

The event name cut off at 255 characters and lower-cased for fulltext search.

DESCRIPTION
VARCHAR(255)

The event description.

DESCRIPTION_LOWER
VARCHAR(255)

The event description cut off at 255 characters and lower-cased for fulltext search.

DATE_TIME
DATE_TIME

The UTC timestamp when the event occured.

EVENT_HASH
VARCHAR(255)

An unique auto-generated hash to identify the event.

Example entries look like this:

> SELECT * FROM "AO_4811F4_JOB"
| ID | NAME                    | NAME_LOWER              | DETAILS                                                                                                                 | DETAILS_LOWER                                                                                                          | DATE_TIME               | EVENT_HASH                                                       |
|----|-------------------------|-------------------------|-------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------|-------------------------|------------------------------------------------------------------|
|  1 | PluginEnabledEvent      | pluginenabledevent      | on plugin install/update → triggered: JobRunDataMigrationTasks, JobIndexCustomFieldSearchTable                          | on plugin install/update → triggered: jobrundatamigrationtasks, jobindexcustomfieldsearchtable                         | 2019-05-02 21:39:29.324 | 1f7c9db29aa93491fe434acb1a9a23e70352e5ffc872c583718232520d06b190 |
| 23 | CustomFieldCreatedEvent | customfieldcreatedevent | ID 10206, name Seasonal Fruit Choice, type cascadingselect → triggered: update row in customfield search table          | id 10206, name seasonal fruit choice, type cascadingselect → triggered: update row in customfield search table         | 2019-05-11 13:24:51.452 | 129d8d0deef00752b1b4fc9189b2348a4992bffb5a0584abd304a2b603f3f42b |
| 37 | CustomFieldUpdatedEvent | customfieldupdatedevent | ID 10204, name Z Special Character 1 öäüÆæØ type cascadingselect → triggered: update row in customfield search table    | id 10204, name z special character 1 öäüÆæØ type cascadingselect → triggered: update row in customfield search table   | 2019-05-13 09:32:25.635 | 68e84993276ed6d9d674ddca828c7db69e34c5b42a69a2ea6d713dc80305477e |

Events and Background Tasks

The app needs to react to certain events in order to keep permissions and search-indexes up to date. Therefore depending on the event different background tasks are performed. The table below shows all events that the app listens to and which tasks are triggered. Since app version 2.0.0 the app listens to these events.

PluginEnabledEvent

When: Once during Jira startup. Or right after app is installed.
Then: Trigger Jobs: JobRunDataMigrationTasks and JobIndexCustomFieldSearchTable. These jobs will only be executed if they have not been run once before.

CustomFieldCreatedEvent

When: Every time somone creates a customfield.
Then: The customfield is added to the customfield search table.

CustomFieldUpdatedEvent

When: Every time somone updates a customfield. For example renaming it, or changing the description.
Then: The customfield is updated in the customfield search table.

CustomFieldDeletedEvent

When: Every time somone deletes a customfield.
Then: All permissions on that customfield are deleted. The customfield is deleted from the customfield search table.

UserDeletedEvent

When: Every time somone deletes a Jira user.
Then: All permissions of that user are deleted.

GroupDeletedEvent

When: Every time somone deletes a Jira group.
Then: All permissions of that group are deleted.

UserKeyChangeHandler#update

When: Every time somone uses the GDPR Anonymize User Functionality to anonymize a user (userName, userKey, email are changed to e.g. JIRAUSER123).
Then: All permissions of that user are changed. The userKey in the database table is updated to the new userKey.
(app version 2.1.0 - 2.5.5 and Jira 8.3+. Has been removed in v2.6.0 and replaced by UserAnonymizationHandler)
This Atlassian Video 'The User Who Must Not be Named: GDPR and Your Jira App' explains it in detail.

UserAnonymizationHandler#update

When: Every time somone uses the GDPR Anonymize User Functionality to anonymize a user (userName, userKey, email are changed to e.g. JIRAUSER123).
Then: All permissions of that user are deleted by userKey.
(app version 2.6.0+ and Jira 8.3+)
This Atlassian Video 'The User Who Must Not be Named: GDPR and Your Jira App' explains it in detail.

Caches

We are using the Atlassian Cache 2 implementation.

Global Settings Cache

This cache exists since app version 2.3.0. We have chosen cache type cluster that is limited to one entry and expires after 15 minutes. It is using a CacheLoader to load the setting from the database, if not present. We have chosen this cluster type, since the change of a global setting has to be present across the cluster immediately.

new CacheSettingsBuilder()
  .maxEntries(1)
  .remote()
  .replicateSynchronously()
  .replicateViaCopy()
  .expireAfterWrite(15, TimeUnit.MINUTES)

Scheduled Jobs

We are using the Atlassian SchedulerService implementation.

Event-Log Auto-Truncate Job

This job exists since app version 2.5.4. We have chosen cache runMode RUN_ONCE_PER_CLUSTER. The user can set the cronExpression via the GUI. By default this Job is deactivated. The user has to opt-in and activate this feature if needed. If activated the job will truncate the Customfield Editor EventLog Table at the given times.

Schedule jobSchedule = Schedule.forCronExpression(schedule.getCronExpression());
JobConfig jobConfig = JobConfig
   .forJobRunnerKey(JobRunnerKey.of(MaintenanceEventLogTruncateJob.JOBRUNNERKEY))
   .withSchedule(jobSchedule)
   .withRunMode(RunMode.RUN_ONCE_PER_CLUSTER);
schedulerService.scheduleJob(jobId, jobConfig);

Deprecated App Data Storage

Prior to version 2.0.0 the app stored all settings via the Shared Access Layer (PluginSettingsFactory) which is a Jira API providing simple save and load methods for storing data. This is useful for internal App Settings. This data should usually not be accessed via a database query tool.

Note: Prior to v2.0.0 the userlist contained userNames. Starting with v2.0.0 the user permissions store the userKey. You can read the GDPR BlogPost to see why.

The data is stored by Jira in a table called propertyentry with foreignkey references to the table propertystring. The data can be accessed by using property keys.

Deprecated Global Permissions

jiracustomfieldeditorplugin.<customFieldId>.globalpermissions

For each customfield that has global permissions set, this setting exists. The format is stringified JSON as shown in the example below.

Example entries look like this:

> SELECT * FROM "propertyentry" WHERE property_key = "jiracustomfieldeditorplugin.customfield_10200.globalpermissions"
| id    | entity_name     | entity_id | property_key                                                    | propertytype |
|-------|-----------------|-----------|-----------------------------------------------------------------|--------------|
| 10519 | jira.properties |         1 | jiracustomfieldeditorplugin.customfield_10200.globalpermissions |            5 |
> SELECT * FROM "propertystring" WHERE id = 10519
| id    | propertyvalue                                    |
|-------|--------------------------------------------------|
| 10519 | {"userlist":["admin","foo"],"grouplist":[]}      |

Deprecated Context Permissions

jiracustomfieldeditorplugin.<customFieldId>.contextpermissions

For each customfield that has context permissions set, this setting exists. The format is stringified JSON as shown in the example below.

Example entries look like this:

> SELECT * FROM "propertyentry" WHERE property_key = "jiracustomfieldeditorplugin.customfield_10200.contextpermissions"
| id    | entity_name     | entity_id | property_key                                                     | propertytype |
|-------|-----------------|-----------|------------------------------------------------------------------|--------------|
| 10518 | jira.properties |         1 | jiracustomfieldeditorplugin.customfield_10200.contextpermissions |            5 |
> SELECT * FROM "propertystring" WHERE id = 10518
| id    | propertyvalue                                                    |
|-------|------------------------------------------------------------------|
| 10518 | [{"contextId": 123, "userlist":["admin","foo"],"grouplist":[]}]  |

Manual Deprecated Permission Migration

If for some reason the migration job fails to migrate your permissions even though it shows SUCCESS, please contact the support.

It can be fixed by extracting the old permissions via an SQL Query and creating an migration script that uses the REST API.

> SELECT "propertyentry"."id", "propertyentry"."property_key", "propertystring".* FROM "propertyentry" 
      LEFT JOIN "propertystring" ON "propertystring"."id" = "propertyentry"."id"
      WHERE property_key LIKE 'jiracustomfieldeditorplugin%' ;
| id    | property_key                                                     | id    | propertyvalue                                                 |
|-------+------------------------------------------------------------------+-------+---------------------------------------------------------------|
| 10519 | jiracustomfieldeditorplugin.customfield_10200.globalpermissions  | 10519 | {"userlist":["admin","foo"],"grouplist":["team-a"]}           |
| 10520 | jiracustomfieldeditorplugin.customfield_10200.contextpermissions | 10520 | {"contextId": 123,"userlist":["admin","foo"],"grouplist":[]}  |

You will get a lot of these lines and you can transform them (or ask the support to transform them for you) into a simple migration script.

For the above query it can look like this:

#!/bin/bash

# jiracustomfieldeditorplugin.customfield_10200.globalpermissions  {"userlist":["admin","foo"],"grouplist":["team-a"]} 

curl -X PATCH -u admin:adminPass -H "Content-Type: application/json" \
     "https://jira.foo.io/rest/jiracustomfieldeditorplugin/1/admin/customfields/10200" 
     -d '{"user": "admin", "action": "GRANT", "type": "GLOBAL"}'

curl -X PATCH -u admin:adminPass -H "Content-Type: application/json" \
     "https://jira.foo.io/rest/jiracustomfieldeditorplugin/1/admin/customfields/10200" 
     -d '{"user": "foo", "action": "GRANT", "type": "GLOBAL"}'

curl -X PATCH -u admin:adminPass -H "Content-Type: application/json" \
     "https://jira.foo.io/rest/jiracustomfieldeditorplugin/1/admin/customfields/10200" 
     -d '{"group": "team-a", "action": "GRANT", "type": "GLOBAL"}'

# jiracustomfieldeditorplugin.customfield_10200.contextpermissions  {"contextId": 123,"userlist":["admin","foo"],"grouplist":[]}

curl -X PATCH -u admin:adminPass -H "Content-Type: application/json" \
     "https://jira.foo.io/rest/jiracustomfieldeditorplugin/1/admin/customfields/10200" 
     -d '{"contextId": 123, "user": "admin", "action": "GRANT", "type": "CONTEXT"}'

curl -X PATCH -u admin:adminPass -H "Content-Type: application/json" \
     "https://jira.foo.io/rest/jiracustomfieldeditorplugin/1/admin/customfields/10200" 
     -d '{"contextId": 123, "user": "foo", "action": "GRANT", "type": "CONTEXT"}'

You can execute this on Linux via your terminal and it will patch in the missing permissions that were not migrated.