2012/10/16 2:40, Jeff Janes wrote:
On Sun, Oct 14, 2012 at 9:43 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
Satoshi Nagayasu <sn...@uptime.jp> writes:
(2012/10/14 13:26), Fujii Masao wrote:
The tracing lwlock usage seems to still cause a small performance
overhead even if reporting is disabled. I believe some users would
prefer to avoid such overhead even if pg_stat_lwlocks is not available.
It should be up to a user to decide whether to trace lwlock usage, e.g.,
by using trace_lwlock parameter, I think.
Frankly speaking, I do not agree with disabling performance
instrument to improve performance. DBA must *always* monitor
the performance metrix when having such heavy workload.
This brings up a question that I don't think has been honestly
considered, which is exactly whom a feature like this is targeted at.
TBH I think it's of about zero use to DBAs (making the above argument
bogus). It is potentially of use to developers, but a DBA is unlikely
to be able to do anything about lwlock-level contention even if he has
the knowledge to interpret the data.
Waiting on BufFreelistLock suggests increasing shared_buffers.
Waiting on ProcArrayLock perhaps suggests use of a connection pooler
(or does it?)
WALWriteLock suggests doing something about IO, either moving logs to
different disks, or getting BBU, or something.
WALInsertLock suggests trying to adapt your data loading process so it
can take advantage of the bulk, or maybe increasing wal_buffers.
And a lot of waiting on any of the locks gives a piece of information
the DBA can use when asking the mailing lists for help, even if it
doesn't allow him to take unilateral action.
So I feel it isn't something that should be turned on in production
builds. I'd vote for enabling it by a non-default configure option,
and making sure that it doesn't introduce any overhead when the option
is off.
I think hackers would benefit from getting reports from DBAs in the
field with concrete data on bottlenecks.
If the only way to get this is to do some non-standard compile and
deploy it to production, or to create a "benchmarking" copy of the
production database system including a realistic work-load driver and
run the non-standard compile there; either of those is going to
dramatically cut down on the participation.
Agreed.
The hardest thing to investigate performance issue is
reproducing a situation in the different environment
from the production environment.
I often see people struggling to reproduce a situation
with different hardware and (similar but) different
workload. It is very time consuming, and also it often
fails.
So, we need to collect any piece of information, which
would help us to understand what's going on within
the production PostgreSQL, without any changes of
binaries and configurations in the production environment.
That's the reason why I stick to a "built-in" instrument,
and I disagree to disable such instrument even if it has
minor performance overhead.
A flight-recorder must not be disabled. Collecting
performance data must be top priority for DBA.
Regards,
Cheers,
Jeff
--
Satoshi Nagayasu <sn...@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
