Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cC8yj-0002WC-Nr for pgsql-sql@arkaria.postgresql.org; Wed, 30 Nov 2016 17:47:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cC8yj-000812-AZ for pgsql-sql@arkaria.postgresql.org; Wed, 30 Nov 2016 17:47:33 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cC8yi-00080v-Gc for pgsql-sql@postgresql.org; Wed, 30 Nov 2016 17:47:32 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cC8yf-0007U3-8L for pgsql-sql@postgresql.org; Wed, 30 Nov 2016 17:47:31 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id 7A233207B5; Wed, 30 Nov 2016 12:47:28 -0500 (EST) Received: from frontend2 ([10.202.2.161]) by compute6.internal (MEProxy); Wed, 30 Nov 2016 12:47:28 -0500 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=aklaver.com; h=cc :content-transfer-encoding:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-sender :x-me-sender:x-sasl-enc:x-sasl-enc; s=mesmtp; bh=s60XispeMcjL6Vn bptZ6l8ycWMk=; b=kW07y4T+eJOuX03H1DEKuyrsZg/zVyAuNFvTyUuqYPMssQE G+eBCBtbvE7wyoGQ6xvkfSsVXKOurgpXtJukG+nY6DSGKdKwk+bugVGICx6WY9l7 QZLCbD+zPw9cvA12vfrXID27TNEp3vh0BPalvD4F9M+uuJUAkrwEk0RK7Yz0= DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-sender:x-me-sender:x-sasl-enc:x-sasl-enc; s= smtpout; bh=s60XispeMcjL6VnbptZ6l8ycWMk=; b=HxjW/p+2fLFd52AP9xHs Q4c3yEhXm+oUj0/tuv+7ev3iFcYO15FvrSRsalvq2ZMVonKZW66GcWfCCwOlSulE mvnhsw7nGwdcHJ9J4pzFFYT3fbV2oTz2zZI1P4izEDwn2PMjQ3n3NNHIIC5Th34U guVBjZ/W3C+ocZYRSeHYxOg= X-ME-Sender: X-Sasl-enc: TSOJE97Xe92dL7zJZNf+tYYcMWbyXio9MQcpwLOSQjdM 1480528048 Received: from killi.site (173-160-167-74-washington.hfc.comcastbusiness.net [173.160.167.74]) by mail.messagingengine.com (Postfix) with ESMTPA id C71112415E; Wed, 30 Nov 2016 12:47:27 -0500 (EST) Subject: Re: Fwd: Regarding change in the size of database To: harish Reddy , Amitabh Kant References: Cc: Jayadevan M , "pgsql-sql@postgresql.org" From: Adrian Klaver Message-ID: <883fefef-6248-a13c-87a5-88ce21dac185@aklaver.com> Date: Wed, 30 Nov 2016 09:47:26 -0800 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.5.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org On 11/30/2016 09:28 AM, harish Reddy wrote: > I had a doubt regarding this dead tuples does this effect my server > performance? I have checked at parameter level that auto vacuum is > turned on. and does auto vacuum cause loss of data? Not for live data. It makes the space occupied by dead rows available for use by live rows. For a full explanation see here: https://www.postgresql.org/docs/9.6/static/routine-vacuuming.html > > On Fri, Nov 11, 2016 at 11:04 AM, Amitabh Kant > wrote: > > Rather than looking at connections, you should be looking at the > average number of active queries you have in your db. That should > give you a fair idea about the number of connections required. > > As for number of connections supported, you will have to give more > details on the specs of underlying hardware, and if its a dedicated > db server or sites alongside other services. > > > > Amitabh > > On Fri, Nov 11, 2016 at 10:49 AM, harish Reddy > wrote: > > Thank you I am analyzing my query statics. So i want to know how > many connections that postgres database may support and any way > to archive my database. > > On Fri, Nov 4, 2016 at 10:03 AM, Amitabh Kant > > wrote: > > > > On Thu, Nov 3, 2016 at 10:06 AM, harish Reddy > > wrote: > > Hi amitabhkhant sir > Thank you so much for your answer , > I have upgraded my postgres to 9.3 and we are lagging > lot with performance and could you suggest me the best > possible parameters to active connections of 200 and > could you suggest how to install pgbouncer in postgres > 9.3 and setting up it > > Thanks and Regards > Harish Reddy > > > On Nov 3, 2016 9:20 AM, "Amitabh Kant" > > > wrote: > > > > On Thu, Oct 27, 2016 at 4:53 PM, harish Reddy > > > wrote: > > > Hi Sir, > > Thank you for you feedback my postgres is > running on 9.1 version and when i checked > that *autovacuum *in* * my production by > command*ps -axww | grep autovacuum *it says the > output as it has some process running with this > id so how to solve my problem but in postgress > config file it was commented. > > My application is an online ERP which is > supported by *openbravo* has an users of about > *150(arount 50 active users)* with it and could > you suggest me the perfect variables to set us > in postgres config file. > > The system has a RAM of 16 GB and the following > variables > > Variable Setting value > max_connections 200 > shared_buffers 4096MB > work_mem 24MB > maintenance_work_mem 512MB > effective_cache_size 4096MB > > > > > On Thu, Oct 27, 2016 at 9:04 AM, Jayadevan M > > wrote: > > > On Wed, Oct 26, 2016 at 9:51 PM, harish > Reddy > wrote: > > Hi Jayadevan, > > Firstly Thank you so much for your > valuable information provided, So what > should i do for increasing my database > performance? and could you suggest me > how to continue to the vacuum process > and will it decrease my database > performance? > > > Please read this article > https://wiki.postgresql.org/wiki/Guide_to_reporting_problems > > i.e - "Mention your database version", "A > description of what you are trying to > achieve and what results you expect" etc etc. > And this. > https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server > > > Do you have autovacuum working? > https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html > > > > > > Try installing pgbouncer for connection pooling if > you need 200 active connections. You can check for > active connections using answers on this > page: http://serverfault.com/questions/128284/how-to-see-active-connections-and-current-activity-in-postgresql-8-4 > > > Another suggestion that might come your way is to > upgrade your postgres version as 9.1 has recently > been made EOL. > > "explain analyze" can be used to debug slow queries. > See this page for more > info: https://www.postgresql.org/docs/9.1/static/sql-explain.html > > > If you need further help, you will have to be more > specific on what performance problems you are > facing, with their explain anaylze output for folks > here to help you out. > > Amitabh > > > There are no "best possible parameters" without knowing what > is the nature of problem. More specifically, which queries > are getting slow. Run your queries with "explain analyze > verbsose" on queries which are getting slow, and then post > back here to get better answers. > > You will also have to give more info about your OS etc for > folks here to help you out. This was suggested to you > earlier: https://wiki.postgresql.org/wiki/Guide_to_reporting_problems > > > For pgbouncer, see this https://pgbouncer.github.io > > > Amitabh > > > > -- Adrian Klaver adrian.klaver@aklaver.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql