CODEWITHSUNDEEP
postgresqlconfigurationpostgresql-9.4
RMu
RMu

Reputation: 869

Postgres memory calculation effective_cache_size

I am verifying some of the configuration in my production Postgres instance. Our DB server has 32 GB RAM. From pg_settings, I see that effective_cache_size is set to: 

postgres=> select name, setting, unit from pg_settings where name like 'effective_cache_size'; 
         name         | setting | unit 
----------------------+---------+------
 effective_cache_size | 7851762 | 8kB
(1 row)

As per my understanding, this value accounts to 7851762 X 8 KB = 62.8 GB. If my calculation is right, we are basically telling the optimizer that we have 62 GB for this parameter whereas we have only 32 GB of physical RAM.

Please correct me if I am calculating this parameter wrong. I always get confused with calculating parameter allocations for units with 8 KB.

Upvotes: 2

Views: 7450

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246483

7851762 times 8 kB is approximately 60 GB.

I would configure the setting to 30 GB if the machine is dedicated to the PostgreSQL database.

This parameter tells PostgreSQL how much memory there is available for caching its files. If the value is high, PostgreSQL will estimate nested loop joins with an index scan on the inner side cheaper, because it assumes that the index will probably be cached.

Upvotes: 4

Related Questions