Well the simple answer is that pg_autovacuum didn't see 10,000 inserts updates or deletes.
pg_autovacuum saw: 476095 - 471336 = 4759 U/D's relevant for vacuuming and
634119 - 629121 = 4998 I/U/D's relevant for performing analyze.

The tough question is why is pg_autovacuum not seeing all the updates. Since autovacuum depends on the stats system for it's numbers, the most likely answer is that the stats system is not able to keep up with the workload, and is ignoring some of the updates. Would you check to see what the stats system is reporting for numbers of I/U/D's for the file_92 table? The query pg_autovacuum uses is:

select a.oid,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltuples,
from pg_class a, pg_stat_all_tables b
where a.oid=b.relid and a.relkind = 'r'

Take a look at the n_tup_ins, upd, del numbers before and see if they are keeping up with the actual number if I/U/D's that you are performing. If they are, then it's a pg_autovacuum problem that I will look into further, if they are not, then it's a stats system problem that I can't really help with.

Good luck,


Otto Blomqvist wrote:

Hello !

I'm running pg_autovacuum on a 1GHz, 80Gig, 512Mhz machine. The database is
about 30MB tarred. We have about 50000 Updates/Inserts/Deletes per day. It
runs beautifully for ~4 days. Then the HDD activity and the Postmaster CPU
usage goes up ALOT. Even though I have plenty (?) of FSM (2 million) pages.
I perform a vacuum and everything is back to normal for another 4 days. I
could schedule a manual vacuum each day but the util is not called
pg_SemiAutoVacuum so I'm hoping this is not necessary. The same user that
ran the manual vacuum is running pg_autovacuum. The normal CPU usage is
about 10% w/ little HD activity.

Im running autovacuum with the following flags -d 3 -v 300 -V 0.1 -s 180 -S
0.1 -a 200 -A 0.1

Below are some snipplets regarding vacuuming from the busiest table

This is the last VACUUM ANALYZE performed by pg_autovacuum before I ran the
manual vacuum

[2005-03-24 02:05:43 EST] DEBUG:        Performing: VACUUM ANALYZE
[2005-03-24 02:05:52 EST] INFO:         table name: secom."public"."file_92"
[2005-03-24 02:05:52 EST] INFO:         relid: 9384219;   relisshared: 0
[2005-03-24 02:05:52 EST] INFO:         reltuples: 106228.000000;  relpages:
[2005-03-24 02:05:52 EST] INFO:         curr_analyze_count: 629121;
curr_vacuum_count: 471336
[2005-03-24 02:05:52 EST] INFO:         last_analyze_count: 629121;
last_vacuum_count: 471336
[2005-03-24 02:05:52 EST] INFO:         analyze_threshold: 10822;
vacuum_threshold: 10922

This is the last pg_autovacuum debug output before I ran the manual vacuum

[2005-03-24 09:18:44 EST] INFO:         table name: secom."public"."file_92"
[2005-03-24 09:18:44 EST] INFO:         relid: 9384219;   relisshared: 0
[2005-03-24 09:18:44 EST] INFO:         reltuples: 106228.000000;  relpages:
[2005-03-24 09:18:44 EST] INFO:         curr_analyze_count: 634119;
curr_vacuum_count: 476095
[2005-03-24 09:18:44 EST] INFO:         last_analyze_count: 629121;
last_vacuum_count: 471336
[2005-03-24 09:18:44 EST] INFO:         analyze_threshold: 10822;
vacuum_threshold: 10922

file_92 had about 10000 Inserts/Deletes between 02:05  and 9:20

Then i Ran a vacuum verbose

23 Mar 05 - 9:20 AM
INFO:  vacuuming "public.file_92"
INFO:  index "file_92_record_number_key" now contains 94 row versions in
2720 pages
DETAIL:  107860 index row versions were removed.
2712 index pages have been deleted, 2060 are currently reusable.
CPU 0.22s/0.64u sec elapsed 8.45 sec.
INFO:  "file_92": removed 107860 row versions in 9131 pages
DETAIL:  CPU 1.13s/4.27u sec elapsed 11.75 sec.
INFO:  "file_92": found 107860 removable, 92 nonremovable row versions in
9131 pages
DETAIL:  91 dead row versions cannot be removed yet.
There were 303086 unused item pointers.
0 pages are entirely empty.
CPU 1.55s/5.00u sec elapsed 20.86 sec.
INFO:  "file_92": truncated 9131 to 8423 pages
DETAIL:  CPU 0.65s/0.03u sec elapsed 5.80 sec.
INFO:  free space map: 57 relations, 34892 pages stored; 34464 total pages
DETAIL:  Allocated FSM size: 1000 relations + 2000000 pages = 11784 kB
shared memory.

Also, file_92 is just a temporary storage area, for records waiting to be
processed. Records are in there typically ~10 sec.

Over 100'000 Index Rows removed, 300'000 unused item pointers ? How could
autovacuum let this happen ? I would estimate the table had about 10000
inserts/deletes between the last pg_autovacuum "Vacuum analyze" and my
manual vacuum verbose.

It is like the suction is not strong enough ;)

Any ideas ? It would be greatly appreciated as this is taking me one step
closer to the looney bin.


/Otto Blomqvist

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

-- Matthew O'Connor V.P. of Operations Terrie O'Connor Realtors 201-934-4900 x27

---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Reply via email to