Re: RFC: replace pg_stat_activity.waiting with something more descriptive

> 18 сент. 2015 г., в 20:16, Robert Haas <robertmhaas(at)gmail(dot)com> написал(а):
>
> On Fri, Sep 18, 2015 at 4:08 AM, Vladimir Borodin <root(at)simply(dot)name> wrote:
>> For both scenarios on linux we got approximately the same results - version
>> with timings was faster then version with sampling (sampling was done every
>> 10 ms). Vanilla PostgreSQL from REL9_4_STABLE gave ~15500 tps and version
>> with timings gave ~14500 tps while version with sampling gave ~13800 tps. In
>> all cases processor was 100% utilized. Comparing vanilla PostgreSQL and
>> version with timings on constant workload (12000 tps) gave the following
>> results in latencies for queries:
>
> If the timing is speeding things up, that's most likely a sign that
> the spinlock contention on that workload is so severe that you are
> spending a lot of time in s_lock. Adding more things for the system
> to do that don't require that lock will speed the system up by
> reducing the contention. Instead of inserting gettimeofday() calls,
> you could insert a for loop that counts to some large number without
> doing any useful work, and that would likely have a similar effect.
>
> In any case, I think your experiment clearly proves that the presence
> or absence of this instrumentation *is* performance-relevant and that
> we *do* need to worry about what it costs. If the system gets 20%
> faster when you call gettimeofday() a lot, does that mean we should
> insert gettimeofday() calls all over the system in random places to
> speed it up?

No, probably you misunderstood the results, let me explain one more time. Unpatched PostgreSQL from REL9_4_STABLE gave 15500 tps. Version with timings - 14500 tps which is 6,5% worse. Version with sampling wait events every 10 ms gave 13800 tps (11% worse than unpatched and 5% worse than with timings).

We also made a test with a stable workload of 12000 tps for unpatched version and version with timings. In thas test we saw that response times are a bit worse in version with timings as shown in the table below. You should read this table as follows: 99% of all queries in unpatched version fits in 79 ms while in version with timings 99% of all queries fits in 97 ms which is 18 ms slower, and so on. That test also showed that version with timings consumes extra 7% of CPU to handle the same workload as unpatched version.

So this is the cost of waits monitoring with timings on lwlock stress workload - 6,5% less throughput, a bit worse timings and extra 7% of CPU. If you will insert gettimeofday() calls all over the system in random places, you expectedly will not speed up, you will be getting slower.

q'th vanilla timing
99% 79.0 97.0 (+18.0)
98% 64.0 76.0 (+12.0)
95% 38.0 47.0 (+9.0)
90% 16.0 21.0 (+5.0)
85% 7.0 11.0 (+4.0)
80% 5.0 7.0 (+2.0)
75% 4.0 5.0 (+1.0)
50% 2.0 3.0 (+1.0)

>
> I do agree that if we're going to include support for timings, having
> them be controlled by a GUC is a good idea.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
May the force be with you…
https://simply.name