In the previous article, I have shared basic about the Performance Schema of MySQL.
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.
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.
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.
After enable, You can use this schema:
Find out all tables of the Performance Schema:
Some of the important tables are:
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:
SELECT * FROM setup_instruments
WHERE NAME RLIKE 'stage/sql/[a-c]';
Example to enable one of parameter:
UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'stage/sql/altering table';
Peformance schema setup_consumers table:
SELECT * FROM setup_consumers
WHERE NAME LIKE '%stages%';
Example to enable one parameter:
UPDATE setup_consumers SET ENABLED ='YES'
WHERE NAME = 'events_stages_current';
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.