From: ogjunk-pgjedan@yahoo.com
To: pgsql-admin@postgresql.org
Subject: Re: VACUUMing for 30 minutes
Date: Thu, 23 Dec 2004 10:15:55 -0800 (PST)
Message-ID: <20041223181555.25535.qmail@web12706.mail.yahoo.com> (raw)
In-Reply-To: <20041222163844.93592.qmail@web12707.mail.yahoo.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: ogjunk-pgjedan@yahoo.com
Subject: Re: VACUUMing for 30 minutes
In-Reply-To: <20041223181555.25535.qmail@web12706.mail.yahoo.com>
References: <20041222163844.93592.qmail@web12707.mail.yahoo.com> <20041223181555.25535.qmail@web12706.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox