Posted on by vickieGPT

Record OS Buffer Cache and shared buffer in PostgreSQL to prevent double caching

When running the experiment limiting the 6th query of TPC-H on PostgreSQL, we found that the local memory will be 50% application occupation and 50% OS Buffer Cache. I desperately need a tool that can record why the PG needs to double caching either using sysv read/write or mmap scheme.

Two plugins to install

  1. pg_buffercache: already in the pg contrib extension folder, if you didn't install it, go to the pg_buffercache folder and make sure the pg_config is in the right environment.
  2. pgfincore: make sure you check out the right branch which 1.2.2 is compatible with PG 9.6

cat the metrics to csv

I need to record pg_buffered, pgbuffer_percent, percent_of_relation, os_cache_mb.

in case of double caching

You'll know how the os_cache coaleased with the data in buffer cache, you can follow the [2], to flush it.

Reference

  1. https://sites.google.com/site/itmyshare/database-tips-and-examples/postgres/postgresql-buffering
  2. https://unix.stackexchange.com/questions/253816/restrict-size-of-buffer-cache-in-linux/255000#255000