MySQL Bugs: #101764: Contribution by Facebook: Add CPU time to statement metrics

Bug #101764	Contribution by Facebook: Add CPU time to statement metrics
Submitted:	26 Nov 2020 0:38	Modified:	31 Jan 2022 9:59
Reporter:	FBContrib Admin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Background innformation provided by Facebook:
Add cpu time to performance schema metrics.

Use case:
Cpu_time for statements is useful for finding expensive queries. It also helps find potential performance regressions on existing workloads when new updates are pushed out.

Repo: https://github.com/mysql/mysql-server 
Patch on top of 8.0.20: https://github.com/mysql/mysql-server/commit/7d10c82196c

How to repeat:
See description

Suggested fix:
See contribution code attached

Add CPU time to statement metrics 
(*) This code is contributed under the Facebook agreement

Contribution: fb_patch_195.txt (text/plain), 75.37 KiB.

Posted by developer:
 
Noted the following in the 8.0.28 changelog:
A new statement metric, CPU_TIME, is now available, enabling you to measure the CPU time spent on a query.

The following changes were made to support this:

A timer, THREAD_CPU, was added to the Performance Schema PERFORMANCE_TIMERS table.

A consumer, events_statements_cpu was added to the Performance Schema setup_consumers table.

events_statements_cpu is disabled by default.

A Performance Schema command option, performance-schema-consumer-events-statements-cpu to enable or disable the consumer, events_statements_cpu.

See Performance Schema Command Options for more information.

CPU_TIME column was added to the following Performance Schema tables:

events_statements_current

events_statements_history

events_statements_history_long

CPU_TIME is the time spent on CPU for the current thread, expressed in picoseconds.

SUM_CPU_TIME column was added to the following Performance Schema tables:

events_statements_summary_by_thread_by_event_name

events_statements_summary_by_account_by_event_name

events_statements_summary_by_user_by_event_name

events_statements_summary_by_host_by_event_name

events_statements_summary_global_by_event_name

events_statements_summary_by_digest

events_statements_summary_by_program

prepared_statements_instances

SUM_CPU_TIME is the CPU time spent on the current thread, expressed in picoseconds, for the corresponding aggregations.

CPU_LATENCY column was added to the following sys schema tables:

statement_analysis

user_summary_by_statement_type

user_summary_by_statement_latency

host_summary_by_statement_type

host_summary_by_statement_latency

processlist

session

CPU latency is the CPU time spent on the current thread, expressed in human-readable form.

CPU_LATENCY column was added to the following sys schema x$ tables:

x$statement_analysis

x$user_summary_by_statement_type

x$host_summary_by_statement_type

x$processlist

x$session

x$user_summary_by_statement_latency

x$host_summary_by_statement_latency

CPU latency is the CPU time spent on the current thread, expressed in picoseconds.

Our thanks to Facebook for their contribution to this fix.