20 Common Performance_schema FAQs

by nivas on January 16, 2017

1.What are the different types of tables ?

Current/Summary/History/

Setup tables:
Setup_consumers
Setup_actors
Setup_instruments
………………….

Waits

| events_waits_current |
| events_waits_history |
| events_waits_history_long |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_by_user_by_event_name |
| events_waits_summary_global_by_event_name |

Statements

| events_statements_current |
| events_statements_history |
| events_statements_history_long |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest |
| events_statements_summary_by_host_by_event_name |
| events_statements_summary_by_thread_by_event_name |
| events_statements_summary_by_user_by_event_name |
| events_statements_summary_global_by_event_name

Stages

| events_stages_current |
| events_stages_history |
| events_stages_history_long |
| events_stages_summary_by_account_by_event_name |
| events_stages_summary_by_host_by_event_name |
| events_stages_summary_by_thread_by_event_name |
| events_stages_summary_by_user_by_event_name |
| events_stages_summary_global_by_event_name

Misc

2. By default what instruments are turned on ?

Only global,thread,transaction and statements instrumentation is enabled by default

Others like stage,synch events etc,, are disabled by default

You can check the complete list of enabled instruments in setup_instruments

3. What consumers are enabled by default ?

mysql> select * from performance_schema.setup_consumers ;
+——————————–+———+
| NAME | ENABLED |
+——————————–+———+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | NO |
| events_statements_history_long | NO |
| events_waits_current | NO |
| events_waits_history | NO |
| events_waits_history_long | NO |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+——————————–+———+
12 rows in set (0.00 sec)

4. How big the history table can get ?

Performance_schema_events_statements_history_size is the number of rows by thread. Default is 10

5. What is the difference between history and history long ?

Performance_schema_events_statements_history_long_size Default is 10000. Maximum number of rows

6. What are some examples of wait events ?

wait/io/file/sql/binlog
wait/io/file/innodb/innodb_data_file
wait/io/socket — Network io

7. What are the time units for statement execution ?

Picoseconds
One trillion of a second

8. How much memory is being used by performance_schema ?

SELECT * FROM memory_summary_global_by_event_name

WHERE EVENT_NAME LIKE ‘memory/performance_schema/%’;

9. What is the overhead ?

10% — As recorded by Percona and Mark
20% — If all instruments are enabled

10.How to check the previous statments run by a particular thread ?

Select * from events_statement_history where thread_id=60 \G

11. What is part of stored routine call ?

statement/sp/statement1
statement/sp/statement2

12. What is the replacement for show profile ?

Stage tables

13. What is the kind of replacement for slow log ?

Events_statements_summary_by_digest

14. What is the default size of events_statements_summary_by_digest ? How to know whether it is sufficient for your workload or not ?

The events_statements_summary_by_digest table has a limited maximum number of rows (200 by default, but MySQL 5.6.5 can be modified with the performance_schema_digests_size variable). As a consequence, when the table is full, statement digest values that have no already existing row will be added to a special “catch-all” row with DIGEST = NULL. In plain English: you won’t have meaningful info for those statements.

15. What are important columns in events_statements_summary_by_digest ?

Column

Description

SCHEMA NAME

Database name. Records are summarised together with DIGEST.

DIGEST

Performance Schema digest. Records are summarised together with SCHEMA NAME.

DIGEST TEXT

The unhashed form of the digest.

COUNT_STAR

Number of summarized events

SUM_TIMER_WAIT

Total wait time of the summarized events that are timed.

MIN_TIMER_WAIT

Minimum wait time of the summarized events that are timed.

AVG_TIMER_WAIT

Average wait time of the summarized events that are timed.

MAX_TIMER_WAIT

Maximum wait time of the summarized events that are timed.

SUM_LOCK_TIME

Sum of the LOCK_TIME column in the events_statements_current table.

SUM_ERRORS

Sum of the ERRORS column in the events_statements_current table.

SUM_WARNINGS

Sum of the WARNINGS column in the events_statements_current table.

SUM_ROWS_AFFECTED

Sum of the ROWS_AFFECTED column in the events_statements_current table.

SUM_ROWS_SENT

Sum of the ROWS_SENT column in the events_statements_current table.

SUM_ROWS_EXAMINED

Sum of the ROWS_EXAMINED column in the events_statements_current table.

SUM_CREATED_TMP_DISK_TABLES

Sum of the CREATED_TMP_DISK_TABLES column in the events_statements_current table.

SUM_CREATED_TMP_TABLES

Sum of the CREATED_TMP_TABLES column in the events_statements_current table.

SUM_SELECT_FULL_JOIN

Sum of the SELECT_FULL_JOIN column in the events_statements_current table.

SUM_SELECT_FULL_RANGE_JOIN

Sum of the SELECT_FULL_RANGE_JOIN column in the events_statements_current table.

SUM_SELECT_RANGE

Sum of the SELECT_RANGE column in the events_statements_current table.

SUM_SELECT_RANGE_CHECK

Sum of the SELECT_RANGE_CHECK column in the events_statements_current table.

SUM_SELECT_SCAN

Sum of the SELECT_SCAN column in the events_statements_current table.

SUM_SORT_MERGE_PASSES

Sum of the SORT_MERGE_PASSES column in the events_statements_current table.

SUM_SORT_RANGE

Sum of the SORT_RANGE column in the events_statements_current table.

SUM_SORT_ROWS

Sum of the SORT_ROWS column in the events_statements_current table.

SUM_SORT_SCAN

Sum of the SORT_SCAN column in the events_statements_current table.

SUM_NO_INDEX_USED

Sum of the NO_INDEX_USED column in the events_statements_current table.

SUM_NO_GOOD_INDEX_USED

Sum of the NO_GOOD_INDEX_USED column in the events_statements_current table.

FIRST_SEEN

Time at which the digest was first seen.

LAST_SEEN

Time at which the digest was most recently seen.

SUM_ROWS_AFFECTED :

This is for update,insert or delete statements

ROWS_SENT Vs ROWS_EXAMINED

If rows_examined is by far larger than rows_sent, say 100 larger, then the query is a great candidate for optimization.

SELECT_RANGE

It refers to number of SELECT queries that were performed to satisfy a limitedrange of conditions. The queries

SELECT * FROM tbl1 WHERE col1 BETWEEN 5 AND 13; and

SELECT * FROM tbl1 WHERE col1 > 5 AND col1 < 13; SUM_CREATED_TMP_TABLES Vs SUM_CREATED_TMP_DISK_TABLES How many are created on disk . If the ratio is more then there is a definite problem SORT_RANGE SELECT * FROM users WHERE points > (SELECT points FROM users WHERE id = {user_id}) ORDER By points ASC LIMIT 3;

SORT_MERGE_PASSES

Sort_merge_passes consists of two steps. MySQL will first try to do sorted in memory, The use of memory size is determined by the Sort_buffer_size system variable, If its size is not all the records are read into memory, MySQL will give the in memory sort the results stored in the temporary file, After MySQL finds all records, The temporary file records of a sort. This sort again will increase Sort_merge_passes. In fact, MySQL will use another temporary file to save again to sort the results, so usually see numerical Sort_merge_passes increased two times to build temporary file number. Because of the use of the temporary files, so the speed can be slow, increase Sort_buffer_size will reduce the number of Sort_merge_passes and create a temporaryfile.

16. Some of the metrics you can collect with statements_digest table ?

Queries not using indexes
Queries examining more rows compared to rows sent
Queries doing more sort_merge_passes / temporary tables to disk
Queries throwing more errors
Queries throwing more warnings
Queries sorted by avg exec time
Queries sorted by max exec time
Queries spending more time waiting for locks

17. What is the use of events_waits_current table in real time ?

Client A (ID 2)> select sleep(10);
While the query is still executing, see what event the code is waiting for.

Monitor> select * from EVENTS_WAITS_CURRENT where THREAD_ID=3\G
*************************** 1. row ***************************
THREAD_ID: 3
EVENT_ID: 46
EVENT_NAME: wait/synch/cond/sql/Item_func_sleep::cond
SOURCE: item_func.cc:3527
TIMER_START: 5617352941894736
TIMER_END: NULL
TIMER_WAIT: NULL

SPINS: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 139803207157216
NESTING_EVENT_ID: NULL
OPERATION: timed_wait
NUMBER_OF_BYTES: NULL
FLAGS: 0
1 row in set (0.00 sec)
Looking at the results, we can tell that the query is still waiting (TIMER_WAIT is NULL) for an instrumented condition

18. What are the recent wait events of this connection ?

After the sleep(10) is completed, let’s look at the wait history for Client A connection.

Monitor> select THREAD_ID, EVENT_ID, EVENT_NAME, SOURCE, TIMER_WAIT, OBJECT_INSTANCE_BEGIN, OPERATION from EVENTS_WAITS_HISTORY where THREAD_ID=3 order by THREAD_ID, EVENT_ID;
+———–+———-+———————————————+——————–+—————+———————–+————+
| THREAD_ID | EVENT_ID | EVENT_NAME | SOURCE | TIMER_WAIT | OBJECT_INSTANCE_BEGIN | OPERATION |
+———–+———-+———————————————+——————–+—————+———————–+————+
| 3 | 43 | wait/synch/mutex/mysys/THR_LOCK_malloc | safemalloc.c:181 | 399243 | 18775392 | lock |
| 3 | 44 | wait/synch/mutex/mysys/THR_LOCK_malloc | safemalloc.c:294 | 210035 | 18775392 | lock |
| 3 | 45 | wait/synch/mutex/sql/LOCK_user_locks | item_func.cc:3837 | 500907 | 18580768 | lock |
| 3 | 46 | wait/synch/cond/sql/Item_func_sleep::cond | item_func.cc:3527 | 5000241974790 | 140221896639968 | timed_wait |
| 3 | 47 | wait/synch/cond/sql/Item_func_sleep::cond | item_func.cc:3527 | 5000237350843 | 140221896639968 | timed_wait |
| 3 | 48 | wait/synch/mutex/mysys/my_thread_var::mutex | item_func.cc:3853 | 612102 | 19805600 | lock |
| 3 | 49 | wait/synch/mutex/sql/LOCK_plugin | sql_plugin.cc:1013 | 356883 | 18701152 | lock |
| 3 | 50 | wait/synch/rwlock/sql/LOGGER::LOCK_logger | log.h:582 | 276046 | 18672512 | read_lock |
| 3 | 51 | wait/synch/mutex/sql/LOG::LOCK_log | log.cc:2515 | 372062 | 18989864 | lock |
| 3 | 52 | wait/synch/mutex/sql/THD::LOCK_thd_data | sql_class.cc:3267 | 276046 | 19798240 | lock |
+———–+———-+———————————————+——————–+—————+———————–+————+
10 rows in set (0.00 sec)

19.How to maintain all history ?

https://www.percona.com/blog/2015/10/13/mysql-query-digest-with-performance-schema/

20. What are the improvements in 5.7 ?

Memory tables in detail
Meta data lock troubleshooting

Comments on this entry are closed.

Previous post: