Database repacking - take 3
Previous issues:
The other maintenance windows did not last long enough to complete all index repacking (as expected). Here, we'll continue to do index repacking during rather low-traffic periods.
In contrast to last time, we'll start earlier at 4pm UTC. This is still past the peak traffic hours, but a about 5K transactions/s more than at 6pm UTC. However, we're watching the process closely and can cancel anytime if we observe negative impact. The previous settings have not yielded unexpected or negative impact really.
C1
Production Change - Criticality 1Change Objective | Database maintenance - removing table and index bloat |
---|---|
Change Type | Type described above |
Services Impacted | PostgreSQL |
Change Team Members | @abrandl |
Change reviewer | |
Change Severity | C1 |
Buddy check or tested in staging | Partly tested in staging, see "Testing" |
Schedule of the change | Proposed time: 2019-04-09 from 4pm UTC |
Duration of the change | 4 hours max |
Detailed steps for the change. Each step must include: | see below |
Refer to #753 (closed) for more details. The same considerations apply here although execution of #753 (closed) went without issues.
Maintenance details
Note: This maintenance is going to run for a maximum of 4 hours. If the repacking is still running, it is going to be cancelled at that point. In this case, we schedule a follow-up change to repack the remaining objects.
Steps:
-
Change max_wal_size
to30GB
:ALTER SYSTEM SET max_wal_size TO '30GB'; SELECT pg_reload_conf();
. Watch checkpoint frequency in Grafana and consider increasing it further during the maintenance. -
[Most likely >> 2 hours] Repack indexes with $1843593
bash indexes3.sh &>> repacking-20190409.log
Finish:
-
Change max_wal_size
to default:ALTER SYSTEM RESET max_wal_size; SELECT pg_reload_conf();
.