pg_xlog becomes extremely large during CREATE INDEX - Mailing list pgsql-general
| From | Jeffrey W. Baker |
|---|---|
| Subject | pg_xlog becomes extremely large during CREATE INDEX |
| Date | |
| Msg-id | 1084411119.3934.7.camel@heat Whole thread Raw |
| Responses |
Re: pg_xlog becomes extremely large during CREATE INDEX
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-general |
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
pgsql-general by date: