Strange problem with autovacuum

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Strange problem with autovacuum

Efraín Déctor

Hello list.

I have a strange problem with one of our servers (PostgreSQl 9.2.8). Every monday the autovacuum workers seems to stop working (the workers decrease in number) this causes that 5 tables that are constantly updated and deleted to be really slow.

I have this settings on postgresql.conf:

maintenance_work_mem = 7936MB
autovacuum = on
#log_autovacuum_min_duration = -1
autovacuum_max_workers = 12
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1

Per table I have this settings:

datosr

  autovacuum_enabled=true,
  autovacuum_vacuum_threshold=50,
  autovacuum_vacuum_scale_factor=0.1,
  autovacuum_vacuum_cost_limit=1000

datose

  autovacuum_enabled=true,
  autovacuum_vacuum_threshold=50,
  autovacuum_vacuum_scale_factor=0.1,
  autovacuum_vacuum_cost_limit=1000

idig_h

  autovacuum_enabled=true,
  autovacuum_vacuum_threshold=1000,
  autovacuum_vacuum_cost_limit=2000

iodig

 autovacuum_enabled=true,
  autovacuum_vacuum_threshold=1000,
  autovacuum_vacuum_cost_limit=2000

ia
  autovacuum_vacuum_threshold=50,
  autovacuum_vacuum_scale_factor=.1,
  autovacuum_vacuum_cost_limit=1000

I bases those settings by reading this blog entry: https://blog.2ndquadrant.com/autovacuum-tuning-basics/

It seems that I am doing something wrong, could you guys please point me in the right direction?


Thank you very much

Reply | Threaded
Open this post in threaded view
|

Re: Strange problem with autovacuum

Stephen Froehlich

What are your checkpoint / wal settings?

 

Are there any log entries at those times about autovacuums being cancelled?  The log will typically give a reason.

 

--Stephen

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Efraín Déctor
Sent: Tuesday, November 14, 2017 8:46 AM
To: [hidden email]
Subject: [NOVICE] Strange problem with autovacuum

 

CableLabs WARNING: The sender of this email could not be validated and may not match the person in the "From" field.

Hello list.

I have a strange problem with one of our servers (PostgreSQl 9.2.8). Every monday the autovacuum workers seems to stop working (the workers decrease in number) this causes that 5 tables that are constantly updated and deleted to be really slow.

I have this settings on postgresql.conf:

maintenance_work_mem = 7936MB
autovacuum = on
#log_autovacuum_min_duration = -1
autovacuum_max_workers = 12
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1

Per table I have this settings:

datosr

  autovacuum_enabled=true,
  autovacuum_vacuum_threshold=50,
  autovacuum_vacuum_scale_factor=0.1,
  autovacuum_vacuum_cost_limit=1000

datose

  autovacuum_enabled=true,
  autovacuum_vacuum_threshold=50,
  autovacuum_vacuum_scale_factor=0.1,
  autovacuum_vacuum_cost_limit=1000

idig_h

  autovacuum_enabled=true,
  autovacuum_vacuum_threshold=1000,
  autovacuum_vacuum_cost_limit=2000

iodig

 autovacuum_enabled=true,
  autovacuum_vacuum_threshold=1000,
  autovacuum_vacuum_cost_limit=2000

ia
  autovacuum_vacuum_threshold=50,
  autovacuum_vacuum_scale_factor=.1,
  autovacuum_vacuum_cost_limit=1000

I bases those settings by reading this blog entry: https://blog.2ndquadrant.com/autovacuum-tuning-basics/

It seems that I am doing something wrong, could you guys please point me in the right direction?

 

Thank you very much

Reply | Threaded
Open this post in threaded view
|

Re: Strange problem with autovacuum

Efraín Déctor

Hello.

Checkpoint settings:

checkpoint_segments = 128              
checkpoint_timeout = 1h 

It doesnt seem that the workers are being cancelled (I do not have any indication of this on the logs).

Thanks


El 14/11/2017 a las 10:15 a. m., Stephen Froehlich escribió:

What are your checkpoint / wal settings?

 

Are there any log entries at those times about autovacuums being cancelled?  The log will typically give a reason.

 

--Stephen

 

From: [hidden email] [[hidden email]] On Behalf Of Efraín Déctor
Sent: Tuesday, November 14, 2017 8:46 AM
To: [hidden email]
Subject: [NOVICE] Strange problem with autovacuum

 

CableLabs WARNING: The sender of this email could not be validated and may not match the person in the "From" field.

Hello list.

I have a strange problem with one of our servers (PostgreSQl 9.2.8). Every monday the autovacuum workers seems to stop working (the workers decrease in number) this causes that 5 tables that are constantly updated and deleted to be really slow.

I have this settings on postgresql.conf:

maintenance_work_mem = 7936MB
autovacuum = on
#log_autovacuum_min_duration = -1
autovacuum_max_workers = 12
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1

Per table I have this settings:

datosr

  autovacuum_enabled=true,
  autovacuum_vacuum_threshold=50,
  autovacuum_vacuum_scale_factor=0.1,
  autovacuum_vacuum_cost_limit=1000

datose

  autovacuum_enabled=true,
  autovacuum_vacuum_threshold=50,
  autovacuum_vacuum_scale_factor=0.1,
  autovacuum_vacuum_cost_limit=1000

idig_h

  autovacuum_enabled=true,
  autovacuum_vacuum_threshold=1000,
  autovacuum_vacuum_cost_limit=2000

iodig

 autovacuum_enabled=true,
  autovacuum_vacuum_threshold=1000,
  autovacuum_vacuum_cost_limit=2000

ia
  autovacuum_vacuum_threshold=50,
  autovacuum_vacuum_scale_factor=.1,
  autovacuum_vacuum_cost_limit=1000

I bases those settings by reading this blog entry: https://blog.2ndquadrant.com/autovacuum-tuning-basics/

It seems that I am doing something wrong, could you guys please point me in the right direction?

 

Thank you very much


Reply | Threaded
Open this post in threaded view
|

Re: Strange problem with autovacuum

David G Johnston
In reply to this post by Efraín Déctor
On Tue, Nov 14, 2017 at 8:45 AM, Efraín Déctor <[hidden email]> wrote:

Hello list.

I have a strange problem with one of our servers (PostgreSQl 9.2.8). Every monday the autovacuum workers seems to stop working (the workers decrease in number) this causes that 5 tables that are constantly updated and deleted to be really slow.


​Best guess is you have a weekly query being run that requires significant locking to execute and is thus preventing vacuum from doing its thing.

pg_stat_activity and pg_locks should be consulted during those times to gain a better feel for everything that is running during those troughs.

Maybe a Sunday night pg_dump?

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Strange problem with autovacuum

Efraín Déctor

Hello.

I have 2 processes that runs on monday. I will stop them and see if it helps.

I understand the while the procesess are running the autovacuum will stop, but shouldnt they resume their work after the procesess are finished?

Thanks


El 14/11/2017 a las 10:49 a. m., David G. Johnston escribió:
On Tue, Nov 14, 2017 at 8:45 AM, Efraín Déctor <[hidden email]> wrote:

Hello list.

I have a strange problem with one of our servers (PostgreSQl 9.2.8). Every monday the autovacuum workers seems to stop working (the workers decrease in number) this causes that 5 tables that are constantly updated and deleted to be really slow.


​Best guess is you have a weekly query being run that requires significant locking to execute and is thus preventing vacuum from doing its thing.

pg_stat_activity and pg_locks should be consulted during those times to gain a better feel for everything that is running during those troughs.

Maybe a Sunday night pg_dump?

David J.


Reply | Threaded
Open this post in threaded view
|

Re: Strange problem with autovacuum

Peter Geoghegan-4
In reply to this post by Efraín Déctor
On Tue, Nov 14, 2017 at 7:45 AM, Efraín Déctor
<[hidden email]> wrote:
> I have a strange problem with one of our servers (PostgreSQl 9.2.8).

9.2 just went out of support, and 9.2.8 is a very old point release.
You may be impacted by bugs that were fixed long ago.

--
Peter Geoghegan


--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

Re: Strange problem with autovacuum

Efraín Déctor
I will try to upgrade and see if helps.

Thank you


El 14/11/2017 a las 12:10 p. m., Peter Geoghegan escribió:
> On Tue, Nov 14, 2017 at 7:45 AM, Efraín Déctor
> <[hidden email]> wrote:
>> I have a strange problem with one of our servers (PostgreSQl 9.2.8).
> 9.2 just went out of support, and 9.2.8 is a very old point release.
> You may be impacted by bugs that were fixed long ago.
>



--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Previous Thread Next Thread