Performance guardrails for "AO_E8B6CC_MESSAGE*" tables

XMLWordPrintable

    • Type: Suggestion
    • Resolution: Unresolved
    • None
    • Component/s: DVCS Accounts
    • 1
    • 14

      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

            Assignee:
            Unassigned
            Reporter:
            Eduard M (Inactive)
            Votes:
            2 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated: