pgsql 9.3 for debian/sid in pgdg repo has asserts enabled causing big performance issues

Hello,
please CC me on replies as i am not subscribed to the mailing list.

In short:
The following part from latest changelog:
* debian/rules: Add stub to enable cassert builds (disabled by default).

does not work in practice as in debian/rules file the

#CASSERT_FLAGS := --enable-cassert

line is not commented out as it should have been.

That causes severe peformance issues at least in our use-case of batch-inserting lots of data with runtime going from ca. 1min to >=6min.

Checking with perf shows lots of cpu-cycles spent in AllocSetCheck.

Doing local rebuilt of the source package with the assert disabled fixes the performance issue for me.

apt-cache policy postgresql-9.3
postgresql-9.3:
Installed: 9.3.17-1.pgdg+1
Candidate: 9.3.17-1.pgdg+1
Version table:
9.3.17-1.pgdg+1 500
500 https://apt.postgresql.org/pub/repos/apt sid-pgdg/main amd64 Packages

Apart from 9.3 version as mentioned above it seems to affect more versions.

I did not check manually but checked pg_config output from postgresql-server-dev packages searching for 'assert' and got the following:

for i in `ls /usr/lib/postgresql/`; do echo "Version: $i"; /usr/lib/postgresql/$i/bin/pg_config | grep -c assert; done
Version: 10
0
Version: 8.4
0
Version: 9.0
0
Version: 9.1
0
Version: 9.2
1
Version: 9.3
1
Version: 9.4
1
Version: 9.5
1
Version: 9.6
0

So apparently 9.2..9.5 version has that problem as well.

Regards,
Stefan

Re: Stefan Huehner 2017-06-10 <20170610132747(dot)GD11504(at)huehner(dot)biz>
> In short:
> The following part from latest changelog:
> * debian/rules: Add stub to enable cassert builds (disabled by default).
>
> does not work in practice as in debian/rules file the
>
> #CASSERT_FLAGS := --enable-cassert
>
> line is not commented out as it should have been.
>
> That causes severe peformance issues at least in our use-case of batch-inserting lots of data with runtime going from ca. 1min to >=6min.

Hi,

I'm sorry for not properly announcing and documenting this recent
change properly, we should put a note on the wiki page.

What's happening is that in git the line is indeed commented out, but
then the apt.postgresql.org build scripts will activate the line when
building for sid-pgdg (which is the whole point why that stub logic
got introduced). The idea is that one of the distributions on
apt.postgresql.org should have cassert enabled so the testsuites
exercise more test cases, for the server packages as well as for
extension packages. As sid is Debian's development branch, it made
sense to enable cassert there.

You seem to use the sid-pgdg packages for production data - is there a
reason you are not using jessie-pgdg or stretch-pgdg?

Christoph

On Sat, Jun 10, 2017 at 10:21:02PM +0200, Christoph Berg wrote:
> Re: Stefan Huehner 2017-06-10 <20170610132747(dot)GD11504(at)huehner(dot)biz>
> > In short:
> > The following part from latest changelog:
> > * debian/rules: Add stub to enable cassert builds (disabled by default).
> >
> > does not work in practice as in debian/rules file the
> >
> > #CASSERT_FLAGS := --enable-cassert
> >
> > line is not commented out as it should have been.
> >
> > That causes severe peformance issues at least in our use-case of batch-inserting lots of data with runtime going from ca. 1min to >=6min.
>
> Hi,
thanks for the fast reply Christoph,
>
> I'm sorry for not properly announcing and documenting this recent
> change properly, we should put a note on the wiki page.
>

Yes, please, mqaybe even news entry in the package?
As at least for our use-case that makes the sid-pgdg completely unusable here. As that overhead is not just small percentage but like factor 5-10 slower.

> What's happening is that in git the line is indeed commented out, but
> then the apt.postgresql.org build scripts will activate the line when
> building for sid-pgdg (which is the whole point why that stub logic
> got introduced). The idea is that one of the distributions on
> apt.postgresql.org should have cassert enabled so the testsuites
> exercise more test cases, for the server packages as well as for
> extension packages. As sid is Debian's development branch, it made
> sense to enable cassert there.
>
From the technical topic i understand just the drawback consequence is:
- sid-pgdg now much better for pg or extension development
- but unusably slow for application development just using postgres as db
- Of course i don't know how many other people are affected so much

A possible alternative could be do have another set of builds for that purpose apart from sid. But that would be quite some extra effort probably on your end :(

> You seem to use the sid-pgdg packages for production data - is there a
> reason you are not using jessie-pgdg or stretch-pgdg?

Not production data just the bootstrap of our application.
Part of that bootstrap is inserting a couple of thousands row into database (via java, jdbc, batched insert) and that is now factor 5-10 slower at least. Didn't look into more details yet as not noticed today that this was a packaging issue and not severe regression on our side.

I am using sid on my local development box and have been using sid-pgdg for past years without issues.

So far there was never a reason to not use the 'matching' sid-pgdg if i run sid for the rest.

My question is if mis-matching i.e. stretch-pgdg with sid will work in the long run (After strech release) ?
I.e. sid some day removing libssl1.0.x but older packages still linking to it (just example)

Stefan

On 2017-06-10 22:21:02 +0200, Christoph Berg wrote:
> Re: Stefan Huehner 2017-06-10 <20170610132747(dot)GD11504(at)huehner(dot)biz>
> > In short:
> > The following part from latest changelog:
> > * debian/rules: Add stub to enable cassert builds (disabled by default).
> >
> > does not work in practice as in debian/rules file the
> >
> > #CASSERT_FLAGS := --enable-cassert
> >
> > line is not commented out as it should have been.
> >
> > That causes severe peformance issues at least in our use-case of batch-inserting lots of data with runtime going from ca. 1min to >=6min.
>
> Hi,
>
> I'm sorry for not properly announcing and documenting this recent
> change properly, we should put a note on the wiki page.
>
> What's happening is that in git the line is indeed commented out, but
> then the apt.postgresql.org build scripts will activate the line when
> building for sid-pgdg (which is the whole point why that stub logic
> got introduced). The idea is that one of the distributions on
> apt.postgresql.org should have cassert enabled so the testsuites
> exercise more test cases, for the server packages as well as for
> extension packages. As sid is Debian's development branch, it made
> sense to enable cassert there.

FWIW, I think that's a seriously bad default. We want people to be able
to do performance tests on sid too, and that's just about impossible
with cassert enabled builds. Besides that, there's plenty people using
sid as their daily driver, while still actually using postgres in some
way.

- Andres