MySQL SQL Statistics

Info

MYSQL SQL Statistics will be renamed to SQL Statistics (DB) in the future.

This is DB's own SQL statistics provided by MySQL's performance_schema.events_statements_summary_by_digest view. You can check delta values of execution count, execution time, number of rows processed per SQL, etc. on the screen without querying the DB directly.

• The agent calculates and collects delta values at 1-hour intervals.

• Collects up to the agent option statements_row_limit based on sum_timer_wait. (Default: 5,000 records)

• DBX agent version 1.6.10 or later is required.

Note

Data collection begins approximately 1 hour after configuration is complete.

Prerequisites

Both DB configuration and Agent configuration are required to display data on this screen.

DB configuration

1. Enable Performance Schema (my.cnf)

   MySQL restart is required after changes.

   [mysqld]
   performance_schema = on

2. Grant monitoring account permissions

   Grant permissions in the DB so that the agent can query performance_schema.

   GRANT SELECT ON performance_schema.* TO 'whatap'@'%';

Agent configuration

Add the following setting to the whatap.conf file.

statements=true

Option	Type	Default	Description
statements	boolean	false	Enable SQL statistics collection

Advanced options

Option	Type	Default	Description
statements_interval	int	1	SQL statistics collection interval (in hours)
statements_min_row	int	10000	Threshold for collecting SQL statistics data. See collection criteria below.
statements_row_limit	int	5000	Maximum number of records to collect (top by sum_timer_wait)

Collection criteria (statements_min_row)

Data is collected if any one of the following three metrics from performance_schema.events_statements_summary_by_digest exceeds statements_min_row (OR).

Metric	Description
sum_rows_affected	Number of rows affected
sum_rows_sent	Number of rows sent
sum_rows_examined	Number of rows examined

Note

If the amount of SQL collected is too much or too little depending on the DB workload, you can control the collection volume by adjusting the statements_min_row and statements_row_limit values.

MYSQL SQL Statistics

Basic options

In the top filter area, you can set the query period, target instance, filter conditions, and more.

• Time: Select the date and time to query

• Instance: Select the target DB instance

• Filter: Filter data by condition (AND logic applied for multiple conditions)

• Sort order: Click column headers to sort ascending/descending

• Query count: Set the number of items to display in the table

• Search in results: Search by query keyword within the retrieved results

• (Column selection): Add/remove columns to display, change order by drag

• (Download): Export in CSV format

Column guide

Basic information

Column	Description
digest_text	Normalized SQL statement (literal values replaced with ?)
digest	Hash value of the SQL statement
schema_name	Schema (database) name where the SQL was executed
instance	DB instance name where the SQL was executed

Execution and time

Column	Description
count_star	SQL execution count
timer_wait	Total execution time (seconds)
lock_time	Total table lock wait time (seconds)

Row processing

Column	Description
rows_affected	Number of rows changed by INSERT, UPDATE, DELETE
rows_sent	Number of rows returned to the client
rows_examined	Number of rows reviewed by the server (excluding storage engine internal processing)

Temporary tables

Column	Description
created_tmp_disk_tables	Number of internal temporary tables created on disk
created_tmp_tables	Number of internal temporary tables created in memory

Join (Select)

Column	Description
select_full_join	Number of full table scans without using indexes in joins
select_full_range_join	Number of joins using range scan
select_range	Number of times index range scan was used on the first table
select_range_check	Number of joins where index usability is rechecked for each row
select_scan	Number of full scans on the first table

Sort

Column	Description
sort_merge_passes	Number of times sorted by merge sort to a temp file due to insufficient sort buffer
sort_range	Number of sorts using range scan
sort_rows	Total number of sorted rows
sort_scan	Number of sorts using full scan

Index usage

Column	Description
no_index_used	Number of table scans without using any index
no_good_index_used	Number of executions without an appropriate index

Data interpretation guide

Identifying SQL to tune

• SQL with high timer_wait and high count_star: Frequently executed and slow → Top priority tuning target

• SQL with high rows_examined / rows_sent ratio: Scanning many unnecessary rows → Index check

• SQL with high lock_time: Lock contention occurring → Check concurrency issues

When index check is needed

• select_full_join > 0: Review adding indexes for join

• select_range_check > 0: Check join conditions and index configuration

• no_index_used > 0: Index creation needed

• no_good_index_used > 0: Existing indexes are not appropriate

Memory/disk efficiency check

• If created_tmp_disk_tables is high, consider increasing tmp_table_size and max_heap_table_size

• If sort_merge_passes is high, consider increasing sort_buffer_size

• Check disk conversion rate using created_tmp_disk_tables / created_tmp_tables ratio

Practical usage scenarios

Scenario 1. Finding slow SQL

1. Sort in descending order by timer_wait

2. Check count_star of top SQL → SQL that is frequently executed and slow is the top priority tuning target

Scenario 2. Finding inefficient SQL

1. Check SQL with high rows_examined but low rows_sent

2. No index or inappropriate index — scanning many unnecessary rows

3. Check the Plan (execution plan) for the SQL to decide whether to add an index

Scenario 3. Checking lock contention

1. Check SQL with high lock_time

2. There may be heavy concurrent access to that table or long-running transactions

Additional features

SQL details

Click the SQL column to open the details window. Refer to the following for descriptions of the SQL details window.

DB reference

events_statements_summary_by_digest

Since the data on the screen is retrieved from the DB's events_statements_summary_by_digest view, changes to DB settings or data deletion directly affect the collected data.

performance_schema_digests_size

The performance_schema_digests_size parameter determines how many different SQL patterns (digests) can be stored. If set to -1, auto sizing is performed.

-- Example: The following two SQLs are normalized to the same digest.
SELECT * FROM user WHERE id = 10;
SELECT * FROM user WHERE id = 20;
→ SELECT * FROM user WHERE id = ?

When the number of digests exceeds the limit, new SQL patterns are not aggregated as individual statistics but are summed into the DIGEST = NULL row. In this case, you cannot identify which SQL it is, and some SQL may be missing from monitoring.

-- Check current limit
SHOW VARIABLES LIKE 'performance_schema_digests_size';

-- Check digest usage
SELECT COUNT(*) FROM performance_schema.events_statements_summary_by_digest;

-- Check for overflow (if statistics are accumulating in NULL digest, limit has been exceeded)
SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST IS NULL;

TRUNCATE initialization

Initializing the view with the TRUNCATE command deletes all existing data and records new data. Note that the delta values collected by the agent are also reset.