Thread: BUG #15953: performance issues using 11.5-1.pgdg+1 over 11.5-1

BUG #15953: performance issues using 11.5-1.pgdg+1 over 11.5-1

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      15953
Logged by:          richard
Email address:      easteregg@verfriemelt.org
PostgreSQL version: 11.5
Operating system:   Debian Sid x64
Description:

hi,

i upgraded my server from 11.4-1 ( note, this was the debian package before
) to 11.5-1.pgdg+1 which uses the postgresql apt repository package. after
this upgrade, i noticed a lot more system load and all 4 cores of my server
hit 100% usage by the database.

visualized you can see it here: 
CPU usage https://verfriemelt.org/pg-issue-01.png
TPS https://verfriemelt.org/pg-issue-02.png

at ~11:10 i installed the new version, afterwards you see the cpu hit 100%
usage and than followed some desperate measures to get the database to
perform normal again, mostly reindex and vacuum full on the given database.
at 14:30 i wondered and installed the debian provided 11.5-1 package and
everything went back to 'normal' without any other changes. there was not a
single cause like a long running query. just everything went a lot slower
and used more cpu. there were no obvious differences in query planning.

the two versions were the following:

# apt-cache policy postgresql-11
postgresql-11:
  Installed: 11.5-1
  Candidate: 11.5-1.pgdg+1
  Version table:
     11.5-1.pgdg+1 500
        500 http://apt.postgresql.org/pub/repos/apt sid-pgdg/main amd64
Packages
 *** 11.5-1 500
        500 http://ftp.de.debian.org/debian sid/main amd64 Packages
        100 /var/lib/dpkg/status

using pg_config --configure i found only one difference in the build
options, which is --enable-cassert on the postgresql provided version.

whast the deal?


RE: BUG #15953: performance issues using 11.5-1.pgdg+1 over 11.5-1

From
easteregg@verfriemelt.org
Date:
after the bugreport i found in the pgapt repository the jenkins build pipeline, and there its configured to enable this debug feature the debian sid build.
this is kind of unexpected, because wiki for the apt repo states the following:

> What's the difference between the PostgreSQL packages provided by Debian/Ubuntu and by the PGDG apt repository?
>
> Pretty little, actually. The PGDG packages are built from the same source as the Debian
> packages (Ubuntu is using the same source for the PostgreSQL server packages as well).
> We try to follow Debian unstable's versions as close as possible, i.e. the packages 
> available here are the same, just rebuilt with a ".pgdg" appended to the version number. 
> We are also using the same postgresql-common infrastructure packages.

so if i want to avoid this performance penalty for my devbox, i have to mixmatch debian releases in the sources, which will break eventually.
why not providing a normal build of postgresql and a postgresql-assertions-enabled package?

someone noticed this already here: https://www.postgresql.org/message-id/20170610202102.vz5l4eudghktvwgj%40msg.df7cb.de

and the commit in the pgapt repository responsible for this is this one:

commit 451838f53eecb8f769c1c5bb8b07c84e32717aeb
Author: Christoph Berg <myon@debian.org>
Date:   Mon Apr 17 12:57:04 2017 +0200

    Enable cassert in the PostgreSQL server packages on sid