From: | "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net> |
---|---|
To: | <akavan(at)cox(dot)net> |
Cc: | <matthew(at)zeut(dot)net>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pg_autovacuum |
Date: | 2003-09-02 21:54:57 |
Message-ID: | 3818.67.80.199.173.1062539697.squirrel@matth.zeut.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Bjørn T Johansen | 2003-09-02 22:06:48 | Tomcat Connection Pool? |
Previous Message | Nigel J. Andrews | 2003-09-02 21:38:43 | Re: Commercial postgresql |