Re: Postgres Performance Tuning

[Adarsh Sharma <adarsh.sharma@xxxxxxxxxx>]

tv@xxxxxxxx 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