Re: PG writes a lot to the disk

2008/3/20, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
> Another way that SELECT can cause disk writes is if it sets hint bits on
> recently-committed rows. However, if the tables aren't actively being
> modified any more, you'd expect that sort of activity to settle out pretty
> quickly.
>
> I concur with the temporary-file theory --- it's real hard to see how
> analyzing the tables would've fixed it otherwise.
>

That's exactly it, I concur with your first explanation because:
- We have no modification at all on SELECT simply because it's a
slony replicated table and any update is forbidden (no nextval, no
trigger, nothin)
- While monitoring the SELECT activity, write activity happened
within the tables files only, and without changing their size. No
other file was created, which eliminates the possibility of using
temporary files.
- Every table was recently commited, as it was a 3 days old replicated
database from scratch.

The most problematic query was like:
"SELECT * FROM blah WHERE tree <@ A.B.C ;" (more complicated but it's the idea)
We have millions of rows in blah, and blah was created a few hours
ago, with no ANALYZE after the injection of data.

All this make me think that PG was setting some bit on every row it
used, which caused this massive write activity (3MB/s) in the table
files. I'm talking about approx. 50 SELECT per second for a single
server.

And to prove that I made a test. I switched slony off on a server (no
update anymore), synced the disks, got the mtime of every file in the
base/ folder, executed hundreds of queries of the form:

SELECT 1
FROM _comment
INNER JOIN _article ON _article.id = _comment.parent_id
WHERE _comment.path <@ '%RANDOM_VALUE%'
;

During the massive activity, I took a new snapshot of the modified
files in the base/ folder.

The only files which were modified are:
base/16387/1819754
base/16387/18567

# SELECT relname FROM pg_class WHERE relfilenode IN (1819754, 18567) ;
relname
----------
_comment
_article

So *yes* table files are modified during SELECT, and it can result in
a lot of write if the queries plan work on a lot of rows.

Thansk for your help, I'm relieved =)

--
Laurent Raufaste
<http://www.glop.org/>