Re: [HACKERS] [FEATURE PATCH] pg_stat_statements with plans (v02)

On 2018-03-02 18:07:32 +0100, Julian Markwort wrote:
> Andres Freund wrote on 2018-03-01:
> > I think the patch probably doesn't apply anymore, due to other changes
> > to pg_stat_statements since its posting. Could you refresh?
>
> pgss_plans_v02.patch applies cleanly to master, there were no changes to pg_stat_statements since the copyright updates at the beginning of January.
> (pgss_plans_v02.patch is attached to message 1bd396a9-4573-55ad-7ce8-fe7adffa1bd9(at)uni-muenster(dot)de and can be found in the current commitfest as well.)

Yea, I misread the diff to think you added a conflicting version. Due
to:
-DATA =3D pg_stat_statements--1.4.sql pg_stat_statements--1.4--1.5.sql \
+DATA =3D pg_stat_statements--1.5.sql pg_stat_statements--1.4--1.5.sql \

and I'd checked that 1.5 already exists. But you just renamed the file,
presumably because it's essentially rewriting the whole file? I'm not
sure I'm a big fan of doing so, because that makes testing the upgrade
path more work.

> I've tried to gather some meaningful results, however either my testing methodology was flawed (as variance between all my passes of pgbench was rather high) or the takeaway is that the feature only generates little overhead.
> This is what I've run on my workstation using a Ryzen 1700 and 16GB of RAM and an old Samsung 840 Evo as boot drive, which also held the database:
> The database used for the tests was dropped and pgbench initialized anew for each test (pgss off, pgss on, pgss on with plan collection) using a scaling of 16437704*0.003~=50 (roughly what the phoronix test suite uses for a buffer test).
> Also similar to the phoronix test suite, I used 8 jobs and 32 connections for a normal multithreaded load.
>
> I then ran 10 passes, each for 60 seconds, with a 30 second pause between them, as well as another test which ran for 10 minutes.

What workload did you run? read/write or readonly? This seems like a
feature were readonly makes a lot more sense. But ~1800 tps strongly
suggests that's not what you did?

> With pg_stat_statements on, the latter test (10 minutes) resulted in 1833 tps, while the patched version resulted in 1700 tps, so a little over 7% overhead? Well, the "control run", without pg_stat_statements delivered only 1806 tps, so variance seems to be quite high.

That's quite some overhead, I'd say.

> If anybody has any recommendations for a setup that generates less variance, I'll try this again.

I'd suggest disabling turboost, in my experience that makes tests
painful to repeat, because it'll strongly depend on the current HW
temperature.

Greetings,

Andres Freund