Re: pg_autovacuum not having enough suction ? - Mailing list pgsql-performance

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,
           b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del
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,

Matthew


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
>"public"."file_92"
>[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:
>9131
>[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:
>9131
>[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
>needed
>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.
>
>Thanks
>
>/Otto Blomqvist
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>

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