MySQL: Performance Schema

by Mark Nielsen
Copyright August 2023

In time, we will adding more sections.

Links
Setup
Monitoring
MySQL Workbench
Queries

Setup

In your my.cnf, see this

[mysqld]
performance_schema=ON

and you can verify it is on with a global query: " SHOW VARIABLES LIKE 'performance_schema';" which should output

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

Monitoring

There are a few notes on monitoring.

If you do it manually, you should not query the same results. The tables are not indexed.
Read more on limitations at : https://orangematter.solarwinds.com/2020/01/12/mysql-query-performance-statistics-in-the-performance-schema/
And other limitations in performance_schema : https://dev.mysql.com/doc/refman/8.0/en/performance-schema-restrictions.html
It is easier to monitor with a tool such as
- MySQL Workbench. It is free.
  https://dev.mysql.com/doc/workbench/en/wb-performance-reports.html
- Percona PMM. Its free.
  https://www.percona.com/software/database-tools/percona-monitoring-and-management
New Relic and Solar Winds cost money, but have good interfaces. Particularly catching queries that occupy a lot of time or resources.

My recommendation, first use workbench, then PMM, and Solar Windows or New Relic.

MySQL Workbench

First, if this is a new server you must do a few things:

In /etc/mysql/mysql.conf.d/mysqld.conf or wherever your my.cnf is, put this in it.
```
[mysqld]
performance_schema = ON
bind-address            = 0.0.0.0
mysqlx-bind-address     = 0.0.0.0
```
In addition, remove bind-address and mysqlx-bind-address if later in the file.
Create an account. NOTE: Change "BAD_PASSWORD" to another password. We are making this account with most permissions. Do not do this is production. Only on a test system.
```
create user test_user@'%' identified by 'BAD_PASSWORD';
grant all privileges on *.* to test_user@'%';
```
Continue making your database connection from https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-new.html
NOTE: Set in the connection to require SSL.

Now workbench is installed.

Open database connection.
Under View, then Panels, hide the secondary side bar.
Click on Performance Reports in the left menu.
Browse the reports.

Queries

Recommendations:

Pull the queries in a python script or other script.
Apply a regex patterns matching for values searched. This hides the queries.
Run a slow query on two or more queries with actual values. Do not display the query, just the results. If secure, display the query as well.
Display a report of the slowest running queries, the ones with the most locks, and the worst performance (read versus executed rows).

TODO: give an regex python script. Look ay mysqldumpslow. You can make a Perl method or function out of it. And convert it to Python.

Links with example queries:

https://archive.fosdem.org/2015/schedule/event/pfs_troubleshooting/attachments/slides/605/export/events/attachments/pfs_troubleshooting/slides/605/P_S.pdf
https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html
https://dev.mysql.com/doc/refman/5.7/en/monitor-innodb-mutex-waits-performance-schema.html
https://dev.to/emmanuelnk/how-to-monitor-long-running-alters-in-aws-rds-mysql-57-using-the-performance-schema-4m4o
https://percona.community/blog/2022/10/18/mysql-tracing-a-single-query-with-performance_schema/




-------------------------------------------

To detect which indexes are not used.
ref: https://www.databasejournal.com/mysql/an-overview-of-the-mysql-performance-schema/

SELECT object_schema, object_name, index_name
 FROM performance_schema.table_io_waits_summary_by_index_usage
 WHERE index_name IS NOT NULL
 AND count_star = 0
 ORDER BY object_schema, object_name;

Also, finding hogging queries

SELECT PROCESSLIST_ID AS id, 
        PROCESSLIST_USER AS user, 
        PROCESSLIST_HOST AS host, 
        PROCESSLIST_DB AS db, 
        PROCESSLIST_COMMAND AS command, 
        PROCESSLIST_TIME AS time, 
        PROCESSLIST_STATE AS state, 
        LEFT(PROCESSLIST_INFO, 80) AS info
 FROM performance_schema.threads
 WHERE PROCESSLIST_ID IS NOT NULL
 AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump')
 ORDER BY PROCESSLIST_TIME ASC;

But I would descend and limit by 10. The longest running query is probably the issue.
NOTE: This is similar to "show processlist". You can modify it. 

SELECT PROCESSLIST_ID AS id, 
        PROCESSLIST_USER AS user, 
        PROCESSLIST_HOST AS host, 
        PROCESSLIST_DB AS db, 
        PROCESSLIST_COMMAND AS command, 
        PROCESSLIST_TIME AS time, 
        PROCESSLIST_STATE AS state, 
        LEFT(PROCESSLIST_INFO, 80) AS info
 FROM performance_schema.threads
 WHERE PROCESSLIST_ID IS NOT NULL
 AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump')
 ORDER BY PROCESSLIST_TIME DESC limit 10;

-- -----------------------------------------------------------------
Here are the things we can examine
mysql> desc events_statements_history;
+-------------------------+------------------------------------------------+------+-----+---------+-------+
| Field                   | Type                                           | Null | Key | Default | Extra |
+-------------------------+------------------------------------------------+------+-----+---------+-------+
| THREAD_ID               | bigint unsigned                                | NO   | PRI | NULL    |       |
| EVENT_ID                | bigint unsigned                                | NO   | PRI | NULL    |       |
| END_EVENT_ID            | bigint unsigned                                | YES  |     | NULL    |       |
| EVENT_NAME              | varchar(128)                                   | NO   |     | NULL    |       |
| SOURCE                  | varchar(64)                                    | YES  |     | NULL    |       |
| TIMER_START             | bigint unsigned                                | YES  |     | NULL    |       |
| TIMER_END               | bigint unsigned                                | YES  |     | NULL    |       |
| TIMER_WAIT              | bigint unsigned                                | YES  |     | NULL    |       |
| LOCK_TIME               | bigint unsigned                                | NO   |     | NULL    |       |
| SQL_TEXT                | longtext                                       | YES  |     | NULL    |       |
| DIGEST                  | varchar(64)                                    | YES  |     | NULL    |       |
| DIGEST_TEXT             | longtext                                       | YES  |     | NULL    |       |
| CURRENT_SCHEMA          | varchar(64)                                    | YES  |     | NULL    |       |
| OBJECT_TYPE             | varchar(64)                                    | YES  |     | NULL    |       |
| OBJECT_SCHEMA           | varchar(64)                                    | YES  |     | NULL    |       |
| OBJECT_NAME             | varchar(64)                                    | YES  |     | NULL    |       |
| OBJECT_INSTANCE_BEGIN   | bigint unsigned                                | YES  |     | NULL    |       |
| MYSQL_ERRNO             | int                                            | YES  |     | NULL    |       |
| RETURNED_SQLSTATE       | varchar(5)                                     | YES  |     | NULL    |       |
| MESSAGE_TEXT            | varchar(128)                                   | YES  |     | NULL    |       |
| ERRORS                  | bigint unsigned                                | NO   |     | NULL    |       |
| WARNINGS                | bigint unsigned                                | NO   |     | NULL    |       |
| ROWS_AFFECTED           | bigint unsigned                                | NO   |     | NULL    |       |
| ROWS_SENT               | bigint unsigned                                | NO   |     | NULL    |       |
| ROWS_EXAMINED           | bigint unsigned                                | NO   |     | NULL    |       |
| CREATED_TMP_DISK_TABLES | bigint unsigned                                | NO   |     | NULL    |       |
| CREATED_TMP_TABLES      | bigint unsigned                                | NO   |     | NULL    |       |
| SELECT_FULL_JOIN        | bigint unsigned                                | NO   |     | NULL    |       |
| SELECT_FULL_RANGE_JOIN  | bigint unsigned                                | NO   |     | NULL    |       |
| SELECT_RANGE            | bigint unsigned                                | NO   |     | NULL    |       |
| SELECT_RANGE_CHECK      | bigint unsigned                                | NO   |     | NULL    |       |
| SELECT_SCAN             | bigint unsigned                                | NO   |     | NULL    |       |
| SORT_MERGE_PASSES       | bigint unsigned                                | NO   |     | NULL    |       |
| SORT_RANGE              | bigint unsigned                                | NO   |     | NULL    |       |
| SORT_ROWS               | bigint unsigned                                | NO   |     | NULL    |       |
| SORT_SCAN               | bigint unsigned                                | NO   |     | NULL    |       |
| NO_INDEX_USED           | bigint unsigned                                | NO   |     | NULL    |       |
| NO_GOOD_INDEX_USED      | bigint unsigned                                | NO   |     | NULL    |       |
| NESTING_EVENT_ID        | bigint unsigned                                | YES  |     | NULL    |       |
| NESTING_EVENT_TYPE      | enum('TRANSACTION','STATEMENT','STAGE','WAIT') | YES  |     | NULL    |       |
| NESTING_EVENT_LEVEL     | int                                            | YES  |     | NULL    |       |
| STATEMENT_ID            | bigint unsigned                                | YES  |     | NULL    |       |
| CPU_TIME                | bigint unsigned                                | NO   |     | NULL    |       |
| MAX_CONTROLLED_MEMORY   | bigint unsigned                                | NO   |     | NULL    |       |
| MAX_TOTAL_MEMORY        | bigint unsigned                                | NO   |     | NULL    |       |
| EXECUTION_ENGINE        | enum('PRIMARY','SECONDARY')                    | YES  |     | NULL    |       |
+-------------------------+------------------------------------------------+------+-----+---------+-------+


-------------------------
To see all queries

select thread_id, event_id, sql_text from events_statements_history  where sql_text is not NULL;

To see which queries are the worst performing in terms of rows_sent versus rows_examined.

----------------------
These are my queries.

To see worst performing queries

select   rows_examined, rows_sent, cast((rows_sent/rows_examined)*100 as unsigned)  as rows_percent, sql_text
  from events_statements_history
  where sql_text is not NULL and rows_examined > 100
  order by rows_percent ASC, rows_examined;

-----------------------------------
Queries with most rows examined

select   rows_examined, sql_text
  from events_statements_history
  where sql_text is not NULL and rows_examined > 100
  order by  rows_examined desc;

NOTE: You may want to pattern match this. 

-----------------------------------
Queries with most rows sent

select   rows_examined, sql_text
  from events_statements_history
  where sql_text is not NULL and rows_examined > 100
  order by  rows_examined desc;

NOTE: You may want to pattern match this. 

------

To get the queries with most rows examined (the busiest)
1. regex all queries and group them
2. For each query pattern, add up the rows_examined, and rows_sent.
3. Display results. 

-------------------------

Eanble all recordings, this might slow the server.
UPDATE  performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'stage/%';
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_stages%';

Get all timings for queries where statement is not null and more than 100 rows. 

select esh1.thread_id, esh2.nesting_event_id, esh2.event_id, esh2.timer_wait, esh2.event_name
       from performance_schema.events_statements_history_long esh1
       	    join performance_schema.events_stages_history_long esh2
	      on (esh1.thread_id=esh2.thread_id and esh1.event_id = esh2.nesting_event_id)
where esh1.sql_text is not NULL and esh1.rows_examined > 100;


Sum the times, display the highest.

select esh1.thread_id, esh1.event_id, sum(esh2.timer_wait)/1000000000000 as total_time, esh1.sql_text
       from performance_schema.events_statements_history_long esh1
            join performance_schema.events_stages_history_long esh2
              on (esh1.thread_id=esh2.thread_id and esh1.event_id = esh2.nesting_event_id)
       where esh1.sql_text is not NULL and esh1.rows_examined > 100
       	 and esh1.current_schema not in ('mysql', 'performance_schema')
       group by esh1.thread_id, esh1.event_id, esh1.sql_text
       order by total_time desc\G

I tried events_stages_current and events_stages_history, but they emptied the queries. Current is only if it is currently running.
Not sure about events_stages_history;

Detect highest times per stage for the highest 4 queries for stages more than 0.0001

select esh1.thread_id, esh1.event_id, sum(esh2.timer_wait)/1000000000000 as total_time, esh1.sql_text
       from performance_schema.events_statements_history_long esh1
            join performance_schema.events_stages_history_long esh2 on
	       (esh1.thread_id=esh2.thread_id and esh1.event_id = esh2.nesting_event_id)
       where esh1.sql_text is not NULL and esh1.rows_examined > 100
         and esh1.current_schema not in	('mysql', 'performance_schema')
         and esh1.sql_text not like '%performance_schema%'
       group by esh1.thread_id, esh1.event_id, esh1.sql_text
       order by total_time desc   limit 4;

Only select select statements.

create database if not exists temp;
use temp;
drop table if exists total_time;
select i3.i, i5.i2 from mark.i as i3 join mark.i2 as i5 on (i3.i = i5.i2) limit 200;

create temporary table total_time
select esh1.thread_id, esh1.event_id, sum(esh2.timer_wait)/1000000000 as total_time_ms, esh1.sql_text
       from performance_schema.events_statements_history_long esh1
            join performance_schema.events_stages_history_long esh2 on
               (esh1.thread_id=esh2.thread_id and esh1.event_id = esh2.nesting_event_id)
       where esh1.sql_text is not NULL and esh1.rows_examined > 100
      	 and esh1.current_schema not in ('mysql', 'performance_schema')
      	 and esh1.sql_text not like '%performance_schema%' and esh1.sql_text  like '%select%'
       group by esh1.thread_id, esh1.event_id, esh1.sql_text
       order by total_time_ms desc   limit 4;


Without the query.

select t.thread_id,t.event_id, esh2.nesting_event_id, esh2.event_name,
   esh2.timer_wait/1000000000 as timer_wait_ms, t.total_time_ms
       from total_time t
            join performance_schema.events_stages_history_long esh2 on
	       (t.thread_id=esh2.thread_id and t.event_id = esh2.nesting_event_id)
;


With query -- kinda of messy.

select t.thread_id,t.event_id, esh2.nesting_event_id, esh2.event_name,
    esh2.timer_wait/1000000000 as timer_wait_ms, t.total_time_ms, t.sql_text as sql1
       from total_time t
            join performance_schema.events_stages_history_long esh2 on
               (t.thread_id=esh2.thread_id and t.event_id = esh2.nesting_event_id)
;


Change format;

select t.thread_id,t.event_id, esh2.nesting_event_id, esh2.event_name,
   esh2.timer_wait/1000000000 as timer_wait_ms, t.total_time_ms, t.sql_text as sql1
       from total_time t
            join performance_schema.events_stages_history_long esh2 on
               (t.thread_id=esh2.thread_id and t.event_id = esh2.nesting_event_id)
\G

 
----------------------
TODO: Queries by otehr things, like IO