Обсуждение: SHARED LOCKS , EXCLUSIVE LOCKS, ACCESS EXCLUSIVE LOCKS
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?
Regards,
Aditya.
Hi;
It's normal to see locks on tables during queries. These are usually locks used automatically by postgres as a result of the operations you perform on your database. You should check the document for the lock modes postgres uses.
Lock causes slowness if it causes other queries to wait. You can see the queries waiting for lock from pg_locks view. Access Exclusive Lock completely locks the table, does not allow read and write operations, blocks queries.
It's normal to see locks on tables during queries. These are usually locks used automatically by postgres as a result of the operations you perform on your database. You should check the document for the lock modes postgres uses.
Lock causes slowness if it causes other queries to wait. You can see the queries waiting for lock from pg_locks view. Access Exclusive Lock completely locks the table, does not allow read and write operations, blocks queries.
Commands such as drop table, truncate, reindex, vacuum full, alter table use this lock. And autovacuum uses a weaker lock on the table, not using an exclusive lock.
aditya desai <admad123@gmail.com>, 4 Nis 2021 Paz, 13:42 tarihinde şunu yazdı:
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?Regards,Aditya.
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
Thanks Amine and Justin. I will check and try this.
Regards,
Aditya.
On Sun, Apr 4, 2021 at 10:49 PM Justin Pryzby <pryzby@telsasoft.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
On 4/4/21 6:42 AM, 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? > Suggest you read this part of The Fine Manual: <https://www.postgresql.org/docs/current/explicit-locking.html> cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com