[PERFORM] Slow updates, poor IO

I've just had an interesting encounter with the slow full table update
problem that is inherent with MVCC

The system is 64 bit linux with 2.6.25 kernel feeding scsi disks.

the table is

CREATE TABLE file (
fileid integer NOT NULL,
fileindex integer DEFAULT 0 NOT NULL,
jobid integer NOT NULL,
pathid integer NOT NULL,
filenameid integer NOT NULL,
markid integer DEFAULT 0 NOT NULL,
lstat text NOT NULL,
md5 text NOT NULL,
perms text
);

ALTER TABLE ONLY file
ADD CONSTRAINT file_pkey PRIMARY KEY (fileid);

CREATE INDEX file_fp_idx ON file USING btree (filenameid, pathid);
CREATE INDEX file_jobid_idx ON file USING btree (jobid);

There are 2.7M rows.

running update file set perms='0664' took about 10 mins

during this period, vmstat reported Blocks Out holding in the 4000 to
6000 range.

When I dropped the indexes this query ran in 48sec.
Blocks out peaking at 55000.

So there is a double whammy.
MVCC requires more work to be done when indexes are defined and then
this work
results in much lower IO, compounding the problem.

Comments anyone?

--john

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance