From: John Huttley <John@mib-infotech.co.nz>
To: pgsql-performance@postgresql.org
Cc: Dan Langille <dan@langille.org>
Subject: Slow updates, poor IO
Date: Fri, 26 Sep 2008 07:24:55 +1200
Message-ID: <48DBE587.3050406@mib-infotech.co.nz> (raw)
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
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: John@mib-infotech.co.nz, dan@langille.org
Subject: Re: Slow updates, poor IO
In-Reply-To: <48DBE587.3050406@mib-infotech.co.nz>
* 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