Обсуждение: [GENERAL] Why autvacuum is not started?
I have running Postgresql 9.4 and... if i have a table with following configuration:
autovacuum_vacuum_scale_factor=0.0,
autovacuum_analyze_scale_factor=0.0,
autovacuum_vacuum_threshold=1000,
autovacuum_analyze_threshold=1000,
autovacuum_enabled=true
Why autovacuum is not started if the table has more than 1000 inserts???
![](https://docs.google.com/a/sw-argos.com/uc?id=0B2szNsINEd6KQWI3VlQzUGd0Y0diR0stRHNELWIzbmFLOFk4&export=download)
first i have setted autovacuum_vacuum_threshold to 10,000 inserts (it is normal each 2 hours ), then to 5,000, 3,000 and finally to 1,000 but autovacuum is not triggered but autoanalyze.
"select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables where relname like 'sta%';"
last_vacuum=>2017-01-05 10:40:34.228633-06
last_autovacuum => null
last_analyze=> 2017-01-04 15:02:47.438715-06
last_autoanalyze=> 2017-01-09 10:35:51.391114-06
--
On 01/09/2017 08:45 AM, Edmundo Robles wrote: > I have running Postgresql 9.4 and... if i have a table with > following configuration: > autovacuum_vacuum_scale_factor=0.0, > autovacuum_analyze_scale_factor=0.0, > autovacuum_vacuum_threshold=1000, > autovacuum_analyze_threshold=1000, > autovacuum_enabled=true > > Why autovacuum is not started if the table has more than 1000 inserts??? https://www.postgresql.org/docs/9.4/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-THRESHOLD "autovacuum_vacuum_threshold (integer) Specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters. " INSERTs don't count. They do for analyze though: "autovacuum_analyze_threshold (integer) Specifies the minimum number of inserted, updated or deleted tuples needed to trigger an ANALYZE in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters. " > > first i have setted autovacuum_vacuum_threshold to 10,000 inserts > (it is normal each 2 hours ), then to 5,000, 3,000 and finally to > 1,000 but autovacuum is not triggered but autoanalyze. > > "select relname,last_vacuum, last_autovacuum, last_analyze, > last_autoanalyze from pg_stat_user_tables where relname like 'sta%';" > > last_vacuum=>2017-01-05 10:40:34.228633-06 > last_autovacuum => null > last_analyze=> 2017-01-04 15:02:47.438715-06 > last_autoanalyze=> 2017-01-09 10:35:51.391114-06 > > -- > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Jan 9, 2017 at 8:45 AM, Edmundo Robles <edmundo@sw-argos.com> wrote:
I have running Postgresql 9.4 and... if i have a table with following configuration:autovacuum_vacuum_scale_factor=0.0, autovacuum_analyze_scale_factor=0.0, autovacuum_vacuum_threshold=1000, autovacuum_analyze_threshold=1000, autovacuum_enabled=trueWhy autovacuum is not started if the table has more than 1000 inserts???
Inserts do not generate obsolete tuples, and so are not counted against the "vacuum threshold" as described here:
But inserts can change the data distributions, so do count against "analyze threshold".
Due to index-only-scans and freeze maps, there are reasons to revisit this topic, so that insert only tables do get vacuumed and not just analyzed. But that re-think has yet to be finished, and certainly won't be back-ported to 9.4.
Cheers,
Jeff