pg_xlog becomes extremely large during CREATE INDEX - Page 2

Jeffrey W. Baker

Greetings,

I have a 23GB data table upon which I am building a primary key of three
columns. The data is mounted in a 137GB device and pg_xlog is mounted
on a separate 3.5GB device. I have configured 24 checkpoint segments,
which I expect gives me a worst-case usage in pg_xlog of 384MB.
Unfortunately, during the CREATE INDEX, pg_xlog becomes full!

[There's an ls(1) listing at the end of this mail, if you care to see]

The operation created 222 files in pg_xlog before it filled the device,
and there's no indication that it was planning to stop there. Is there
some factor, other than checkpoint segments, which controls the size of
pg_xlog, or is there some other operation that also writes files in
pg_xlog, or, is the server just running away?

I have reset the xlogs and I'm trying again.

Regards,
Jeffrey

mistral:/pg_xlog_full# ls -l
total 3641692
-rw------- 1 postgres postgres 16777216 May 12 15:51 000000060000009 2
-rw------- 1 postgres postgres 16777216 May 12 15:51 000000060000009 3
-rw------- 1 postgres postgres 16777216 May 12 15:51 000000060000009 4
-rw------- 1 postgres postgres 16777216 May 12 15:51 000000060000009 5
-rw------- 1 postgres postgres 16777216 May 12 15:52 000000060000009 6
-rw------- 1 postgres postgres 16777216 May 12 15:52 000000060000009 7
-rw------- 1 postgres postgres 16777216 May 12 15:52 000000060000009 8
-rw------- 1 postgres postgres 16777216 May 12 15:52 000000060000009 9
-rw------- 1 postgres postgres 16777216 May 12 15:52 000000060000009 A
-rw------- 1 postgres postgres 16777216 May 12 15:52 000000060000009 B
-rw------- 1 postgres postgres 16777216 May 12 15:53 000000060000009 C
-rw------- 1 postgres postgres 16777216 May 12 15:53 000000060000009 D
-rw------- 1 postgres postgres 16777216 May 12 15:53 000000060000009 E
-rw------- 1 postgres postgres 16777216 May 12 15:53 000000060000009 F
-rw------- 1 postgres postgres 16777216 May 12 15:53 00000006000000A 0
-rw------- 1 postgres postgres 16777216 May 12 15:53 00000006000000A 1
-rw------- 1 postgres postgres 16777216 May 12 15:54 00000006000000A 2
-rw------- 1 postgres postgres 16777216 May 12 15:54 00000006000000A 3
-rw------- 1 postgres postgres 16777216 May 12 15:54 00000006000000A 4
-rw------- 1 postgres postgres 16777216 May 12 15:54 00000006000000A 5
-rw------- 1 postgres postgres 16777216 May 12 15:54 00000006000000A 6
-rw------- 1 postgres postgres 16777216 May 12 15:54 00000006000000A 7
-rw------- 1 postgres postgres 16777216 May 12 15:54 00000006000000A 8
-rw------- 1 postgres postgres 16777216 May 12 15:55 00000006000000A 9
-rw------- 1 postgres postgres 16777216 May 12 15:55 00000006000000A A
-rw------- 1 postgres postgres 16777216 May 12 15:55 00000006000000A B
-rw------- 1 postgres postgres 16777216 May 12 15:55 00000006000000A C
-rw------- 1 postgres postgres 16777216 May 12 15:55 00000006000000A D
-rw------- 1 postgres postgres 16777216 May 12 15:55 00000006000000A E
-rw------- 1 postgres postgres 16777216 May 12 15:56 00000006000000A F
-rw------- 1 postgres postgres 16777216 May 12 15:56 00000006000000B 0
-rw------- 1 postgres postgres 16777216 May 12 15:56 00000006000000B 1
-rw------- 1 postgres postgres 16777216 May 12 15:56 00000006000000B 2
-rw------- 1 postgres postgres 16777216 May 12 15:56 00000006000000B 3
-rw------- 1 postgres postgres 16777216 May 12 15:56 00000006000000B 4
-rw------- 1 postgres postgres 16777216 May 12 15:57 00000006000000B 5
-rw------- 1 postgres postgres 16777216 May 12 15:57 00000006000000B 6
-rw------- 1 postgres postgres 16777216 May 12 15:57 00000006000000B 7
-rw------- 1 postgres postgres 16777216 May 12 15:57 00000006000000B 8
-rw------- 1 postgres postgres 16777216 May 12 15:57 00000006000000B 9
-rw------- 1 postgres postgres 16777216 May 12 15:57 00000006000000B A
-rw------- 1 postgres postgres 16777216 May 12 15:58 00000006000000B B
-rw------- 1 postgres postgres 16777216 May 12 15:58 00000006000000B C
-rw------- 1 postgres postgres 16777216 May 12 15:58 00000006000000B D
-rw------- 1 postgres postgres 16777216 May 12 15:58 00000006000000B E
-rw------- 1 postgres postgres 16777216 May 12 15:58 00000006000000B F
-rw------- 1 postgres postgres 16777216 May 12 15:58 00000006000000C 0
-rw------- 1 postgres postgres 16777216 May 12 15:58 00000006000000C 1
-rw------- 1 postgres postgres 16777216 May 12 15:59 00000006000000C 2
-rw------- 1 postgres postgres 16777216 May 12 15:59 00000006000000C 3
-rw------- 1 postgres postgres 16777216 May 12 15:59 00000006000000C 4
-rw------- 1 postgres postgres 16777216 May 12 15:59 00000006000000C 5
-rw------- 1 postgres postgres 16777216 May 12 15:59 00000006000000C 6
-rw------- 1 postgres postgres 16777216 May 12 15:59 00000006000000C 7
-rw------- 1 postgres postgres 16777216 May 12 16:00 00000006000000C 8
-rw------- 1 postgres postgres 16777216 May 12 16:00 00000006000000C 9
-rw------- 1 postgres postgres 16777216 May 12 16:00 00000006000000C A
-rw------- 1 postgres postgres 16777216 May 12 16:00 00000006000000C B
-rw------- 1 postgres postgres 16777216 May 12 16:00 00000006000000C C
-rw------- 1 postgres postgres 16777216 May 12 16:01 00000006000000C D
-rw------- 1 postgres postgres 16777216 May 12 16:01 00000006000000C E
-rw------- 1 postgres postgres 16777216 May 12 16:01 00000006000000C F
-rw------- 1 postgres postgres 16777216 May 12 16:01 00000006000000D 0
-rw------- 1 postgres postgres 16777216 May 12 16:01 00000006000000D 1
-rw------- 1 postgres postgres 16777216 May 12 16:01 00000006000000D 2
-rw------- 1 postgres postgres 16777216 May 12 16:02 00000006000000D 3
-rw------- 1 postgres postgres 16777216 May 12 16:02 00000006000000D 4
-rw------- 1 postgres postgres 16777216 May 12 16:02 00000006000000D 5
-rw------- 1 postgres postgres 16777216 May 12 16:02 00000006000000D 6
-rw------- 1 postgres postgres 16777216 May 12 16:02 00000006000000D 7
-rw------- 1 postgres postgres 16777216 May 12 16:03 00000006000000D 8
-rw------- 1 postgres postgres 16777216 May 12 16:03 00000006000000D 9
-rw------- 1 postgres postgres 16777216 May 12 16:03 00000006000000D A
-rw------- 1 postgres postgres 16777216 May 12 16:03 00000006000000D B
-rw------- 1 postgres postgres 16777216 May 12 16:03 00000006000000D C
-rw------- 1 postgres postgres 16777216 May 12 16:03 00000006000000D D
-rw------- 1 postgres postgres 16777216 May 12 16:04 00000006000000D E
-rw------- 1 postgres postgres 16777216 May 12 16:04 00000006000000D F
-rw------- 1 postgres postgres 16777216 May 12 16:04 00000006000000E 0
-rw------- 1 postgres postgres 16777216 May 12 16:04 00000006000000E 1
-rw------- 1 postgres postgres 16777216 May 12 16:04 00000006000000E 2
-rw------- 1 postgres postgres 16777216 May 12 16:04 00000006000000E 3
-rw------- 1 postgres postgres 16777216 May 12 16:05 00000006000000E 4
-rw------- 1 postgres postgres 16777216 May 12 16:05 00000006000000E 5
-rw------- 1 postgres postgres 16777216 May 12 16:05 00000006000000E 6
-rw------- 1 postgres postgres 16777216 May 12 16:05 00000006000000E 7
-rw------- 1 postgres postgres 16777216 May 12 16:05 00000006000000E 8
-rw------- 1 postgres postgres 16777216 May 12 16:06 00000006000000E 9
-rw------- 1 postgres postgres 16777216 May 12 16:06 00000006000000E A
-rw------- 1 postgres postgres 16777216 May 12 16:06 00000006000000E B
-rw------- 1 postgres postgres 16777216 May 12 16:06 00000006000000E C
-rw------- 1 postgres postgres 16777216 May 12 16:07 00000006000000E D
-rw------- 1 postgres postgres 16777216 May 12 16:07 00000006000000E E
-rw------- 1 postgres postgres 16777216 May 12 16:07 00000006000000E F
-rw------- 1 postgres postgres 16777216 May 12 16:07 00000006000000F 0
-rw------- 1 postgres postgres 16777216 May 12 16:07 00000006000000F 1
-rw------- 1 postgres postgres 16777216 May 12 16:08 00000006000000F 2
-rw------- 1 postgres postgres 16777216 May 12 16:08 00000006000000F 3
-rw------- 1 postgres postgres 16777216 May 12 16:08 00000006000000F 4
-rw------- 1 postgres postgres 16777216 May 12 16:08 00000006000000F 5
-rw------- 1 postgres postgres 16777216 May 12 16:08 00000006000000F 6
-rw------- 1 postgres postgres 16777216 May 12 16:09 00000006000000F 7
-rw------- 1 postgres postgres 16777216 May 12 16:09 00000006000000F 8
-rw------- 1 postgres postgres 16777216 May 12 16:09 00000006000000F 9
-rw------- 1 postgres postgres 16777216 May 12 16:09 00000006000000F A
-rw------- 1 postgres postgres 16777216 May 12 16:09 00000006000000F B
-rw------- 1 postgres postgres 16777216 May 12 16:09 00000006000000F C
-rw------- 1 postgres postgres 16777216 May 12 16:10 00000006000000F D
-rw------- 1 postgres postgres 16777216 May 12 16:10 00000006000000F E
-rw------- 1 postgres postgres 16777216 May 12 16:10 000000070000000 0
-rw------- 1 postgres postgres 16777216 May 12 16:10 000000070000000 1
-rw------- 1 postgres postgres 16777216 May 12 16:10 000000070000000 2
-rw------- 1 postgres postgres 16777216 May 12 16:11 000000070000000 3
-rw------- 1 postgres postgres 16777216 May 12 16:11 000000070000000 4
-rw------- 1 postgres postgres 16777216 May 12 16:11 000000070000000 5
-rw------- 1 postgres postgres 16777216 May 12 16:11 000000070000000 6
-rw------- 1 postgres postgres 16777216 May 12 16:12 000000070000000 7
-rw------- 1 postgres postgres 16777216 May 12 16:12 000000070000000 8
-rw------- 1 postgres postgres 16777216 May 12 16:12 000000070000000 9
-rw------- 1 postgres postgres 16777216 May 12 16:12 000000070000000 A
-rw------- 1 postgres postgres 16777216 May 12 16:12 000000070000000 B
-rw------- 1 postgres postgres 16777216 May 12 16:13 000000070000000 C
-rw------- 1 postgres postgres 16777216 May 12 16:13 000000070000000 D
-rw------- 1 postgres postgres 16777216 May 12 16:13 000000070000000 E
-rw------- 1 postgres postgres 16777216 May 12 16:13 000000070000000 F
-rw------- 1 postgres postgres 16777216 May 12 16:13 000000070000001 0
-rw------- 1 postgres postgres 16777216 May 12 16:14 000000070000001 1
-rw------- 1 postgres postgres 16777216 May 12 16:14 000000070000001 2
-rw------- 1 postgres postgres 16777216 May 12 16:14 000000070000001 3
-rw------- 1 postgres postgres 16777216 May 12 16:14 000000070000001 4
-rw------- 1 postgres postgres 16777216 May 12 16:15 000000070000001 5
-rw------- 1 postgres postgres 16777216 May 12 16:15 000000070000001 6
-rw------- 1 postgres postgres 16777216 May 12 16:15 000000070000001 7
-rw------- 1 postgres postgres 16777216 May 12 16:15 000000070000001 8
-rw------- 1 postgres postgres 16777216 May 12 16:15 000000070000001 9
-rw------- 1 postgres postgres 16777216 May 12 16:16 000000070000001 A
-rw------- 1 postgres postgres 16777216 May 12 16:16 000000070000001 B
-rw------- 1 postgres postgres 16777216 May 12 16:16 000000070000001 C
-rw------- 1 postgres postgres 16777216 May 12 16:16 000000070000001 D
-rw------- 1 postgres postgres 16777216 May 12 16:17 000000070000001 E
-rw------- 1 postgres postgres 16777216 May 12 16:17 000000070000001 F
-rw------- 1 postgres postgres 16777216 May 12 16:17 000000070000002 0
-rw------- 1 postgres postgres 16777216 May 12 16:17 000000070000002 1
-rw------- 1 postgres postgres 16777216 May 12 16:18 000000070000002 2
-rw------- 1 postgres postgres 16777216 May 12 16:18 000000070000002 3
-rw------- 1 postgres postgres 16777216 May 12 16:18 000000070000002 4
-rw------- 1 postgres postgres 16777216 May 12 16:18 000000070000002 5
-rw------- 1 postgres postgres 16777216 May 12 16:19 000000070000002 6
-rw------- 1 postgres postgres 16777216 May 12 16:19 000000070000002 7
-rw------- 1 postgres postgres 16777216 May 12 16:19 000000070000002 8
-rw------- 1 postgres postgres 16777216 May 12 16:19 000000070000002 9
-rw------- 1 postgres postgres 16777216 May 12 16:20 000000070000002 A
-rw------- 1 postgres postgres 16777216 May 12 16:20 000000070000002 B
-rw------- 1 postgres postgres 16777216 May 12 16:20 000000070000002 C
-rw------- 1 postgres postgres 16777216 May 12 16:20 000000070000002 D
-rw------- 1 postgres postgres 16777216 May 12 16:20 000000070000002 E
-rw------- 1 postgres postgres 16777216 May 12 16:21 000000070000002 F
-rw------- 1 postgres postgres 16777216 May 12 16:21 000000070000003 0
-rw------- 1 postgres postgres 16777216 May 12 16:21 000000070000003 1
-rw------- 1 postgres postgres 16777216 May 12 16:21 000000070000003 2
-rw------- 1 postgres postgres 16777216 May 12 16:22 000000070000003 3
-rw------- 1 postgres postgres 16777216 May 12 16:22 000000070000003 4
-rw------- 1 postgres postgres 16777216 May 12 16:22 000000070000003 5
-rw------- 1 postgres postgres 16777216 May 12 16:22 000000070000003 6
-rw------- 1 postgres postgres 16777216 May 12 16:23 000000070000003 7
-rw------- 1 postgres postgres 16777216 May 12 16:23 000000070000003 8
-rw------- 1 postgres postgres 16777216 May 12 16:23 000000070000003 9
-rw------- 1 postgres postgres 16777216 May 12 16:23 000000070000003 A
-rw------- 1 postgres postgres 16777216 May 12 16:24 000000070000003 B
-rw------- 1 postgres postgres 16777216 May 12 16:24 000000070000003 C
-rw------- 1 postgres postgres 16777216 May 12 16:24 000000070000003 D
-rw------- 1 postgres postgres 16777216 May 12 16:24 000000070000003 E
-rw------- 1 postgres postgres 16777216 May 12 16:25 000000070000003 F
-rw------- 1 postgres postgres 16777216 May 12 16:25 000000070000004 0
-rw------- 1 postgres postgres 16777216 May 12 16:25 000000070000004 1
-rw------- 1 postgres postgres 16777216 May 12 16:25 000000070000004 2
-rw------- 1 postgres postgres 16777216 May 12 16:26 000000070000004 3
-rw------- 1 postgres postgres 16777216 May 12 16:26 000000070000004 4
-rw------- 1 postgres postgres 16777216 May 12 16:26 000000070000004 5
-rw------- 1 postgres postgres 16777216 May 12 16:26 000000070000004 6
-rw------- 1 postgres postgres 16777216 May 12 16:26 000000070000004 7
-rw------- 1 postgres postgres 16777216 May 12 16:27 000000070000004 8
-rw------- 1 postgres postgres 16777216 May 12 16:27 000000070000004 9
-rw------- 1 postgres postgres 16777216 May 12 16:27 000000070000004 A
-rw------- 1 postgres postgres 16777216 May 12 16:27 000000070000004 B
-rw------- 1 postgres postgres 16777216 May 12 16:28 000000070000004 C
-rw------- 1 postgres postgres 16777216 May 12 16:28 000000070000004 D
-rw------- 1 postgres postgres 16777216 May 12 16:28 000000070000004 E
-rw------- 1 postgres postgres 16777216 May 12 16:28 000000070000004 F
-rw------- 1 postgres postgres 16777216 May 12 16:29 000000070000005 0
-rw------- 1 postgres postgres 16777216 May 12 16:29 000000070000005 1
-rw------- 1 postgres postgres 16777216 May 12 16:29 000000070000005 2
-rw------- 1 postgres postgres 16777216 May 12 16:29 000000070000005 3
-rw------- 1 postgres postgres 16777216 May 12 16:30 000000070000005 4
-rw------- 1 postgres postgres 16777216 May 12 16:30 000000070000005 5
-rw------- 1 postgres postgres 16777216 May 12 16:30 000000070000005 6
-rw------- 1 postgres postgres 16777216 May 12 16:30 000000070000005 7
-rw------- 1 postgres postgres 16777216 May 12 16:31 000000070000005 8
-rw------- 1 postgres postgres 16777216 May 12 16:31 000000070000005 9
-rw------- 1 postgres postgres 16777216 May 12 16:31 000000070000005 A
-rw------- 1 postgres postgres 16777216 May 12 16:32 000000070000005 B
-rw------- 1 postgres postgres 16777216 May 12 16:32 000000070000005 C
-rw------- 1 postgres postgres 16777216 May 12 16:32 000000070000005 D
-rw------- 1 postgres postgres 16777216 May 12 16:32 000000070000005 E
-rw------- 1 postgres postgres 16777216 May 12 16:33 000000070000005 F
-rw------- 1 postgres postgres 16777216 May 12 16:33 000000070000006 0
-rw------- 1 postgres postgres 16777216 May 12 16:33 000000070000006 1
-rw------- 1 postgres postgres 16777216 May 12 16:33 000000070000006 2
-rw------- 1 postgres postgres 16777216 May 12 16:34 000000070000006 3
-rw------- 1 postgres postgres 16777216 May 12 16:34 000000070000006 4
-rw------- 1 postgres postgres 16777216 May 12 16:34 000000070000006 5
-rw------- 1 postgres postgres 16777216 May 12 16:34 000000070000006 6
-rw------- 1 postgres postgres 16777216 May 12 16:34 000000070000006 7
-rw------- 1 postgres postgres 16777216 May 12 16:35 000000070000006 8
-rw------- 1 postgres postgres 16777216 May 12 16:35 000000070000006 9
-rw------- 1 postgres postgres 16777216 May 12 16:35 000000070000006 A
-rw------- 1 postgres postgres 16777216 May 12 16:35 000000070000006 B
-rw------- 1 postgres postgres 16777216 May 12 16:36 000000070000006 C
-rw------- 1 postgres postgres 16777216 May 12 16:36 000000070000006 D
-rw------- 1 postgres postgres 16777216 May 12 16:36 000000070000006 E
-rw------- 1 postgres postgres 16777216 May 12 16:36 000000070000006 F
-rw------- 1 postgres postgres 16777216 May 12 16:45 000000070000007 0

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05

Subscribe Reply

8503

Jeffrey W. Baker

On Thu, 2004-05-13 at 21:00, Tom Lane wrote:

"Jeffrey W. Baker" <jw*****@acm.or g> 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()*10 00000), floor(rand()*10 00000), floor(rand()*10 00000), floor(rand()*10 00000)), "\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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #11

Tom Lane

"Jeffrey W. Baker" <jw*****@acm.or g> writes:

On Thu, 2004-05-13 at 21:00, Tom Lane wrote:
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.

What I wanted to know was whether you see a checkpoint process
persisting throughout the write-the-index phase of the CREATE INDEX.
There probably will be one at pretty nearly all times, but is it
always the same one (same PID)?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #12

Vivek Khera

>>>>> "TL" == Tom Lane <tg*@sss.pgh.pa .us> writes:

TL> However, that still doesn't explain how you got into the current state.
TL> Had you once had checkpoint_segm ents set much higher than the current
TL> value of 24? On looking at the code I see that it doesn't make any
TL> attempt to prune future log segments after a decrease in
TL> checkpoint_segm ents, so if a previous misconfiguratio n had allowed the
TL> number of future segments to get really large, that could be the root of
TL> the issue.

Wow... that explains it!

I bumped up checkpoint segments to 50 for a restore since it made it
run way faster. In normal operation I don't need that many so I
dropped it back down but it didn't reclaim any space so I figured I
might as well keep it at 50...
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.c om Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #13

Tom Lane

Vivek Khera <kh***@kcilink. com> writes:

"TL" == Tom Lane <tg*@sss.pgh.pa .us> writes:
TL> ... On looking at the code I see that it doesn't make any
TL> attempt to prune future log segments after a decrease in
TL> checkpoint_segm ents, so if a previous misconfiguratio n had allowed the
TL> number of future segments to get really large, that could be the root of
TL> the issue. Wow... that explains it! I bumped up checkpoint segments to 50 for a restore since it made it
run way faster. In normal operation I don't need that many so I
dropped it back down but it didn't reclaim any space so I figured I
might as well keep it at 50...

How long did you wait? I believe the code will prune excess segments as
they come around to be recycled. It just doesn't kill them immediately.

I think that part of what's going on in Jeff's example is that he's
looking at the state immediately after a spike in database traffic, and
not having any patience to see if the system will recover after some
period with more-normal traffic levels.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #14

Tom Lane

I wrote:

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,

I have to take that back: there definitely is a locking problem.
Perhaps there is an I/O bandwidth issue too.

What I see happening on closer analysis is that btree CREATE INDEX can
hold "exclusive context lock" on some shared buffers for significant
periods of time. It tries to write all the levels of the btree in
parallel, so it is spitting out level-zero pages at a great rate,
level-one pages at a lesser rate, etc. For a large index there could
be many btree levels, and pages in the higher levels will be held locked
in the shared buffer arena for considerable periods.

CHECKPOINT scans the shared buffer arena and tries to write every dirty
page it finds. This requires getting shared context lock, and so will
be blocked by the lock CREATE INDEX is holding.

I am toying with the idea that CREATE INDEX shouldn't use the shared
buffer manager at all; there is no need for other backends to touch the
index until the creating transaction commits. We'd need to be able to
fsync the index file before committing. That isn't part of the smgr API
right now, but could easily be added.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #15

Alvaro Herrera

On Sat, May 15, 2004 at 12:23:18AM -0400, Tom Lane wrote:

What I see happening on closer analysis is that btree CREATE INDEX can
hold "exclusive context lock" on some shared buffers for significant
periods of time. It tries to write all the levels of the btree in
parallel, so it is spitting out level-zero pages at a great rate,
level-one pages at a lesser rate, etc. For a large index there could
be many btree levels, and pages in the higher levels will be held locked
in the shared buffer arena for considerable periods.

I'm not sure how btree from-scratch-building work, but could it be made
so that it first build level 0 completely, scanning the heap; then build
level 1 scanning the level 0, and so on?

I also wonder why index creation would write XLog information; it won't
be used to recreate the tree if the machine goes down while the index is
created. If you plan on not using the shared bufmgr and issuing a sync
before comit, you could get rid of logging during build ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Investigac ión es lo que hago cuando no sé lo que estoy haciendo"
(Wernher von Braun)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #16

Tom Lane

Alvaro Herrera <al******@dcc.u chile.cl> writes:

On Sat, May 15, 2004 at 12:23:18AM -0400, Tom Lane wrote:
... It tries to write all the levels of the btree in
parallel, so it is spitting out level-zero pages at a great rate,
level-one pages at a lesser rate, etc. For a large index there could
be many btree levels, and pages in the higher levels will be held locked
in the shared buffer arena for considerable periods.
I'm not sure how btree from-scratch-building work, but could it be made
so that it first build level 0 completely, scanning the heap; then build
level 1 scanning the level 0, and so on?
I don't think that would be a win; it would require an extra read pass
over nearly all of the index. The idea here is to reduce the amount of
I/O, not increase it.
I also wonder why index creation would write XLog information;

Yeah, that was implicit in my comment about not using the buffer
manager, and it was why I was concerned about being able to fsync
the index file. Without xlog for backup, you'd better be able to
force the index to disk before you commit.

It turns out that btree index build currently writes each page three
times: once to extend the file, once into WAL, and then finally to write
the completed index page. It is difficult to do any better as long as
you're using the shared buffer manager. (When extending a file, the
buffer manager wants to allocate disk space before it will give you a
buffer to write into. This is the right thing for heap insertions and
extending an existing index, because we don't force buffers to disk
before commit. So if it didn't work that way, you might commit your
transaction before finding out there's no disk space to hold the data.
But we could dispense with that worry for index build if we ensure that
we write and fsync all the pages before commit.) So I was thinking
about keeping the same index build process, but working in private
storage instead of shared buffers, and writing direct to smgr. This
would not only eliminate the locking conflict against checkpoints,
but reduce the amount of I/O involved quite a lot.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 23 '05 #17

Jeffrey W. Baker

On Fri, 2004-05-14 at 20:55, Tom Lane wrote:

Vivek Khera <kh***@kcilink. com> writes:
"TL" == Tom Lane <tg*@sss.pgh.pa .us> writes:
TL> ... On looking at the code I see that it doesn't make any
TL> attempt to prune future log segments after a decrease in
TL> checkpoint_segm ents, so if a previous misconfiguratio n had allowed the
TL> number of future segments to get really large, that could be the root of
TL> the issue.

Wow... that explains it!

I bumped up checkpoint segments to 50 for a restore since it made it
run way faster. In normal operation I don't need that many so I
dropped it back down but it didn't reclaim any space so I figured I
might as well keep it at 50...

How long did you wait? I believe the code will prune excess segments as
they come around to be recycled. It just doesn't kill them immediately.

I think that part of what's going on in Jeff's example is that he's
looking at the state immediately after a spike in database traffic, and
not having any patience to see if the system will recover after some
period with more-normal traffic levels.

It won't ever return to normal levels after the device fills up, the
server panics, and all the databases are inconsistent.

-jwb

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 23 '05 #18

Similar topics

27216

CREATE INDEX on large table

by: dfurtney | last post by:

SQL Server 7/2000: We have reasonably large tables (3,000,000 rows) that we need to add some indexes for. In a test, it took over 12 hours to CREATE a new INDEX against this table. One of us suggested that we create a temp table with the new index and copy the data from the old table into the new one, then rename it. I understand this took 15 minutes. Why the heck would it be faster to move the data and build multiple indexes...

Microsoft SQL Server

3181

VACUUM degrades performance significantly. Database becomes unusable!

by: Stephen | last post by:

Hello, Is it normal for plain VACUUM on large table to degrade performance by over 9 times? My database becomes unusable when VACUUM runs. From reading newsgroups, I thought VACUUM should only slow down by 10% to 15%. Other MVCC databases like MySQL InnoDB can even VACUUM discretely (runs internally). Is it my Linux system or is it PostgreSQL? The database is mostly read-only. There are 133,000 rows and each row is about 2.5kB in size...

PostgreSQL Database

5969

website doc search is extremely SLOW

by: D. Dante Lorenso | last post by:

Trying to use the 'search' in the docs section of PostgreSQL.org is extremely SLOW. Considering this is a website for a database and databases are supposed to be good for indexing content, I'd expect a much faster performance. I submitted my search over two minutes ago. I just finished this email to the list. The results have still not come back. I only searched for: SECURITY INVOKER

PostgreSQL Database

5610

Large object insert/update and oid use

by: Louis LeBlanc | last post by:

Hey folks. I'm new to the list, and not quite what you'd call a DB Guru, so please be patient with me. I'm afraid the lead up here is a bit verbose . . . I am working on an application that uses very high volume DB transactions - in the order of tens of millions per day . . . Anyway, the current database which will remain nameless, but begins with O and rymes with debacle (sorta), has a problem with high volume work when it comes to...

PostgreSQL Database

2979

lost pg_xlog // recover database-structure without it?

by: Dennis Breithaupt | last post by:

Hy all, I request your support for the following problem. (with "postmaster (PostgreSQL) 7.4.1") On a development-system I lost the pg_xlog-directory due to a system crash, where the physical device on which the mountpoint pg_xlog was located got broken. LOG: could not open file

PostgreSQL Database

9012

pg_clog and pg_xlog empty, postgresql refuses to start

by: Glen Parker | last post by:

First things first: Postgresql 8.4.2 on Fedora Core 2 X86. Something seems to have happened to my pg_xlog and pg_clog directories after (I believe) a power outage. In the course of trying to figure out why the server wouldn't start, I cleaned out pg_clog and pg_xlog, in an obviously vain attempt to reset things. I was under the impression that the server could start without those files. Now I seem to be stuck. So now what?

PostgreSQL Database

8067

pg_xlog

by: Reynard Hilman | last post by:

Hi, Is there a way to translate information in pg_xlog files to a more readable format? Basically we have someone accidentally emptied a text column containing quite a large amount of text, and unfortunately have no backups. I know the pg_xlog is for WAL, and probably is not the proper way to restore a lost data. but I find some of the text in one of the files in pg_xlog directory. I can get the text part but there are also non text...

PostgreSQL Database

2460

Dealing with large text files

by: =?Utf-8?B?RENX?= | last post by:

Hello all: I have a situation where I need to read a text file containing several million rows (insurance eligibility files). In additional to sequential operations, I also need to support a 'seek' on the file. The file itself is not in a fixed-field format and each line could be different lengths. I obviously don't want to simply start at the top of the file and read lines till I hit the requested index. What other options do I...

C# / C Sharp

9589

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9423

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10222

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9866

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

8876

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

5310

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

3967

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3570

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2815

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General