ps-top - a top-like program for MySQL
ps-top is a program which collects information from MySQL 5.6+'s performance_schema database and uses this information to display server load in real-time. Data is shown by table or filename and the metrics also show how this is split between select, insert, update or delete activity. User activity is now shown showing the number of different hosts that connect with the same username and the activity of those users. There are also statistics on mutex and sql stage timings.
Install each binary by doing:
$ go install github.com/sjmudd/ps-top@latest
Check the version of go you are using as older versions of GO may not work.
The sources will be downloaded together with the dependencies and
the binary will be built and installed into $GOPATH/bin/. If
this path is in your PATH setting then the program can be run
directly without having to specify any specific path.
Sometimes you may want to combine different tables together and show
the combined output. A typical example might be if you have lots
of similarly named tables. Should you want to do this you can
use the following configuration file ~/.pstoprc to hole the
configuration information:
[munge]
<regexp_match> = <replacement_string>
_[0-9]{8}$ = _YYYYMMDD
_[0-9]{6}$ = _YYYYMMAccess to MySQL can be made by one of the following methods:
~/.my.cnf.--defaults-file=/path/to/.my.cnf.--host=somehost --port=999 --user=someuser --password=somepass, or--socket=/path/to/mysql.sock --user=someuser --password=somepass--askpass which will request this from the
user on startupThe user if not specified will default to the contents of $USER.
The port if not specified will default to 3306.
--use-environment ps-top
will look for the credentials in the environment
variable MYSQL_DSN and connect with that. This is a GO DSN and
is expected to be in the format:
user:pass@tcp(host:port)/performance_schema and currently ALL
fields must be filled in. With a suitable wrapper function this
allows you to access one of many different servers without making
the credentials visible on the command line.An example setting could be to use TLS which is not fully supported at the moment with command line parameters:
$ export MYSQL_DSN='user:pass@tcp(host:3306)/performance_schema?tls=skip-verify&allowCleartextPasswords=1'
$ ps-topThe performance_schema database MUST be enabled for ps-top to work.
By default on MySQL this is enabled, but on MariaDB >= 10.0.12 it is disabled.
So please check your settings. Simply configure in /etc/my.cnf:
performance_schema = 1
If you change this setting you'll need to restart MariaDB for it to take effect.
ps-top needs SELECT grants to access performance_schema
tables. They will not run if access to the required tables is not
available.
setup_instruments: To view mutex_latency or stages_latency
ps-top will try to change the configuration if needed and if you
have grants to do this. If the server is --read-only or you do not
have sufficient grants to change these tables these views may be empty.
Pior to stopping ps-top will restore the setup_instruments configuration
back to its original settings if it had successfully updated the table
when starting up.
ps-top can show 7 different views of data, the views
are updated every second by default. The views are named:
table_io_latency: Show activity by table by the time waiting to perform operations on them.table_io_ops: Show activity by number of operations MySQL performs on them.file_io_latency: Show where MySQL is spending it's time in file I/O.table_lock_latency: Show order based on table locksuser_latency: Show ordering based on how long users are running
queries, or the number of connections they have to MySQL. This is
really missing a feature in MySQL (see: bug#75156)
to provide higher resolution query times than seconds. It gives
some info but if the queries are very short then the integer runtime
in seconds makes the output far less interesting. Total idle time is also
shown as this gives an indication of perhaps overly long idle queries,
and the sum of the values here if there's a pile up may be interesting.mutex_latency: Show the ordering by mutex latency [1].stages_latency: Show the ordering by time in the different SQL query stages [1].You can change the polling interval and switch between modes (see below).
[1] See Grants above. These views may appear empty if setup_instruments is not
configured correctly.
When in ps-top mode the following keys allow you to navigate around the different ps-top displays or to change it's behaviour.
<tab> - change display modes between: latency, ops, file I/O, lock, user, mutex, stages and memory modes.See also:
As of v1.0.7 ps-stats has been removed. I never used it and it is simpler to remove unused functionality.
As of v0.5.0 the original utility was renamed from pstop which
could work in stdout or top mode into two utilities named
ps-top and ps-stats. This change of name was triggered to avoid
the name conflict with the Oracle command pstop(1). See
https://docs.oracle.com/cd/E19683-01/816-0210/6m6nb7mii/index.html. While
the two commands are not related it was felt better to avoid the
name overload, and while ps-top is reasonably young this change
should not yet cause too much trouble.
This program was started as a simple project to allow me to learn go, which I'd been following for a while, but hadn't used in earnest. This probably shows in the code so suggestions on improvement are most welcome.
BSD 2-Clause License
Feedback and patches welcome. I am especially interested in hearing
from you if you are using ps-top, or if you have ideas of how I can
better use other information from the performance_schema tables
to provide a more complete vision of what MySQL is doing or where
it's busy. The tool has been used by myself and colleagues and
helped quickly identify bottlenecks and problems in several systems.
Simon J Mudd sjmudd@pobox.com