2

I am running Postgresql 11 in production and observing a weird step function kind of thing in storage as follow: enter image description here

The above graph is for a single day and it is happening everyday at random times. I have checked and there are no bulk inserts from our side. Also, these steps are weird because storage again comes back to its normal value after some time.

I also have read replica and because of this, there is sudden increase in disk usage of read replica which results in high replication times of around 30s, which in further results in conflict with recovery problem. Is there a way to deal with this? Is this common behaviour of Postgres. I am running postgresql on Google Cloud SQL and it has been happening for a long time but I observed today, so I am wondering is it a normal thing or do I need to check something?

One suspicion I have is that it might be because of vaccumm but I am not sure? Please help me with this.

Edit 1:

There are spikes like below whenever this occur for read/write io. Red is the write one and blue is the read one

enter image description here

6
  • 1
    Does that usage meter includes WAL files? Commented Jul 11, 2020 at 14:39
  • I think it must include that. I am running things on Google Cloud SQL. Though I am not sure what is their convention but I guess it does include that as well.
    – hardik24
    Commented Jul 11, 2020 at 14:54
  • Yes, that looks like WAL. Do you use replication slots with a standby that is down occasionally? Does pg_stat_archiver report oddities? Commented Jul 13, 2020 at 6:44
  • @LaurenzAlbe We do use replication slot but standby is not down in general. Actually, similar graph happens on standby server as well. It is up most of the time. How do i check pg_stat_archiver status and problem? Is it there in logs. Since, I am using managed service, I generally don't have access to everything.
    – hardik24
    Commented Jul 13, 2020 at 10:05
  • My another observation is that the peak generally coincide with vacuum time of a big table. Can that be the case?
    – hardik24
    Commented Jul 13, 2020 at 10:14

1 Answer 1

0

Since you have no filesystem access you won't get the answer but some guesses only, so here comes mine: if there are no bulk INSERTs, this might be some temporary space created by SELECT, for example big query with "Sort Method: external merge Disk", or some TEMPORARY tables, materialized view REFRESH etc. Such query would obviously take some time, so check your query log for very long duration and EXPLAIN them if found.

The usage sliding down, however, suggests it's WAL-related. SELECT * FROM pg_stat_archiver or pg_stat_replication might show something interesting, e.g. I check for streaming lag as follows:

SELECT active, pg_wal_lsn_diff(pg_current_wal_insert_lsn(), restart_lsn) AS retained_bytes FROM pg_replication_slots

What also might happen is that the space reported itself is skewed compared to datasize. For example (this is not the case, but makes reading such graphs easier) reclaiming disk space from btrfs or after removing many files on ext3/4 filesystem might take some significant time (I've seen like 10 minutes or so on btrfs).

1
  • For the extra information regarding this... This happens when vacuum of some big table completes.
    – hardik24
    Commented Jul 20, 2020 at 14:05

Your Answer

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

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