This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous article, I have shared basic about the Performance Schema of MySQL.
MySQL 5.5: Introduced PERFORMANCE_SCHEMA storage engine to inspect the performance data
In this post, I am sharing basic command and configuration of Performance Schema.
Using below command, You can find performance_schema is enabled or disabled.
|
1 2 3 4 5 6 |
SHOW VARIABLES LIKE 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | ON | +--------------------+-------+ |
It is enabled by default, but in the case of any reason if it is not enabled, open your my.cnf or my.ini file and put below parameter value.
|
1 2 |
[mysqld] performance_schema=ON |
Now It requires restarting the MySQL service because this server parameter enables at server startup.
Using below query, You can also check MySQL database engine information.
|
1 2 |
SELECT * FROM INFORMATION_SCHEMA.ENGINES; |
After enable, You can use this schema:
|
1 |
USE performance_schema; |
Find out all tables of the Performance Schema:
|
1 |
SHOW TABLES; |
Some of the important tables are:
|
1 2 |
SELECT * FROM events_waits_current; -- To see what the server is doing at the moment SELECT * FROM file_instances; -- To get instances of instruments for file I/O operations and their associated files |
Performance schema has also few parameter tables like instruments and staging:
The Performance Schema instruments stages, which are steps during the statement-execution process, such as parsing a statement, opening a table, or performing a file sort operation.
Stages correspond to the thread states displayed by SHOW PROCESSLIST or that are visible in the INFORMATION_SCHEMA.PROCESSLIST table. Stages begin and end when state values change.
Performance schema setup_instruments table:
|
1 2 |
SELECT * FROM setup_instruments WHERE NAME RLIKE 'stage/sql/[a-c]'; |
Example to enable one of parameter:
|
1 2 |
UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'stage/sql/altering table'; |
Peformance schema setup_consumers table:
|
1 2 |
SELECT * FROM setup_consumers WHERE NAME LIKE '%stages%'; |
Example to enable one parameter:
|
1 2 |
UPDATE setup_consumers SET ENABLED ='YES' WHERE NAME = 'events_stages_current'; |
