Thread: Autovacuum daemon internal handling
Hi, Our production server is running PostgreSQL v8.2.3 on Red Hat Enterprise Linux Server release 5 (Tikanga). I need a clarification on how autovacuum daemon internally works/handles in the following specific use case/situation: 1. Does autovacuum daemon works with one table at a time or does it work with multiple tables at the same time? Reason to ask this question is, let's say I've "autovacuum_naptime" set to 1 minute, and there are 500 tables in one database. Assuming there are 10 tables that has to be vacuumed and analyzed based on the threshold defined in one of the autovacuum daemon execution time, and if takes more than 1 minute to complete vacuum and analyze operation on even a single table (out of 10 tables), how does autovacuum daemon works/handles in this case? 2. The columns "last_autovaccum" and 'last_autoanalyze" in pg_stat_user_tables shows the start time or end time of the operation? Regards, Gnanam
Excerpts from Gnanakumar's message of mié ago 11 01:07:08 -0400 2010: Hi, > 1. Does autovacuum daemon works with one table at a time or does it work > with multiple tables at the same time? Only one. > Reason to ask this question is, let's say I've "autovacuum_naptime" set to 1 > minute, and there are 500 tables in one database. Assuming there are 10 > tables that has to be vacuumed and analyzed based on the threshold defined > in one of the autovacuum daemon execution time, and if takes more than 1 > minute to complete vacuum and analyze operation on even a single table (out > of 10 tables), how does autovacuum daemon works/handles in this case? in 8.2 "naptime" means "time to sleep after we finish a job". So even if the previous task takes an hour, it will still sleep a minute before doing another round. (Note that this setting has a different meaning in later releases). If autovacuum cannot keep up with all the vacuumable tables, you're in trouble and should probably schedule vacuum externally. (This also changed in later releases). > 2. The columns "last_autovaccum" and 'last_autoanalyze" in > pg_stat_user_tables shows the start time or end time of the operation? End time. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
"Gnanakumar" <gnanam@zoniac.com> wrote: > Our production server is running PostgreSQL v8.2.3 There are a lot of bug fixes and security fixes you're missing by not using a recent minor release: http://www.postgresql.org/support/versioning http://www.postgresql.org/docs/8.2/static/release.html -Kevin
> in 8.2 "naptime" means "time to sleep after we finish a job". So even > if the previous task takes an hour, it will still sleep a minute before > doing another round. (Note that this setting has a different meaning in > later releases). I couldn’t understand the difference in meaning of "autovacuum_naptime" between 8.2 and later releases from the documentation. May be I'm not understanding/seeing the subtle difference in the documentation lines mentioned. I referredthese 3 doc links: http://www.postgresql.org/docs/8.2/interactive/runtime-config-autovacuum.html http://www.postgresql.org/docs/8.3/interactive/runtime-config-autovacuum.html http://www.postgresql.org/docs/8.4/interactive/runtime-config-autovacuum.html 8.2 doc says "Specifies the delay between activity rounds for the autovacuum daemon...." 8.3 & 8.4 doc says "Specifies the minimum delay between autovacuum runs on any given database....." Can you please make me clear on this? > If autovacuum cannot keep up with all the vacuumable tables, you're in > trouble and should probably schedule vacuum externally. (This also > changed in later releases). Can you please point me to relevant documentation links on this change? >> 2. The columns "last_autovaccum" and 'last_autoanalyze" in >> pg_stat_user_tables shows the start time or end time of the operation? > End time. Is there any way to find out the start time of "last_autovaccum" and/or 'last_autoanalyze" for a given table in 8.2.3? Sothat I can isolate the tables that are taking too long time to complete vacuum and/or analyze and I can perform them externallyif need.
Excerpts from Gnanakumar's message of jue ago 12 00:56:34 -0400 2010: > > in 8.2 "naptime" means "time to sleep after we finish a job". So even > > if the previous task takes an hour, it will still sleep a minute before > > doing another round. (Note that this setting has a different meaning in > > later releases). > > I couldn’t understand the difference in meaning of "autovacuum_naptime" between 8.2 and later releases from the documentation. May be I'm not understanding/seeing the subtle difference in the documentation lines mentioned. I referredthese 3 doc links: > http://www.postgresql.org/docs/8.2/interactive/runtime-config-autovacuum.html > http://www.postgresql.org/docs/8.3/interactive/runtime-config-autovacuum.html > http://www.postgresql.org/docs/8.4/interactive/runtime-config-autovacuum.html > > 8.2 doc says "Specifies the delay between activity rounds for the autovacuum daemon...." > 8.3 & 8.4 doc says "Specifies the minimum delay between autovacuum runs on any given database....." > > Can you please make me clear on this? The main difference is that 8.2 has only one process working at a time, whereas in 8.3 and later there can be several. When there's only one process, the only way for it to process several databases is sequentially; the naptime is how long to sleep between each item in the list. In 8.3, naptime is the time that lapses between two processes starting, while respecting the limit of maximum processes that you configured in max_workers. So if the time is already up but all processes are busy elsewhere, the next autovacuum will have to wait until one of those finishes. > > If autovacuum cannot keep up with all the vacuumable tables, you're in > > trouble and should probably schedule vacuum externally. (This also > > changed in later releases). > > Can you please point me to relevant documentation links on this change? Since there are multiple processes, you can configure more so that all tables can be vacuumed in time. > >> 2. The columns "last_autovaccum" and 'last_autoanalyze" in > >> pg_stat_user_tables shows the start time or end time of the operation? > > > End time. > Is there any way to find out the start time of "last_autovaccum" and/or 'last_autoanalyze" for a given table in 8.2.3? So that I can isolate the tables that are taking too long time to complete vacuum and/or analyze and I can performthem externally if need. No, I don't think there is in 8.2, unless you crank the debug level down to DEBUG2 which means log a lot of stuff (probably too noisy to be useful). -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Thanks for the update. > The main difference is that 8.2 has only one process working at a time, > whereas in 8.3 and later there can be several. When there's only one > process, the only way for it to process several databases is > sequentially; the naptime is how long to sleep between each item in the > list. > In 8.3, naptime is the time that lapses between two processes starting, > while respecting the limit of maximum processes that you configured in > max_workers. So if the time is already up but all processes are busy > elsewhere, the next autovacuum will have to wait until one of those > finishes. > Since there are multiple processes, you can configure more so that all > tables can be vacuumed in time. >> Is there any way to find out the start time of "last_autovaccum" and/or 'last_autoanalyze" for a given table in 8.2.3? So that I can isolate the tables that are taking too long time to complete vacuum and/or analyze and I can performthem externally if need. > No, I don't think there is in 8.2, unless you crank the debug level down > to DEBUG2 which means log a lot of stuff (probably too noisy to be > useful).