3

I am trying to improve server performance, and it is clear that MySQL is a major contributor to the problem. Troubleshooting it, however, is very hard. I am using the slow query log to target specific kinds of queries, but the real issue is that MySQL is used by Java processes, PHP processes, and cron jobs (which are typically also PHP processes, but run through the command line versus via Apache)

Usually when the server gets slow, I will run some commands like "ps" or "top" to try to find the culprit, but even if I know MySQL is to blame, I don't know which of the three "realms" I mentioned might be actually causing slowdown. In other words, I'd love to somehow break it down and see that, for example, 80% of the demands placed on MySQL at that moment are due to queries from PHP, while only 20% come from Java. Since I have automated tasks and periodic events triggered from both "realms", it is hard to isolate their effects through trial-and-error alone.

Everything resides on one server, so all MySQL queries come from localhost. Could I also maybe "tag" queries, add comments to them, or otherwise add some kind of meta-data so I could analyze those tags later for relative load?

I doubt there is a way to get info quite like that, but if anyone can help provide a way to further breakdown MySQL load in a way that helps identify the source of those queries (process IDs might work too), it would help immensely. Thanks!

2
  • It's not clear from your description, but are all these processes running on the same server?
    – kashani
    Aug 17, 2009 at 16:53
  • Yes, all on the same machine. Aug 17, 2009 at 17:22

9 Answers 9

3

I'd suggest using MySQL Proxy. With MySQL Proxy you can intercept and log every query going into your server.

A simpler approach would be running mysqlbinlog to analyze the binary log files generated by MySQL (see man mysqlbinlog).

Anyway, if you haven't done this already, it would be a good idea to run mysqltuner to see if there are any obvious problems or bottlenecks (like for example, an unusual number of queries performing JOINs without indexes).

1

Have you tried logging into the mysql service and issuing a SHOW FULL PROCESSLIST or using mytop to see which queries are running?

SHOW FULL PROCESSLIST provides a list of all queries and tasks the MySQL is currently doing. It needs to be run as a user with either 'PROCESS' or 'SUPER' priveleges in the database. mysqladmin has a processlist argument as well.

mytop is very good for showing what the server is doing on an ongoing basis. Think of it as something like top for MySQL.

Both of these will tell you what commands are running, where they are coming from and how long MySQL has been trying to service them so far.

Once you are armed with that information you can go about deciding what to do. If the the system is write bound you may need to partition your data. If you are read bound, there are things that can help. Indexing tables properly can give a significant boost to read performance in many cases. If you have a lot of identical queries adding a cache may help. Adding read slaves can also improve performance. Caching and adding slaves will increase performance but will add some additional complexity to your network and application.

1

One possibility is to make sure the different processes use different credentials to connect to mysql. I'm pretty sure mysql logs the username that executed the query in the query log.

1

Yes you can, but profiling without using a client-based profiler requires that you pass the requests through something that can catch them -- In this case, that's going to be MySql-Proxy. There's instructions on their website on how to set it up to snag and profile queries, and then you can run explains and other operations against the ones that seem to be spending a long time.

Another method that's not so complicated but may work is to set your slow-query timeout to a very low value, and then make sure you turn the slow query log on. That way you'll see anything that takes longer than a few seconds. This won't help you if the developers (or their ORM) makes a bunch of basic queries and then summarizes the data in the application, of course.

1

SHOW FULL PROCESSLIST;

That shows you the processes and runtimes. To differentiate the source, you can use different logins for each 'realm', OR prepend the realm in all queries. eg, this is a valid query:

/* php */ SELECT * FROM items;

as is:

/* java */ SELECT * FROM items;

etc.

But you still can't see system load caused by the queries. Can be helpful anyway, and you could hack your monitoring tools to graph the data to look for correlations with your performance.

0

Maatkit's mk-query-digest script won't classify by source but it will give you a lot of insight into which queries are hogging resources the most.

http://www.maatkit.org/doc/mk-query-digest.html

0

We mark queries with comments, as mibus suggested above. We then regularly write the FULL PROCESSLIST output to files. When we have performance problems, we import these files into the database and munge the data to see long queries. For us at least, these cause the most load.

I would be careful with running MySQL Proxy on a production system. I have found it to lock up occasionally. Additionally you have a further piece of infrastructure you need to monitor.

0

Have you looking into using mysql profiling?

http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

mysql>set profiling=1;
<run your queries with profiling enabled>
mysql>show profiles;

The output of this will be a table with QueryID, Duration of Query, and Query string. Similar to this:

mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        0 | 0.00007300 | set profiling=1                               |
|        1 | 0.00044700 | select count(*) from client where broker_id=2 |
+----------+------------+-----------------------------------------------+

From here, you can break that query further down with

mysql>show profile for query <QueryID>;

This will give you a breakdown of how much time the query spent at each stage of execution. You can further delve into the specifics of how much CPU time is spent on a query as well:

mysql> show profile cpu for query 4;
+----------------------+------------+------------+------------+
| Status               | Duration   | CPU_user   | CPU_system |
+----------------------+------------+------------+------------+
| (initialization)     | 0.00002900 | 0.00000000 | 0.00000000 |
| checking permissions | 0.00000800 | 0.00000000 | 0.00000000 |
| init                 | 0.00004000 | 0.00000000 | 0.00000000 |
| Opening table        | 0.00009400 | 0.00100000 | 0.00000000 |
| System lock          | 0.00000500 | 0.00000000 | 0.00000000 |
| Table lock           | 0.00000700 | 0.00000000 | 0.00000000 |
| setup                | 0.00004200 | 0.00000000 | 0.00000000 |
| creating table       | 0.00195800 | 0.00000000 | 0.00100000 |
| After create         | 0.00010900 | 0.00000000 | 0.00000000 |
| copy to tmp table    | 0.52264500 | 0.55591600 | 0.04199300 |
| rename result table  | 0.11289400 | 0.00199900 | 0.00000000 |
| end                  | 0.00004600 | 0.00000000 | 0.00000000 |
| query end            | 0.00000700 | 0.00000000 | 0.00000000 |
| freeing items        | 0.00001300 | 0.00000000 | 0.00000000 |
+----------------------+------------+------------+------------+

I suggest reading the information page to find out exactly what information you would like as the tool is fairly verbose, but this should help you in finding your bottlenecks within the mysql daemon.

1
  • I should add that the way to go from your offending query to finding out who requested it would be using general_log. Where this may be a hassle to find the culprit it will also show you where you are spending all your time. I would think refining your processor hungry query is going to be the end solution.
    – sclarson
    Oct 27, 2009 at 19:01
0

First step is to not have all your processes use the same user. Especially if all of them are coming through 'localhost'. Even for queries coming from the same tool (as in php), have the ones in cron use a separate user from the ones running through apache.

Once that is done, you can use mysqlbinlog to parse the slow logs (it has some great fields to order the queries/normalize the queries) or even use something more advanced like a profiler to find the slower queries

1
  • also, innotop is a very good tool that shows each query running at a given time and can do explain on it and also allows you to check on locks (deadlocks are one big reason for bad performance). I actually find it more informative than mytop
    – TechieGurl
    Oct 11, 2010 at 20:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .