LWLock - lock_manager

This event occurs when the PostgreSQL engine maintains the shared lock’s memory area to allocate, check, and deallocate a lock when a fast path lock isn’t possible.

When you issue a SQL statement, PostgreSQL records locks to protect the structure, data, and integrity of your database during concurrent operations. The engine can achieve this goal using a fast path lock or a path lock that isn’t fast. A path lock that isn’t fast is more expensive and creates more overhead than a fast path lock.

To reduce the overhead of locks that are taken and released frequently, but that rarely conflict, backend processes can use fast path locking. The database uses this mechanism for locks that meet the following criteria:

• They use the DEFAULT lock method.
• They represent a lock on a database relation rather than a shared relation.
• They are weak locks that are unlikely to conflict.
• The engine can quickly verify that no conflicting locks can possibly exist.

The engine can’t use fast path locking when either of the following conditions is true:

• The lock doesn’t meet the preceding criteria.
• No more slots are available for the backend process.

For more information about fast path locking, see fast path in the PostgreSQL lock manager README and pg-locks in the PostgreSQL documentation.

In this example, a table named purchases stores five years of data, partitioned by day. Each partition has two indexes. The following sequence of events occurs:

1. You query many days worth of data, which requires the database to read many partitions.
2. The database creates a lock entry for each partition. If partition indexes are part of the optimizer access path, the database creates a lock entry for them, too.
3. When the number of requested locks entries for the same backend process is higher than 16, which is the value of FP_LOCK_SLOTS_PER_BACKEND, the lock manager uses the non–fast path lock method.

Modern applications might have hundreds of sessions. If concurrent sessions are querying the parent without proper partition pruning, the database might create hundreds or even thousands of non–fast path locks. Typically, when this concurrency is higher than the number of CPU cores, the lock_manager wait event appears.

Note

The lock_manager wait event isn’t related to the number of partitions or indexes in a database schema. Instead, it’s related to the number of non–fast path locks that the database must control.

When the lock_manager wait event occurs more than normal, possibly indicating a performance problem, the most likely causes of sudden spikes are as follows:

• Concurrent active sessions are running queries that don’t use fast path locks. These sessions also exceed the maximum CPU cores.
• A large number of concurrent active sessions are accessing a heavily partitioned table. Each partition has multiple indexes.
• The database is experiencing a connection storm. By default, some applications and connection pool software create more connections when the database is slow. This practice makes the problem worse. Tune your connection pool software so that connection storms don’t occur.
• A large number of sessions query a parent table without pruning partitions.
• A data definition language (DDL), data manipulation language (DML), or a maintenance command exclusively locks either a busy relation or tuples that are frequently accessed or modified.

If the CPU wait event occurs, it doesn’t necessarily indicate a performance problem. Respond to this event only when performance degrades and this wait event is dominating DB load.

Partition pruning is a query optimization strategy that excludes unneeded partitions from table scans, thereby improving performance. Partition pruning is turned on by default. If it is turned off, turn it on as follows.

SET enable_partition_pruning = on;

Queries can take advantage of partition pruning when their WHERE clause contains the column used for the partitioning. For more information, see Partition Pruning in the PostgreSQL documentation.

Your database might contain unused or rarely used indexes. If so, consider deleting them. Do either of the following:

• Learn how to find unnecessary indexes by reading Unused Indexes in the PostgreSQL wiki.
• Run PG Collector pg-collector. This SQL script gathers database information and presents it in a consolidated HTML report. Check the “Unused indexes” section.

To find out whether your queries use fast path locking, query the fastpath column in the pg_locks table. If your queries aren’t using fast path locking, try to reduce number of relations per query to fewer than 16.

If lock_manager is first or second in the list of top waits, check whether the following wait events also appear in the list:

• Relation
• transactionid
• tuple

If the preceding events appear high in the list, consider tuning these wait events first. These events can be a driver for lock_manager.

You might have a hardware bottleneck, such as CPU starvation or maximum usage of your storage device bandwidth. In these cases, consider reducing the hardware bottlenecks. Consider the following actions:

• Scale up your instance class.
• Optimize queries that consume large amounts of CPU and memory.
• Change your application logic.
• Archive your data.

If your total number of active connections exceeds the maximum CPU cores, more OS processes require CPU than your instance type can support. In this case, consider using or tuning a connection pool.

For more information about connection pooling, see the following resources:

• pgbouncer
• Connection Pools and Data Sources