Query Performance-Identifying Slow Queries Tutorial

Figuring out which queries are slow is usually easier than figuring out why they’re slow and making the necessary changes to fix them. The easiest way to track them is to let MySQL do some of the work for you. By enabling the slow query log, you instruct MySQL to log every query that takes longer than a specified number of seconds. In addition to the query, it also logs some other metadata.

# Time: 090708 18:00:25

# User@Host: root[root] @ localhost []

# Query_time: 24  Lock_time: 0  Rows_sent: 0  Rows_examined: 957115

SET timestamp=1247056225;

INSERT INTO topsearch(key_count,keyword,queryId) SELECT sum(key_count) key_count, keyword, queryId FROM (SELECT keyword, queryId, count( keyword ) key_count FROM running_queries WHERE keyword != ” AND queryId != ” GROUP BY keyword, queryId ORDER BY key_count DESC LIMIT 45)t GROUP BY keyword ORDER BY keyword;

While the log contains a lot of useful information, there’s one very important bit of information missing: an idea of why the query was slow. Sure, if the log says 12,000,000 rows were examined and 1,200,000 sent to the client, you know why it was slow. But things are rarely that clear cut. Worse yet, you may find a slow query, paste it into your favorite MySQL client, and find that it executes in a fraction of a second.

You must be careful not to read too much information into the slow query log. When a query appears in the log, it doesn’t mean that it’s a bad query—or even a slow one. It simply means that the query took a long time then. It doesn’t mean that the query will take a long time now or in the future.

There are any numbers of reasons why a query may be slow at one time but not at others:

• A table may have been locked, causing the query to wait. The Lock_time indicates how long the query waited for locks to be released.
• None of the data or indexes may have been cached in memory yet. This is common when MySQL is first started or hasn’t been well tuned.
• A nightly backup process was running, making all disks I/O considerably slower.
• The server may have been handling hundreds of other unrelated queries at that same time, and there simply wasn’t enough CPU power to do the job efficiently.

The list could go on. The bottom line is this: the slow query log is nothing more than a partial record of what happened. You can use it to generate a list of possible suspects, but you really need to investigate each of them in more depth. Of course, if you happen to see the same query appearing in the log over and over, there’s a very good chance you have a slow query on your hands.

MySQL also comes with mysqldumpslow, a Perl script that can summarize the slow query log and provide a better idea of how often each slower query executes. That way you don’t waste time trying to optimize a 30-second slow query that runs once a day, while there are five other 2-second slow queries that run thousands of time per day. You can download this file at the end of this article.

There is one more tool which is used to find out slow queries i.e. mytop to perform real-time query monitoring, including slow queries.

The most basic reason a query doesn’t perform well is because it’s working with too much data. Some queries just have to sift through a lot of data and can’t be helped. That’s unusual, though; most bad queries can be changed to access less data. We’ve found it useful to analyze a poorly performing query in two steps:

1. Find out whether your application is retrieving more data than you need. That usually means it’s accessing too many rows, but it might also be accessing too many columns.
2. Find out whether the MySQL server is analyzing more rows than it needs.

Are You Asking the Database for Data You Don’t Need?

Some queries ask for more data than they need and then throw some of it away. This demands extra work of the MySQL server, adds network overhead,* and consumes memory and CPU resources on the application server.

Here are a few typical mistakes:

Fetching more rows than needed

One common mistake is assuming that MySQL provides results on demand, rather than calculating and returning the full result set. We often see this in applications designed by people familiar with other database systems. These developers are used to techniques such as issuing a SELECT statement that returns many rows, then fetching the first N rows, and closing the result set (e.g., fetching the 100 most recent articles for a news site when they only need to show 10 of them on the front page). They think MySQL will provide them with these 10 rows and stop executing the query, but what MySQL really does is generate the complete result set. The client library then fetches all the data and discards most of it. The best solution is to add a LIMIT clause to the query.

Fetching all columns from a multitable join

If you want to retrieve all actors who appear in Academy Dinosaur, don’t write the query this way:

mysql> SELECT * FROM user

-> INNER JOIN user_course USING(user_id)

-> INNER JOIN desc USING(desc_id)

-> WHERE dec.title = ‘Academy End’;

That returns all columns from all three tables. Instead, write the query as follows:

mysql> SELECT * FROM user….

Fetching all columns

You should always be suspicious when you see SELECT *. Do you really need all columns? Probably not. Retrieving all columns can prevent optimizations such as covering indexes, as well as adding I/O, memory, and CPU overhead for the server. Some DBAs ban SELECT * universally because of this fact, and to reduce the risk of problems when someone alters the table’s column list.

Of course, asking for more data than you really need is not always bad. In many cases we’ve investigated, people tell us the wasteful approach simplifies development, as it lets the developer use the same bit of code in more than one place. That’s a reasonable consideration, as long as you know what it costs in terms of performance. It may also be useful to retrieve more data than you actually need if you use. Some type of caching in your application, or if you have another benefit in mind. Fetching and caching full objects may be preferable to running many separate queries that retrieve only parts of the object.

Download : mysqldumpslow