performace_schema is a dynamic database created when you restart the mysql, all the data from performance_schema database will be clear. This database has its own storage engine –“performance_schema”
Even you may have this database but it could be possible that event gathering is not enabled for MySQL server then you will not get any data on this database. You can enable to retrieve the internal events of mysql. It gathers data using instruments and consumers
It can be verify using
Mysql> SHOW VARIABLES LIKE ‘performance_schema’
To get information about which all instruments and consumers are enabled
Mysql>SELECT * FROM setup_instruments;
Mysql>SELECT * FROM setup_consumers;
We can update this tables and enabled those events as per our requirement, default not all are enabled.
This database is not maintain any log in binary log and do not get replicated. This is specific to given instance.
Performance schema database collect very useful data for performance point of view and provide internal information about the events and help DBA to understand system. It will provide detail information about queries and memory usage and all performance related information with minimum impact on the system.
SELECT EVENT_NAME, SUM_TIMER_WAIT
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
Similarly there are several important events which will help to resolve any performance problems.