PAMEACS

MySQL Performance Schema

Having come from a background of working with Oracle databases it was the norm to use the Oracle wait interface to tune sql and performance. The performance_schema is the first step in delivering wait state tuning to MySQL and its cousins such as MariaDB. As people learn more about performance_schema they will find it has some hidden gems for tuning. One thing that will come to light in time is that the old set the buffer to 80% or OS RAM will be obsolete in years to come. A much better tuner of the Oracle database Cary Millsap has many examples where high buffer cache usage did not relate to great performance in the database. I have seem similar in my time.

In an optimal world of database data retrieval the real performance measure is how fast the data is returned to the end user. If the simple result is in the buffer cache then there is a good chance the data can be retrieved quickly. In a typical Wordpress site with a daily blog update and little data gathering within the server the main performance requirement is serving the content quickly. This content is relatively static and as such is easy to serve from cache. However it is a misnomer to think that having most or even all your data in memory is stable in performance over using disk to get data.

lets take an example

Lets say I have a 1 million row table in memory and the actions of my SQL is that I do a full table scan of that table and then go to disk to get a single record from another table. So I have to conduct 1miiion I/O or buffer reads to find a record on disk. Would that be an efficient process. My instincts say possibly not. When tuning by buffer cache hits, this can be hidden from the casual user and as such be unavailable to the less knowledgeable tuner


The key part of performance schema is the wait monitoring for this.


Mark Leith's sys schema and ps_helper is part of the journey to improving the usability of the content in performance_schema. ps_helper was renamed to sys schema and is now more functional in providing a useful service.



Full Site