Tuesday, April 5, 2016

Running SYS Schema - "diagnostics" to get insight of your MySQL Database



You may want to dump diagnostic information about the running MySQL Database.  
 
For MySQL 5.7, the SYS schema comes with the installation.  You can find the corresponding information on



For MySQL 5.6,the SYS schema can be downloaded from the following URL

The zip file as ‘mysql-sys-master.zip’ is expanded, where the sys_56.sql can be loaded into the MySQL 5.6 database.

# mysql -uroot -h127.0.0.1 < sys_56.sql

Using MySQL Workbench and connecting to the Server, you can find the SYS schema installed.


Running the “diagnostics” stored procedure :
                Parameter :
120 : the max execution time for diagnostics
30 :  The interval time for output
current : The mode of diagnostics information
                                               
mysql > tee output.txt;
mysql > call sys.diagnostics (120, 30, ‘current’);
mysql > tee off;



The following section gives you a brief about this diagnostic output.

Part I : The System Environment and Configuration
It shows the basic information about the OS and the MySQL Server.  
+-------------------------+---------------------------------------------------------+
| Name                    | Value                                                   |
+-------------------------+---------------------------------------------------------+
| Hostname                | virtual-21.localhost                                    |
| Port                    | 3306                                                    |
| Socket                  | /var/lib/mysql/data/EE/EE.sock                          |
| Datadir                 | /var/lib/mysql/data/EE/data1/                           |
| Server UUID             | e0a37616-a942-11e5-9d3f-080027dbc0db                    |
| ----------------------- | ------------------------------------------------------- |
| MySQL Version           | 5.6.24-enterprise-commercial-advanced-log               |
| Sys Schema Version      | 1.5.0                                                   |
| Version Comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| Version Compile OS      | linux-glibc2.5                                          |
| Version Compile Machine | x86_64                                                  |
| ----------------------- | ------------------------------------------------------- |
| UTC Time                | 2015-12-23 08:39:01                                     |
| Local Time              | 2015-12-23 16:39:01                                     |
| Time Zone               | SYSTEM                                                  |
| System Time Zone        | HKT                                                     |
| Time Zone Offset        | 08:00:00                                                |
+-------------------------+---------------------------------------------------------+
17 rows in set (0.01 sec)

The Configuration part has the Global Variables listing at the time of the execution.
-------------------------------------------------------------------------------------------|
| Variable_name                                          | value                           |
+--------------------------------------------------------+---------------------------------|
| autocommit                                             | ON                              |
| automatic_sp_privileges                                | ON                              |
| wait_timeout                                           | 28800                           |
+--------------------------------------------------------+---------------------------------|
444 rows in set (0.02 sec)


Part II : The Performance Schema Info

Listing about what has been setup about Performance Schema (Actors, Consumers, Instruments, Objects, Threads)



Part III : Replication Status
For my case, it is empty.
+---------------------------+
| Replication Status        |
+---------------------------+
| No Replication Configured |
+---------------------------+
1 row in set (0.05 sec)


Part IV – For each Iteration, the following list of information will be printed out to the output file.  (in my case, each iteration is about 30 seconds interval until 120seconds max time)
+---------------------+--------------------------+

| NOW()               | The following output is: |
+---------------------+--------------------------+
| 2015-12-23 16:39:01 | Iteration Number 1       |
+---------------------+--------------------------+
1 row in set (0.34 sec)

+--------------------------+
| The following output is: |
+--------------------------+
| SHOW MASTER STATUS       |
+--------------------------+
1 row in set (0.34 sec)

+---------------------------+----------+--------------+------------------+--------------------------------------------+
| File                      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+---------------------------+----------+--------------+------------------+--------------------------------------------+
| myreplication1-bin.000004 |      191 |              |                  | e0a37616-a942-11e5-9d3f-080027dbc0db:1-416 |
+---------------------------+----------+--------------+------------------+--------------------------------------------+
The Show Engine InnoDB status, Transaction, and process list are listed.
The details is not discussed in this blog.



Part V – End of execution, the following information is captured and compared.

a.   The performance schema status
b.   The Schema Information
+------------------------+
| Total Number of Tables |
+------------------------+
|                    231 |
+------------------------+
1 row in set (1 min 30.56 sec)

c.   The Overall Status
a.   Performance Schema Statement Digest Average Latency Histogram
  . = 1 unit
  * = 2 units
  # = 3 units

(0 - 5729ms)      321 | ###########################################################################################################
(5729 - 11458ms)  0   |
(11458 - 17187ms) 1   | .
(17187 - 22916ms) 0   |
(22916 - 28645ms) 0   |
(28645 - 34374ms) 0   |
(34374 - 40104ms) 0   |
(40104 - 45833ms) 0   |
(45833 - 51562ms) 0   |
(51562 - 57291ms) 0   |
(57291 - 63020ms) 1   | .
(63020 - 68749ms) 0   |
(68749 - 74478ms) 1   | .
(74478 - 80207ms) 0   |
(80207 - 85936ms) 0   |
(85936 - 91665ms) 0   |

  Total Statements: 325; Buckets: 16; Bucket Size: 5729 ms;
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (1 min 30.64 sec)



There are some configuration values for the diagnostics - sys.sys_config as shown :





‘diagnostics.allow_i_s_tables’  - ON/OFF, if ON, the information schema about the tables information will be dumped. 
‘diagnostics.include_raw’ – ON/OFF, if ON, the raw information will be included.