Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 8C6841337BE9 for ; Mon, 4 Apr 2011 07:39:42 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 41564-06 for ; Mon, 4 Apr 2011 10:39:24 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp180.iad.emailsrvr.com (smtp180.iad.emailsrvr.com [207.97.245.180]) by mail.postgresql.org (Postfix) with ESMTP id 799031337A5D for ; Mon, 4 Apr 2011 07:39:24 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp38.relay.iad1a.emailsrvr.com (SMTP Server) with ESMTP id 96A673484F1; Mon, 4 Apr 2011 06:39:24 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp38.relay.iad1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id 8DD833481CC; Mon, 4 Apr 2011 06:39:23 -0400 (EDT) Message-ID: <4D999FD8.9070909@orkash.com> Date: Mon, 04 Apr 2011 16:09:20 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: tv@fuzzy.cz CC: pgsql-performance@postgresql.org Subject: Re: Postgres Performance Tuning References: <4D999211.9080507@orkash.com> <8b57d989ff79004f9159326f008abefb.squirrel@sq.gransy.com> In-Reply-To: <8b57d989ff79004f9159326f008abefb.squirrel@sq.gransy.com> Content-Type: multipart/alternative; boundary="------------060202090800020201060101" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201104/9 X-Sequence-Number: 43071 This is a multi-part message in MIME format. --------------060202090800020201060101 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 > > --------------060202090800020201060101 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit 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



--------------060202090800020201060101--