Re: Fwd: Regarding change in the size of database

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 <amitabhkant(at)gmail(dot)com
> <mailto:amitabhkant(at)gmail(dot)com>> 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 <harishr536(at)gmail(dot)com
> <mailto:harishr536(at)gmail(dot)com>> 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
> <amitabhkant(at)gmail(dot)com <mailto:amitabhkant(at)gmail(dot)com>> wrote:
>
>
>
> On Thu, Nov 3, 2016 at 10:06 AM, harish Reddy
> <harishr536(at)gmail(dot)com <mailto:harishr536(at)gmail(dot)com>> 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"
> <amitabhkant(at)gmail(dot)com <mailto:amitabhkant(at)gmail(dot)com>>
> wrote:
>
>
>
> On Thu, Oct 27, 2016 at 4:53 PM, harish Reddy
> <harishr536(at)gmail(dot)com <mailto:harishr536(at)gmail(dot)com>>
> 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
> <maymala(dot)jayadevan(at)gmail(dot)com
> <mailto:maymala(dot)jayadevan(at)gmail(dot)com>> wrote:
>
>
> On Wed, Oct 26, 2016 at 9:51 PM, harish
> Reddy <harishr536(at)gmail(dot)com
> <mailto:harishr536(at)gmail(dot)com>> 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
> <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
> <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
> <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
> <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
> <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
> <https://wiki.postgresql.org/wiki/Guide_to_reporting_problems>
>
> For pgbouncer, see this https://pgbouncer.github.io
> <https://pgbouncer.github.io>
>
> Amitabh
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com