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!