Обсуждение: Fwd: Regarding change in the size of database

On Wed, Oct 26, 2016 at 10:31 AM, harish Reddy <harishr536@gmail.com> wrote:

---------- Forwarded message ----------
From: harish Reddy <harishr536@gmail.com>

Subject: Regarding change in the size of database

Hi Sir,

I had my PostgreSQL DB in Procduction environment was about 45GB and when I taken it's dump and restored into my local environment it was about 20GB . I want to know what is going wrong and when i had observed it is differing to a single table also  so could you suggest us what is the issue and possible solution and in a day it has incresed over 1 GB again
suggest me where can i get the solution

Thanks and Regards
Harish Reddy

Probably there was table bloat in your production environment. When you restored, the  bloat would have disappeared. 20 GB is the actual size. Read details about bloat here

http://dba.stackexchange.com/questions/126258/what-is-table-bloating-in-databases

Proper vacuum is the solution.

Regards,

Jayadevan

On Wed, Oct 26, 2016 at 9:51 PM, harish Reddy <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

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

 

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> wrote:

On Wed, Oct 26, 2016 at 9:51 PM, harish Reddy <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

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

 

Hi Michael sir,

Firstly Thank you so much for your explanation, Sir my request is that could you suggest us the best postgres config parameters to set for my instance which will work around 60 concurrent users and my above problem as solved by upgrading my postgres from 9.1 to 9.3 and restored our backup so my db size had its original now.
So sir as am one of a new developer could u suggest what would be the best parameters to set and how to tune my postgres database performance?
Is there any tool to record my stastics? And any tool for optimizing the queries ?
Thanks and Regards
Harish Reddy

On Oct 28, 2016 5:10 AM, "Michael Moore" <michaeljmoore@gmail.com> wrote:

Read this and I think you will understand what you need to do.  http://blog.lerner.co.il/in-postgresql-as-in-life-dont-wait-too-long-to-commit/

On Thu, Oct 27, 2016 at 4:23 AM, harish Reddy <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> wrote:

On Wed, Oct 26, 2016 at 9:51 PM, harish Reddy <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

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

 

Read this and I think you will understand what you need to do.  http://blog.lerner.co.il/in-postgresql-as-in-life-dont-wait-too-long-to-commit/

On Thu, Oct 27, 2016 at 4:23 AM, harish Reddy <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> wrote:

On Wed, Oct 26, 2016 at 9:51 PM, harish Reddy <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

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

 

On Thu, Oct 27, 2016 at 4:53 PM, harish Reddy <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> wrote:

On Wed, Oct 26, 2016 at 9:51 PM, harish Reddy <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

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

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> wrote:

On Thu, Nov 3, 2016 at 10:06 AM, harish Reddy <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> wrote:

On Thu, Oct 27, 2016 at 4:53 PM, harish Reddy <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> wrote:

On Wed, Oct 26, 2016 at 9:51 PM, harish Reddy <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

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

On Thu, Nov 3, 2016 at 10:06 AM, harish Reddy <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> wrote:

On Thu, Oct 27, 2016 at 4:53 PM, harish Reddy <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> wrote:

On Wed, Oct 26, 2016 at 9:51 PM, harish Reddy <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

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

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> 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> wrote:

On Thu, Nov 3, 2016 at 10:06 AM, harish Reddy <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> wrote:

On Thu, Oct 27, 2016 at 4:53 PM, harish Reddy <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> wrote:

On Wed, Oct 26, 2016 at 9:51 PM, harish Reddy <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

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