Re: SHARED LOCKS , EXCLUSIVE LOCKS, ACCESS EXCLUSIVE LOCKS

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