How to get rid of BufferMapping?

Question

I work with a partition table (200 partitions), in each partition of 5.000.000 records (table with id sequence, index on every partition and total 10 column, 7 int and 3 text).

I start 200 sessions through java at the same time and try to make the following request:

create or replace procedure create_temp_users(thread integer) language plpgsql as $$
begin
    execute format('create temp table temp_users as
            select * from users_partition_%s sj', thread);
end $$;

And I get light, but long locks

LWLock  BufferMapping create_temp_users(0)
LWLock  BufferMapping create_temp_users(1)
LWLock  BufferMapping create_temp_users(2)
LWLock  BufferMapping create_temp_users(3)
LWLock  BufferMapping create_temp_users(4)
...

server spec:

CPU(s):                176
Model name:            Intel(R) Xeon(R) Gold 6238 CPU @ 2.10GHz
MemTotal:              2113275652 kB

postgres params:

maintenance_work_mem    100GB
max_parallel_maintenance_workers    80
max_parallel_workers    160
max_parallel_workers_per_gather 80
segment_size    1GB
work_mem    32MB
shared_buffers  496GB
server_version  13.6
server_version_num  130006

Server without replica. How to remove or reduce light locks? I tried to increase work_mem, temp_buffers, remove index. I tried analyze and even copy data from one temp table to another, nothing helps :(

Answer 1

I think that there are two aggravating factors here: the huge shared_buffers and the high number of active session.

The BufferMapping lock is taken whenever a shared buffer is assigned new content. See this enlightening passage from src/backend/storage/buffer/README:

Buffer Manager's Internal Locking

Before PostgreSQL 8.1, all operations of the shared buffer manager itself were protected by a single system-wide lock, the BufMgrLock, which unsurprisingly proved to be a source of contention. The new locking scheme avoids grabbing system-wide exclusive locks in common code paths. It works like this:

• There is a system-wide LWLock, the BufMappingLock, that notionally protects the mapping from buffer tags (page identifiers) to buffers. (Physically, it can be thought of as protecting the hash table maintained by buf_table.c.) To look up whether a buffer exists for a tag, it is sufficient to obtain share lock on the BufMappingLock. Note that one must pin the found buffer, if any, before releasing the BufMappingLock. To alter the page assignment of any buffer, one must hold exclusive lock on the BufMappingLock. This lock must be held across adjusting the buffer's header fields and changing the buf_table hash table. The only common operation that needs exclusive lock is reading in a page that was not in shared buffers already, which will require at least a kernel call and usually a wait for I/O, so it will be slow anyway.

• As of PG 8.2, the BufMappingLock has been split into NUM_BUFFER_PARTITIONS separate locks, each guarding a portion of the buffer tag space. This allows further reduction of contention in the normal code paths. The partition that a particular buffer tag belongs to is determined from the low-order bits of the tag's hash value. The rules stated above apply to each partition independently. If it is necessary to lock more than one partition at a time, they must be locked in partition-number order to avoid risk of deadlock.

There are 128 buffer locks defined in src/include/storage/lwlock.h:

/* Number of partitions of the shared buffer mapping hashtable */
#define NUM_BUFFER_PARTITIONS  128

Since your shared buffers are large, each of these partitions is large, so each lock protects a lot of buffers. With 200 sessions competing for shared buffers to cache table data, there will be contention. Obviously not all the data from this partitioned table were cached when you started.

I would start by reducing the number of sessions. Unless you have a really powerful machine and storage system, that may be too many anyway. Reducing shared_buffers might be something you can try as well, but only an experiment can show if that is a net benefit.

If you are ready to rebuild PostgreSQL, you could also increase NUM_BUFFER_PARTITIONS and see if that makes your life better.