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.include_raw’ – ON/OFF, if ON, the raw information will be included.
No comments:
Post a Comment