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,
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
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
results in much lower IO, compounding the problem.
Sent via pgsql-performance mailing list (firstname.lastname@example.org)
To make changes to your subscription: