Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use lightweight locks in PostgreSQL." in daily operation, I believe many people are using lightweight locks in PostgreSQL. There are doubts on the problem. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation, hoping to answer "how to use lightweight locks in PostgreSQL." The confusion helps! Next, please follow the editor to study!

Brief introduction

Lightweight locks (LWLocks, called Latchs in other databases) lightweight locks in PG are used to control memory access, and there are only two levels of locks: shared and exclusive.

You can view information about Lock/Wait through the system view pg_stat_activity

[local]: 5432 pg12@testdb=# select * from pg_stat_activity where pid = 5914 -[RECORD 1]-+-- datid | 16384datname | testdbpid | 5914usesysid | 10usename | pg12application_name | psqlclient_addr | client_hostname | client_port |-1backend_start | 2019-08-22 11:40:58.504462+08xact_start | | 2019-08-22 12:16:02.528978+08query_start | 2019-08-22 12:16:04.372427+08state_change | 2019-08-22 12:16:04.374888+08wait_event_type | Clientwait_event | ClientReadstate | idle in transactionbackend_xid | 716backend_xmin | query | delete from t_prewarm where id = 1 | Backend_type | client backendTime: 14.262 ms [local]: 5432 pg12@testdb=# select * from pg_stat_activity where pid = 5964 -[RECORD 1]-+-- datid | 16384datname | testdbpid | 5964usesysid | 10usename | pg12application_name | psqlclient_addr | client_hostname | client_port |-1backend_start | 2019-08 -22 11:41:10.420664+08xact_start | 2019-08-22 12:16:11.812598+08query_start | 2019-08-22 12:16:18.718567+08state_change | 2019-08-22 12:16:18.718572+08wait_event_type | Lockwait_event | transactionidstate | activebackend_xid | backend_xmin | 716query | select * from t_prewarm where id = 1 for share Backend_type | client backendTime: 4.655 ms

LWLock Typ

WALInsertLock: protect WAL buffers. WAL buffers can be added to improve contention. Setting synchronous_commit=off and full_page_writes=off reduces contention, but it is not recommended.

Use this lock when WALWriteLock:WAL Record flushing or WAL segment switching. Brush _ commit=off clears the wait. Full _ page_writes=off reduces the data size.

LockMgrLock: appears in a wait event under a read-only workload. No matter how big or small, relations is locked. The Lock is not a single lock, but at least 16 partitions. Therefore, it is important to use multiple tables in benchmarking.

ProcArrayLock: contains the ProcArray structure.

CLogControlLock: contains the CLogControl structure. If it occurs frequently in pg_stat_activity, check the $PGDATA/pg_clog (PG11+:pg_xact) directory, which should be on the cached file system.

SInvalidReadLock: contains the sinval structure. Readers use shared locks, while updates in SICleanupQueue and other array ranges request exclusive locks. If there is great pressure on the shared cache, the appearance of the structure can be seen in the system view, and increasing shared_buffers can reduce contention.

BufMappingLocks: the area containing buffers. PG sets up 128 buffer areas to manage the entire cache.

Spinlocks

The lowest level of lock is a spin lock, which is implemented using a mechanism specific to CPU.

So far, about "how to use lightweight locks in PostgreSQL." The study is over. I hope I can solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.