Timeline for Postgres slow replication debug strategy
Current License: CC BY-SA 4.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 1, 2023 at 16:52 | comment | added | Laurenz Albe |
Please add additional information like log entries by editing the question. The changes in disk space utilization can be explained by temporary files. But that is unrelated to the weird LSNs in pg_stat_replication.
|
|
| Nov 1, 2023 at 13:08 | comment | added | mj_ | @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? | |
| Oct 31, 2023 at 16:28 | comment | added | mj_ | I found max_logical_worker_processes in severalnines.com/blog/… | |
| Oct 31, 2023 at 15:36 | comment | added | jjanes | max_logical_worker_processes does not exist in any version. Maybe that is from some fork or non-standard extension. | |
| Oct 31, 2023 at 15:31 | comment | added | jjanes | 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. | |
| Oct 31, 2023 at 7:39 | comment | added | Laurenz Albe | The LSNs look weird. The first thing I'd do is look for messages in the log. | |
| Oct 30, 2023 at 18:30 | history | asked | mj_ | CC BY-SA 4.0 |