pstop – a top-like program for MySQL (based on performance_schema)

I have been working with MySQL for some time and it has changed significantly from what I was using in 5.0 to what we have now in 5.6. One of the biggest handicap we’ve had in the past is to not be able to see what MySQL is doing or why.

MySQL 5.5 introduced us to performance_schema. It was a good start but quite crude. MySQL 5.6 gave us a significant increase in stuff that allows you to see what is going on inside MySQL. That’s great, except it’s hard to read, the documentation is good, but not oriented at the DBA but more at the MySQL developer (that’s what it seems like at least). So most of us have ignored it. Others complained about the overhead and said it’s not good to use it.

Mark Leith developed mysql-sys as a way to see this great information in a more usable way. It’s only a set of views so doesn’t really have much overhead. However, one thing I missed was getting the information of what was happening inside performance_schema in real-time, top-like, so I could see where a server was busy, and what it was doing. So inspired by mysql-sys and also as a way for me to start playing with go I have built P_S top, or pstop.

You can find it on github here: https://github.com/sjmudd/pstop.

What does pstop show you?  It takes some counters from performance_schema and subtracts the values from when it started up. The output is in four different screens which you toggle between using the <tab> key.  The idea is to look at the total latency (wait time) and order by table or file that causes it in heaviest first.  Table waits are also then split between read, insert, update and delete and there’s a screen which shows some locking information.

Access to the db server is currently via a ~/.my.cnf defaults file. I probably need to make this more sophisticated, and allow the credentials to be provided directly but have not done that yet.  I have used this on a couple of systems which I monitor for work and it has been most informative in showing where the load is, which table or file generates it and how that varies over time.  This information was already in performance_schema but there have not been any tools to get this out.

Here are a couple of examples:

Latency by table name

Operations by table name

Latency by filename

You can also see these screen samples here. I think that if you compile and build this and point it to a server of your own you’ll find the output much more interesting.

So please let me know what you think. I hope you find it interesting and useful.

Published by

Simon J Mudd

Born in England, I now live in Spain, but spent a few years living in the Netherlands. I previously worked in banking (financial markets) both in IT and as a broker, but IT has always had a stronger influence. Now working at booking.com as a Senior Database Administrator. Other interests include photography, and travel. Simon is married, with two children and lives in Madrid.

6 thoughts on “pstop – a top-like program for MySQL (based on performance_schema)”

  1. Simon,

    I believe you have vast experience in administering Mysql, could you pleas help.

    We have master slave setup & my slave is lagging aprroximately 20000 seconds behind.

    What could be the issue.

    Mysql Master – mysql-bin.000245 | 498082505

    Mysql slave – mysql-bin.000245 | Read_Master_Log_Pos: 403750311

    1. With the little information you provide it is impossible to know.
      ps-top would be one of the tools that might help you find that out, and there are other things you would need to look at like show processlist on the slave,
      apart from checking various configuration settings, and you have adequate hardware for the task you are doing etc.

Leave a Reply