From: Sabin Coanda <sabin.coanda@deuromedia.ro>
To: pgsql-performance@postgresql.org
Subject: vacuum a lot of data when insert only
Date: Thu, 21 Jun 2007 19:53:54 +0300
Message-ID: <f5eaek$19hl$1@news.hub.org> (raw)
Hi there,
Reading different references, I understand there is no need to vacuum a
table where just insert actions perform. So I'm surprising to see a table
with just historical data, which is vacuumed at the nightly cron with a
simple VACUUM VERBOSE on about 1/3 of indexes amount.
Take a look on the fragment log concerning this table:
INFO: vacuuming "public.tbTEST"
INFO: scanned index "tbTEST_pkey" to remove 1357614 row versions
DETAIL: CPU 0.31s/1.38u sec elapsed 4.56 sec.
INFO: "tbTEST": removed 1357614 row versions in 16923 pages
DETAIL: CPU 0.70s/0.13u sec elapsed 2.49 sec.
INFO: index "tbTEST_pkey" now contains 2601759 row versions in 12384 pages
DETAIL: 1357614 index row versions were removed.
5415 index pages have been deleted, 2452 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "tbTEST": found 1357614 removable, 2601759 nonremovable row versions
in 49153 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 29900 unused item pointers.
16923 pages contain useful free space.
0 pages are entirely empty.
CPU 2.12s/1.87u sec elapsed 11.41 sec.
INFO: "tbTEST": truncated 49153 to 32231 pages
DETAIL: CPU 0.23s/0.06u sec elapsed 0.31 sec.
I found the following statistics in pg_stat_user_tables:
n_tup_ins = 11444229
n_tup_upd = 0
n_tup_del = 0
The structure of the table is the following:
CREATE TABLE "tbTEST"
(
"PK_ID" integer NOT NULL DEFAULT nextval('"tbTEST_PK_ID_seq"'::regclass),
"FK_SourceTypeID" integer,
"SourceID" integer DEFAULT -1,
"Message" character varying(500) NOT NULL DEFAULT ''::character varying,
"DateAndTime" timestamp without time zone NOT NULL,
CONSTRAINT "tbTEST_pkey" PRIMARY KEY ("PK_ID"),
CONSTRAINT "tbTEST_FK_SourceTypeID_fkey" FOREIGN KEY ("FK_SourceTypeID")
REFERENCES "tbLISTS" ("PK_ID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
Postgres version is 8.2.3.
What's happen ?
TIA,
Sabin
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sabin.coanda@deuromedia.ro
Subject: Re: vacuum a lot of data when insert only
In-Reply-To: <f5eaek$19hl$1@news.hub.org>
* 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