Slow updates, poor IO

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux