ps-top

command module
v1.1.9 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Dec 6, 2023 License: BSD-2-Clause Imports: 11 Imported by: 0

README

ps-top

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.

Installation

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.

Configuration

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}$ = _YYYYMM
MySQL Access

Access to MySQL can be made by one of the following methods:

  • Default: use a defaults-file named ~/.my.cnf.
  • use an explicit defaults-file with --defaults-file=/path/to/.my.cnf.
  • connect to a host with --host=somehost --port=999 --user=someuser --password=somepass, or
  • connect via a socket with --socket=/path/to/mysql.sock --user=someuser --password=somepass
  • to avoid the password being stored or provided as a command line argument you can use --askpass which will request this from the user on startup

The user if not specified will default to the contents of $USER. The port if not specified will default to 3306.

  • If you use the command line option --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-top
MySQL/MariaDB configuration

The 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.

Grants

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.

Views

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 locks
  • user_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.

Keys

When in ps-top mode the following keys allow you to navigate around the different ps-top displays or to change it's behaviour.

  • h - gives you a help screen.
      • reduce the poll interval by 1 second (minimum 1 second)
      • increase the poll interval by 1 second
  • q - quit
  • t - toggle between showing the statistics since resetting ps-top started or you explicitly reset them (with 'z') [REL] or showing the statistics as collected from MySQL [ABS].
  • z - reset statistics. That is counters you see are relative to when you "reset" statistics.
  • <tab> - change display modes between: latency, ops, file I/O, lock, user, mutex, stages and memory modes.
  • left arrow - change to previous screen
  • right arrow - change to next screen

See also

See also:

Incompatible Changes

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.

Contributing

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.

Licensing

BSD 2-Clause License

Feedback

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

Code Documenton

godoc.org/github.com/sjmudd/ps-top

Documentation

Overview

ps-top - Top like program which collects information from MySQL's performance_schema database.

Source Files

View all Source files

Directories

Path Synopsis
Package app is the "runtime" for the ps-top application.
 Package app is the "runtime" for the ps-top application.
Package baseobject contains the library routines for base stuff of an object
 Package baseobject contains the library routines for base stuff of an object
Package config stores some common information used in various places.
 Package config stores some common information used in various places.
Package connector is used to specify how to connect to MySQL.
 Package connector is used to specify how to connect to MySQL.
Package event hides the retrieval of events from different sources
 Package event hides the retrieval of events from different sources
Package filename contains the routines for converting a filename to a MySQL object name.
 Package filename contains the routines for converting a filename to a MySQL object name.
Package global provides information on global variables and status
 Package global provides information on global variables and status
Package log provides some adjustments to the standard logging = it is called on startup to optionally stop all logging.
 Package log provides some adjustments to the standard logging = it is called on startup to optionally stop all logging.
model
fileinfo
Package fileinfo holds the routines which manage the file_summary_by_instance table.
 Package fileinfo holds the routines which manage the file_summary_by_instance table.
filter
Package filter contains the routines for managing a database filter.
 Package filter contains the routines for managing a database filter.
memoryusage
Package memoryusage manages collecting data from performance_schema which holds information about memory usage
 Package memoryusage manages collecting data from performance_schema which holds information about memory usage
mutexlatency
Package mutexlatency provides library routines for ps-top.
 Package mutexlatency provides library routines for ps-top.
stageslatency
Package stageslatency is the nterface to events_stages_summary_global_by_event_name
 Package stageslatency is the nterface to events_stages_summary_global_by_event_name
tableio
Package tableio contains the routines for managing performance_schema.tableio_waits_by_table.
 Package tableio contains the routines for managing performance_schema.tableio_waits_by_table.
tablelocks
Package tablelocks contains the library routines for managing the table_lock_waits_summary_by_table table.
 Package tablelocks contains the library routines for managing the table_lock_waits_summary_by_table table.
userlatency
Package userlatency file contains the library routines for managing the information_schema.processlist table.
 Package userlatency file contains the library routines for managing the information_schema.processlist table.
Package pstable contains the library routines for managing a generic performance_schema table via an interface definition.
 Package pstable contains the library routines for managing a generic performance_schema table via an interface definition.
Package rc provides routines to read ~/.pstoprc ps-top / ps-stats configuration - and to munge some table names based on the [munge] section (if present)
 Package rc provides routines to read ~/.pstoprc ps-top / ps-stats configuration - and to munge some table names based on the [munge] section (if present)
Package setupinstruments manages the configuration of performance_schema.setupinstruments.
 Package setupinstruments manages the configuration of performance_schema.setupinstruments.
Package utils includes several library routines for ps-top
 Package utils includes several library routines for ps-top
Package version returns the version of the application
 Package version returns the version of the application
Package view provides a simple way of checking access to a table
 Package view provides a simple way of checking access to a table
Package wait manages waits between data collections from MySQL.
 Package wait manages waits between data collections from MySQL.
wrapper
fileinfolatency
Package fileinfolatency holds the routines which manage the file_summary_by_instance table.
 Package fileinfolatency holds the routines which manage the file_summary_by_instance table.
memoryusage
Package memoryusage holds the routines which manage the file_summary_by_instance table.
 Package memoryusage holds the routines which manage the file_summary_by_instance table.
mutexlatency
Package mutexlatency holds the routines which manage the server mutexs
 Package mutexlatency holds the routines which manage the server mutexs
stageslatency
Package stageslatency holds the routines which manage the stages table.
 Package stageslatency holds the routines which manage the stages table.
tableiolatency
Package tableiolatency holds the routines which manage the tableio statisticss.
 Package tableiolatency holds the routines which manage the tableio statisticss.
tableioops
Package tableioops holds the routines which manage the table ops
 Package tableioops holds the routines which manage the table ops
tablelocklatency
Package tablelocklatency holds the routines which manage the file_summary_by_instance table.
 Package tablelocklatency holds the routines which manage the file_summary_by_instance table.
userlatency
Package userlatency holds the routines which manage the user latency information
 Package userlatency holds the routines which manage the user latency information

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.