0

I'm using logical replication to move data from server X to Y. We had a hiccup on server X that caused the replication to fall severely behind. We don't know what it was, and right now I'm trying to get things back on track. The replication is trying to replicate a little over 200 tables, some of them very large, as well as a large number of mat views that are recalculated nightly. There shouldn't have been many changes to the tables.

I look at AWS performance monitoring as well as pg_stat_activity to see what replication is doing, and I see our one PID in pg_stat_activity for replication and a consistent, yet low volume of activity for that SQL in AWS. There's also a lot of network bandwidth available to send.

I naturally want to see if I can speed things up.

I found an article that describes the various parameters that I can configure, and I wanted to ask about what kind of response I should see. My objective is to raise the number of workers in pg_stat_activity for replication, if possible. Right now I see only 1 for the publisher.

  • max_wal_senders: 25 on both publisher and subscriber
  • max_worker_processes: 10 on both publisher and subscriber
  • max_logical_worker_processes: not present in my version of Postgres (13)
  • max_replication_slots: 10. I'm using 1.
  • wal_receiver_timeout: 10 min.

I check pg_stat_replication too to see the *_lsn values, and here's what I see.

  • send_lsn: 2F036/680AAAC8
  • write_lsn: 2F127/596E8EC0
  • flush_lsn: 2F127/596E8EC0
  • replay_lsn: 2F127/596E8EC0

What's confusing to me here is why is send_lsn behind the rest of the *_lsn values? Shouldn't the sent value be greater than the stuff written/flushed to disk?

Finally, I see our free space on the device sawtoothing. It did this all weekend. enter image description here

Am I going down the right track? I want to push the pedal to the metal here.

Improve this question
6
  • The LSNs look weird. The first thing I'd do is look for messages in the log.
    – Laurenz Albe
    Commented Oct 31, 2023 at 7:39
  • I've got of think that something bad happened at the moment FreeStorageSpace hit zero and then instantly rebounded. Look in the log files to see if you can figure out what.
    – jjanes
    Commented Oct 31, 2023 at 15:31
  • max_logical_worker_processes does not exist in any version. Maybe that is from some fork or non-standard extension.
    – jjanes
    Commented Oct 31, 2023 at 15:36
  • I found max_logical_worker_processes in severalnines.com/blog/…
    – mj_
    Commented Oct 31, 2023 at 16:28
  • @LaurenzAlbe I looked through the logs yesterday over a three day period. I started looking at lines like /Users/user/Downloads/postgresql.log(1).2023-10-28-00:2023-10-28 00:00:26 UTC:10.111.27.165(48883):user@database4083]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp4083.0", size 23437312. I added up the size of the files assuming the file name is pgsql_tmp<file_id>.<file_part_id>. One of the queries wrote over 260 GB of data. It was a mat view refresh where we know the SQL is horrible. Am I correct in thinking that this adds to the WAL and thereby slows replication?
    – mj_
    Commented Nov 1, 2023 at 13:08

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.