Re: Postgres Performance Tuning

tv(at)fuzzy(dot)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(at)s8-mysd-2 ~]# cat /proc/sys/kernel/shmmax

6442450944

[root(at)s8-mysd-2 ~]# cat /proc/sys/kernel/shmall

6442450944

[root(at)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
>
>