Search code examples
postgresqlamazon-web-servicesrds

Resize Shared buffer size in Postgresql hosted in AWS RDS

We are facing performance issue on Production Postgresql Database server which is hosted in AWS RDS server. So that we installed postgresql in EC2 instance Linux server for pre-production and tried with shared buffer value 15% of RAM value we get some positive response.

So that, Definitely we can assign shared buffer value 15% to 30 % of RAM value. But when i trying to resize shared buffer value in Production DB server which i hosted in AWS RDS. it says invalid parameter value. By default following value is assigned for shared buffer parameter. shared buffer = {DBInstanceClassMemory/32768}
Please help me to resize shared buffer value is min 15 % of RAM memory. My Instance specification is : 2 CPU, 7.5 GB RAM (db.m3.large).

Solution

  • If you want to set shared_buffers to 15% of RAM, put 147456 as value instead of {DBInstanceClassMemory/32768}...

    shared buffers is set as number of 8kB blocks => calculating is such: 

    postgres=> select (15*7.5*1024*1024 /100)/8;
      ?column?
---------------------
 147456.000000000000
(1 row)

    where:

    • 15 is 15%
    • 7.5 is 7.5 GB of RAM
    • 1024*1024 to convert GB to KB (to unify against shared_buffers units
    • 8 is 8kB

    then you have to reboot your RDS instance and check value with:

    show shared_buffers; in psql