Search Postgresql Archives

pg_xlog becomes extremely large during CREATE INDEX

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 0000000600000092
-rw-------    1 postgres postgres 16777216 May 12 15:51 0000000600000093
-rw-------    1 postgres postgres 16777216 May 12 15:51 0000000600000094
-rw-------    1 postgres postgres 16777216 May 12 15:51 0000000600000095
-rw-------    1 postgres postgres 16777216 May 12 15:52 0000000600000096
-rw-------    1 postgres postgres 16777216 May 12 15:52 0000000600000097
-rw-------    1 postgres postgres 16777216 May 12 15:52 0000000600000098
-rw-------    1 postgres postgres 16777216 May 12 15:52 0000000600000099
-rw-------    1 postgres postgres 16777216 May 12 15:52 000000060000009A
-rw-------    1 postgres postgres 16777216 May 12 15:52 000000060000009B
-rw-------    1 postgres postgres 16777216 May 12 15:53 000000060000009C
-rw-------    1 postgres postgres 16777216 May 12 15:53 000000060000009D
-rw-------    1 postgres postgres 16777216 May 12 15:53 000000060000009E
-rw-------    1 postgres postgres 16777216 May 12 15:53 000000060000009F
-rw-------    1 postgres postgres 16777216 May 12 15:53 00000006000000A0
-rw-------    1 postgres postgres 16777216 May 12 15:53 00000006000000A1
-rw-------    1 postgres postgres 16777216 May 12 15:54 00000006000000A2
-rw-------    1 postgres postgres 16777216 May 12 15:54 00000006000000A3
-rw-------    1 postgres postgres 16777216 May 12 15:54 00000006000000A4
-rw-------    1 postgres postgres 16777216 May 12 15:54 00000006000000A5
-rw-------    1 postgres postgres 16777216 May 12 15:54 00000006000000A6
-rw-------    1 postgres postgres 16777216 May 12 15:54 00000006000000A7
-rw-------    1 postgres postgres 16777216 May 12 15:54 00000006000000A8
-rw-------    1 postgres postgres 16777216 May 12 15:55 00000006000000A9
-rw-------    1 postgres postgres 16777216 May 12 15:55 00000006000000AA
-rw-------    1 postgres postgres 16777216 May 12 15:55 00000006000000AB
-rw-------    1 postgres postgres 16777216 May 12 15:55 00000006000000AC
-rw-------    1 postgres postgres 16777216 May 12 15:55 00000006000000AD
-rw-------    1 postgres postgres 16777216 May 12 15:55 00000006000000AE
-rw-------    1 postgres postgres 16777216 May 12 15:56 00000006000000AF
-rw-------    1 postgres postgres 16777216 May 12 15:56 00000006000000B0
-rw-------    1 postgres postgres 16777216 May 12 15:56 00000006000000B1
-rw-------    1 postgres postgres 16777216 May 12 15:56 00000006000000B2
-rw-------    1 postgres postgres 16777216 May 12 15:56 00000006000000B3
-rw-------    1 postgres postgres 16777216 May 12 15:56 00000006000000B4
-rw-------    1 postgres postgres 16777216 May 12 15:57 00000006000000B5
-rw-------    1 postgres postgres 16777216 May 12 15:57 00000006000000B6
-rw-------    1 postgres postgres 16777216 May 12 15:57 00000006000000B7
-rw-------    1 postgres postgres 16777216 May 12 15:57 00000006000000B8
-rw-------    1 postgres postgres 16777216 May 12 15:57 00000006000000B9
-rw-------    1 postgres postgres 16777216 May 12 15:57 00000006000000BA
-rw-------    1 postgres postgres 16777216 May 12 15:58 00000006000000BB
-rw-------    1 postgres postgres 16777216 May 12 15:58 00000006000000BC
-rw-------    1 postgres postgres 16777216 May 12 15:58 00000006000000BD
-rw-------    1 postgres postgres 16777216 May 12 15:58 00000006000000BE
-rw-------    1 postgres postgres 16777216 May 12 15:58 00000006000000BF
-rw-------    1 postgres postgres 16777216 May 12 15:58 00000006000000C0
-rw-------    1 postgres postgres 16777216 May 12 15:58 00000006000000C1
-rw-------    1 postgres postgres 16777216 May 12 15:59 00000006000000C2
-rw-------    1 postgres postgres 16777216 May 12 15:59 00000006000000C3
-rw-------    1 postgres postgres 16777216 May 12 15:59 00000006000000C4
-rw-------    1 postgres postgres 16777216 May 12 15:59 00000006000000C5
-rw-------    1 postgres postgres 16777216 May 12 15:59 00000006000000C6
-rw-------    1 postgres postgres 16777216 May 12 15:59 00000006000000C7
-rw-------    1 postgres postgres 16777216 May 12 16:00 00000006000000C8
-rw-------    1 postgres postgres 16777216 May 12 16:00 00000006000000C9
-rw-------    1 postgres postgres 16777216 May 12 16:00 00000006000000CA
-rw-------    1 postgres postgres 16777216 May 12 16:00 00000006000000CB
-rw-------    1 postgres postgres 16777216 May 12 16:00 00000006000000CC
-rw-------    1 postgres postgres 16777216 May 12 16:01 00000006000000CD
-rw-------    1 postgres postgres 16777216 May 12 16:01 00000006000000CE
-rw-------    1 postgres postgres 16777216 May 12 16:01 00000006000000CF
-rw-------    1 postgres postgres 16777216 May 12 16:01 00000006000000D0
-rw-------    1 postgres postgres 16777216 May 12 16:01 00000006000000D1
-rw-------    1 postgres postgres 16777216 May 12 16:01 00000006000000D2
-rw-------    1 postgres postgres 16777216 May 12 16:02 00000006000000D3
-rw-------    1 postgres postgres 16777216 May 12 16:02 00000006000000D4
-rw-------    1 postgres postgres 16777216 May 12 16:02 00000006000000D5
-rw-------    1 postgres postgres 16777216 May 12 16:02 00000006000000D6
-rw-------    1 postgres postgres 16777216 May 12 16:02 00000006000000D7
-rw-------    1 postgres postgres 16777216 May 12 16:03 00000006000000D8
-rw-------    1 postgres postgres 16777216 May 12 16:03 00000006000000D9
-rw-------    1 postgres postgres 16777216 May 12 16:03 00000006000000DA
-rw-------    1 postgres postgres 16777216 May 12 16:03 00000006000000DB
-rw-------    1 postgres postgres 16777216 May 12 16:03 00000006000000DC
-rw-------    1 postgres postgres 16777216 May 12 16:03 00000006000000DD
-rw-------    1 postgres postgres 16777216 May 12 16:04 00000006000000DE
-rw-------    1 postgres postgres 16777216 May 12 16:04 00000006000000DF
-rw-------    1 postgres postgres 16777216 May 12 16:04 00000006000000E0
-rw-------    1 postgres postgres 16777216 May 12 16:04 00000006000000E1
-rw-------    1 postgres postgres 16777216 May 12 16:04 00000006000000E2
-rw-------    1 postgres postgres 16777216 May 12 16:04 00000006000000E3
-rw-------    1 postgres postgres 16777216 May 12 16:05 00000006000000E4
-rw-------    1 postgres postgres 16777216 May 12 16:05 00000006000000E5
-rw-------    1 postgres postgres 16777216 May 12 16:05 00000006000000E6
-rw-------    1 postgres postgres 16777216 May 12 16:05 00000006000000E7
-rw-------    1 postgres postgres 16777216 May 12 16:05 00000006000000E8
-rw-------    1 postgres postgres 16777216 May 12 16:06 00000006000000E9
-rw-------    1 postgres postgres 16777216 May 12 16:06 00000006000000EA
-rw-------    1 postgres postgres 16777216 May 12 16:06 00000006000000EB
-rw-------    1 postgres postgres 16777216 May 12 16:06 00000006000000EC
-rw-------    1 postgres postgres 16777216 May 12 16:07 00000006000000ED
-rw-------    1 postgres postgres 16777216 May 12 16:07 00000006000000EE
-rw-------    1 postgres postgres 16777216 May 12 16:07 00000006000000EF
-rw-------    1 postgres postgres 16777216 May 12 16:07 00000006000000F0
-rw-------    1 postgres postgres 16777216 May 12 16:07 00000006000000F1
-rw-------    1 postgres postgres 16777216 May 12 16:08 00000006000000F2
-rw-------    1 postgres postgres 16777216 May 12 16:08 00000006000000F3
-rw-------    1 postgres postgres 16777216 May 12 16:08 00000006000000F4
-rw-------    1 postgres postgres 16777216 May 12 16:08 00000006000000F5
-rw-------    1 postgres postgres 16777216 May 12 16:08 00000006000000F6
-rw-------    1 postgres postgres 16777216 May 12 16:09 00000006000000F7
-rw-------    1 postgres postgres 16777216 May 12 16:09 00000006000000F8
-rw-------    1 postgres postgres 16777216 May 12 16:09 00000006000000F9
-rw-------    1 postgres postgres 16777216 May 12 16:09 00000006000000FA
-rw-------    1 postgres postgres 16777216 May 12 16:09 00000006000000FB
-rw-------    1 postgres postgres 16777216 May 12 16:09 00000006000000FC
-rw-------    1 postgres postgres 16777216 May 12 16:10 00000006000000FD
-rw-------    1 postgres postgres 16777216 May 12 16:10 00000006000000FE
-rw-------    1 postgres postgres 16777216 May 12 16:10 0000000700000000
-rw-------    1 postgres postgres 16777216 May 12 16:10 0000000700000001
-rw-------    1 postgres postgres 16777216 May 12 16:10 0000000700000002
-rw-------    1 postgres postgres 16777216 May 12 16:11 0000000700000003
-rw-------    1 postgres postgres 16777216 May 12 16:11 0000000700000004
-rw-------    1 postgres postgres 16777216 May 12 16:11 0000000700000005
-rw-------    1 postgres postgres 16777216 May 12 16:11 0000000700000006
-rw-------    1 postgres postgres 16777216 May 12 16:12 0000000700000007
-rw-------    1 postgres postgres 16777216 May 12 16:12 0000000700000008
-rw-------    1 postgres postgres 16777216 May 12 16:12 0000000700000009
-rw-------    1 postgres postgres 16777216 May 12 16:12 000000070000000A
-rw-------    1 postgres postgres 16777216 May 12 16:12 000000070000000B
-rw-------    1 postgres postgres 16777216 May 12 16:13 000000070000000C
-rw-------    1 postgres postgres 16777216 May 12 16:13 000000070000000D
-rw-------    1 postgres postgres 16777216 May 12 16:13 000000070000000E
-rw-------    1 postgres postgres 16777216 May 12 16:13 000000070000000F
-rw-------    1 postgres postgres 16777216 May 12 16:13 0000000700000010
-rw-------    1 postgres postgres 16777216 May 12 16:14 0000000700000011
-rw-------    1 postgres postgres 16777216 May 12 16:14 0000000700000012
-rw-------    1 postgres postgres 16777216 May 12 16:14 0000000700000013
-rw-------    1 postgres postgres 16777216 May 12 16:14 0000000700000014
-rw-------    1 postgres postgres 16777216 May 12 16:15 0000000700000015
-rw-------    1 postgres postgres 16777216 May 12 16:15 0000000700000016
-rw-------    1 postgres postgres 16777216 May 12 16:15 0000000700000017
-rw-------    1 postgres postgres 16777216 May 12 16:15 0000000700000018
-rw-------    1 postgres postgres 16777216 May 12 16:15 0000000700000019
-rw-------    1 postgres postgres 16777216 May 12 16:16 000000070000001A
-rw-------    1 postgres postgres 16777216 May 12 16:16 000000070000001B
-rw-------    1 postgres postgres 16777216 May 12 16:16 000000070000001C
-rw-------    1 postgres postgres 16777216 May 12 16:16 000000070000001D
-rw-------    1 postgres postgres 16777216 May 12 16:17 000000070000001E
-rw-------    1 postgres postgres 16777216 May 12 16:17 000000070000001F
-rw-------    1 postgres postgres 16777216 May 12 16:17 0000000700000020
-rw-------    1 postgres postgres 16777216 May 12 16:17 0000000700000021
-rw-------    1 postgres postgres 16777216 May 12 16:18 0000000700000022
-rw-------    1 postgres postgres 16777216 May 12 16:18 0000000700000023
-rw-------    1 postgres postgres 16777216 May 12 16:18 0000000700000024
-rw-------    1 postgres postgres 16777216 May 12 16:18 0000000700000025
-rw-------    1 postgres postgres 16777216 May 12 16:19 0000000700000026
-rw-------    1 postgres postgres 16777216 May 12 16:19 0000000700000027
-rw-------    1 postgres postgres 16777216 May 12 16:19 0000000700000028
-rw-------    1 postgres postgres 16777216 May 12 16:19 0000000700000029
-rw-------    1 postgres postgres 16777216 May 12 16:20 000000070000002A
-rw-------    1 postgres postgres 16777216 May 12 16:20 000000070000002B
-rw-------    1 postgres postgres 16777216 May 12 16:20 000000070000002C
-rw-------    1 postgres postgres 16777216 May 12 16:20 000000070000002D
-rw-------    1 postgres postgres 16777216 May 12 16:20 000000070000002E
-rw-------    1 postgres postgres 16777216 May 12 16:21 000000070000002F
-rw-------    1 postgres postgres 16777216 May 12 16:21 0000000700000030
-rw-------    1 postgres postgres 16777216 May 12 16:21 0000000700000031
-rw-------    1 postgres postgres 16777216 May 12 16:21 0000000700000032
-rw-------    1 postgres postgres 16777216 May 12 16:22 0000000700000033
-rw-------    1 postgres postgres 16777216 May 12 16:22 0000000700000034
-rw-------    1 postgres postgres 16777216 May 12 16:22 0000000700000035
-rw-------    1 postgres postgres 16777216 May 12 16:22 0000000700000036
-rw-------    1 postgres postgres 16777216 May 12 16:23 0000000700000037
-rw-------    1 postgres postgres 16777216 May 12 16:23 0000000700000038
-rw-------    1 postgres postgres 16777216 May 12 16:23 0000000700000039
-rw-------    1 postgres postgres 16777216 May 12 16:23 000000070000003A
-rw-------    1 postgres postgres 16777216 May 12 16:24 000000070000003B
-rw-------    1 postgres postgres 16777216 May 12 16:24 000000070000003C
-rw-------    1 postgres postgres 16777216 May 12 16:24 000000070000003D
-rw-------    1 postgres postgres 16777216 May 12 16:24 000000070000003E
-rw-------    1 postgres postgres 16777216 May 12 16:25 000000070000003F
-rw-------    1 postgres postgres 16777216 May 12 16:25 0000000700000040
-rw-------    1 postgres postgres 16777216 May 12 16:25 0000000700000041
-rw-------    1 postgres postgres 16777216 May 12 16:25 0000000700000042
-rw-------    1 postgres postgres 16777216 May 12 16:26 0000000700000043
-rw-------    1 postgres postgres 16777216 May 12 16:26 0000000700000044
-rw-------    1 postgres postgres 16777216 May 12 16:26 0000000700000045
-rw-------    1 postgres postgres 16777216 May 12 16:26 0000000700000046
-rw-------    1 postgres postgres 16777216 May 12 16:26 0000000700000047
-rw-------    1 postgres postgres 16777216 May 12 16:27 0000000700000048
-rw-------    1 postgres postgres 16777216 May 12 16:27 0000000700000049
-rw-------    1 postgres postgres 16777216 May 12 16:27 000000070000004A
-rw-------    1 postgres postgres 16777216 May 12 16:27 000000070000004B
-rw-------    1 postgres postgres 16777216 May 12 16:28 000000070000004C
-rw-------    1 postgres postgres 16777216 May 12 16:28 000000070000004D
-rw-------    1 postgres postgres 16777216 May 12 16:28 000000070000004E
-rw-------    1 postgres postgres 16777216 May 12 16:28 000000070000004F
-rw-------    1 postgres postgres 16777216 May 12 16:29 0000000700000050
-rw-------    1 postgres postgres 16777216 May 12 16:29 0000000700000051
-rw-------    1 postgres postgres 16777216 May 12 16:29 0000000700000052
-rw-------    1 postgres postgres 16777216 May 12 16:29 0000000700000053
-rw-------    1 postgres postgres 16777216 May 12 16:30 0000000700000054
-rw-------    1 postgres postgres 16777216 May 12 16:30 0000000700000055
-rw-------    1 postgres postgres 16777216 May 12 16:30 0000000700000056
-rw-------    1 postgres postgres 16777216 May 12 16:30 0000000700000057
-rw-------    1 postgres postgres 16777216 May 12 16:31 0000000700000058
-rw-------    1 postgres postgres 16777216 May 12 16:31 0000000700000059
-rw-------    1 postgres postgres 16777216 May 12 16:31 000000070000005A
-rw-------    1 postgres postgres 16777216 May 12 16:32 000000070000005B
-rw-------    1 postgres postgres 16777216 May 12 16:32 000000070000005C
-rw-------    1 postgres postgres 16777216 May 12 16:32 000000070000005D
-rw-------    1 postgres postgres 16777216 May 12 16:32 000000070000005E
-rw-------    1 postgres postgres 16777216 May 12 16:33 000000070000005F
-rw-------    1 postgres postgres 16777216 May 12 16:33 0000000700000060
-rw-------    1 postgres postgres 16777216 May 12 16:33 0000000700000061
-rw-------    1 postgres postgres 16777216 May 12 16:33 0000000700000062
-rw-------    1 postgres postgres 16777216 May 12 16:34 0000000700000063
-rw-------    1 postgres postgres 16777216 May 12 16:34 0000000700000064
-rw-------    1 postgres postgres 16777216 May 12 16:34 0000000700000065
-rw-------    1 postgres postgres 16777216 May 12 16:34 0000000700000066
-rw-------    1 postgres postgres 16777216 May 12 16:34 0000000700000067
-rw-------    1 postgres postgres 16777216 May 12 16:35 0000000700000068
-rw-------    1 postgres postgres 16777216 May 12 16:35 0000000700000069
-rw-------    1 postgres postgres 16777216 May 12 16:35 000000070000006A
-rw-------    1 postgres postgres 16777216 May 12 16:35 000000070000006B
-rw-------    1 postgres postgres 16777216 May 12 16:36 000000070000006C
-rw-------    1 postgres postgres 16777216 May 12 16:36 000000070000006D
-rw-------    1 postgres postgres 16777216 May 12 16:36 000000070000006E
-rw-------    1 postgres postgres 16777216 May 12 16:36 000000070000006F
-rw-------    1 postgres postgres 16777216 May 12 16:45 0000000700000070



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux