Thread: PostgreSQL Database performance

On Fri, Sep 2, 2016 at 9:38 PM, Pradeep <pgundala@avineonindia.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.

Actually effective_cache_size allocates nothing. It tells the pgsql
server about how much memory the machine it is running on is using for
OS level caching. On  32G machine with 1G or so of shared_buffers that
number is about right.

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

2.7G is ok. Postgresql expects the OS to help out with caching so it
doesn't need to grab all the memory in the machine etc. In fact that
would be counterproductive in most situations.

> 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

This is WAY too high for work_mem. Work_mem is how much memory a
single sort can grab at once. Each query may run > 1 sort, and you
could have 100 queries running at once.

This setting is 110GB. That's about 109.9GB too high for safety. When
things go wrong with this too big, they go very wrong, sending the
machine into a swap storm from which it may not return.

> maintenance_work_mem = 2GB
>
> checkpoint_segments = 64
>
> checkpoint_completion_target = 0.9

Too high of a checkpoint completion target may cause buffers to get
written out more often than needed. but it varies based on load etc.

> wal_buffers = 16MB
>
> default_statistics_target = 100

It's far more likely that you've just got poorly written queries. I'd
make a post with explain analyze output etc. Here's a good resource
for reporting slow queries:

https://wiki.postgresql.org/wiki/Slow_Query_Questions

--
To understand recursion, one must first understand recursion.

> On Sep 6, 2016, at 12:08 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
> On Fri, Sep 2, 2016 at 9:38 PM, Pradeep <pgundala@avineonindia.com> wrote:
>>
>> max_connections = 100
>> shared_buffers = 512MB
>> effective_cache_size = 24GB
>> work_mem = 110100kB
>
> This is WAY too high for work_mem. Work_mem is how much memory a
> single sort can grab at once. Each query may run > 1 sort, and you
> could have 100 queries running at once.
>
> This setting is 110GB. That's about 109.9GB too high for safety. When
> things go wrong with this too big, they go very wrong, sending the
> machine into a swap storm from which it may not return.

It's an oddly spelled 110MB, which doesn't seem unreasonable.

>
> It's far more likely that you've just got poorly written queries. I'd
> make a post with explain analyze output etc. Here's a good resource
> for reporting slow queries:
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions

+1

Cheers,
  Steve

On Tue, Sep 6, 2016 at 1:18 PM, Steve Atkins <steve@blighty.com> wrote:
>
>> On Sep 6, 2016, at 12:08 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>
>> On Fri, Sep 2, 2016 at 9:38 PM, Pradeep <pgundala@avineonindia.com> wrote:
>>>
>>> max_connections = 100
>>> shared_buffers = 512MB
>>> effective_cache_size = 24GB
>>> work_mem = 110100kB
>>
>> This is WAY too high for work_mem. Work_mem is how much memory a
>> single sort can grab at once. Each query may run > 1 sort, and you
>> could have 100 queries running at once.
>>
>> This setting is 110GB. That's about 109.9GB too high for safety. When
>> things go wrong with this too big, they go very wrong, sending the
>> machine into a swap storm from which it may not return.
>
> It's an oddly spelled 110MB, which doesn't seem unreasonable.

oh yeah. still kind biggish but not as big as I had thought.

On Tue, Sep 6, 2016 at 11:12 PM, Pradeep <pgundala@avineonindia.com> wrote:
> 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?

Postgres reserves some memory for itself and relies on the operating
system to buffer the rest.  So this is not really unusual or
interesting.  What would be interesting is specific examples of things
that are not running as fast as you think they should be.

merlin

On 9/2/2016 8:38 PM, Pradeep wrote:
> ...  In task bar it is showing 2.7GB Utilization ...

odd, the task bar doesn't show any sort of memory utilization on any of
my windows systems.    are you referring instead to the Task Manager ?

Note the Windows Task Manager by default doesn't show shared memory
resources either, you'd have to go to the 'details...' view, then add a
column for 'Memory (shared working set)' to see this.

also note, the 'working set' is the memory thats actually being used,
not potentially allocated.   so even if you've told PG it can have 10GB
of shared buffers, if your database accesses since restarting the
database server have only touched 2GB of actual data, thats all that can
be 'working set'


--
john r pierce, recycling bits in santa cruz

On 9/6/16 2:08 PM, Scott Marlowe wrote:
>> checkpoint_completion_target = 0.9
> Too high of a checkpoint completion target may cause buffers to get
> written out more often than needed. but it varies based on load etc.

The odds on that don't seem to be terribly high. Even if that is a
common occurrence if it's enough to make a difference then you're
already close to the limits of your IO, and if that's true then you
definitely want to spread the checkpoint out over a longer interval.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461