Too slow "Analyze" for the table with data in Thai language

От

Timur Luchkin

Тема

Дата

5 июля 2016 г. в 12:45:28

Msg-id

emb8e7a3a5-a559-41e0-8288-2163d6553f08@luchkin-new

обсуждение

Список

pgsql-bugs

Дерево обсуждения

Too slow "Analyze" for the table with data in Thai language "Timur Luchkin" <timur.luchkin@gmail.com> 5 июля 2016 г. в 12:45:28

Re: Too slow "Analyze" for the table with data in Thai language Tom Lane <tgl@sss.pgh.pa.us> 5 июля 2016 г. в 15:33:15

Re: Too slow "Analyze" for the table with data in Thai language Peter Geoghegan <pg@bowt.ie> 5 июля 2016 г. в 23:53:21

Re: Too slow "Analyze" for the table with data in Thai language Thomas Munro <thomas.munro@enterprisedb.com> 6 июля 2016 г. в 01:32:40

Re: Too slow "Analyze" for the table with data in Thai language "Timur Luchkin" <timur.luchkin@gmail.com> 6 июля 2016 г. в 14:57:05

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

В списке pgsql-bugs по дате отправления

Предыдущее

От: Heikki Rauhala

Дата: 5 июля 2016 г. в 11:44:21

Сообщение: Re: "insert [...] on conflict" hangs on conflict on an unmentioned gist index

Следующее

От: Tom Lane

Дата: 5 июля 2016 г. в 15:33:15

Сообщение: Re: Too slow "Analyze" for the table with data in Thai language