1

My employer runs Postgres on a decently "large" VM. It is currently configured with 24 cores and 128 GB physical RAM.

Our monitoring solution indicates that the Postgres processes never consume more than about 11 GB of RAM even during periods of heaviest load. Presumably all the remaining free RAM is used by the OS to cache the filesystem.

My question: What configuration settings, if tweaked, are most likely to provide performance gains given a workload that's a mixture of transactional and analytical?

In other words, given there's an embarassingly large amount of free RAM, where am I likely to derive the most "bang for my buck" settings-wise?

EDITED TO ADD:

Here are the current values for some settings frequently mentioned in tuning guides. Note: I didn't set these values; I'm just reading what's in the conf file:

shared_buffers = 32GB
work_mem = 144MB
effective_cache_size = 120GB

"sort_mem" and "max_fsm_pages" weren't set anywhere in the file.

The Postgres version is 9.3.5.

jph
  • 2,181
  • 3
  • 30
  • 55

1 Answers1

1

The setting that controls Postgres memory usage is shared_buffers. The recommended setting is 25% of RAM with a maximum of 8GB.

Since 11GB is close to 8GB, it seems your system is tuned well. You could use effective_cache_size to tell Postgres you have a server with a large amount of memory for OS disk caching.

Two good places for starting Postgres performance tuning:

  • Turn on SQL query logging and explain analyze slow or frequent queries
  • Use pg_activity (a "top" for Postgres) to see what keeps your server busy
Community
  • 1
  • 1
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • For transactional loads I think we're doing pretty well. When the analytics jobs aren't running the disk I/O and CPU are minimal. In fact, we probably have "too much" hardware. The analytical stuff, when it runs, tends to max out disk I/O and comes close to maxing out CPU. That's really what I'd like to expedite if at all possible. (Yes, I know that optimizing the queries themselves and/or table structure and/or indexing strategy are likely to have the biggest impact. Work is happening on that front as well.) – jph May 19 '15 at 15:12