Thread: BUG #14917: process hang on create index

The following bug has been logged on the website:

Bug reference:      14917
Logged by:          Boris Sagadin
Email address:      sagadin@gmail.com
PostgreSQL version: 9.5.10
Operating system:   Ubuntu 16.04, CentOS 7
Description:

This happens just on one customer table, UTF-8 is used. Can recreate and
provide more details.

CREATE INDEX on this table never ends. Data can be inserted into table.
Table can be dumped and recreated, but creating index always hangs the
process.

perf top -u postgres on CenOS 7.4, on Ubuntu is very similar.

97.24%  libc-2.17.so      [.] get_next_seq
   0.94%  libc-2.17.so      [.] __strcoll_l
   0.50%  postgres          [.] pg_detoast_datum_packed
   0.21%  libc-2.17.so      [.] __memcpy_ssse3_back

On Fri, Nov 17, 2017 at 8:26 PM,  <sagadin@gmail.com> wrote:
> This happens just on one customer table, UTF-8 is used. Can recreate and
> provide more details.

Self-contained examples aimed at reproducing a problem or a set of
problems are always welcome if you think that there is a bug of some
kind. Without more details, there is not much that can be done anyway.
-- 
Michael

Boris Sagadin <sagadin@gmail.com> writes:
>  I don't know how to create a small, reproducible example, customer
> personal data is contained.

Well, then you're going to have to do your own debugging, because the
information you've provided so far is just about useless for identifying
the problem.

One place to start is by collecting a few stack traces while it seems to
be looping:

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
        regards, tom lane

Boris Sagadin <sagadin@gmail.com> writes:
> Thanks Tom, I made a few traces on the process, one at the beginning and a
> few later when it seems stuck, below is the output.

Well, it looks to me like it's not hung, it's just busy sorting.
It is making progress, since the successive traces show different
values being compared.

97% of the runtime in strcoll() is a bit weird.  strcoll() is known
to be unpleasantly slow in some locales, but that seems excessive.
Perhaps you've discovered a performance bug in strcoll() for certain
input data.  That wouldn't be our problem though --- you'd need to
complain to the glibc maintainers.

It'd be interesting to dump out the data in that column into a text file
and see if sorting that with sort(1) (in the same locale!) is equally
slow.  If it is, that would confirm the assignment of blame to strcoll().

But the glibc folk would want to see sample data too, so one way or
another you're going to need to sanitize that data enough to show
it to somebody else.
        regards, tom lane

Boris Sagadin <sagadin@gmail.com> wrote:
>After a fresh DB start and CREATE INDEX idx_table123_fast ON table123 USING
>btree (k, n), strace looks normal to a point:

>brk(0x7f13d970a000)                     = 0x7f13d970a000
>mmap(NULL, 12587008, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,

Are these text strings individually very large?

-- 
Peter Geoghegan

On Tue, Nov 21, 2017 at 12:19 AM, Boris Sagadin <sagadin@gmail.com> wrote:
> No string is over 200 chars. By function name I assumed the get_next_seq is
> pgsql related, but now I've learned it's glibc. Sorting in shell with same
> locale is indeed very slow, I used just 1% of data in that column and it
> finishes sorting in a few minutes. Thanks, I'll check with glibc.

If you upgrade to Postgres 10, you could then use ICU collations on
text columns, where CREATE INDEX can be expected to perform
significantly better:

https://blog.anayrat.info/en/2017/11/19/postgresql-10--icu--abbreviated-keys/

-- 
Peter Geoghegan