Postgres Pro
Downloads
Home   >   mailing lists

Thread: Keep needing to run manual analyze

Keep needing to run manual analyze

From
Rob Northcott
Date:

We have an application that uses a Postgres database (currently 9.6).  All the autovacuum/analyze settings are just left at the defaults.

We’ve had a few instances recently where users have complained of slow performance and running a manual Analyze (not vacuum) on their database has resolved it.

Should it be considered normal that this needs to be done manually periodically even when autovacuum is on?

It’s not a very heavy application by a lot of people’s standards (it’s just a point-of-sale/stock-control system) but there are a couple of hundred databases on the server, all in fairly constant use through the day.

Is it possible that the autovacuum/analyze isn’t running for some reason?  If so, is there anything I should look at to make it work better or should I just be looking at scheduling a manual vacuum/analyze periodically on all databases?

 

I’ve had a search around but there seem to be lots of differing opinions on what needs to be done manually and what should be handled by the autovacuum.

 

Any hints most welcome…

 

Med vänlig hälsning / Best Regards

 

Rob Northcott

Software Developer (UK Office, formerly TEAM Systems)

 

Phone   +44 1752 712052

 

Compilator AB

Södergatan 22

SE-211 34 Malmö

Sweden

www.compilator.com

 

Asset 2@2x

 

THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL AND IS THUS FOR USE ONLY BY THE INTENDED RECIPIENT. IF YOU RECEIVED THIS IN ERROR, PLEASE CONTACT THE SENDER AND DELETE THE E-MAIL AND ITS ATTACHMENTS FROM ALL COMPUTERS.

 

Attachment

Re: Keep needing to run manual analyze

From
Michael Lewis
Date:
On Wed, Jan 6, 2021 at 10:29 AM Rob Northcott <Rob.Northcott@compilator.com> wrote:

We have an application that uses a Postgres database (currently 9.6).  All the autovacuum/analyze settings are just left at the defaults.

We’ve had a few instances recently where users have complained of slow performance and running a manual Analyze (not vacuum) on their database has resolved it.

Should it be considered normal that this needs to be done manually periodically even when autovacuum is on?

It’s not a very heavy application by a lot of people’s standards (it’s just a point-of-sale/stock-control system) but there are a couple of hundred databases on the server, all in fairly constant use through the day.


You may need more workers, and a higher cost limit before work is paused for cost_delay. Depending how many tables per database in the cluster, more workers would likely be ideal, or *maybe* a smaller naptime if there are tons of tables overall and all of them are relatively small/see little changes.

It really depends on your workload and *why* the tables aren't getting analyzed as frequently as you need. If your cost limit/delay mean that the auto vacuum/analyze is rather throttled (and default settings would be that situation given today's I/O throughput on any decent production machine), and you have some large tables with many large indexes are constantly in need of vacuuming and you don't have sufficient maintenance work memory configured to avoid re-scanning the indexes repeatedly to get the work done... you may never be getting around to the other tables. If you have a table that is (nearly) all inserts, then a periodic vacuum/analyze done manually is prudent before PG13.

Are you logging all auto vaccums/analyzes and able to run a pg badger or similar analysis on it? It would be helpful to see some stats on what is going on currently.

Re: Keep needing to run manual analyze

From
Laurenz Albe
Date:
On Wed, 2021-01-06 at 17:28 +0000, Rob Northcott wrote:
> We have an application that uses a Postgres database (currently 9.6).  All the autovacuum/analyze settings are just
leftat the defaults.
 
> We’ve had a few instances recently where users have complained of slow performance and running a manual Analyze
>  (not vacuum) on their database has resolved it.
> Should it be considered normal that this needs to be done manually periodically even when autovacuum is on?
> It’s not a very heavy application by a lot of people’s standards (it’s just a point-of-sale/stock-control system)
>  but there are a couple of hundred databases on the server, all in fairly constant use through the day.
> Is it possible that the autovacuum/analyze isn’t running for some reason?  If so, is there anything I
>  should look at to make it work better or should I just be looking at scheduling a manual vacuum/analyze
>  periodically on all databases?

That is not necessary.

You should figure out what tables need more frequent ANALYZE runs to get good execution plans
and tune autoanalyze for these tables, for example by reducing the scale factor for them.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

RE: Keep needing to run manual analyze

From
Rob Northcott
Date:

From: Michael Lewis <mlewis@entrata.com>
Sent: 06 January 2021 18:11
To: Rob Northcott <Rob.Northcott@compilator.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Keep needing to run manual analyze

 

On Wed, Jan 6, 2021 at 10:29 AM Rob Northcott <Rob.Northcott@compilator.com> wrote:

We have an application that uses a Postgres database (currently 9.6).  All the autovacuum/analyze settings are just left at the defaults.

We’ve had a few instances recently where users have complained of slow performance and running a manual Analyze (not vacuum) on their database has resolved it.

Should it be considered normal that this needs to be done manually periodically even when autovacuum is on?

It’s not a very heavy application by a lot of people’s standards (it’s just a point-of-sale/stock-control system) but there are a couple of hundred databases on the server, all in fairly constant use through the day.

 

>You may need more workers, and a higher cost limit before work is paused for cost_delay. Depending how many tables per database >in the cluster, more workers would likely be ideal, or *maybe* a smaller naptime if there are tons of tables overall and all of them >are relatively small/see little changes.

 

>It really depends on your workload and *why* the tables aren't getting analyzed as frequently as you need. If your cost limit/delay >mean that the auto vacuum/analyze is rather throttled (and default settings would be that situation given today's I/O throughput on >any decent production machine), and you have some large tables with many large indexes are constantly in need of vacuuming and >you don't have sufficient maintenance work memory configured to avoid re-scanning the indexes repeatedly to get the work done... >you may never be getting around to the other tables. If you have a table that is (nearly) all inserts, then a periodic >vacuum/analyze done manually is prudent before PG13.

 

>Are you logging all auto vaccums/analyzes and able to run a pg badger or similar analysis on it? It would be helpful to see some stats >on what is going on currently.

 

Thanks for the tips.  I don’t think it’s being logged unfortunately (but we could always turn it on if we need more info), but what you’ve said at least confirms that manual analyze shouldn’t be necessary.  I’ll have to go and read up on autovacuum settings (it’s not something I’ve really looked into in detail before and just left the default settings, which look like they’re not doing what we need).

RE: Keep needing to run manual analyze

From
Rob Northcott
Date:

-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at> 
Sent: 07 January 2021 02:06
To: Rob Northcott <Rob.Northcott@compilator.com>; pgsql-general@postgresql.org
Subject: Re: Keep needing to run manual analyze

On Wed, 2021-01-06 at 17:28 +0000, Rob Northcott wrote:
>> We have an application that uses a Postgres database (currently 9.6).  All the autovacuum/analyze settings are just
leftat the defaults.
 
>> We’ve had a few instances recently where users have complained of slow 
>> performance and running a manual Analyze  (not vacuum) on their database has resolved it.
>> Should it be considered normal that this needs to be done manually periodically even when autovacuum is on?
>> It’s not a very heavy application by a lot of people’s standards (it’s 
>> just a point-of-sale/stock-control system)  but there are a couple of hundred databases on the server, all in fairly
constantuse through the day.
 
>> Is it possible that the autovacuum/analyze isn’t running for some 
>> reason?  If so, is there anything I  should look at to make it work 
>> better or should I just be looking at scheduling a manual vacuum/analyze  periodically on all databases?

>That is not necessary.

>You should figure out what tables need more frequent ANALYZE runs to get good execution plans and tune autoanalyze for
thesetables, >for example by reducing the scale factor for them.
 

Thanks - that confirms what the previous poster said, that we should be able to set up autovacuum to do what we need.
I'llgo away and read up on it.  Thanks for the tips.