From: Adarsh Sharma <adarsh.sharma@orkash.com>
To: tv@fuzzy.cz
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres Performance Tuning
Date: Mon, 04 Apr 2011 16:09:20 +0530
Message-ID: <4D999FD8.9070909@orkash.com> (raw)
In-Reply-To: <8b57d989ff79004f9159326f008abefb.squirrel@sq.gransy.com>
References: <4D999211.9080507@orkash.com>
<8b57d989ff79004f9159326f008abefb.squirrel@sq.gransy.com>
tv@fuzzy.cz wrote:
>> max_connections = 700
>> shared_buffers = 4096MB
>> temp_buffers = 16MB
>> work_mem = 64MB
>> maintenance_work_mem = 128MB
>> wal_buffers = 32MB
>> checkpoint_segments = 32
>> random_page_cost = 2.0
>> effective_cache_size = 4096MB
>
> First of all, there's no reason to increase wal_buffers above 32MB. AFAIK
> the largest sensible value is 16MB - I doubt increasing it further will
> improve performance.
>
> Second - effective_cache_size is just a hint how much memory is used by
> the operating system for filesystem cache. So this does not influence
> amount of allocated memory in any way.
>
>> but Still Postgres Server uses Swap Memory While SELECT & INSERT into
>> database tables.
>
> Are you sure it's PostgreSQL. What else is running on the box? Have you
> analyzed why the SQL queries are slow (using EXPLAIN)?
Thanks , Below is my action points :-
max_connections = 300 ( I don't think that application uses more than
300 connections )
shared_buffers = 4096MB
temp_buffers = 16MB
work_mem = 64MB
maintenance_work_mem = 128MB
wal_buffers = 16MB ( As per U'r suggestions )
checkpoint_segments = 32
random_page_cost = 2.0
effective_cache_size = 8192MB ( Recommended 50% of RAM )
My Shared Memory Variables are as:-
[root@s8-mysd-2 ~]# cat /proc/sys/kernel/shmmax
6442450944
[root@s8-mysd-2 ~]# cat /proc/sys/kernel/shmall
6442450944
[root@s8-mysd-2 ~]
Please let me know if any parameter need some change.
As now I am going change my parameters as per the below link :-
http://airumman.blogspot.com/2011/03/postgresql-parameters-for-new-dedicated.html
But one thing I am not able to understand is :-
Start the server and find out how much memory is still available for the
OS filesystem cache
U'r absolutely right I am also researching on the explain of all select
statements and i find one reason of poor indexing on TEXT columns.
Thanks & best Regards,
Adarsh Sharma
>
> regards
> Tomas
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adarsh.sharma@orkash.com, tv@fuzzy.cz
Subject: Re: Postgres Performance Tuning
In-Reply-To: <4D999FD8.9070909@orkash.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox