MySQL Performance Schema to identify SQL Statements which the execution duration exceeds average timing by 30%

 

Statement Digest in MySQL 

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-digests.html

The parser is also used by the STATEMENT_DIGEST_TEXT() and STATEMENT_DIGEST() functions, which applications can call to compute a normalized statement digest and a digest hash value, respectively, from an SQL statement.

events_statement_summary_by_digest in Performance Schema contains information

+-----------------------------+-----------------+------+-----+---------+-------+

| Field                       | Type            | Null | Key | Default | Extra |

+-----------------------------+-----------------+------+-----+---------+-------+

| SCHEMA_NAME                 | varchar(64)     | YES  | MUL | NULL    |       |

| DIGEST                      | varchar(64)     | YES  |     | NULL    |       |

| DIGEST_TEXT                 | longtext        | YES  |     | NULL    |       |

| COUNT_STAR                  | bigint unsigned | NO   |     | NULL    |       |

| SUM_TIMER_WAIT              | bigint unsigned | NO   |     | NULL    |       |

| MIN_TIMER_WAIT              | bigint unsigned | NO   |     | NULL    |       |

| AVG_TIMER_WAIT              | bigint unsigned | NO   |     | NULL    |       |

| MAX_TIMER_WAIT              | bigint unsigned | NO   |     | NULL    |       |

| SUM_LOCK_TIME               | bigint unsigned | NO   |     | NULL    |       |

| SUM_ERRORS                  | bigint unsigned | NO   |     | NULL    |       |

| SUM_WARNINGS                | bigint unsigned | NO   |     | NULL    |       |

| SUM_ROWS_AFFECTED           | bigint unsigned | NO   |     | NULL    |       |

| SUM_ROWS_SENT               | bigint unsigned | NO   |     | NULL    |       |

| SUM_ROWS_EXAMINED           | bigint unsigned | NO   |     | NULL    |       |

| SUM_CREATED_TMP_DISK_TABLES | bigint unsigned | NO   |     | NULL    |       |

| SUM_CREATED_TMP_TABLES      | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_FULL_JOIN        | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_FULL_RANGE_JOIN  | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_RANGE            | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_RANGE_CHECK      | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_SCAN             | bigint unsigned | NO   |     | NULL    |       |

| SUM_SORT_MERGE_PASSES       | bigint unsigned | NO   |     | NULL    |       |

| SUM_SORT_RANGE              | bigint unsigned | NO   |     | NULL    |       |

| SUM_SORT_ROWS               | bigint unsigned | NO   |     | NULL    |       |

| SUM_SORT_SCAN               | bigint unsigned | NO   |     | NULL    |       |

| SUM_NO_INDEX_USED           | bigint unsigned | NO   |     | NULL    |       |

| SUM_NO_GOOD_INDEX_USED      | bigint unsigned | NO   |     | NULL    |       |

| SUM_CPU_TIME                | bigint unsigned | NO   |     | NULL    |       |

| COUNT_SECONDARY             | bigint unsigned | NO   |     | NULL    |       |

| FIRST_SEEN                  | timestamp(6)    | NO   |     | NULL    |       |

| LAST_SEEN                   | timestamp(6)    | NO   |     | NULL    |       |

| QUANTILE_95                 | bigint unsigned | NO   |     | NULL    |       |

| QUANTILE_99                 | bigint unsigned | NO   |     | NULL    |       |

| QUANTILE_999                | bigint unsigned | NO   |     | NULL    |       |

| QUERY_SAMPLE_TEXT           | longtext        | YES  |     | NULL    |       |

| QUERY_SAMPLE_SEEN           | timestamp(6)    | NO   |     | NULL    |       |

| QUERY_SAMPLE_TIMER_WAIT     | bigint unsigned | NO   |     | NULL    |       |

+-----------------------------+-----------------+------+-----+---------+-------+

The events_statements_history_long[events_statement_current / events_statement_history] contains information about a SQL statement and Digest together with the execution time.  With referencing the events_statement_history_long, we can track the execution timing compared to the average execution time from table "events_statements_summary_by_digest".

1. To enable 'performance_schema" to collect events_statements_history_long, we need to enable the consumer (for the *history_long).  The default value is OFF which means the events_statements_history_long is empty without any data.   Enabling the events_statements_history_long allows more SQL statements to be captured and compared with the average execution time.

mysql > update setup_consumers set enabled='YES' where name = 'events_statements_history_long';

2. Retrieving the list of SQL Statements where the digest is the same and the execution time is having 30% over the average timing.

mysql > select a.sql_text,  a.digest, (a.timer_end - a.timer_start) duration, b.avg_timer_wait 

          > from events_statements_history_long a, events_statements_summary_by_digest b 

          > where a.digest = b.digest and b.avg_timer_wait > (a.timer_end - a.timer_start) *1.3

Performance schema / sys schema contains valuable information which we can use them to track / tune our system.  It can be the SQL.  It can be storage or files.  

You can share more with others about how you can reuse the performance_schema tables to improve your application.

Enjoy reading!

China Encryption（国密算法）with MySQL

China Encryption Standards 

There are SM1,SM2, SM3, SM4, SM7, SM9, ...etc with China encryption standards.  With international encryption standards, we can leverage encryption libraries from publicly available source(s). For example, one of the most common and popular use encryption library is OpenSSL. 

This tutorial is written to provide steps with one of the "SSL" library having SMx (China Encryption Library) namely BabaSSL with MySQL.  It does not serve as a purpose of the only way adopting China Encryption Standards with MySQL. Applying compatible / Latest OpenSSL library from China Encryption vendor(s)/source(s) may possibly work in similar way as documented in this tutorial.

Environment (for the tutorial sharing) 

1. Compute Resource (VM) 

2. Operating System - Compute VM is provisioned with Oracle Linux 8 

3. MySQL community with 8.0.xis installed via public yum repository 

4. BabaSSL 8.3 [ BabaSSL 8.3.2-dev ] is used. It is based on OpenSSL 1.1.1h dated 22 Sep 2020

Introduction 

MySQL leverages OpenSSL library to provide ciphers with TLS encryption over communication channel.  To allow switching the OpenSSL library with China Standards and MySQL can dynamically use the underlying library feature to enforce the communication using China Encryption Standard.

Steps 

1. Provision VM and OS/packages update 

2. Download BabaSSL 8.3.2 stable source build for compilation 

3. Compile BabaSSL and Install BabaSSL 

4. Install MySQL 8.0.30 (from yum repository) 

5. Configure TLS with SMx for MySQL 

6. Change system service for mysqld with alternate BabaSSL library path 

7. Reload and restart mysqld service 

8. MySQL (mysql) client with BabaSSL library over TLS with SMx connection 

Provision VM and OS / Packages update

Compute Instance (VM) is provisioned with Oracle Linux 8.  Once the VM is provisioned and it is ready for connection, login to the shell terminal and apply update and package updates

Login from terminal as  

```

ssh -i <privatekey>  opc@<public IP> 

# sudo yum update

# sudo yum install wget

```

Download BabaSSL 8.3.2 stable source build for compilation 

To download the 8.3 stable source zip file, execute the following commands and extract the zip file 

```

# wget https://github.com/Tongsuo-Project/Tongsuo/archive/refs/heads/8.3-stable.zip

# unzip 8.3-stable.zip

```

To compile the source, change directory to the unzipped folder 'Tongsuo-8.3-stable" and execute commands as follows :

```

# cd Tongsuo-8.3-stable

# mkdir bld

# ../config

# make

```

The installation will put the BabaSSL into /usr/local/bin and /usr/local/lib64 and corresponding default installation path(s).   

Note : The default installation does not replace any standard OS system own OpenSSL but it puts into /usr/local as alternate installation.

```

# sudo make install

```

Finally, make change to /etc/profile and append the following

```

# export LD_LIBRARY_PATH=/usr/local/lib64:$LD_LIBRARY_PATH

```

Exit the terminal and re-login to the VM 

Launch a SSH connection to the Compute VM with new library path appended (from updated /etc/profile).   

To check BabaSSL and SMx ciphers, execute the following commands and validate if BabaSSL is installed.

```

# openssl version

# openssl -v ciphers|grep SM

```

Output should be displayed as follows :

MySQL Router Restful API

MySQL Router with InnoDB Cluster allows transparent routing access for application to backend MySQL Servers.  

Restful API (Http access) feature was added to MySQL Router since 8.0.17.

This article is written to provide a tutorial with Router Restful API.   It is tested with MySQL 8.0.29 release.

Writing MySQL Shell Plugin (REPORT)

MySQL Shell is a powerful tool.  It allows Javascript, Python and SQL access to MySQL.

Usage of MySQL Shell can be
1. As admin tool to MySQL Server
2. As Scripting Engine to run Javascript or Python or SQL to access MySQL Server
3. As a tools to allow Document Store access (like collection API) with MySQL Server
4. As MySQL InnoDB Cluster admin Tooling

Customization and extensible framework is essential to a tool as such DB administrators can create customized report and extension to do the daily job.

This tutorial is written to provide a short summary how we can create a MySQL Shell Report in Python.

What is MySQL Shell Report
MySQL Shell report can be executed thru "\show"


 There are 3 'report's coming with MySQL Shell - namely 'query', 'thread', 'threads'.

Query Report :
Getting the "help" with a report can simply to run "\show query --help"


Running a Report with a connected session as such the query is executed and printed on the screen (e.g. Running "SELECT 1" query)





Creating a New Report in Python - DB Size

MySQL Shell allows customization based on the $HOME/.mysqlsh/plugins/ folder

When MySQL Shell is launched, it looks for all initialization script (init.py / init.js) within the "plugins" folder.

File : $HOME/.mysqlsh/plugins/ext/dbutil/init.py

---

# The function definition is given as follows - show_dbsize (  3 arguments ) def show_dbsize(session, args, options):   query = "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)', count(table_name) as '# of tables' FROM information_schema.TABLES GROUP BY table_schema"   if (options.has_key('limit')):     query += ' limit ' + str(options['limit'])   result = session.run_sql(query);   report = []   if (result.has_data()):     report = [result.get_column_names()]     for row in result.fetch_all():         report.append(list(row))   return {"report": report} # Register the show_dbsize function as a MySQL Shell report shell.register_report("dbsize", "list", show_dbsize,         {         "brief":"To list the tables without Primary Key.",         'details': ['You need the SELECT privilege on INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS.'],         'options': [             {                 'name': 'limit',                 'brief': 'The maximum number of rows to return.',                 'shortcut': 'l',                 'type': 'integer'             }         ],         'argc': '0'     } )

---

Once the file is created under the $HOME/.mysqlsh/plugins/ext/dbutil/init.py, MySQL Shell looks up NEW report and the "\show" shows the added "dbsize" report.



With a CONNECTED session, and Running the report can be simply to execute "\show dbsize"



There is OPTION "limit" which we put into the Python init.py.  To show only 3 lines, we can add option (-l 3) as shown as follows.



Enjoy Reading!