PosgtgreSQL hot standby reading WAL from muli-attached volume?
Hi, I wonder can we set up a hot standby in such a way that we don't need any log streaming nor shipping, where instead every hot standby just mounts the same disk in read-only mode which the master uses to write his WAL files? Even without a clustered file system, e.g., a UFS on FreeBSD, one can have the master mount in read-write mode while all the hot standbys would mount the volume read-only. Given that WAL logs are written out at a certain rate, one can at regular intervals issue mount -u /pg_wal and it should refresh the metadata, I assume. I am re-reading about hot-standby, and it strikes me that this method is essentially the "log shipping" method only that there is no actual "shipping" involved, the new log files simply appear all of a sudden on the disk. I suppose there is a question how we know when a new WAL file is finished appearing? And as I read the log-shipping method may not be suitable for hot standby use? Is this something that has been written about already? regards, -Gunther ___ [email protected] mailing list https://lists.freebsd.org/mailman/listinfo/freebsd-performance To unsubscribe, send any mail to "[email protected]"
Re: SHARED LOCKS , EXCLUSIVE LOCKS, ACCESS EXCLUSIVE LOCKS
Thanks Amine and Justin. I will check and try this. Regards, Aditya. On Sun, Apr 4, 2021 at 10:49 PM Justin Pryzby wrote: > On Sun, Apr 04, 2021 at 04:12:14PM +0530, aditya desai wrote: > > Hi, > > We have few select queries during which we see SHARED LOCKS and EXCLUSIVE > > LOCKS on tables. Can these locks cause slowness? Is there any way to > reduce > > the locks? > > > > What must be causing ACCESS EXCLUSIVE LOCKS when the application is > running > > select queries? Is it AUTOVACUUM? > > I suggest to review all the logging settings, and consider setting: > log_destination = 'stderr,csvlog' > > > log_checkpoints = on > > > > log_lock_waits = on > > > > log_min_messages= info > > > log_min_error_statement = notice > > > > log_temp_files = 0 > > > log_min_duration_statement = '9sec' > > > > log_autovacuum_min_duration = '99sec' > > > > You should probably set up some way to monitor logs. > We set log_destination=csvlog and import them into the DB. > Then I have nagios checks for slow queries, errors, many tempfiles, etc. > > https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG > https://www.postgresql.org/message-id/[email protected] > > -- > Justin >
