Re: TRUNCATE TABLE

On Sat, Aug 04, 2007 at 11:39:31PM +0200, Adriaan van Os wrote:
> Kevin Grittner wrote:
> >>>>On Mon, Jul 16, 2007 at 7:18 PM, in message
> >>>><25418(dot)1184631498(at)sss(dot)pgh(dot)pa(dot)us>,
> >Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>Somehow, autovac is doing something that makes the filesystem go nuts
> >>every so often, and take an astonishingly long time to create an empty
> >>file. But autovac itself doesn't create or delete any files, so what's
> >>up here?
> >
> >Have you ruled out checkpoints as the culprit?
>
> That's a good question. I will do some more tests, but I also suspect fsync
> "cascading"
> <http://www.uwsg.iu.edu/hypermail/linux/kernel/0708.0/1435.html>.

Interesting. I'm guessing that ext3 has to sync out the entire journal
up to the point in time that fsync() is called, regardless of what
files/information the journal contains. Fortunately I think it's common
knowledge to mount PostgreSQL filesystems with data=writeback, which
hopefully eliminates much of that bottleneck... but if you don't do
noatime you're probably still spewing a lot out to the drive.
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)