Re: RFC: replace pg_stat_activity.waiting with something more descriptive - Mailing list pgsql-hackers
| From | Vladimir Borodin |
|---|---|
| Subject | Re: RFC: replace pg_stat_activity.waiting with something more descriptive |
| Date | |
| Msg-id | 5F3DD73A-2A85-44BF-9F47-54049A81C981@simply.name Whole thread Raw |
| In response to | Re: RFC: replace pg_stat_activity.waiting with something more descriptive (Robert Haas <robertmhaas@gmail.com>) |
| Responses |
Re: RFC: replace pg_stat_activity.waiting with something
more descriptive
(Robert Haas <robertmhaas@gmail.com>)
|
| List | pgsql-hackers |
18 сент. 2015 г., в 20:16, Robert Haas <robertmhaas@gmail.com> написал(а):On Fri, Sep 18, 2015 at 4:08 AM, Vladimir Borodin <root@simply.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
pgsql-hackers by date: