Skip site navigation (1) Skip section navigation (2)

Re: explanation for seeks in VACUUM

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: explanation for seeks in VACUUM
Date: 2007-12-15 03:53:14
Message-ID: 1197690794.28804.321.camel@dogma.ljc.laika.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, 2007-12-14 at 19:04 -0500, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > Where am I going wrong? Are many of these lseeks no-ops or something?
> 
> They're not supposed to be, but if you only tracked seeks and not
> reads or writes, it's hard to be sure what's going on.

The seeks were comparable to reads and writes, which is what surprised
me:

$ ktrace -p 42440; sleep 5; ktrace -C
$ kdump -f ktrace.out | grep "GIO   fd 38 read" | wc -l
   11479
$ kdump -f ktrace.out | grep "GIO   fd 38 wrote" | wc -l
   11480
$ kdump -f ktrace.out | grep "lseek.0x26" | wc -l
   22960

> 8.2's VACUUM should process a btree index (this is a btree index no?)
> in physical order, so I'd expect lseeks only when a page is already in
> buffers --- at least on the read side.  On the write side things might
> be a great deal less predictable.  You're cleaning out about one tuple
> in 30, so the odds are that nearly every index page is getting dirtied,
> and they're going to need to be written sometime.

Actually, the table I was VACUUMing had both btree and GIN indexes, and
I'm not entirely sure which one was happening at the time. I will
investigate more.

My intuition tells me that, if the pages are being read and dirtied
sequentially, it would be able to write mostly sequentially (at least in
theory).

In the box that was causing the problem (which had more constrained
memory than my reproduced case), it seemed to be swamped by random I/O
-- low CPU usage, and low disk usage (about 1-2 MB/s write), yet VACUUM
would take forever and the box would appear very sluggish.

Regards,
	Jeff Davis




In response to

pgsql-performance by date

Next:From: Loïc MarteauDate: 2007-12-15 11:43:10
Subject: Re: update 600000 rows
Previous:From: Steve CrawfordDate: 2007-12-15 01:16:56
Subject: Re: update 600000 rows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group