Re: VACUUMing for 30 minutes

Hello,

So I run VACUUM VERBOSE (just like that, without specifying a table)
and got some output.  The 2 big tables take 99% of the vacuuming time.


Now, I run VACUUM religiously every night, across all tables, but maybe
that's an overkill for th number of updates and inserts in this DB.
Maybe somebody can give an advice.

Big table #1:


INFO:  --Relation public.url--
INFO:  Index pk_url_id: Pages 29650; Tuples 114184: Deleted 47.
    CPU 3.61s/0.72u sec elapsed 129.67 sec.
INFO:  Index url_href_key: Pages 108190; Tuples 114182: Deleted 47.
    CPU 12.13s/2.30u sec elapsed 922.25 sec.
INFO:  Index ix_url_last_mod_date: Pages 37536; Tuples 114182: Deleted
47.
    CPU 4.39s/0.82u sec elapsed 270.24 sec.
INFO:  Index ix_url_last_code: Pages 38823; Tuples 114182: Deleted 47.
    CPU 3.75s/0.61u sec elapsed 160.87 sec.
INFO:  Removed 47 tuples in 3 pages.
    CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  Pages 303359: Changed 23, Empty 0; Tup 114182: Vac 47, Keep 273,
UnUsed 11798506.
    Total CPU 40.30s/7.10u sec elapsed 1564.58 sec.


Big table #2:


INFO:  --Relation public.user_url--
INFO:  Index pk_user_url_id: Pages 1291; Tuples 137980: Deleted 59.
    CPU 0.09s/0.07u sec elapsed 4.71 sec.
INFO:  Index ix_user_url_user_id_url_id: Pages 1633; Tuples 137980:
Deleted 59.
    CPU 0.08s/0.04u sec elapsed 1.55 sec.
INFO:  Index ix_user_url_add_date: Pages 1186; Tuples 137980: Deleted
59.
    CPU 0.15s/0.07u sec elapsed 4.38 sec.
INFO:  Index ix_user_url_last_click_date: Pages 1124; Tuples 137980:
Deleted 59.
    CPU 0.13s/0.05u sec elapsed 3.39 sec.
INFO:  Index ix_user_url_click_count: Pages 977; Tuples 137980: Deleted
59.
    CPU 0.13s/0.06u sec elapsed 2.07 sec.
INFO:  Removed 59 tuples in 8 pages.
    CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  Pages 5052: Changed 0, Empty 0; Tup 137980: Vac 59, Keep 31,
UnUsed 215734.
    Total CPU 0.86s/0.35u sec elapsed 17.65 sec.


It looks like 'Tuples' number corresponds to the number of rows and
'Deleted' refers to the number of rows that were DELETEd, so vacuuming
basically re-claims the gaps in the index caused by deletion.

Does the above 'rate of table modification' warrant nightly VACUUMing,
or should I relax and do it weekly or even monthly?

Thanks,
Otis



--- ogjunk-pgjedan@yahoo.com wrote:

> Hello,
>
> --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > <ogjunk-pgjedan@yahoo.com> writes:
> > > VACUUMing this DB takes about 30 minutes, and during that time
> the
> > DB
> > > is pretty unresponsive, although the PG process is not using a
> lot
> > of
> > > CPU (load ~ 1) nor memory (~20MB for the VACUUM process).
> >
> > How big is the DB physically ("du $PGDATA" results)?
>
> 4.2 GB:
>
> # du -h ~postgres/data
> 3.6M    /var/lib/pgsql/data/base/1
> 3.6M    /var/lib/pgsql/data/base/16975
> 4.0K    /var/lib/pgsql/data/base/16976/pgsql_tmp
> 4.1G    /var/lib/pgsql/data/base/16976
> 4.1G    /var/lib/pgsql/data/base
> 152K    /var/lib/pgsql/data/global
> 129M    /var/lib/pgsql/data/pg_xlog
> 1.1M    /var/lib/pgsql/data/pg_clog
> 4.2G    /var/lib/pgsql/data
>
> > If you've been lax
> > about vacuuming or not had your FSM parameters set high enough,
> there
> > could be a whole lot of dead space for VACUUM to scan through.
>
> I've been vacuuming every night, like a good DBwife.
>
> > If so,
> > VACUUM FULL or possibly CLUSTER would be the best way to re-compact
> > the
> > tables.  (VACUUM VERBOSE on your larger tables would be another way
> > to
> > investigate this.)
>
> I will try VACUUM VERBOSE on the biggest (and most active) table
> tonight and report the findings.
>
> > The other possibility is that you have a seriously slow disk drive
> > :-(
>
> It looks like I have an ATA-6 drive with only 2MB cache, and the
> following throughput:
>
> # /sbin/hdparm -tT /dev/hda
>
>
>
> /dev/hda:
>  Timing buffer-cache reads:   128 MB in  0.56 seconds =228.57 MB/sec
>  Timing buffered disk reads:  64 MB in  1.18 seconds = 54.24 MB/sec
>
> Not SCSI, not RAID, but not the slowest HDD on the continent.
>
> Thanks,
> Otis
>
>
> ---------------------------(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
>