1

After updating server (and also the pc for additional tests) to Postgres 9.5 I tried to import PG 9.4 dumps and/or to restore 9.5 dumps:

pg_dump testdb > db.sql
psql -d testdb -f db.sql

Restoring these dumps in PG 9.4 takes less than 30 minutes, restoring them in PG 9.5 takes several hours (even if I make a PG 9.5 dump and try to restore this one)!!! The test dumps have a size of 8 GB.

Does anybody know what the reason could be? Tried it with the original PG 9.4 and 9.5 configs as well as with different options like increasing max_wal_size in 9.5 e.g.

Do I miss a specific default option in 9.5 which may be different to 9.4?

Example Log:

LOG:  duration: 278349.128 ms  statement: COPY test (id, ...)
LOG:  duration: 646487.952 ms  statement: ALTER TABLE ONLY test ...

The same with creating index... It takes hours with PG 9.5.

Barmi
  • 439
  • 1
  • 6
  • 15
  • Hmm... checked the disk with badblocks -sv /dev/sda. But no errors. The update is still slow - but just on the server. Installed PG 9.4, same result. Any idea what could cause the super long durations? Thanks in advance. – Barmi Jul 19 '16 at 22:32
  • It does not seem to be a PG problem. More likely it's a language or locale problem with PG on Ubuntu 16.04, because installing huge indexed tables with foreign languages and therefore with many utf-8 chars cause the problem. Do we miss a language package? On another server with Ubuntu 14.04 everything works fine. – Barmi Jul 20 '16 at 09:26

1 Answers1

0

Interesting that nobody found the answer...

It's a glibc bug: After upgrading from Ubuntu 14.10 (glibc 2.19) to 15.04 (glibc 2.21), we are experiencing a big performance regression in one particular PostgreSQL index creation. The index creation now takes more than 10 minutes, while before it was done in about 30 seconds.

https://sourceware.org/bugzilla/show_bug.cgi?id=18441

Barmi
  • 439
  • 1
  • 6
  • 15