Обсуждение: pg_autovacuum
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
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
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.
> > 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
>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
Вложения
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.
>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
Вложения
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
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
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
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.)
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 #
> >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
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 #
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
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 #