MySQL 5.5: How to configure and enable Performance Schema Engine?

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.

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';