3. Performance Schema

Introduction

Performance Schema provides low-level metrics on operations running inside MySQL Server.

• Instrument
  • Any portion of MySQL code that we want to capture
  • performance_schema.setup_instruments table contains instruments and their status
    • The more instruments are enabled, the higher CPU usage will be
  • ex) In order to collect info about metadata locks, we should enable wait/lock/metadata/sql/mdl instrument
• Consumer
  • Table that stores the info about what code was instrumented
  • performance_schema.setup_consumers table contains consumers and their status

Resource Consumption

Memory

• Data collected by performance schema is kept in Memory
  • The amount of memory for each consumer is adjustable
    • Limit can be configured through system variables
  • Some tables support auto-scaling, which they allocate minimal amount of memory at startup and adjust their size as needed
    • However, this memory is never freed once allocated
    • even if the instrument is disabled or table is truncated

CPU

• Every instrumented call adds 2 more macro calls to store data
• Actual CPU utilization depends on the specific instrument
  • ex) One query calls one statement-related instrument, whereas tons of lock-related instrument can be called

Use case

Examining SQL Statements

In order to find out which statements require optimiztion, we can execute below query

SHOW CREATE TABLE performance_schema.event_statement_history

Important columns are like:

• CREATED_TMP_DISK_TABLES
• CREATED_TMP_TABLES
  • Not a bad sign per se, but may be coverted into disk-based tmp tables
• SELECT_FULL_JOIN
  • Join performend a full table scan
• SELECT_RANGE_CHECK
  • Join without indexes, which checks for keys after each row
• SORT_ROWS
  • Not a bad sign per se, but should be compared with RETURNED_ROWS
• SORT_SCAN
  • Sorting was done by scanning a table
• NO_INDEX_USED
  • okay for small tables
• NO_GOOD_INDEX_USED

Examining Metadata Locks

The statement holding meatadata lock is not listed on process list like Waiting for a metadata lock, if it is a part of multiple statements transaction

• Instrument
  • /wait/lock/metadata/sql/mdl
• Consumers
  • metadata_locks

SELECT * FROM metadata_locks JOIN threads ON (owner_thread_id=thread_id);

Examining Memory Usage

Memory usage can be examined by enabling memory instrument,
and statistics for them is provided by tables named memory_summary prefix

• global
• thread
• account
• host
• user

SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYES_USED/1024/1024 AS CURRENT_MB,
HIGH_NUMBER_OF_BYTES_USED/1024/1024 AS HIGH_MB,
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/innodb/%'
ORDER BY CURRENT_MB DESC;

---

Sys Schema

It consists only of views and stored routines over performance_schema

Statements

Find problematic statements (Digest Query)

• statements_with_full_table_scans
• statements_with_erros_or_warnings
• statements_with_temp_tables
• etc

Memory Usage

Provides memory statistics in a better way aggregated by host, user, thread, global

• memory_global_total
• memory_by_thread_by_current_bytes
• etc