MMUG9: Madrid MySQL Users Group meeting to take place on 20th Noevember 2014

Madrid MySQL Users Group will have its next meeting on the 20th of November. Details can be found on the group’s Meetup page.

We plan to talk about pstop, which I’ve announced earlier and also the latest changes in MariaDB and MySQL since our last meeting.  The meeting will be in Spanish. I hope to see you there.

La próxima reunión de Madrid MySQL Users Group tendrá lugar el jueves 20 de noviembre. Se puede encontrar más detalles en la página del grupo.  Hablaremos sobre pstop y los últimos cambios en MariaDB y MySQL desde nuestra última reunión.  La reunión será en español.  Espero veros allí.

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.