Monday, March 28, 2011

Question: What do you use to capture and analyse MySQL processlist?

I have recently been evaluating MONyog and one of the key things that I was hoping that it would provide for me was an easy way to answer the all important question for troubleshooting...

"What was happening at the time?"

If I see some query that is normally fast took far too long or perhaps replication fell behind significantly for a while -- I will always ask myself "What was happening at the time?"

I'd check out stuff like SHOW FULL PROCESSLIST, SHOW ENGINE INNODB STATUS and some system level things like iostat and vmstat or mpstat, etc.

I saw that MONyog has the ability to do things like periodically sniff SHOW PROCESSLIST, or even use MySQL Proxy for query analysis purposes.

This seems to capture how often queries run, whether they used an index, how long they took, etc. but the data is not available to be seen in a time-based snapshot style format.

I know that MySQL Enterprise Monitor that Oracle have on offer as a part of the MySQL Enterprise offerings sort of has this kind of feature -- they have a spiffy way to click and drag a time portion of a graph and then be taken to Query Analyzer to see what was happening during the window.

This of course is still not quite a substitute for the full SHOW FULL PROCESSLIST output -- so I open the question to you, oh MySQL blog-o-sphere...

What do you use to capture Processlist and InnoDB Status info so that you can refer back to it?

Do you just use some cron and have it periodically write to a file? .. or is there some other nifty tool out there that I haven't heard of?

Lachlan

10 comments:

  1. I recommend Maatkit mk-query-digest and tcpdump. It provides a wealth of information.
    http://www.mysqlperformanceblog.com/2009/07/01/gathering-queries-from-a-server-with-maatkit-and-tcpdump/

    ReplyDelete
  2. My own mycheckpoint (shameless plug) captures all server & status variables. In case of alert (user defined condition is raised), a processlist summary is captured.

    So, status + variables: routinely (e.g. every 5 minutes); PROCESSLIST: only on some alert.

    ReplyDelete
  3. I don't know of a nifty tool however in my consulting I do the following when I am reviewing a new system. Note: my process would change if I was managing a system, normally I have a very short period of time to gather a lot of potential pain points.

    . Gather Variables Daily. compare and diff with my.cnf
    . Gather Status and InnoDB status per minute. Crunch with own script and statpack. (Generally I rollup statpack to per hour).
    . Gather processlist every 5 seconds, analyze and report
    . TCP capture and analysis on demand
    . Gather OS vmstat/iostat per 1 second/per 5 seconds depending on test period
    . Test Code on isolated system with general query log

    Both TCP analysis/processlist/slow query are all sampling techniques so they only give you a picture.

    Having a test system with general query log, and a benchmarking tool or manual user operation is the best way to get a full picture of SQL usage.
    The Analyzing approach of this is also different but it's critical in determining potential bottlenecks, transaction boundaries, debugging deadlocks and identifying sampling times (e.g. what time of day)

    ReplyDelete
  4. I use MyTOP and Desktops by sysinternals.com.

    MyTOP is an older version of what is now InnoTOP. I have modified it for my own needs (http://www.mysqlfanboy.com/mytop/) but you might like it. If you lots of MyISAM but if you use InnoDB you might like InnoTOP better.

    Desktops lets me keep MyTOP windows open to each of my servers. Like Linux, I can press Alt-F1 through F4 and switch to them. When I get an alert via SMS I press Alt-F4 and there is the LOCK or long running query. I can press 'K' for kill and put in the process number and all is well. (Most people will think I'm crazy for that but that's life in my chair.)

    ReplyDelete
  5. @Anon: Thanks Anon - I have used that technique before to capture and view query digests, however, I am not sure it is what I'm looking for in this instance.

    @Shlomi - Thanks will check it out!

    ReplyDelete
  6. Hi, I am a MONyog developer and thought I could add more to this discussion.

    Indeed MONyog sniffs queries through PROCESSLIST and PROXY and this information can be found in the 'Query Analyzer' feature of MONyog.
    You can specify the time frame by clicking on the 'Change Filter' link. Then you would only get aggregated information of those queries fired during that time frame.

    Another thing to note is that, you can get historic data of all the GLOBAL VARIABLES and GLOBAL STATUS along with the other important metrics in the 'Monitors/Advisors' feature.
    In the 'Monitors/Advisors' page, choose 'History/Trends' and click on the 'Change' link to specify the time frame. You can even get a graphical representation of these values!

    -Shalmali

    ReplyDelete
  7. Hi Lachlan,

    our graphical tool Jet Profiler uses SHOW PROCESSLIST to aggregate statistics. You'll see a graph of for example number of slow queries running. You can zoom in on spikes and get a top list of the heaviest queries during the spike and more.

    http://www.jetprofiler.com/

    ReplyDelete
  8. If wanted to keep an eye on the processlist over time, rather taan a cron-job I'd use a MySQL EVENT, the advantage of which is that I do not have to put a MySQL password in some script somewhere, beside many other advantages. I'd put it in a MySQL Stored Procedure that I'd call from the EVENT, so I can run the process at will also. This is the method I use, in addition to using my own (plug coming up) MyQuery SQL editor. And some some things the my sqlstats MySQL 5.5 plugin.

    ReplyDelete
  9. "Do you just use some cron and have it periodically write to a file? " That is what we do. We cron it, using something such as http://pastebin.com/TnSq9dFd

    This will create daily subdirs in /var/log/mysql_pl, and inside each three files per minute for processlist, innodb and unix process data. It will autocycle after a week.

    ReplyDelete
  10. @Shalmali: Thanks for those handy hints! The ability to filter the Analyzer by timeframe is definitely useful. Of course.. I think the answer I'm getting is that in general, there is no real substitute for the actual PROCESSLIST as it was during points in time, since you can see more information like the state of each running query. Obviously this can/should be supplemented with SHOW ENGINE INNODB STATUS where applicable.

    ReplyDelete