Thread: pg_autovacuum

pg_autovacuum

From
Adam Kavan
Date:
I decided to try pg_autovacuum and am having a problem.  It never analyzes
or vacuums.  I looked at the code and had it display

printf("%s,%s,%s\n",PQgetvalue(res, j, PQfnumber(res,
"n_tup_ins")),PQgetvalue(res, j, PQfnumber(res,
"n_tup_upd")),PQgetvalue(res, j, PQfnumber(res, "n_tup_del")));

All of these values are always 0 for all tables irregardless of activity on
the tables.  Any clue what could be causing this?  In postgresql.conf I have :

# - Query/Index Statistics Collector -

stats_start_collector = true
#stats_command_string = true
#stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = false

which is fine if I am correct.  Am I missing something?  I am using
7.4beta2 but the same problem occurred yesterday with 7.4beta1.

--- Adam Kavan
--- akavan@cox.net


Re: pg_autovacuum

From
"Matthew T. O'Connor"
Date:
On Fri, 2003-08-29 at 17:21, Adam Kavan wrote:
> I decided to try pg_autovacuum and am having a problem.  It never analyzes
> or vacuums.  I looked at the code and had it display
>
> printf("%s,%s,%s\n",PQgetvalue(res, j, PQfnumber(res,
> "n_tup_ins")),PQgetvalue(res, j, PQfnumber(res,
> "n_tup_upd")),PQgetvalue(res, j, PQfnumber(res, "n_tup_del")));
>
> All of these values are always 0 for all tables irregardless of activity on
> the tables.  Any clue what could be causing this?  In postgresql.conf I have :
>
> # - Query/Index Statistics Collector -
>
> stats_start_collector = true
> #stats_command_string = true
> #stats_block_level = true
> stats_row_level = true
> stats_reset_on_server_start = false
>
> which is fine if I am correct.  Am I missing something?  I am using
> 7.4beta2 but the same problem occurred yesterday with 7.4beta1.

All of this appears to be in order.  I think we need some more
information.  First, what options are you setting?  What is the exact
command you are using to launch pg_autovaccum.  Some output from
pg_autovacuum with debug level set to 2 would be helpful.  Also, what OS
/ compiler are you using?

BTW: are you sure that you have performed enough inserts / updates /
deletes to exceed the thresholds? (debug level 2 will tell you what the
thresholds are.

Matthew


Re: pg_autovacuum

From
"Matthew T. O'Connor"
Date:
On Fri, 2003-08-29 at 17:21, Adam Kavan wrote:
> I decided to try pg_autovacuum and am having a problem.

FYI, I just downloaded beta2 and compiled it on RH9 and pg_autovacuum
works fine.


Re: pg_autovacuum

From
"Matthew T. O'Connor"
Date:
>
> What do you mean which options am I setting?  I am using
>
> ./pg_autovacuum -U akavan -s30 -d3

Yeah, this is what I was looking for.

> I am using redhat 9 with gcc 3.2.2. And I will paste some output from
> debug  level 2 at the end of this post.  During the run I inserted 10523
> lines  into PointUsage then deleted them... between pg_autovac scans.  I
> only  included the first scan of my database because I didn't want to
> clutter up  the list to much, the output is mostly unchanged between
> scans (the amount  of time taken at the bottom changes nothing else
> does.

[snip]

> [2003-09-02 02:46:00 PM]   table name:     data.public.PointUsage
> [2003-09-02 02:46:00 PM]      relfilenode: 17498
> [2003-09-02 02:46:00 PM]      reltuples: 5;  relpages: 1
> [2003-09-02 02:46:00 PM]      curr_analyze_count:  0; cur_delete_count:
>  0 [2003-09-02 02:46:00 PM]      ins_at_last_analyze: 0;
> del_at_last_vacuum: 0 [2003-09-02 02:46:00 PM]      insert_threshold:
> 505;
> delete_threshold    1010
> [2003-09-02 02:46:00 PM] added table: data.public.PointUsage

[snip more log detail...]

> [2003-09-02 02:46:00 PM]   table name:     data.public.PointUsage
> [2003-09-02 02:46:00 PM]      relfilenode: 17498
> [2003-09-02 02:46:00 PM]      reltuples: 5;  relpages: 1
> [2003-09-02 02:46:00 PM]      curr_analyze_count:  0; cur_delete_count:
>  0 [2003-09-02 02:46:00 PM]      ins_at_last_analyze: 0;
> del_at_last_vacuum: 0 [2003-09-02 02:46:00 PM]      insert_threshold:
> 505;
> delete_threshold    1010
> [2003-09-02 02:46:00 PM] 1 All DBs checked in: 260135 usec, will sleep
> for  30 secs.

OK, so all you have shown me is the output from the first loop of
pg_autovacuum.  The PointUsage table was added, the stats system had
reported 0 inserts update or deletes when the table was added, and when it
was checked 0.2 seconds later, it still had reported no deletes, so at
this point it is correct in not doing a vacuum.  What I really need is the
logoutput from before, during and just after you insert / update / delete
from the PointUsage table.  Also, setting your threshold values lower
helps if you just want to see pg_autovacuum actually fire off a vacuum.
So you probabaly want to do somethign like this:

pg_autovacuum -d2 -s1 -S0 -v10 -V0 -a10 -A0 -L pg_autovacuum.out

this will set debug=2, sleep only 1 sec after each look, and perform
vacuums and analyzes after only 10 insert/update/delets and log it all to
pg_autovacuum.out

Run your update script

Kill pg_autovacuum

bzip the logfile and send it to the mailing list (or just to me if you
prefer).  Also a copy of your script might help.

Matthew



Re: pg_autovacuum

From
Adam Kavan
Date:
>So you probabaly want to do somethign like this:
>
>pg_autovacuum -d2 -s1 -S0 -v10 -V0 -a10 -A0 -L pg_autovacuum.out
>
>this will set debug=2, sleep only 1 sec after each look, and perform
>vacuums and analyzes after only 10 insert/update/delets and log it all to
>pg_autovacuum.out
>
>Run your update script
>
>Kill pg_autovacuum
>
>bzip the logfile and send it to the mailing list (or just to me if you
>prefer).  Also a copy of your script might help.
>
>Matthew

Ok I ran pg_autovacuum using the supplied command line and have attached
the resulting log.  My script is just INSERT INTO "PointUsage"
VALUES(DEFAULT,32,now(),1024); repeated several thousand times.  I ran
autovac, then my script, then verified all of the rows were inserted then
ran DELETE FROM "PointUsage" WHERE "Value" = 1024; to kill all of the new
rows, and then killed autovac.  The results are attached.

Thank you very much for taking the time to look at this for me.

--- Adam Kavan
--- akavan@cox.net

Attachment

Re: pg_autovacuum

From
"Matthew T. O'Connor"
Date:
On Tue, 2003-09-02 at 18:30, Adam Kavan wrote:
> Ok I ran pg_autovacuum using the supplied command line and have attached
> the resulting log.  My script is just INSERT INTO "PointUsage"
> VALUES(DEFAULT,32,now(),1024); repeated several thousand times.  I ran
> autovac, then my script, then verified all of the rows were inserted then
> ran DELETE FROM "PointUsage" WHERE "Value" = 1024; to kill all of the new
> rows, and then killed autovac.  The results are attached.

First, I meant to ask for debug level 3, (-d3, not -d2) sorry....  Also,
are you sure that pg_autovacuum is connecting to the same database
cluster as your insert delete script?

In the output you should see the cur_analyze_count increase with each
insert and the curr_delete_count increase with every delete.  Also, the
debug output should tell you the threshold you have to reach for a
vacuum or analyze.


Re: pg_autovacuum

From
Adam Kavan
Date:
>First, I meant to ask for debug level 3, (-d3, not -d2) sorry....  Also,
>are you sure that pg_autovacuum is connecting to the same database
>cluster as your insert delete script?

I have attached a new log with a debug level of 3.  And I am sure they are
connecting to the same cluster, I only have one postgres box that is active.


>In the output you should see the cur_analyze_count increase with each
>insert and the curr_delete_count increase with every delete.  Also, the
>debug output should tell you the threshold you have to reach for a
>vacuum or analyze.

And there is the problem, all of the counts stay at 0 no matter what I do.

--- Adam Kavan
--- akavan@cox.net

Attachment

Re: pg_autovacuum

From
"Matthew T. O'Connor"
Date:
On Tue, 2003-09-02 at 20:40, Adam Kavan wrote:
> And there is the problem, all of the counts stay at 0 no matter what I do.

OK, so why is this happening... a bug in the stats system?  Ignoring
pg_autovaccu, what numbers do you get from the stats system when you do
a:

SELECT relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables
where relname = 'foobar';

both before and after your insert / update script.

Matthew


Re: pg_autovacuum

From
Adam Kavan
Date:
At 09:41 PM 9/2/03 -0400, Matthew T. O'Connor wrote:
>On Tue, 2003-09-02 at 20:40, Adam Kavan wrote:
> > And there is the problem, all of the counts stay at 0 no matter what I do.
>
>OK, so why is this happening... a bug in the stats system?  Ignoring
>pg_autovaccu, what numbers do you get from the stats system when you do
>a:
>
>SELECT relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables
>where relname = 'foobar';
>
>both before and after your insert / update script.
>
>Matthew
Before updates:

   relname   | n_tup_ins | n_tup_upd | n_tup_del
------------+-----------+-----------+-----------
  PointUsage |         0 |         0 |         0
(1 row)

After updates:

   relname   | n_tup_ins | n_tup_upd | n_tup_del
------------+-----------+-----------+-----------
  PointUsage |         0 |         0 |         0
(1 row)

After deleting updates:

   relname   | n_tup_ins | n_tup_upd | n_tup_del
------------+-----------+-----------+-----------
  PointUsage |         0 |         0 |         0
(1 row)

This is the problem... I just don't know how to fix it, or even what is
causing it.  I am 100% sure that I am inserting into the same database that
I executed the select from.  Here are the relevent lines from postgresql.conf:

stats_start_collector = true
#stats_command_string = true
#stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = false

Any ideas?

--- Adam Kavan
--- akavan@cox.net



Re: pg_autovacuum

From
Adam Kavan
Date:
At 10:48 AM 9/4/03 -0400, Matthew T. O'Connor wrote:
>On Thu, 2003-09-04 at 09:01, Jan Wieck wrote:
> > Do the stats_* options appear twice in the config file? If that isn't
> > the case, the only possible problem left I can think of is that you're
> > running on a platform where no AF_UNIX sockets are available, like under
> > Windows?
>
>Nope he is running under RH9 (I don't know if it's stock, or highly
>modified, but RH9 none the less)  Also, the config file is mostly just
>default values with the appropriate stat settings uncommented and set to
>true. (also pg_autovacuum will fail to run if it detects improper stat
>system settings, so that isn't the problem.)

Its a stock system, no changes fresh off the ftp.  The only change I have
made is installing postgres.

--- Adam Kavan
--- akavan@cox.net



Re: pg_autovacuum

From
"Matthew T. O'Connor"
Date:
On Thu, 2003-09-04 at 09:01, Jan Wieck wrote:
> Do the stats_* options appear twice in the config file? If that isn't
> the case, the only possible problem left I can think of is that you're
> running on a platform where no AF_UNIX sockets are available, like under
> Windows?

Nope he is running under RH9 (I don't know if it's stock, or highly
modified, but RH9 none the less)  Also, the config file is mostly just
default values with the appropriate stat settings uncommented and set to
true. (also pg_autovacuum will fail to run if it detects improper stat
system settings, so that isn't the problem.)


Re: pg_autovacuum

From
Jan Wieck
Date:
Do the stats_* options appear twice in the config file? If that isn't
the case, the only possible problem left I can think of is that you're
running on a platform where no AF_UNIX sockets are available, like under
Windows?


Jan

Adam Kavan wrote:

>>
>>And you restarted the postmaster since activating these non-standard
>>config options? Are you sure that *this* postgresql.conf file is what is
>>used by the database on startup?
>>
>>
>>Jan
>
> Yes and yes, I tried changing the lines right above these about logging
> statistics information and restarted postmaster, I got a bunch of
> information in my logs, indicating that I am using the right conf file, but
> I had no luck with getting information on the number of inserts.
>
> --- Adam Kavan
> --- akavan@cox.net

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: pg_autovacuum

From
Adam Kavan
Date:
>
>And you restarted the postmaster since activating these non-standard
>config options? Are you sure that *this* postgresql.conf file is what is
>used by the database on startup?
>
>
>Jan

Yes and yes, I tried changing the lines right above these about logging
statistics information and restarted postmaster, I got a bunch of
information in my logs, indicating that I am using the right conf file, but
I had no luck with getting information on the number of inserts.

--- Adam Kavan
--- akavan@cox.net


Re: pg_autovacuum

From
Jan Wieck
Date:

Adam Kavan wrote:

> At 09:41 PM 9/2/03 -0400, Matthew T. O'Connor wrote:
>>On Tue, 2003-09-02 at 20:40, Adam Kavan wrote:
>> > And there is the problem, all of the counts stay at 0 no matter what I do.
>>
>>OK, so why is this happening... a bug in the stats system?  Ignoring
>>pg_autovaccu, what numbers do you get from the stats system when you do
>>a:
>>
>>SELECT relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables
>>where relname = 'foobar';
>>
>>both before and after your insert / update script.
>>
>>Matthew
> Before updates:
>
>    relname   | n_tup_ins | n_tup_upd | n_tup_del
> ------------+-----------+-----------+-----------
>   PointUsage |         0 |         0 |         0
> (1 row)
>
> After updates:
>
>    relname   | n_tup_ins | n_tup_upd | n_tup_del
> ------------+-----------+-----------+-----------
>   PointUsage |         0 |         0 |         0
> (1 row)
>
> After deleting updates:
>
>    relname   | n_tup_ins | n_tup_upd | n_tup_del
> ------------+-----------+-----------+-----------
>   PointUsage |         0 |         0 |         0
> (1 row)
>
> This is the problem... I just don't know how to fix it, or even what is
> causing it.  I am 100% sure that I am inserting into the same database that
> I executed the select from.  Here are the relevent lines from postgresql.conf:
>
> stats_start_collector = true
> #stats_command_string = true
> #stats_block_level = true
> stats_row_level = true
> stats_reset_on_server_start = false

And you restarted the postmaster since activating these non-standard
config options? Are you sure that *this* postgresql.conf file is what is
used by the database on startup?


Jan

>
> Any ideas?
>
> --- Adam Kavan
> --- akavan@cox.net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: pg_autovacuum

From
Adam Kavan
Date:
At 02:25 PM 9/4/03 -0400, Jan Wieck wrote:
 >Hmmmm ... would need to take a look at the code ... but a wild guess would
 >be the communication between the backends and the collector daemon. I'm
 >not sure if the UDP crap^H^H^H^Hstuff I used in there is IPV6 safe. What
 >version of PostgreSQL was that?

On Matthew's advice I've posted my problem to Hackers.

--- Adam Kavan
--- akavan@cox.net


Re: pg_autovacuum

From
Jan Wieck
Date:

Adam Kavan wrote:

> At 10:48 AM 9/4/03 -0400, Matthew T. O'Connor wrote:
>>On Thu, 2003-09-04 at 09:01, Jan Wieck wrote:
>> > Do the stats_* options appear twice in the config file? If that isn't
>> > the case, the only possible problem left I can think of is that you're
>> > running on a platform where no AF_UNIX sockets are available, like under
>> > Windows?
>>
>>Nope he is running under RH9 (I don't know if it's stock, or highly
>>modified, but RH9 none the less)  Also, the config file is mostly just
>>default values with the appropriate stat settings uncommented and set to
>>true. (also pg_autovacuum will fail to run if it detects improper stat
>>system settings, so that isn't the problem.)
>
> Its a stock system, no changes fresh off the ftp.  The only change I have
> made is installing postgres.

Hmmmm ... would need to take a look at the code ... but a wild guess
would be the communication between the backends and the collector
daemon. I'm not sure if the UDP crap^H^H^H^Hstuff I used in there is
IPV6 safe. What version of PostgreSQL was that?


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #