The MySQL Performance Schema allows you to check and monitor MySQL Server execution on a low level. The MySQL Performance Schema was first seen in MySQL 5.5 and has seen a lot of improvements when MySQL 5.6 came out.
MySQL Performance Schema has its own engine that allows you to check and access data and information about server execution without affecting the server performance. It also uses temporary tables or views so you do not need to use persistent disk storage that much. And you also benefit by having all the memory allocation performed when you start up your servers, so that you do not go through memory sizing or memory allocation while the server is running, thereby giving you faster performance.
The MySQL Performance Schema is enabled out of the box, meaning you do not have to do anything to use it. But you can either disable or enable it by restarting the server and setting the performance_schema variable to “on” or “off.” This is especially useful for those who are using earlier versions of MySQL, such as those using versions before MySQL 5.6.6.
The MySQL Performance Schema is useful for a lot of people as it gives you insights of behind the scene happenings when your server is running. The app developer can check to see what’s slowing down an application, while it can help database administrators who are working with hot tables, and end users who are in a stuck session. It also reduces disk spin, lessens the traffic on link, and helps avoid low throughput on systems.
The MySQL Performance Schema is the brainchild of Marc Alff, who started developing it in 2008. Currently, Alff is the chief architect and lead of Oracle MySQL’s performance schema team.
It comes with a new storage engine, also named Performance Schema and the statistics are stored in tables using non-persistent data.
The tables
The MySQL Performance Schema works on tables in performance schema database. It would make it easier for you if you make this database the default so that you do not need to reference it using the DB name.
The tables are saved in this database and you can see information about its structure and the tables within by using SHOW statements or choosing the INFORMATION_SCHEMA database.
There are 52 tables that you could find in the performance schema, and these include data tables and configuration tables. More specifically, these are:
- Configuration Tables: Where logistical information regarding monitored entities are stored, allowing you to set what it is you want to know and which parameters are to be reported.
- Instance Tables: Logs and records what object instances are instrumented.
- Event Tables: Records the event histories and current events.
- Summary Tables: Where you could find data about completed events.
- Others: Include those tables that are outside of these tables, such as performance_timers, host_cache, session_connect_attrs, and session_account_connect_attrs.
Want to learn more about the MySQL Performance Schema, MySQL in general or Oracle products? Contact Four Cornerstone today at 1 (817) 377 1144 and ask about our Oracle consulting in Dallas.
