0

We have PostgreSQL 12.9 running on Ubuntu 20.04.3 LTS in a streaming replication setup with 1 primary and 3 standbys where the following options have been configured on all servers:

  • hot_standby=on
  • hot_standby_feedback=on
  • max_standby_archive_delay=30s
  • max_standby_streaming_delay=30s
  • wal_receiver_status_interval=10s

And on the primary only, we have configured:

  • vacuum_defer_cleanup_age=1000

Our largest table is about 1TB in size (half for heap, half for index) with 6.5B rows, and is also the most active one with INSERTS/DELETES occurring frequently, so for that table, we have specified autovacuum_vacuum_scale_factor=0.001. Other server-level settings for autovacuum are:

  • autovacuum_analyze_scale_factor=0.01
  • autovacuum_analyze_threshold=40
  • autovacuum_freeze_max_age=500000000
  • autovacuum_max_workers=20
  • autovacuum_multixact_freeze_max_age=400000000
  • autovacuum_naptime=1s
  • autovacuum_vacuum_cost_delay=10ms
  • utovacuum_vacuum_cost_limit=10000
  • autovacuum_vacuum_scale_factor=0.005
  • autovacuum_vacuum_threshold=50
  • autovacuum_work_mem=-1
  • log_autovacuum_min_duration=100ms

Normally this works well and autovacuum kicks in without issue. However, now that we've seen an increase in TPS on our standby traffic, we are getting many 57014 errors on them "canceling statement due to user request" after about half hour, which is normally how long it takes when actual tuples are removed. This causes spikes in user connections making the clients unstable.

Also we see many of the SELECTs are being blocked, waiting for AccessShareLock from the autovacuum process.

We have traced each of these spikes to the primary which also has an associated 57014 error "canceling autovacuum task". When this occurs we can see no autovacuum is being done for quite some time (several hours).

Things we've tried:

  • Rebuilding indexes to remove bloat. (There is no table bloat, only index bloat).
  • Increasing vacuum_defer_cleanup_age. (Originally the value was 0, and we tried increasing it from 200->400->1000 with no noticeable impact).

In the end, we've had to suspend autovacuum on the table in order to stabilize the system. We still have a nightly job that kicks in to manually run VACUUM (ANALYZE) on the database, but we are hoping there's a better solution.

Improve this question

2 Answers 2

Reset to default
1

A lot of your settings just don't make much sense. Can you explain the reasons for them? hot_standby_feedback and vacuum_defer_cleanup_age and (to some extent) max_standby_streaming_delay are all different ways of addressing more-or-less the same issue. Why do you have all of them set at the same time? There might be a good reason for it, but you should explain it.

Also, autovacuum_naptime=1s seems pretty pointless, though perhaps also pretty harmless. Why do you have that?

Why do you change autovacuum_vacuum_scale_factor at all? Just because the table is large is not a good reason to do that. Is the query performance touching that table critically depend on index-only-scans and needs the visibility map kept up to date for that purpose?

You have increased both autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit from their defaults. But normally one would change those in opposite directions, if at all. Why have you changed them in this way, or indeed at all? That combination is going to make the vacuum quite aggressive in its use of IO, so if the aggressiveness of autovacuum is causing problems, you should probably undo that.

But, it is also hard to figure out what the problem is. Nothing you have told us explains the "canceling statement due to user request" on the replicas. that is due to something outside of the PostgreSQL server requesting the query be cancelled. What is that thing, and why is it there? It could be, for example, hitting ctrl-C while a query was running inside psql, or setting up a client-side timeout in your client, or sending your server backend a SIGINT signal. If you don't want your queries cancelled, stop cancelling them.

The "canceling autovacuum task" error is because someone has requested a strong lock on the table while autovacuum was running, and the autovacuum is willing to sacrifice itself to avoid blocking that someone. Common reasons for such locks would be ALTER TABLE, CREATE INDEX, or a manual VACUUM. If you turn log_lock_waits on, you should see log entries for whatever that is. But, it doesn't seem like this is causing the problem, it is just something you happened to see in the log while looking for a problem. If autovacuum doesn't run on that table a for long time after the cancel, it is probably just because the competing lock is still being held, so it can't run.

which is normally how long it takes when actual tuples are removed.

What does this mean? It is how long those same queries normally take anyway, when there is no problem? It is how long a vacuum normally takes? What are non-actual tuples?

Improve this answer
7
  • Hi @jjanes, thank you for your reply. So here is some info to better explain: hot_standby_feedback is on because we use streaming replication and are trying to avoid timeouts on the standbys. We have not adjusted max_standby_streaming_delay and it's sitting at the default of 30s. vacuum_defer_cleanup_age is what we've tried to use to tune as we know the autovacuum is the cause of query cancels on the standbys. The pattern is very obvious whenever we see the queries timing out on standbys. However it has not made any noticeable difference and we will revert back to the default value.
    – Diane
    Feb 21 at 17:45
  • Similarly we have not tuned autovacuum_naptime and it's sitting at the default value of 1s. We changed autovacuum_vacuum_scale_factor on the table level because it is over 3TB in size and before changing it, table bloat was huge and unable to keep up with vacuuming. We have not touched the default value for autovacuum_vacuum_cost_limit, but did adjust autovacuum_vacuum_cost_delay to be able to vacuum more agressively. FYI we are not IO, CPU, or Memory bound as the servers we are using have 3TB of RAM with huge pages on, 192 cores, and RamSan disks.
    – Diane
    Feb 21 at 17:57
  • We know the issue is autovacuum because every time we see instability, it exactly tracks to the primary cancelling autovacuum, and only then. Since we've turned off autovacuum, the issue has not occurred again. The problem is that with it off, table bloat is growing considerably again. We have logging on for autovacuum, and what I mean by "actual tuples removed" is when the log reports tuples have been removed, instead of skipped.
    – Diane
    Feb 21 at 18:03
  • @Diane The default naptime is 1min, not 1s. Also, the canceling is not certainly not due to max_standby_streaming_delay, as that would generate an error message other than "canceling statement due to user request". It also cannot be directly due to autovac. It could be indirectly due to it, as your autovac loads up your IO, which slows down your queries enough to fall afoul of the (source still unknown) client side cancellation timeouts.
    – jjanes
    Feb 21 at 18:34
  • you're right that naptime is 1min default. The 1s was probably put in to aggressively vacuum and prevent table bloat. Also I never said the cancelling is due to max_standby_streaming_delay. I simply said it's due to autovacuum. We have a graph of all Postgres errors on the primary and standbys, and every time we get the error cancelling autovacuum on the primary, it leads to all standbys getting query timeouts. So what could explain that behavior?
    – Diane
    Feb 21 at 23:11
0

Just wanted to report back that after trying a number of things (reindexing to remove bloat, changing vacuum_defer_age from 0 to 200 to 20000 to 100000), we eventually resolved this by setting old_snapshot_threshold and restarting the servers. It's been over 6 months and we haven't encountered this issue since. We did have to take into account that our backups run on the standby and this counts in terms of query activity, so in our case we had to set the value longer than our backup time (i.e. backup time + 1h).

It should be noted that Postgres 16 removes vacuum_defer_age anyways so it should not be used.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.