EmergencyEMERGENCY? Get 24/7 Help Now!

Percona Cloud Tools: Making MySQL performance easy

 | January 9, 2014 |  Posted In: Insight for DBAs, MySQL, Percona Cloud Tools, Percona Software

PREVIOUS POST
NEXT POST

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

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 [1] 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 [1].

Fortunately, Performance is EASY if you know what to look into [1].
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:

query-profile-sm

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:

Query Text and
query-example-sm

Metrics
query-metrics-sm

…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.

What is more interesting is that Query Analitycs allows us to see historical data.
If we switch to graph we can see the data for the last week:
query-graph-sm

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,

query-graph-95-sm

…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:

  • Be sure your queries (tasks) are performed as expected
  • Queries are executed within the time frame you need
  • If not, then you will be able to see which query (or several) are problematic and requires attention
  • Get detailed metrics for each query

Percona Cloud Tools is to make MySQL Performance Easy!

You can (and should) sign up for free Beta and try the tool yourself.

PREVIOUS POST
NEXT POST
Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.

6 Comments

  • Hi everybody,

    I use Percona Cloud Tools for many weeks and I like this tool, bit today I have a problem with a node.
    I have this error message :
    “Can’t call method “info” on an undefined value at /usr/bin/pt-agent line 6862.”

    Do you know what is the proble and how can I resolve.

    Thx for your help

  • Hi,

    My version percona-toolkit is 2.2.7-1 in three nodes.
    I don’t understand what’s happen but it doesn’t work.
    I can start or uninstall pt-agent.
    What can Ido ?

    Thx

    Aziz

  • is there any way to use cloud-tools with amazon rds? I couldn’t find any documentation on the issue and would love to give it a try!

Leave a Reply

 
 

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 and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.