Understanding Query Execution with Performance Schema
As the scale and complexity of database workloads continue to increase, understanding the nuances of query execution becomes essential for MySQL Database Administrators (DBAs) and Developers. This blog post aims to shed light on how to leverage Performance Schema, a feature introduced in MySQL 5.5, to monitor and analyze performance metrics at a granular level.
The SQL query provided forms the basis of our discussion:
select SUBSTRING(esh.SQL_TEXT, 1, 100) AS SQL_TEXT_SNIPPET,
esh.TIMER_START/1000000000000 AS TIMER_START_SECONDS,
esh.TIMER_END/1000000000000 AS TIMER_END_SECONDS,
(esh.TIMER_END – esh.TIMER_START)/1000000000000 AS EXECUTION_TIME_SECONDS,
esh.TIMER_WAIT/1000000000000 AS SQL_STATEMENT_WAIT_IN_SECONDS,
esh.CPU_TIME/1000000000000 AS CPU_TIME_SECONDS,
dl.LOCK_STATUS AS LOCK_STATUS,
dl.LOCK_TYPE AS LOCK_TYPE,
dl.LOCK_MODE AS LOCK_MODE,
eth.ISOLATION_LEVEL AS ISOLATION_LEVEL,
eth.TIMER_WAIT/1000000000000 AS TRANSACTION_WAIT_IN_SECONDS,
eth.ACCESS_MODE AS ACCESS_MODE,
eth.NUMBER_OF_RELEASE_SAVEPOINT,
eth.NUMBER_OF_SAVEPOINTS
from
performance_schema.events_statements_history esh
left join data_locks dl on esh.THREAD_ID = dl.THREAD_ID
left join events_transactions_history eth on dl.THREAD_ID = eth.THREAD_ID
ORDER BY
EXECUTION_TIME_SECONDS DESC;
Tables used in the query:
- events_statements_history
- data_locks
- events_transactions_history
Query Execution Metrics
Our query starts by selecting metrics from events_statements_history that help us understand the SQL statements being executed:
- SQL_TEXT_SNIPPET: A truncated portion of the executed SQL statement. This is particularly useful for getting a quick glance at the statements being executed without overwhelming the output.
- TIMER_START_SECONDS and TIMER_END_SECONDS: The start and end times of the SQL statement execution, respectively, in seconds.
- EXECUTION_TIME_SECONDS: The total time taken for the SQL statement execution.
- SQL_STATEMENT_WAIT_IN_SECONDS: The total wait time for the SQL statement, indicating how long the statement had to wait before getting necessary resources to execute.
- CPU_TIME_SECONDS: The CPU time consumed by the SQL statement.
Locking Information
Next, the query pulls in data from the data_locks table:
- LOCK_STATUS: The status of the lock associated with the SQL statement’s thread.
- LOCK_TYPE: The type of the lock.
- LOCK_MODE: The mode of the lock (e.g., shared or exclusive).
Transaction Information
Finally, the query garners details from the events_transactions_history table:
- ISOLATION_LEVEL: The transaction isolation level, which determines how/when the changes made by one transaction are visible to others.
- TRANSACTION_WAIT_IN_SECONDS: The total wait time for the transaction.
- ACCESS_MODE: The access mode of the transaction (e.g., read or write).
- NUMBER_OF_RELEASE_SAVEPOINT and NUMBER_OF_SAVEPOINTS: These fields capture the number of savepoints released and the total number of savepoints in a transaction, respectively.
Output
The result of this query provides valuable insights into the performance characteristics of SQL statements, their associated locking behavior, and transactional attributes. This information can be pivotal.
