Thread: autovacuum blues
Hi, I just can't understand why autovacuum is not working. I have a test db/table which I insert values into (by the thousands) and can't work out why my stats don't get updated. Could someone have a quick look at my attached .conf and tell me what I am doing? I am running it on FC5 8.1.4.fc5.1. Cheers Antoine
Attachment
Anton Melser wrote: > Hi, > I just can't understand why autovacuum is not working. I have a test > db/table which I insert values into (by the thousands) and can't work > out why my stats don't get updated. Could someone have a quick look at > my attached .conf and tell me what I am doing? > I am running it on FC5 8.1.4.fc5.1. 1. Check "ps auxw | grep postgres" to see if it's running. 2. If so, check your PG logs and see if you see any activity. Turn connection and statement logging on and see what appears. Oh, and you might want to upgrade to 8.1.5 when convenient - that shouldn't affect this though. -- Richard Huxton Archonet Ltd
On 09/11/06, Richard Huxton <dev@archonet.com> wrote: > Anton Melser wrote: > > Hi, > > I just can't understand why autovacuum is not working. I have a test > > db/table which I insert values into (by the thousands) and can't work > > out why my stats don't get updated. Could someone have a quick look at > > my attached .conf and tell me what I am doing? > > I am running it on FC5 8.1.4.fc5.1. > > 1. Check "ps auxw | grep postgres" to see if it's running. > 2. If so, check your PG logs and see if you see any activity. Turn > connection and statement logging on and see what appears. > ... postgres 1300 0.0 1.1 20180 3048 ? S 12:03 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 1302 0.0 0.2 9968 544 ? S 12:03 0:00 postgres: logger process postgres 1304 0.0 0.4 20316 1188 ? S 12:03 0:00 postgres: writer process postgres 1305 0.0 0.6 10968 1544 ? S 12:03 0:00 postgres: stats buffer process postgres 1306 0.0 0.3 10200 796 ? S 12:03 0:00 postgres: stats collector process ... Any chance you could give me some pointers on activating logging? My thoughts were to log log_planner_stats = on log_min_messages = info Anything else? Cheers Antoine
Hi Anton! I'm not sure how this is with 8.1 but on 7.4.14 we have to enable row level statistics collection for autovacuum: stats_row_level = true Greetings, Matthias > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Anton Melser > Sent: Thursday, November 09, 2006 1:12 PM > To: Richard Huxton > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] autovacuum blues > postgres 1300 0.0 1.1 20180 3048 ? S 12:03 0:00 > /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data > postgres 1302 0.0 0.2 9968 544 ? S 12:03 0:00 > postgres: logger process > postgres 1304 0.0 0.4 20316 1188 ? S 12:03 0:00 > postgres: writer process > postgres 1305 0.0 0.6 10968 1544 ? S 12:03 0:00 > postgres: stats buffer process > postgres 1306 0.0 0.3 10200 796 ? S 12:03 0:00 > postgres: stats collector process > ... > Any chance you could give me some pointers on activating logging? My > thoughts were to log > log_planner_stats = on > log_min_messages = info >
Anton Melser wrote: > Hi, > I just can't understand why autovacuum is not working. I have a test > db/table which I insert values into (by the thousands) and can't work > out why my stats don't get updated. Could someone have a quick look at > my attached .conf and tell me what I am doing? > I am running it on FC5 8.1.4.fc5.1. You can tell whether autovacuum is running by using SHOW autovacuum; Note that if you have a firewall of some sort stopping UDP messages from being transmitted inside your machine, the stats collector may not start, which may stop the autovacuum daemon from starting. If you're not seeing stat updates then there's probably something like that going on. Maybe the system has emitted a warning message at server start; check the logs. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On 09/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Anton Melser wrote: > > Hi, > > I just can't understand why autovacuum is not working. I have a test > > db/table which I insert values into (by the thousands) and can't work > > out why my stats don't get updated. Could someone have a quick look at > > my attached .conf and tell me what I am doing? > > I am running it on FC5 8.1.4.fc5.1. > > You can tell whether autovacuum is running by using > > SHOW autovacuum; > > > Note that if you have a firewall of some sort stopping UDP messages from > being transmitted inside your machine, the stats collector may not > start, which may stop the autovacuum daemon from starting. If you're > not seeing stat updates then there's probably something like that going > on. Maybe the system has emitted a warning message at server start; > check the logs. Thanks for that. Just a clarification, can someone tell me what the "number of tuples" means in the context of the multipliers? I mean, when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I get the min val, but what are we multiplying by 0.4? The total number of tuples in the table? The total modified/deleted? I guess it is just that I assumed that it would kick in regularly, and it doesn't seem to. Probably it is not needed but I always got the feeling that after a good vacuum performance seemed better. Cheers Antoine
On Thu, 2006-11-09 at 18:16 +0100, Anton Melser wrote: > On 09/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote: > > Anton Melser wrote: > > > Hi, > > > I just can't understand why autovacuum is not working. I have a test > > > db/table which I insert values into (by the thousands) and can't work > > > out why my stats don't get updated. Could someone have a quick look at > > > my attached .conf and tell me what I am doing? > > > I am running it on FC5 8.1.4.fc5.1. > > > > You can tell whether autovacuum is running by using > > > > SHOW autovacuum; > > > > > > Note that if you have a firewall of some sort stopping UDP messages from > > being transmitted inside your machine, the stats collector may not > > start, which may stop the autovacuum daemon from starting. If you're > > not seeing stat updates then there's probably something like that going > > on. Maybe the system has emitted a warning message at server start; > > check the logs. > > Thanks for that. Just a clarification, can someone tell me what the > "number of tuples" means in the context of the multipliers? I mean, > when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I > get the min val, but what are we multiplying by 0.4? The total number > of tuples in the table? The total modified/deleted? http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum.html I read that to mean that it multiplies the total number of tuples in the table by autovacuum_vacuum_scale_factor and adds the autovacuum_vacuum_threshold to that number. If the number of updated/deleted tuples exceeds that sum, autovacuum will issue a vacuum. The docs could maybe be more clear on this. Regards, Jeff Davis
Anton Melser wrote: > Thanks for that. Just a clarification, can someone tell me what the > "number of tuples" means in the context of the multipliers? I mean, > when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I > get the min val, but what are we multiplying by 0.4? The total number > of tuples in the table? The total modified/deleted? pg_class.reltuples -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On 09/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Anton Melser wrote: > > > Thanks for that. Just a clarification, can someone tell me what the > > "number of tuples" means in the context of the multipliers? I mean, > > when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I > > get the min val, but what are we multiplying by 0.4? The total number > > of tuples in the table? The total modified/deleted? > > pg_class.reltuples And so... For efficiency reasons, reltuples and relpages are not updated on-the-fly, and so they usually contain somewhat out-of-date values. They are updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX. A stand-alone ANALYZE, that is one not part of VACUUM, generates an approximate reltuples value since it does not read every row of the table. The planner will scale the values it finds in pg_class to match the current physical table size, thus obtaining a closer approximation. So I am still a little unclear... I need to analyse to get relevant stats for autovacuum... but autovacuum needs to be used to update the relevant stats? Can I REALLY do without a cronjob, or am I just thinking wishfully (I can invent expressions if I want!). Cheers Antoine
Anton Melser wrote: > On 09/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote: > >Anton Melser wrote: > > > >> Thanks for that. Just a clarification, can someone tell me what the > >> "number of tuples" means in the context of the multipliers? I mean, > >> when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I > >> get the min val, but what are we multiplying by 0.4? The total number > >> of tuples in the table? The total modified/deleted? > > > >pg_class.reltuples > > So I am still a little unclear... I need to analyse to get relevant > stats for autovacuum... but autovacuum needs to be used to update the > relevant stats? Can I REALLY do without a cronjob, or am I just > thinking wishfully (I can invent expressions if I want!). This means that we use the previously-known value of tuples in the table, plus the number of new tuples that have been inserted, deleted and/or updated (numbers you can see in the pg_stat_* views), and compare them to the thresholds. If I'm being too unclear, here is the relevant code comment: * A table needs to be vacuumed if the number of dead tuples exceeds a * threshold. This threshold is calculated as * * threshold = vac_base_thresh + vac_scale_factor * reltuples * * For analyze, the analysis done is that the number of tuples inserted, * deleted and updated since the last analyze exceeds a threshold calculated * in the same fashion as above. Note that the collector actually stores * the number of tuples (both live and dead) that there were as of the last * analyze. This is asymmetric to the VACUUM case. (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/autovacuum.c?rev=1.28;content-type=text%2Fx-cvsweb-markup) The idea is that you _can_ do without a cronjob. You may need to do a first ANALYZE just to get things warmed up, and then let autovacuum do its job. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
> The idea is that you _can_ do without a cronjob. You may need to do a > first ANALYZE just to get things warmed up, and then let autovacuum do > its job. Thanks guys, I think I have it now! Cheers Antoine