segfault when creating index on huge table

From: Yiqing Jin <yiqing(dot)jin(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: segfault when creating index on huge table
Date: 2015-07-16 17:29:27
Message-ID: CAL8HZuNR2FR1owZHWG-p64GJtNfbbmPx1Y2OXmJ_XuQ3P8YtVA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

I'm trapped with a problem when trying to create an index on a table with
about 2.8 billion records in it.

ALTER TABLE ONLY nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id);
or
create index pknode on nodes(id);

Postgres terminated with segfault during the proocess.

I'm trying to build index after all data loaded to make the whole process
faster, same operation worked on a 300 million records table.

I'm using postgres 9.4.4 with postGIS extension 2.1.8

postgresql.conf:

max_connections = 50# (change requires restart)
shared_buffers = 192000MB
work_mem = 512MB
maintenance_work_mem = 128000MB
dynamic_shared_memory_type = posix
fsync = off
checkpoint_segments = 128
checkpoint_completion_target = 0.9
effective_cache_size = 500GB
default_statistics_target = 500

Hardware:
72 core cpu
755G memory

message from /var/log

2015-07-15T21:40:54.457538-07:00 rd12d02ls-osmdb0002 kernel:
postgres[62626]: segfault at 7f31613f103c ip 0000000000785180 sp
00007fffe2a41cb0 error 4 in postgres[400000+561000]

From pg_log:
< 2015-07-15 21:47:12.447 PDT >LOG: server process (PID 62626) was
terminated by signal 11: Segmentation fault
< 2015-07-15 21:47:12.447 PDT >DETAIL: Failed process was running: create
index pknode on nodes(id);
< 2015-07-15 21:47:12.447 PDT >LOG: terminating any other active server
processes
< 2015-07-15 21:47:12.447 PDT >WARNING: terminating connection because of
crash of another server process
< 2015-07-15 21:47:12.447 PDT >DETAIL: The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
< 2015-07-15 21:47:12.447 PDT >HINT: In a moment you should be able to
reconnect to the database and repeat your command.
< 2015-07-15 21:47:12.501 PDT >LOG: all server processes terminated;
reinitializing
< 2015-07-15 21:47:45.917 PDT >LOG: database system was interrupted; last
known up at 2015-07-15 21:34:41 PDT
< 2015-07-15 21:47:45.948 PDT >LOG: database system was not properly shut
down; automatic recovery in progress
< 2015-07-15 21:47:45.965 PDT >LOG: record with zero length at 1A7/C653FAA8
< 2015-07-15 21:47:45.965 PDT >LOG: redo is not required
< 2015-07-15 21:47:46.783 PDT >LOG: autovacuum launcher started
< 2015-07-15 21:47:46.783 PDT >LOG: database system is ready to accept
connections

I'm new to postgres and is really scratching my head don't know how to
control the memory the indexing process uses. Would really appreciate it
if anyone could point me with some directions, is this a configuration
problem I have for my instance?

Thanks
Yiqing

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Natalie Wenz 2015-07-16 18:07:02 postgres_fdw user mapping and role inheritance
Previous Message Aitor Gil Martin 2015-07-16 07:14:59 Installing odbc_fdw in Postgresql 9.3.5 in Windows