LWLock:lock_manager - Amazon Aurora
Supported engine versionsContextLikely causes of increased waitsActions

LWLock:lock_manager

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

Supported engine versions

This wait event information is relevant for Aurora PostgreSQL version 9.6 and higher.

Context

When you issue a SQL statement, Aurora 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.

Fast path locking

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.

Example of a scaling problem for the lock manager

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 vCPUs, the LWLock:lock_manager wait event appears.

Note

The LWLock: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.

Likely causes of increased waits

When the LWLock: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 vCPU.

  • 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.

Actions

We recommend different actions depending on the causes of your wait event.

Use partition pruning

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.

Remove unnecessary indexes

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. This SQL script gathers database information and presents it in a consolidated HTML report. Check the "Unused indexes" section. For more information, see pg-collector in the AWS Labs GitHub repository.

Tune your queries for fast path locking

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.

Tune for other wait events

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

  • Lock:Relation

  • Lock:transactionid

  • Lock:tuple

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

Reduce hardware bottlenecks

You might have a hardware bottleneck, such as CPU starvation or maximum usage of your Amazon EBS 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.

For more information about CPU, memory, and EBS network bandwidth, see Amazon RDS Instance Types.

Use a connection pooler

If your total number of active connections exceeds the maximum vCPU, 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 the vCPUs for your instance type, see Amazon RDS Instance Types.

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

Upgrade your Aurora PostgreSQL version

If your current version of Aurora PostgreSQL is lower than 12, upgrade to version 12 or higher. PostgreSQL versions 12 and 13 have an improved partition mechanism. For more information about version 12, see PostgreSQL 12.0 Release Notes. For more information about upgrading Aurora PostgreSQL, see Database engine updates for Amazon Aurora PostgreSQL.