0

My question is, How to know the amount of memory allocated to PostgreSQL and whether it is sufficient or not.

closed as too broad by a_horse_with_no_name, McNets, Andriy M, mustaccio, LowlyDBA Nov 14 '17 at 14:04

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Removed postgresql-9.1 since it's not version-specific. This is a very vague question. What sort of memory allocation, for what? Do you mean cache memory? Working memory for index builds, sorts, etc? Generic bookkeeping RAM? What? – Craig Ringer Nov 14 '17 at 5:35
  • I am asking regarding RAM – Arun Raut Nov 14 '17 at 5:41
  • Please suggest as soon as possible as it is urgent – Arun Raut Nov 14 '17 at 6:03
  • 1
    Your question is too vague to answer as it currently stands, I don't understand what you are actually asking. And if it's urgent, that's what postgresql.org/support/professional_support is for. (Disclaimer, I work for one of these companies). Try describing the problem you are having and trying to solve. – Craig Ringer Nov 14 '17 at 7:22
  • 1
    So what is the actual problem you are trying to solve? – a_horse_with_no_name Nov 14 '17 at 7:46
2

@Arun Raut, As you said that you want to know that memory (RAM) allocation in PostgreSQL.

What processes use much RAM and why?

Shared memory is accessible by all postgres server processes.

Normally the most part of it is shared_buffers. As per PostgreSQL BOL Here PostgreSQL suggest to use 25% of RAM, though often less values are used.

The wal_buffer are normally much smaller, 1/32 of shared_buffer is default. Anyway, you are allowed to set it to arbitrarily large value.

The amount of memory used for table and advisory locks is

About 270 * max_locks_per_transaction
* (max_connections + max_prepared_transactions) bytes

you are probably safe, unless you are doing something tricky using lots advisory locks and increase max_locks_per_transaction to really large values

Same for max_pred_locks_per_transaction - predicate locks are used only for non-default transaction isolation levels, make sure not to increase this setting too much.

No more than autovaccum_max_workers workers, each uses maintenance_work_mem or autovaccum_work_mem of RAM.

Ideally, your tables are not too large and your RAM is not too small , so you can afford setting autovaccum_work_mem to reflect your smallest table size.

Practically, you will autovaccum_work_mem to cover all the small tables in your DB, whatever that means.

Backends and their bgworkers are the most important , as there might be quite a few of them, namely max_connections and max_workers.

The work_mem parameter limits the amount of RAM used per operation, i.e. per execution plan node, not per statement.

For your further ref Here, Here and https://stackoverflow.com/questions/30328861/most-impactful-postgres-settings-to-tweak-when-host-has-lots-of-free-ram

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