Then MySQL 5.6, the abstract feature of the MySQL Performance Schema has providing a suitable and active way to get information of queries based on their controlled form. The feature all of it so well that it has almost totally (from my knowledge) substituted the connector extensions and proxy for gathering query information for the Query Analyzer in MySQL Enterprise Monitor (MEM).
MySQL 8 adds additional enhancements to the digest feature in the Performance Schema as well as a example query with information for each digest, percentile info, and a histogram instantaneous. This blog will discover these new structures.
Let’s start out observing at the decent old instant by digest table.
Query Sample
The improper table for digest instant info is the events_statements_summary_by_digest table. This has been everywhere in the meantime MySQL 5.6. In MySQL 8.0 it has been prolonged with six columns of which three have data connected to a sample query will be inspected in this segment.
The three example columns are:
QUERY_SAMPLE_TEXT: An real example of a query.
QUERY_SAMPLE_SEEN: When the example query was seen.
QUERY_SAMPLE_TIMER_WAIT: How long time the example query took to implement (in picoseconds).
As an instance study the query SELECT * FROM world.city WHERE id = <value>. The example info for that request as well as the digest and abstract text (normalized query) may expression like:
There are a limited belongings to note here:
The digest in MySQL 8 is a sha256 hash while in 5.6 and 5.7 it stayed an md5 hash.
The digest text is like the standardized query that the mysqldump slow script can produce for requests in the slow query log; just that the Routine Schema uses a question mark as a placeholder.
The QUERY_SAMPLE_SEEN value is in the structure time zone.
The sys.format_time() function is in the request used to change the picoseconds to a human legible value.
The extreme length of the example text is set with the performance_schema_max_sql_text_length choice. The defaulting is 1024 bytes. It is the similar choice that is used for the SQL_TEXT columns in the declaration events tables. It needs a restart of MySQL to modification the value. Since the request texts are kept in several settings and some of the Routine Schema tables can have thousands of rows, do take attention not to increase it out there what you have retention for.
How is the example query selected? The example is the measured example of a request with the given digest. If the performance_schema_max_digest_sample_age choice is set to a non-zero value (the defaulting is 60 seconds) and the current example is older than the detailed value, it will always be changed.
The events_statements_summary_by_digest also has additional set of new columns: percentile info.
Percentile Statistics
Since the start, the events_statements_summary_by_digest table has comprised some numerical info about the query times for a certain digest: the least, average, extreme, and total request time. In MySQL 8 this has been prolonged to include info about the 95th, 99th, and 99.9th percentile. The info is obtainable in the QUANTILE_95, QUANTILE_99, and QUANTILE_999 column separately. All the standards are in picoseconds.
What does the original columns callous? Based on the histogram material of the request (see the next segment), MySQL computes a high approximation of the query time. For a agreed abstract, 95% of the performed requests are projected to be quicker than the request time specified by QUANTILE_95. Like for the two extra columns.
As an sample, study the same summary as before:
Taking the 95th, 99th, and 99.9th percentile supports forecast the performance of a request and show the binge of the query times. Even more info about the spread can be initiate using the new personal member: histograms.
Histograms
Histograms is a method to put the request implementation times into stacks, so it is likely to see how the request implementation times spread. This can for sample be beneficial to see how consistently the request time is. The regular request time may be acceptable, but if that is built on some requests performing super-fast and others very sluggish, it will still outcome in unfortunate users and clients.
The MAX_TIMER_WAIT column of the events_statements_summary_by_digest table debated this far expressions the high mark, but it does not give or take whether it is a single outlier or a outcome of overall varying request times. The histograms stretch the response to this.
Using the request abstract from later in the blog, the histogram info for the request can be found in the events_statements_histogram_by_digest table like:
In this case, 3694 times (the COUNT_BUCKET column) when the request were performed, the request time was among 63.10 microseconds and 66.07 microseconds, so the completing time in step the recess of bucket number 41. There has been at whole of 7322 implementations (the COUNT_BUCKET_AND_LOWER column) of the request with a query time of 66.07 microseconds or fewer. This earnings that 73.22% (the BUCKET_QUANTILE column) of the requests have a query time of 66.07 microseconds or fewer.
In adding to the exposed columns, there is SCHEMA_NAME and DIGEST (which collected with BUCKET_NUMBER form a unique key). For separately digest there are 450 buckets with the size of the bucket (in terms of change among the low and high timers) slowly pretty bigger and bigger. The first, central, and latter five buckets are:
The bucket beginnings are secure and thus the similar for all digests. There is also a comprehensive histogram in the events_statements_histogram_global.
This comprises the outline to the new Performance Schema digest structures. As monitoring gears start to use this info, it will help make a better monitoring knowledge. Mostly the histograms will advantage from being made known as graphs.