Re: PostgreSQL Database performance

Dear Naveed,

I am using PostgreSQL 9.3 version on Windows .After changing these parameters, I have not seen any resource management utilization.

I have observed before and after changing the parameter values ,it is not reflecting the memory level. Maximum utilization of RAM is 3GB only.

So kindly let me ,whether it will impact the RAM utilization or not?

Thanks & Regards

Pradeep Kanth

Ext : 3026

From: Naveed Shaikh [mailto:naveed(dot)shaikh(at)enterprisedb(dot)com]
Sent: 06 September, 2016 11:22 PM
To: Pradeep
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] PostgreSQL Database performance

Which version of PostgreSQL are you using on your windows?

Increasing work_mem can lead to far less disk-swapping, and therefore far quicker queries. However, it can cause problems if set too high, and should be constrained taking into account max_connections. The following calculation is what is typically recommended to determine a decent work_mem value:

Total RAM * 0.25 / max_connections

If there are large reporting queries that run on the database which require more work memory than a typical connection,work_mem can be set for those particular queries. If, for example, there is a reporting user that only runs infrequent but large reports, a specific work_mem setting can be applied to that particular role.
e.g.

ALTER ROLE reporting SET work_mem = '64MB';

---

Warm Regards,

----------

Naveed Shaikh

On Sat, Sep 3, 2016 at 9:08 AM, Pradeep <pgundala(at)avineonindia(dot)com <mailto:pgundala(at)avineonindia(dot)com> > wrote:

Dear Team,

Could you please help me, after changing the below parameters in PostgreSQL configuration file it was not reflecting in OS level and also Database performance is degrading.

Example: I am using Windows 2008 R2 server .For PostgreSQL I have allocated 24GB RAM out of 32GB.

However after changing the below parameters, In task bar it is showing 2.7GB Utilization even though my utilization is more.

So kindly suggest us, whether it will impact or not in Open source PostgreSQL database

max_connections = 100

shared_buffers = 512MB

effective_cache_size = 24GB

work_mem = 110100kB

maintenance_work_mem = 2GB

checkpoint_segments = 64

checkpoint_completion_target = 0.9

wal_buffers = 16MB

default_statistics_target = 100

Thanks & Regards

Pradeep Kanth