[production] Execute VACCUUM FREEZE for multiple tables

Production Change

Change Summary

Execute a VACCUUM FREEZE operation for multiple tables.

To avoid any autovacuum to prevent wraparound during the PG 12 Upgrade, it is suggested to execute to all the tables that are candidates to execute a VACUUM FREEZE.

The autovacuum to prevent wraparound should be avoided since could require heavy I/O and even if we terminate it it will restart in seconds, since it is a mechanism to keep the database integrity.

The ideal is to execute this procedure 24 hours before the upgrade.

See: https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/issues/12952

Change Details

1. Services Impacted - ServicePatroni
2. Change Technician - @Finotto
3. Change Criticality - C1
4. Change Type - changescheduled
5. Change Reviewer - @nnelson
6. Due Date - 2021-05-07 22:00 UTC
7. Time tracking - ~6h
8. Downtime Component - No downtime expected/required

Scripts to review

Please review the following scripts:

VACUUM FREEZE table candidate identification: https://gitlab.com/gitlab-com/gl-infra/production/-/snippets/2105030

Detailed steps for the change

Pre-Change Steps - steps to be completed before execution of the change

Estimated Time to Complete (mins) - 5 minutes

• Upload the table candidate identification sql script to the primary patroni node.

  git clone git@gitlab.com:gitlab-com/gl-infra/production/snippets/2105030.git /tmp/2105030
  export GITLAB_ENVIRONMENT='gprd'
  export patroni_node=$(bundle exec knife search node "fqdn:patroni-*-db-${GITLAB_ENVIRONMENT}*" --format=json | jq --raw-output '.rows|=sort_by(.automatic.fqdn)|.rows[0]|.automatic.fqdn')
  export leader_patroni_node=$(ssh "${patroni_node}" 'test -e /usr/bin/jq && sudo /usr/local/bin/gitlab-patronictl list --format json 2>/dev/null' | jq --raw-output '.[] | select(.Role=="Leader").Member')
  echo "${leader_patroni_node}"
  rsync --verbose --progress --archive --exclude=".git" /tmp/2105030 "${leader_patroni_node}":/tmp/

• Execute the sql script to provide an overview of the tables to target for the VACUUM FREEZE operation:

  ssh "${leader_patroni_node}" "sudo su --command='gitlab-psql --file=/tmp/vacuum_freeze_table_candidate_identification.sql' gitlab-psql"

• Execute the sql script to generate a list of tables to target for the VACUUM FREEZE operation:

  ssh "${leader_patroni_node}" "sudo su --command='gitlab-psql --tuples-only --no-align --file=/tmp/vacuum_freeze_table_candidate_identification.sql' gitlab-psql | cut -d'|' -f1 > /tmp/vacuum_freeze_table_candidate.txt"

• Generate the VACUUM FREEZE commands:

  ssh "${leader_patroni_node}" "bash /tmp/2105030/generate_vacuum_freeze_commands.sh"

• Count the number of commands to execute:

  ssh "${leader_patroni_node}" "cat /tmp/vacuum_freeze_commands.txt | wc -l"

Change Steps - steps to take to execute the change

Estimated Time to Complete (mins) - 5 minutes per command

• Establish a secure shell session on the leader patroni node and print the commands to execute:

  ssh "${leader_patroni_node}"
  cat /tmp/vacuum_freeze_commands.txt

• Manually iterate through the list, pausing after each single command:

  # For example:
  sudo su --command='gitlab-psql --command="VACUUM FREEZE gitlab_partitions_dynamic.web_hook_logs_part_0c5294f417_202102;"'

• Wait for 2 to 5 minutes while monitoring the IO saturation on the primary database node.

Post-Change Steps - steps to take to verify the change

Estimated Time to Complete (mins) - 20 minutes

• Continue monitoring the IO saturation on the primary database node.

Rollback

Rollback steps - steps to be taken in the event of a need to rollback this change

Estimated Time to Complete (mins) - 0

• No rollback possible

Monitoring

Key metrics to observe

• Metric: Disk IO
  • Location: https://dashboards.gitlab.net/d/pEfSMUhmy/postgresql-disk-io?orgId=1&var-environment=gprd&var-prometheus=Global&var-type=patroni&var-node=patroni-03-db-gprd.c.gitlab-production.internal
  • What changes to this metric should prompt a rollback:
    • An increase in IO Wait sustained for more than 2 minutes.

Summary of infrastructure changes

• Does this change introduce new compute instances?
  • No
• Does this change re-size any existing compute instances?
  • No
• Does this change introduce any additional usage of tooling like Elastic Search, CDNs, Cloudflare, etc?
  • No

Changes checklist

• This issue has a criticality label (e.g. C1, C2, C3, C4) and a change-type label (e.g. changeunscheduled, changescheduled) based on the Change Management Criticalities.
• This issue has the change technician as the assignee.
• Pre-Change, Change, Post-Change, and Rollback steps and have been filled out and reviewed.
• Necessary approvals have been completed based on the Change Management Workflow.
• Change has been tested in staging and results noted in a comment on this issue.
• A dry-run has been conducted and results noted in a comment on this issue.
• SRE on-call has been informed prior to change being rolled out. (In #production channel, mention @sre-oncall and this issue and await their acknowledgement.)
• There are currently no active incidents.