Message-ID: <48DBE587.3050406@mib-infotech.co.nz>
Date: Fri, 26 Sep 2008 07:24:55 +1200
From: John Huttley <John@mib-infotech.co.nz>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
CC: Dan Langille <dan@langille.org>
Subject: Slow updates, poor IO
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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