Re: Fwd: Regarding change in the size of database - Mailing list pgsql-sql

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@gmail.com
> <mailto:amitabhkant@gmail.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@gmail.com
>     <mailto:harishr536@gmail.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@gmail.com <mailto:amitabhkant@gmail.com>> wrote:
>
>
>
>             On Thu, Nov 3, 2016 at 10:06 AM, harish Reddy
>             <harishr536@gmail.com <mailto:harishr536@gmail.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@gmail.com <mailto:amitabhkant@gmail.com>>
>                 wrote:
>
>
>
>                     On Thu, Oct 27, 2016 at 4:53 PM, harish Reddy
>                     <harishr536@gmail.com <mailto:harishr536@gmail.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.jayadevan@gmail.com
>                         <mailto:maymala.jayadevan@gmail.com>> wrote:
>
>
>                             On Wed, Oct 26, 2016 at 9:51 PM, harish
>                             Reddy <harishr536@gmail.com
>                             <mailto:harishr536@gmail.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.klaver@aklaver.com