Re: pg_xlog becomes extremely large during CREATE INDEX

On Thu, 2004-05-13 at 21:00, Tom Lane wrote:
> "Jeffrey W. Baker" <jwbaker(at)acm(dot)org> writes:
> > Okay, I installed a fresh, completely stock 7.4.2 and did the following:
>
> > createdb growxlog
> > echo "create table data (a int, b int, c int, d int, e int)" | psql growxlog
> > perl -e 'use POSIX qw(floor); print "COPY data FROM STDIN;\n"; for ($i = 0; $i < 100000000; $i++) {print(join("\t", $i, floor(rand()*1000000), floor(rand()*1000000), floor(rand()*1000000), floor(rand()*1000000)), "\n")}' | psql growxlog
> > echo "create unique index data_pkey on data(a,b,c)" | psql growxlog
>
> I tried this locally, and what I see happening is that a checkpoint
> process starts shortly after the CREATE INDEX begins whomping out the
> index data --- but it doesn't finish until after the CREATE INDEX does.
> AFAICS there is not any sort of locking problem, it's just that the
> CREATE INDEX is chewing all the I/O bandwidth. If we could get some
> more checkpoints pushed through then the xlog would get truncated, but
> it's not happening.
>
> I'm running this on a development machine with an ok CPU and junk
> consumer-grade-IDE disk drive, so lack of I/O bandwidth is hardly
> surprising; can anyone confirm the observation on better hardware?

We're running it on some pretty manly hardware (dual opterons, 8gb main
memory, 4-way SATA RAIDs), so it happens on both ends of the spectrum.

-jwb