pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedsegfault when creating index on huge table
7+ messages / 2 participants
[nested] [flat]
* segfault when creating index on huge table
@ 2015-07-16 17:29 Yiqing Jin <yiqing.jin@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Yiqing Jin @ 2015-07-16 17:29 UTC (permalink / raw)
To: 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]
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: segfault when creating index on huge table
@ 2015-07-16 18:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Yiqing Jin <yiqing.jin@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2015-07-16 18:14 UTC (permalink / raw)
To: Yiqing Jin <yiqing.jin@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: segfault when creating index on huge table
@ 2015-07-16 20:34 Yiqing Jin <yiqing.jin@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Yiqing Jin @ 2015-07-16 20:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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 install all 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: segfault when creating index on huge table
@ 2015-07-16 21:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Yiqing Jin <yiqing.jin@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2015-07-16 21:14 UTC (permalink / raw)
To: Yiqing Jin <yiqing.jin@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: segfault when creating index on huge table
@ 2015-07-17 01:03 Yiqing Jin <yiqing.jin@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Yiqing Jin @ 2015-07-17 01:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: segfault when creating index on huge table
@ 2015-07-17 03:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Yiqing Jin <yiqing.jin@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2015-07-17 03:16 UTC (permalink / raw)
To: Yiqing Jin <yiqing.jin@gmail.com>; +Cc: pgsql-admin
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=b8f368276916b677f0678982e81723fe30...
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: segfault when creating index on huge table
@ 2015-07-17 04:35 Yiqing Jin <yiqing.jin@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Yiqing Jin @ 2015-07-17 04:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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=b8f368276916b677f0678982e81723fe30...
>
> regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2015-07-17 04:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-07-16 17:29 segfault when creating index on huge table Yiqing Jin <yiqing.jin@gmail.com>
2015-07-16 18:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-07-16 20:34 ` Yiqing Jin <yiqing.jin@gmail.com>
2015-07-16 21:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-07-17 01:03 ` Yiqing Jin <yiqing.jin@gmail.com>
2015-07-17 03:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-07-17 04:35 ` Yiqing Jin <yiqing.jin@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox