One of our primary focuses at Percona is performance. Let me make some statements on what is “performance.”
In doing so I will refer to two pieces of content:
I highly recommend that you familiarize yourself with both of them.
Performance is about tasks and time.
We say that the system is performing well if it executes a task in an acceptable period of time, or that the system executes the required amounts of tasks per given period (sec, minute, hour etc).
To put it in our area of interest, what is a task for a database? Often we consider this as an individual query, and this is good enough detalization. One may think about database task as a “transaction” or set of queries executed during web page access, but I propose we focus on queries.
From this prospective, the database (MySQL) shows good performance if it executes the query in a reasonable amount of time (this is what we know as Response Time), or executes the expected amount of queries per second (hour/day) (this is Throughput).
So we connected a query execution with performance.
And let me state this: most database performance problems are related to queries. Either there are too many queries to handle (the system is overloaded; we ask to perform more tasks then the system is able to handle); or a query takes a long time to execute (the system is not handling the task in optimal way). Of course there could be another reasons, like hardware failure, database misconfiguration, OS issues etc, but, again, in most cases it is Queries what is problem.
“Performance is HARD”
Now, in  Carry Millsap states that “Performance is HARD”, and it is true, if you do not know where to look.
Very often people look into some funny places like cache hit ratio (“You have only 93% cache hit ratio, that your problem!”), amount of IO per second, CPU utilization, aborted_clients, handler_select you name it. Looking at these numbers with some experience and black magic may give you some results, or may not. When you look at these numbers, make some conclusions, then make some changes in hope it helps – it is a performance tuning, not performance optimization .
Fortunately, Performance is EASY if you know what to look into .
With Percona Clould Tools we make Performance EASY – our tools show you what you should be looking for.
Right now we have one module: Query Analytics, which is focused on queries. Query Analytics is based on our command line tool pt-query-digest from Percona Toolkit and we implement our best practices in how we analyze queries working with customers systems. Peter actually names this tool “Peter in the box”.
Percona Cloud Tools / Query Analytics
Let me review what information Query Analytics provides…
1. General Query Profile
Which queries takes the most time in the database, and information on how many queries execute, total time they took, average time per query and 95% response time.
Let’s see an example for the MySQLPerformanceBlog.com for one week time frame. This blog is WordPress based, so the queries are coming from WordPress:
The two top queries are related to users:
SELECT wp_usermeta and
SELECT wp_users, and we can see that both of them are executed
69M times for the last week. Though 95% response time for first one is 277.00µs, which is just fine.
There we can click and see more details about query:
…which shows that execution time for the query was from (Min) 5.00µs to (Max) 529.74ms
Bunch of metrics like InnoDB* are available only of you are using Percona Server.
Where we clearly see a spike in amount of queries. This corresponds to the published post: Multiple column index vs multiple indexes with MySQL 5.6.
And there we can switch between Total and 95% graph,
…which shows that even with the spike, the 95% response time of this query was not affected and that means MySQL (Percona Server) was able to handle the load.
Query Analytics helps you:
Percona Cloud Tools is to make MySQL Performance Easy!
You can (and should) sign up for free Beta and try the tool yourself.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.