Sample Header Ad - 728x90

What is a good test command to cause Postgres to use/fill shared_buffers?

0 votes
1 answer
128 views
I am doing testing on a replica, trying to diagnose why servers take several minutes to shutdown when fast mode is used. It seems to correlate with high shmem usage, can anyone recommend a good query / command to run which would cause postgresql to allocate / use a lot of shared buffers (shmem)? I am a Linux engineer but somewhat new to postgres, and only know the basics from the DBA standpoint, but am knowledgable about systems and Linux as well as memory/OS concepts. I've tried running \dt+ and looking for tables that are reasonable large (1G), turning off the pager and doing SELECT * on the table in psql, but it didn't seem to cause much usage increase in the shared memory. I am looking to increase the footprint by ~ <= 200G, ideally. Preferably something simple which doesn't actually write to disk. I don't want to modify the actual state much, just cause high use of shmem (e.g: perhaps a large select, or something ephemeral). This is on a Linux system 3.x kernel without hugepages (THP is enabled at the moment) and mmap is being used for shmem, mainly. postgres 13.6. I am trying to get ~ 500G of shared memory usage
Asked by Gregg Leventhal (135 rep)
Feb 19, 2023, 08:13 PM
Last activity: Feb 19, 2023, 08:38 PM