We are trying to improve the performance of our application and have been testing read only queries connecting to our stand-by DB. Also, we would like to perform ad-hoc DB dumps (via pg_dump) from the stand-by DB.
We have been testing some of the DB parameters in the config file, but see these as well:
'connecttimeout' => 2, // Set read-only slave connect timeout in seconds. See above.
'latency' => 0.5, // Set read-only slave sync latency in seconds.
'exclude_tables' => [ // Tables to exclude from read-only slave feature.
'table1', // Should not be used, unless in rare cases when some area of the system
'table2', // is malfunctioning and you still want to use readonly feature.
], // Then one can exclude offending tables while investigating.
We've noticed an error if a user runs a log report on a course when connected to the stand-by DB (some report logs can take up to 1 min. to complete):ERROR: canceling statement due to conflict with recovery
Detail: User query might have needed to see row versions that must be removed
We think this table (mdl_logstore_standard_log) is getting updated while running the report, just wondering if it is best practice to add the table name to the exclude_tables parameter. They do keep 2 years worth of logs so there's no way to shorten the time span.We do get the same error when trying to perform a pg_dump on the stand-by DB.
PostgreSQL version: 15.5
Moodle version: 4.1.8
PHP version: 8.1
PostgreSQL Standby DB setting:
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s
Please let me know if you need additional info/details...Much appreciated,
Eugene