Uploaded image for project: 'Jira Software Data Center'
  1. Jira Software Data Center
  2. JSWSERVER-21546

Performance guardrails for "AO_E8B6CC_MESSAGE*" tables

XMLWordPrintable

    • Icon: Suggestion Suggestion
    • Resolution: Unresolved
    • None
    • DVCS Accounts
    • None
    • 1
    • We collect Jira feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

      Problem Definition:

      The "AO_E8B6CC_MESSAGE*" tables grow very large when integrating Github repositories with a lot of data (for example, 1M+ records). This causes very slow queries to run frequently and puts pressure on db CPU.

      Diagnostic Data:

      Queries like this are visible as active for too long in the back-end: 

      29759,"jira","jira","PostgreSQL JDBC Driver",NULL,53364,"2022-10-18 09:36:39.861447+00","2022-10-18 10:04:43.28399+00","SELECT queueItem.""RETRIES_COUNT"",queueItem.""STATE"",queueItem.""STATE_INFO"",queueItem.""ID"",queueItem.""QUEUE"",queueItem.""LAST_FAILED"" FROM public.""AO_E8B6CC_MESSAGE_QUEUE_ITEM"" queueItem JOIN public.""AO_E8B6CC_MESSAGE"" message ON queueItem.""MESSAGE_ID"" = message.""ID"" WHERE message.""ADDRESS"" = $1 AND message.""PRIORITY"" = $2 AND queueItem.""QUEUE"" = $3 AND queueItem.""STATE"" = $4 ORDER BY message.""ID"" ASC LIMIT 1","active"
29890,"jira","jira","PostgreSQL JDBC Driver",NULL,39430,"2022-10-18 08:47:27.278482+00","2022-10-18 10:04:43.253429+00","SELECT COUNT(*) FROM public.""AO_E8B6CC_MESSAGE"" messageMapping JOIN public.""AO_E8B6CC_MESSAGE_TAG"" messageTag ON messageMapping.""ID"" = messageTag.""MESSAGE_ID"" JOIN public.""AO_E8B6CC_MESSAGE_QUEUE_ITEM"" messageQueueItem ON messageMapping.""ID"" = messageQueueItem.""MESSAGE_ID"" WHERE messageTag.""TAG"" = $1 AND messageQueueItem.""STATE"" in ( $2, $3, $4 ) ","active"

      Suggested Solution:

      Several suggestions that I could provide is to:

      1. provide a guardrail mechanism where the repository querying is limited by a more recent timeframe
      2. limit the amount of historical data that is stored
      3. implement a cleanup procedure for older historical data

      Additional notes

      As removing a repo and re-adding it causes a new repo ID to be generated, there needs to be a cleanup of the stale repo ID messages in the tables:

      1. AO_E8B6CC_MESSAGE
      2. AO_E8B6CC_MESSAGE_TAG
      3. AO_E8B6CC_MESSAGE_QUEUE_ITEM

              Unassigned Unassigned
              emarghidan Eduard M
              Votes:
              2 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated: