Day 13 MySQL Performance_Schema

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

[Mysqld]

performance_schema=ON

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.

Tables:

events_waits_current

 

SELECT EVENT_NAME, SUM_TIMER_WAIT

FROM events_waits_summary_global_by_event_name

ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

Similarly there are several important events which will help to resolve any performance problems.

 

 

Reference:

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/performance-schema.html

Advertisements
This entry was posted in MySQL, Others, Performance Tuning, What I learned today and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s