Mysql SQL Statistics

Home > Select Project > Stat/Report > MYSQL SQL Statistics

SQL statistical data (execution count, execution time, number of rows, etc.) provided by MySQL's performance_schema is collected through the performance_schema.events_statements_summary_by_digest view.

Note

• Delta values are calculated and collected from events_statements_summary_by_digest at 1-hour intervals. Therefore, collection begins one hour after configuration.

• When collecting data every 1 hour, up to 5,000 cases are collected based on the sum_timer_wait.

• This function is supported in DBX agent 1.6.10 or later.

• For more information about the events_statements_summary_by_digest view, see the following link.

Tip

• The events_statements_summary_by_digest view is affected by the value (number of rows) of a database parameter called performance_schema_digests_size. If the parameter value is set to -1, auto sizing is made.

• Data is stored according to the set parameter values. When the set capacity is exceeded, new data is no longer collected.

• If you initialize the view with the TRUNCATE command, all collected data is deleted and new data is recorded.

Before use

To use MYSQL SQL Statistics, the following settings are required in the target monitoring DB.

1. Enable Performance Schema in the database configuration (my.cnf).

   my.cnf

   performance_schema = on

2. Set the view role to allow the monitoring account to read the data.

   grant select on performance_schema.* to whatap;

3. Set the options in the DBX agent.

   whatap.conf

   statements=true

Note

See the following DBX agent options for the criteria on how to collect data. It views performance_schema.events_statements_summary_by_digest and performs collection if the sum is greater than statements_min_row.

sum_rows_affected + sum_rows_sent + sum_rows_examined

whatap.conf

# default 10000 row
statements_min_row=10000

Basic screen guide

1. Select the time and date to view data in Time. You can also select a lookup time by clicking the green button.

2. Select a target to view data in Instance.

3. Select .

The top 50 entries appear in the table.

Note

• Select the clock display area to query a random date and time. If you click the text range for date and time, the option appears to select the date and time.

• If you select each column in the table header, you can sort the list by the selected item.

• You can sort the searched results according to the items selected in Sort by. Select a desired item and then select .

• In View count, you can set the number of items to display in the table. After all settings are finished, select .

• To download the viewed data as a CSV file, select .

• If you select query from the searched result list, the SQL details window appears. For more information, see the following.

Column information guide

Field name	Description
digest_text	SQL statement
digest	Hash value of digest_text
schema_name	Schema name
count_star	Execution count
timer_wait	Total execution time (sec)
lock_time	Waited time caused by table lock (sec)
rows_affected	Number of affected rows (insert, update, replace)
rows_sent	Number of returned rows
rows_examined	Number of rows reviewed in the server layer
created_tmp_disk_tables	If the sum of disk temporary tables created by the server while executing commands is too high, consider increasing the sort memory size.
created_tmp_tables	Sum of internal temporary tables created by the server while executing commands.
select_full_join	Number of table scans without using indexes. If it is not 0, index checking is required.
select_full_range_join	Number of joins using the range search on reference tables
select_range	Number of joins using the range in the first table
select_range_check	Number of joins with no key. If it is not 0, checking is required.
select_scan	Number of full scans to the driving table
sort_merge_passes	Count sorted by sort merge
sort_range	Count sorted by range
sort_rows	Number of sorted rows
sort_scan	Count sorted by scan
no_index_used	Number of queries without using any index. Indexing is required.
no_good_index_used	Number of times that no index is found for use among the indexes on the server. If the count increases, index checking is required.

Previewing the query

You can preview the query statement by moving the mouse pointer to the sql column in the search results list.

See query details

• View SQL Statistics: You can go to the SQL statistics menu where you can check statistical information related to the SQL query statement.

• Default Bind: You can bind the part set as a variable in the query sentence to the default value.

• Reset Bind: You can release the bound default value and check the variable.

• Formatting: You can improve readability by applying indentation and formatting to a SQL query statement.

• Plan: To check plan information, enter DB Name, User Name, and Password, and then select .

  • Text: You can check plan information in text format.

  • JSON: By viewing the plan information in Json format, you can quickly find the points where bottlenecks occur.

  Note

  The function to view plan information in JSON format is supported only in DBX agent 1.6.15 or later.

Tip

If you select a table or index while viewing the plan information in JSON format, the Object detail window appears where you can see the configurations for columns and index items in the table.

This function is supported in DBX agent 1.6.15 or later. For more information about the database roles and related settings, see the following.

Filtering the searched results

Adding the filter conditions

1. In the Filter option, select .

2. In Filter key, select a desired filtering criteria.

   • If the value of the selected item is text, you can select any of Includes (blue) and Excludes (red).

   • If the value of the selected item is number, you can select any condition of == (equal to), >= (greater than or equal to), and <= (less than or equal to).

3. In Condition, select a condition.

4. Enter a string or number to match the condition.

5. Select Apply.

Note

• To add filtering conditions, select Add and then repeat Step 1 to 5. Added conditions are applied based on AND (&&).

• To delete some items while adding conditions, select on the right of the filter conditions. To delete all conditions, select Delete All.

• To quickly delete the conditions applied to the Filter option, select .

Modifying the filter conditions

Click any applied to the Filter option. If the Edit filter window appears, modify any desired items and then select Apply.

Setting the table columns

You can hide the table header columns or add any of them. You can also change the column order. Select .

Note

• After configuration, select Confirm to apply the settings in the table.

• In the search bar, enter text to search the desired columns. Only the columns that meet the entered text are displayed.

• Images may differ depending on the product, project, or menu.

Adding columns

From the list, select the items to add as table header columns. To select all items, select Select All.

Deleting columns

From the list, unselect the columns to delete. Alternatively, select on the right of the item to delete from the list.

Changing the column order

Drag an item to reposition from the list, and then move it to the desired position.

Initializing the configuration

To cancel all changes and reset them, select Initialized.