Thread: CREATE INDEX failing; No space left on device; Database recycling itself
CREATE INDEX failing; No space left on device; Database recycling itself
I was trying to create an index on a 37,000,000 row table and received the following error. Evidently I don’t have enough space in my pg_xlog directory to handle this as a single transaction. The file system for pg_xlog is allocated 2GB. The following output is from a psql session directly on the database server.
dw=# CREATE INDEX app_log_username ON ods.applog USING btree (username, application,
dw(# app_function, app_function_parm, access_date);
PANIC: could not write to file "/pgsdata01/dw/pg_xlog/xlogtemp.6021": No space left on device
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The following errors also showed up. So my question is why does the failure of CREATE INDEX cause all my connections to be terminated and the database to go into recovery mode? Sounds kind of drastic.
Feb 17 14:07:51 ybcdrdwdb01 postgres[6021]: [361-1] PANIC: could not write to file "/pgsdata01/dw/pg_xlog/xlogtemp.6021": No space l
eft on device
Feb 17 14:07:51 ybcdrdwdb01 postgres[6021]: [361-2] STATEMENT: CREATE INDEX app_log_username ON ods.applog USING btree (username, ap
plication,
Feb 17 14:07:51 ybcdrdwdb01 postgres[6021]: [361-3] app_function, app_function_parm, access_date);
Feb 17 14:07:51 ybcdrdwdb01 postgres[18735]: [474-1] LOG: server process (PID 6021) was terminated by signal 6
Feb 17 14:07:51 ybcdrdwdb01 postgres[18735]: [475-1] LOG: terminating any other active server processes.
.
.
. some lines not included
.
.
Feb 17 14:07:51 ybcdrdwdb01 postgres[18735]: [476-1] LOG: all server processes terminated; reinitializing
Feb 17 14:07:51 ybcdrdwdb01 postgres[6116]: [477-1] LOG: database system was interrupted at 2005-02-17 14:07:49 CST
Feb 17 14:07:51 ybcdrdwdb01 postgres[6116]: [478-1] LOG: checkpoint record is at 31/2EB0B974
Feb 17 14:07:51 ybcdrdwdb01 postgres[6116]: [479-1] LOG: redo record is at 31/2EB0B974; undo record is at 0/0; shutdown FALSE
Feb 17 14:07:51 ybcdrdwdb01 postgres[6116]: [480-1] LOG: next transaction ID: 39819973; next OID: 39655490
Feb 17 14:07:51 ybcdrdwdb01 postgres[6116]: [481-1] LOG: database system was not properly shut down; automatic recovery in progress
Feb 17 14:07:52 ybcdrdwdb01 postgres[6116]: [482-1] LOG: redo starts at 31/2EB0B9B4
Feb 17 14:08:24 ybcdrdwdb01 postgres[6116]: [483-1] LOG: could not open file "/pgsdata01/dw/pg_xlog/0000003100000078" (log file 49,
segment 120): No such file or directory
Feb 17 14:08:24 ybcdrdwdb01 postgres[6116]: [484-1] LOG: redo done at 31/77FFDD44
Version is PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
I am also wondering why CREATE INDEX has to generate so much activity in the log. The recovery of a corrupted index would be to send the CREATE INDEX command back to the database.
Finally can someone give me a calculation that would tell me how much log space the CREATE INDEX command is going to take? I still need to get the index created with the system I have.
"Sailer, Denis (YBUSA-CDR)" <Denis.Sailer@Yellowbook.com> writes:
> The following errors also showed up. So my question is why does the
> failure of CREATE INDEX cause all my connections to be terminated and
> the database to go into recovery mode? Sounds kind of drastic.
Not having any xlog space is fatal across-the-board: there is no place
to log anybody's transactions, not just the CREATE INDEX.
However, with reasonable checkpointing parameters this shouldn't happen
because checkpoints will occur frequently enough to reclaim the xlog
space. I believe you got bit by the 7.4 problem that allows checkpoints
to get blocked by CREATE INDEX operations. This is fixed in 8.0:
2004-06-02 13:28 tgl
* src/: backend/access/nbtree/nbtpage.c,
backend/access/nbtree/nbtree.c, backend/access/nbtree/nbtsort.c,
backend/access/nbtree/nbtxlog.c, backend/storage/smgr/md.c,
backend/storage/smgr/smgr.c, include/access/nbtree.h,
include/storage/smgr.h: Adjust btree index build to not use shared
buffers, thereby avoiding the locking conflict against concurrent
CHECKPOINT that was discussed a few weeks ago. Also, if not using
WAL archiving (which is always true ATM but won't be if PITR makes
it into this release), there's no need to WAL-log the index build
process; it's sufficient to force-fsync the completed index before
commit. This seems to gain about a factor of 2 in my tests, which
is consistent with writing half as much data. I did not try it
with WAL on a separate drive though --- probably the gain would be
a lot less in that scenario.
> Finally can someone give me a calculation that would tell me how much
> log space the CREATE INDEX command is going to take?
About as much as the finished index will occupy, which you did not give
enough information to estimate.
> I still need to
> get the index created with the system I have.
You might consider updating to 8.0 instead...
regards, tom lane