Re: pg_xlog becomes extremely large during CREATE INDEX

"Jeffrey W. Baker" <jwbaker@acm.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?

I think one reason for the problem is that btree CREATE INDEX is as bad
as VACUUM, if not worse, with respect to chewing all available shared
buffers.  It will fill shared buffers with newly-created dirty pages ---
and I'll bet the CHECKPOINT process is getting stuck with dumping the
majority of those pages to disk.  It might be worth tweaking CREATE
INDEX so it pushes out the newly created pages for itself, thus limiting
the number of dirtied buffers it creates.

            regards, tom lane