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 }

pt-query-digest common usage scenarios

by nivas on April 10, 2015

 

1) To find slow queries

pt-query-digest /var/lib/mysql/cloud-server-01-slow.log > slowqueroutput.log

2) Between a particular time range

pt-query-digest –since ’2015-04-10 00:00:00′ –until ’2015-04-10 23:59:59′ /var/lib/mysql/cloud-server-01-slow.log > slowqueryrange.log

3) For a particular database

pt-query-digest –filter ‘$event->{db} && $event->{db} =~ /employee/’ /var/lib/mysql/cloud-server-01-slow.log > slowqueroutputemp.log

pt-query-digest –filter ‘$event->{db} && $event->{db} =~ /market/’ /var/lib/mysql/cloud-server-01-slow.log > slowqueroutputmkt.log

4) For a particular user

pt-query-digest –filter ‘$event->{user} && $event->{user} =~ /root/’ /var/lib/mysql/cloud-server-01-slow.log > slowqueryoutputuser.log

{ Comments on this entry are closed }

Musings on MySQL enhancements

April 1, 2015

1) Update_time not accurate in information_schema.tables Information_schema.tables has a field called update_time and in 5.6 you will notice that it is always NULL. Future versions of MySQL will fix this problem 2) Multiple instance manager Currently creating , managing and dropping instances in MySQL requires many DBA hours. mysqld_multi is there to help you but […]

Read the full article →

MySQL Vs Db2 – Part 1

March 9, 2015

MySQL is open-source RDBMS and is gaining popularity in the recent years.. I am starting a comparison series for mysql and db2..This series will help MySQL DBAs to catchup Db2 quickly and vice versa.. Please post your feedback 1. Knowing the software version : mysql> select version() ;   +———–+ | version() | +———–+ | […]

Read the full article →

mysqlfailover – Installation and Operations tutorial

March 6, 2015

Configuration   1) Download the utilities. Python connector is a prerequisite and this needs to be installed along with mysql utilities rpm wget http://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.0.2-1.el6.noarch.rpm wget http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.5.3-1.el6.noarch.rpm   2) Install the RPMs. “Mysqlfailover –help” will confirm the install of the utilities rpm –ivh mysql-connector-python-2.0.2-1.el6.noarch.rpm rpm -ivh mysql-utilities-1.5.3-1.el6.noarch.rpm   3) Set the GTID MODE to ON. GTID […]

Read the full article →

mysqldump – 25 tips for DBAs

December 1, 2014

1) Is mysqldump text backup or binary backup It is a text backup . If you open the backup file you will see all the statements that can be used to recreate databases and objects . It also has the insert statements to populate the tables with data 2) What is the syntax for mysqldump […]

Read the full article →

MySQL and swapping

November 24, 2014

Did you ever encounter swap space issue with MySQL ? This problem is really annoying and here are some possible solutions : 1) Track memory usage.. Try to identify the bottleneck using query below. It is not trivial job to zero in on the problem heap.. There are several temp tables being created at run […]

Read the full article →

Slow query log – Confusing information in manuals

October 16, 2014

Below is the excerpt from MySQL manuals on slow query log : “By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed using log_slow_admin_statements and log_queries_not_using_indexes, as described later. “ This manual entry is kind of misleading . It means that queries not […]

Read the full article →

10 common replication problems

September 23, 2014

Below are 10 common replication problems : 1)Session binlog : Setting sql_log_bin = 0 will disable binary logging for the session. So any DML/DDL query executed within that session on the master will not be replicated to slave Example: Turn off binary logging on master : mysql> set sql_log_bin = 0 ; Query OK, 0 […]

Read the full article →

Using information_schema tables – Part 1

September 7, 2014

Below are some of the common scenarios where information_schema is useful : Tables table How to check the database size using information_schema ? SELECT table_schema “Data Base Name”, SUM( data_length + index_length) / 1024 / 1024 “Data Base Size in MB” FROM information_schema.TABLES GROUP BY table_schema ; How to list the tables created before 4 […]

Read the full article →