Too slow "Analyze" for the table with data in Thai language
Hello,
I have a table (dictionary) with international data in many languages.
After the migration to the new hardware the 'analyze' operation instead
of 5 seconds takes 3-5 minutes now (with 100% cpu core usage).
Investigation showed that slow performance introduced by new glibc.
Previous versions of glibc (2.15 - 2.16) works fine, new versions (2.22
and 2.23) have this problem (versions between 2.17 and 2.21 were not
tested).
Further investigation showed that this problem exists with Thai data
only (we have 10 languages in the dictionary) and non-C collation (we
have en_US.utf8) and affects 'Analyze' and 'Order By' (of this
multilingual textual column) operations.
I can change collation of the column to C and it will fix the problem
for 'order by' operation of Thai data, but Im not interested in 'order
by' operation for multilingual table and interested mainly in ANALYZE
(which is not affected in any way by collate change of the column).
I tried to recreate test cluster and change global collate of the
cluster to C and it helps for both operations, but I can't do this in
the production env.
Tested on:
OS: Gentoo (4.4.0-gentoo-r1) and Fedora (4.5.5-300.fc24.x86_64)
PostgreSQL: v9.5.3 and v9.2.15 (I tried both OS repository PG builds and
manually build from the source with default and optimized
postgresql.conf)
I attached a sample dictionary (new_dic.sql.bz2) and steps to reproduce
(new_dic_log.txt.bz2).
--
Timur Luchkin
"Timur Luchkin" <timur.luchkin@gmail.com> writes:
I have a table (dictionary) with international data in many languages.
After the migration to the new hardware the 'analyze' operation instead
of 5 seconds takes 3-5 minutes now (with 100% cpu core usage).
Investigation showed that slow performance introduced by new glibc.
Previous versions of glibc (2.15 - 2.16) works fine, new versions (2.22
and 2.23) have this problem (versions between 2.17 and 2.21 were not
tested).
So ... why do you think this is a Postgres bug and not a glibc bug?
We have no control over what happens inside strcoll().
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Tue, Jul 5, 2016 at 8:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
So ... why do you think this is a Postgres bug and not a glibc bug?
We have no control over what happens inside strcoll().
I'd be interested in seeing exactly where time is spent by glibc.
Perhaps Timur can use perf to show us a profile using the default perf
event type:
https://wiki.postgresql.org/wiki/Profiling_with_perf
--
Peter Geoghegan
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Wed, Jul 6, 2016 at 11:52 AM, Peter Geoghegan <pg@bowt.ie> wrote:
On Tue, Jul 5, 2016 at 8:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
So ... why do you think this is a Postgres bug and not a glibc bug?
We have no control over what happens inside strcoll().I'd be interested in seeing exactly where time is spent by glibc.
Perhaps Timur can use perf to show us a profile using the default perf
event type:
I wonder if it is this bug:
https://sourceware.org/bugzilla/show_bug.cgi?id=18441
Some possibly relevant mailing list traffic that highlights Thai:
https://sourceware.org/ml/libc-alpha/2015-10/msg00149.html
https://sourceware.org/ml/libc-alpha/2016-03/msg00699.html
--
Thomas Munro
http://www.enterprisedb.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hello,
complete result of the perf is in the attachment.
The part with glibc looks like:
# Children Self Samples Command Shared Object
Symbol
# ........ ........ ............ .............
.......................
....................................................
59.26% 59.25% 815883 postgres libc-2.23.so
[.] __strcoll_l
--59.26%--__strcoll_l
p.s.
1. perf executed during 'long' ANALYZE as "perf record -a -g";
2. test server has 2 CPU cores
3. this is dedicated server without any parallel activity
--
Timur Luchkin
Show quoted text
On Tue, Jul 5, 2016 at 8:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
So ... why do you think this is a Postgres bug and not a glibc bug?
We have no control over what happens inside strcoll().I'd be interested in seeing exactly where time is spent by glibc.
Perhaps Timur can use perf to show us a profile using the default perf
event type:https://wiki.postgresql.org/wiki/Profiling_with_perf
--
Peter Geoghegan