0

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

1 Answer 1

1

why servers take several minutes to shutdown when fast mode is used

On shutdown, postgresql creates a checkpoint. Or restartpoint if the database is in hot standby mode. This means writing (with fsync) all changed data from shared buffers. The more changes happened on the primary, the more the database needs to write.

The usual procedure for a production database for a quick restart is:

  • run checkpoint command on primary, wait for execution
  • run the checkpoint command on the replica we want to shut down or restart
  • if the checkpoint was executed for more than ten seconds - repeat from the beginning
  • restart replica

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

pg_prewarm of some big relation.

2
  • Thanks, that all sounds reasonable! Because this is a replica, it doesn't seem to want to let me install the extension for pg_prewarm, but I did end up having success doing a select on a very large table row. I guess the ones I'd tried so far were already in memory. Commented Feb 20, 2023 at 2:41
  • If you install pg_prewarm on the primary, it is automatically installed on the standby. Commented Feb 20, 2023 at 2:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.