MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
Using SYS.SESSION as an alternative to SHOW PROCESSLIST

A modern MySQL server contains a lot of useful meta-data in information_schema and performance_schema, which can help bring visibility into what is happening inside of your database server. However, sometimes this data is quite fine grained and needs finessing in order to get to that point 🙂

MySQL 5.7 includes a new SYS schema installed by default. SYS is a set of views, functions and procedures that aggregate the data in a way that is useful to DBAs trying to perform common tasks.

For Linux users I like to compare performance_schema to /proc, and SYS to vmstat.

SHOW PROCESSLIST

Having introduced SYS as a DBA-task-oriented-set-of-views, is there a more common DBA task than logging into a system and seeing what is running? Here is the SYS way to do it:

Allow me to point out a few things here that are not present in SHOW PROCESSLIST:

  • In row#1 you can see that I am running an ALTER TABLE command to add an index on a table. SYS has included the current stage of that execution (it is currently “read PK and internal sort”), and is 19% complete with that stage. It also includes the current execution time (5.78s) which is more precise.
  • In row #2 you can see that I am running an INSERT .. SELECT command. It is an autocommit transaction that has been running for 410.06 ms. You can also see that is requires a temporary table to execute this statement.
  • In row #3 you can see that I actually have a transaction that has been open for 4.94 minutes. It is my current connection where I am running the command SELECT * from sys.session from. Long running transactions can sometimes cause performance problems as they prevent some house-keeping operations from running.
  • All three connections are created by mysql command line clients (program_name: mysql, pid: os pid), so this is a simulation, but your client library will also populate these connection attributes, and it could be useful in tracking down the source of a problem.

Conclusion

Hopefully this serves as a good demonstration that SYS is both easy to use, and contains a lot of the useful meta data that is hidden away in performance_schema. If this inspires you to look for other examples, I suggest running SHOW TABLES in SYS on your MySQL 5.7 installation. There is a lot of good stuff in there 🙂