Increase checkpoint_completion_target to 0.9

As per https://gitlab.com/gitlab-com/infrastructure/issues/1448 we were recommended to use a value of 0.9 instead of 0.7, giving checkpoints more time to perform their writes.

mentioned in issue #1448 (closed)

assigned to @yorickpeterse

changed milestone to %WoW ending 2017-10-03

mentioned in issue #2873 (closed)

Per the PostgreSQL docs and our Omnibus setup this won't require a restart. However it may require running a gitlab-ctl reconfigure which will mess with our manual pgbouncer tweaks still running on our secondaries.

@jtevnan What's the best way to work around this? I'm thinking of these steps:

1. Commit changes to Chef
2. Run chef-client
3. Manually adjust runtime.conf to match the new changes
4. Reload PostgreSQL

This way the next time a reconfigure runs it won't change anything, and we still get our changes.

added change label

To test things I changed this setting as follows:

alter system set checkpoint_completion_target = 0.9;
select pg_reload_conf();

I'll be monitoring our checkpoint/disk stats for the next 30 minutes to see how this impacts things.

Observations so far (change applied around 12:30 UTC):

Checkpoint timings on the primary are slightly longer as expected:

There is slightly more time between checkpoints, but not a whole lot:

SQL query timings went up, but this could just be coincidence:

Thus far I'm also not seeing a change in disk IO.

On 12:55 UTC I reverted checkpoint_completion_target to 0.7 to see if this changed the SQL timings. Thus far I'm not seeing a change in timings, suggesting it was just a coincidence. I'll change the setting back to 0.9 so we can monitor for a bit longer.

Looking at the last 24 hours of data I'm not really seeing any conclusive changes just yet. I'm going to keep this setting active for this week, then revert it next Monday. That way we can compare two entire work weeks and see if there's any difference.

added experiment label

changed milestone to %WoW

added moved 1 label

Poking around the various statistics we have, including IOPS per disk I have been unable to find any change in disk related patterns by setting this value to 0.9. As such I'll revert it back to 0.7 and close this issue since it appears we don't need a value of 0.9.

closed