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 <pryzby(at)telsasoft(dot)com> 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/20190206232110.GH29720@telsasoft.com
>
> --
> Justin
>