pg_xlog becomes extremely large during CREATE INDEX
| От | Jeffrey W. Baker |
|---|---|
| Тема | pg_xlog becomes extremely large during CREATE INDEX |
| Дата | |
| Msg-id | 1084411119.3934.7.camel@heat обсуждение исходный текст |
| Ответы |
Re: pg_xlog becomes extremely large during CREATE INDEX
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | 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 по дате отправления: