In this blog, I will provide answers to the Q & A for the Performance Schema for MySQL Troubleshooting webinar.
First, I want to thank everybody for attending my March 1, 2018, webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I was unable to answer fully during the webinar.
Q: Is Workbench able to take advantage of the enhancements to Perf schema?
A: MySQL Workbench is a graphical tool for database architects, administrators and developers. It uses Performance Schema for its Performance Schema Reports and Query Statistics dashboards for MySQL Servers of version 5.6 or greater. So the answer is: yes, it is able to take advantage of the enhancements to Performance Schema.
Q: Can we check the history data ?
A: Yes. To do it you need to enable history consumers. You will find instructions here. For all kinds of consumers history table names follow the same pattern:
- *_history contains last N events per thread. Number N is defined by performance_schema_*_history_size variables. Default is -1 (autosized) in version 5.7 and 10 in version 5.6.
- *_history_long contains the most recent M events. Value of M is defined by performance_schema_*_history_long_size variables. Default is -1 (autosized) in version 5.7 and 10000 in version 5.6
For example, if you want to have historical data for statements, you need to enable consumers events_statements_history and events_statements_history_long. If you want to limit the number of queries stored, you need to set variables performance_schema_events_statements_history_size and performance_schema_events_statements_history_long_size.
Q: Are there any guidelines regarding how much memory we should set aside for every X counters/statistics being enabled?
A: No, there is no such guideline I am aware of. But you can use definitions of tables in Performance Schema to calculate the approximate value of how much memory one row could occupy, and make predictions from it. You can also use memory instrumentation in Performance Schema and watch for changes of memory usage under load, adjusting as needed.
Q: How has the performance cost of performance schema changed from 5.6 to 5.7?
A: The worst situation for the performance cost of Performance Schema was in version 5.5. It was discussed in numerous places, but I recommend you read this 2010 post from Dimitri Kravtchuk, a MySQL Performance Architect at Oracle. The result of his post was a huge performance improvement in Performance Schema, reported in this post from 2011. There were more improvements, discussed in 2012. Since then, Performance Schema does not add significant performance overhead unless you enable waits instrumentation.
Version 5.7 added new instrumentation, and as my tests showed this instrumentation did not add any noticeable performance impact.
To summarize: version 5.6 made huge improvements to the performance of Performance Schema, and the new features in version 5.7 did not add any performance regressions.
Q: Will performance schema eat up my disk space? How long will it store all these logs and cause any issues?
A: Performance Schema does not store anything on disk, but uses memory. It stores data until it reaches the size of the consumer tables. When it reaches the maximum size, it removes the oldest data and replaces it with the newest statistics. Read the Performance Schema startup configuration guide on how to limit the maximum size of consumer tables.
Thanks for attending this webinar on Performance Schema for MySQL Troubleshooting. You can find the slides and a recording here.
