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.

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.

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:

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:

Example to enable one of parameter:

Peformance schema setup_consumers table:

Example to enable one parameter:

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of