Bug #66515 PS instrumentation that require mysqld restarts are really hard to use

• Submitted: 23 Aug 2012 13:56
• Modified: 16 Jun 2016 16:31
• Reporter: Mark Callaghan
• Status: Closed
• Category: MySQL Server: Performance Schema
• Severity: S4 (Feature request)
• Version: 5.6.6-m9
• OS: Any
• Assigned to: Marc ALFF
• CPU Architecture: Any

[23 Aug 2012 13:56] Mark Callaghan

Description:
I have a problem with PS instrumentation that can only be enabled at server start.
1) We don't restart servers
2) Some PS data is too expensive to collect all of the time

The result from this is that we won't use the PS to collect that data. I am sure this is true for many other users.

I use PS to get mutex/rwlock/condvar wait stats. The data is great but it reduced peak QPS by between 5% and 15% depending on the number of concurrent clients. I think that is too expensive to be enabled all of the time.

How to repeat:
Run sysbench for a CPU-bound workload on a 32-core server, enable mutex wait stats via:
performance_schema_instrument='wait/synch/%=1'

Suggested fix:
At the cost of a branch mis-prediction you could add a check to see whether lock wait stats were dynamically enabled. For mutexes that do something like the following then this can be even less expensive by only doing it after the else branch below.

if (pthread_mutex_trylock())
  then return;
else
...

[16 Jun 2016 16:31] Paul DuBois

Posted by developer:
 
Noted in 5.7.12 changelog.

Some Performance Schema global instruments, if not enabled at server
startup, could be in a state where it was not possible to enable them
at runtime. This restriction has been lifted. Affected instruments
include mutex, rwlock, cond, and socket instances.

Thanks to Zhang Yingqiang for the patch.