Обсуждение: segfault when creating index on huge table
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
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
Yiqing Jin <yiqing.jin@gmail.com> writes: > 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. That shouldn't happen. Can you get a stack trace from the core dump? https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend regards, tom lane
Hi Tom,
Thanks for your reply!
I’m having trouble getting the full stack trace since it’s a controlled environment and I need to figure out a way to
installall the debug info packages.
but for now i have checked all core dumps which all seems crashed at same point.
warning: no loadable sections found in added symbol-file system-supplied DSO at 0x7fffe2b31000
Core was generated by `postgres: osm osm_planet [local] ALTER TABLE '.
Program terminated with signal 11, Segmentation fault.
#0 0x0000000000785180 in med3_tuple (a=0x7f31613f1028, b=0x7f31613f1040, c=0xffffffff3ffffffd,
cmp_tuple=0x7f43613f1010,state=0x1) at qsort_tuple.c:66
66 {
Missing separate debuginfos, use: debuginfo-install glibc-2.12-1.149.el6_6.5.x86_64
I’ll try get help to get a full stack trace and post here as soon as possible.
Thanks
Yiqing
> On Jul 16, 2015, at 11:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Yiqing Jin <yiqing.jin@gmail.com> writes:
>> 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.
>
> That shouldn't happen. Can you get a stack trace from the core dump?
>
> https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
>
> regards, tom lane
Yiqing Jin <yiqing.jin@gmail.com> writes:
> maintenance_work_mem = 128000MB
Just a thought ... does the crash go away if you reduce that to something
like 50GB? It suddenly strikes me that qsort_tuple is not being careful
about size_t being wider than int.
regards, tom lane
Yes it finished successfully after changing it to 50G! Thanks Tom! > On Jul 16, 2015, at 2:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Yiqing Jin <yiqing.jin@gmail.com> writes: >> maintenance_work_mem = 128000MB > > Just a thought ... does the crash go away if you reduce that to something > like 50GB? It suddenly strikes me that qsort_tuple is not being careful > about size_t being wider than int. > > regards, tom lane
Yiqing Jin <yiqing.jin@gmail.com> writes: > Yes it finished successfully after changing it to 50G! OK. That's only a workaround of course. I believe the actual problem is explained here: http://www.postgresql.org/message-id/flat/25168.1437091531@sss.pgh.pa.us If you need a fix before the next minor releases, see http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b8f368276916b677f0678982e81723fe30e5c582 regards, tom lane
this is very helpful information. thanks again! > On Jul 16, 2015, at 8:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Yiqing Jin <yiqing.jin@gmail.com> writes: >> Yes it finished successfully after changing it to 50G! > > OK. That's only a workaround of course. I believe the actual problem > is explained here: > http://www.postgresql.org/message-id/flat/25168.1437091531@sss.pgh.pa.us > If you need a fix before the next minor releases, see > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b8f368276916b677f0678982e81723fe30e5c582 > > regards, tom lane