PGTOP(1)

📦 version 0.11, in nixos 26.05, nixpkgs unstable

💖 NAME 💖

okay so bestie, meet pgtop — the tool that displays PostgreSQL performance info like top. It’s giving “top but make it PostgreSQL,” and honestly? Obsessed. 💅

✨ SYNOPSIS ✨

pgtop [options]

That’s it. That’s the whole vibe. 🎀

🦋 AVAILABILITY 🦋

The latest version of pgtop is available from https://metacpan.org/author/COSIMO or on github at https://github.com/cosimo/pgtop. Go grab it, bestie. 🦋

🌸 REQUIREMENTS 🌸

In order for pgtop to function properly, you must have the following, no cap:

  • Perl 5.014 or newer
  • Getopt::Long
  • DBI and DBD::Pg
  • Term::ReadKey

Most systems are likely to have all of those installed or readily available. Lowkey convenient. ✨

And you obviously need access to a PostgreSQL server (version 7.2+) with the necessary security to access the pg_stat* relations. No access, no slay. 😭

🎀 Optional Color Support 🎀

In addition, if you want a color mytop (recommended), install Term::ANSIColor from the CPAN:

https://metacpan.org/module/Term::ANSIColor

Once you do, pgtop will automatically use it. However, color is not yet working on Windows. Patches welcome. :-) Windows besties, hang tight. 🫶

⏱️ Optional Hi-Res Timing ⏱️

If you want pgtop to provide more accurate real-time queries-per-second statistics, install the Time::HiRes module from CPAN. pgtop will automatically notice that you have it and use it rather than the standard timing mechanism. Automatic upgrade? We love to see it. ⏱️

🌈 Platforms 🌈

pgtop currently is known to work only on Linux. Its mytop brother, however, has been reported to work on:

  • Linux (2.2.x, 2.4.x) ?
  • FreeBSD (2.2, 3.x, 4.x) ?
  • Mac OS X ?
  • BSDI 4.x ?
  • Solaris 2.x ?
  • Windows NT 4.x (ActivePerl) ?

If you find that it works on another platform, please let us know. Given that it is all Perl code, I expect it to be rather portable to Unix and Unix-like systems. Heck, it might even work on Win32 systems. 🌈

DESCRIPTION 💖

Hiii okay so first things first: help is always welcome in improving this software. Fr fr. Feel free to contact the author (peep AUTHOR down below) with bug reports, fixes, suggestions, and comments — she genuinely wants them 💌. Also, BUGS has a whole list of things this software isn't able to do yet, so like, manage expectations bestie.

Having said all that, here's the tea on how it works and what you can actually do with it ✨

The Basics 🎀

pgtop was inspired by mytop (http://mytop.sourceforge.net/), which in turn was inspired by the system monitoring tool top. Like, the whole lineage is iconic 👑

pgtop will connect to a PostgreSQL server and periodically run queries against the pg_stat* relations and attempt to summarize the information from them in a useful format. No cap, it just vibes in there collecting stats 🦋

WHAT FOLLOWS REFERS TO MYTOP 💀

Okay bestie, heads up: what you read from now on has not been revised, and refers to the original utility mytop. So like, don't come for pgtop about the MySQL mentions okay 😭 it's historical.

The Display 🖥️✨

The mytop display screen is really broken into two parts. The top 4 lines (header) contain summary information about your MySQL server. For example, you might see something like:

MySQL on localhost (4.0.13-log)                        up 1+11:13:00
[23:29:11]
 Queries: 19.3M  qps:  160 Slow:     1.0         Se/In/Up/De(%):
00/80/03/17
             qps now:  219 Slow qps: 0.0  Threads:    1 (   1/  16)
00/74/00/25
 Key Efficiency: 99.3%  Bps in/out: 30.5k/162.8   Now in/out: 32.7k/
3.3k

The first line identifies the hostname of the server (localhost) and the version of MySQL it's running. The right hand side shows the uptime of the MySQL server process in days+hours:minutes:seconds format (much like FreeBSD's top) as well as the current time ⏰

The second line displays the total number of queries the server has processed, the average number of queries per second, the number of slow queries, and the percentage of Select, Insert, Update, and Delete queries.

The third line is real-time values. First is the number of queries per second, then the number of slow queries, followed by query percentages (like on the previous line) 👀

And the fourth line displays key buffer efficiency (how often keys are read from the buffer rather than disk) and the number of bytes that MySQL has sent and received, both over all and in the last cycle.

You can toggle the header by hitting h when running mytop 💅

The second part of the display lists as many threads as can fit on screen. By default they are sorted according to their idle time (least idle first). The display looks like:

    Id     User       Host      Dbase   Time      Cmd Query or State
    --     ----       ----      -----   ----      --- --------------
    61  jzawodn  localhost      music      0    Query show processlist

As you can see, the thread id, username, host from which the user is connecting, database to which the user is connected, number of seconds of idle time, the command the thread is executing, and the query info are all displayed 🫶

Often times the query info is what you are really interested in, so it's good to run mytop in an xterm that is wider than the normal 80 columns if possible. Treat yourself to the wide window, you deserve it 🌈

The thread display color-codes the threads if you have installed color support. The current color scheme only works well in a window with a dark (like black) background 🖤 The colors are selected according to the Command column of the display:

  • Query — Yellow
  • Sleep — White
  • Connect — Green

Those are purely arbitrary and will be customizable in a future release. If they annoy you just start mytop with the -nocolor flag or adjust your config file appropriately 🙈

Arguments 🧚

mytop handles long and short command-line arguments. Not all options have both long and short formats, however. The long arguments can start with one or two dashes - or --. They are shown here with just one.

  • -u or -user username — username to use when logging in to the MySQL server. Default: ``root''.
  • -p or -pass or -password password — password to use when logging in to the MySQL server. Default: none.
  • -h or -host hostname[:port] — hostname of the MySQL server. The hostname may be followed by an optional port number. Note that the port is specified separate from the host when using a config file. Default: ``localhost''.
  • -port or -P port — if you're running MySQL on a non-standard port, use this to specify the port number. Default: 3306.
  • -s or -delay seconds — how long between display refreshes. Default: 5.
  • -d or -db or -database database — use if you'd like mytop to connect to a specific database by default. Default: ``test''.
  • -b or -batch or -batchmode — in batch mode, mytop runs only once, does not clear the screen, and places no limit on the number of lines it will print. This is suitable for running periodically (perhaps from cron) to capture the information into a file for later viewing. You might use batch mode in a CGI script to occasionally display your MySQL server status on the web. Default: unset.
  • -S or -socket /path/to/socket — if you're running mytop on the same host as MySQL, you may wish to have it use the MySQL socket directly rather than a standard TCP/IP connection. If you do, just specify one.

Note that specifying a socket will make mytop ignore any host and/or port that you might have specified. If the socket does not exist (or the file specified is not a socket), this option will be ignored and mytop will use the hostname and port number instead.

Default: none.

  • -header or -noheader — specify if you want the header to display or not. You can toggle this with the h key while mytop is running. Default: header.
  • -color or -nocolor — specify if you want a color display. This has no effect if you don't have color support available. Default: if you have color support, mytop will try color unless you tell it not to.
  • -i or -idle or -noidle — specify if you want idle (sleeping) threads to appear in the list. If sleeping threads are omitted, the default sorting order is reversed so that the longest running queries appear at the top of the list. Default: idle.
  • -prompt or -noprompt — specify if you want to be prompted to type in your database password. This provides a little bit more security since it not only prevents the password from being viewable in a process list, but also doesn't require the password to be stored in plain text in your ~/.mytop config file. You will only be prompted if a password has not been specified in your config file or through another command line option. Default: noprompt.
  • -resolve — if you have skip-resolve set on MySQL (to keep it from doing a reverse DNS lookup on each inbound connection), mytop can replace IP addresses with hostnames by toggling this option. Default: noresolve.
  • -slow_threshold — defines the threshold in seconds after which a query must be considered slow and it's logged to a file (pgtop.log). Default: 60.
  • -slack_webhook — if provided, in the form of a slack incoming webhook URL (e.g. <https://hooks.slack.com/services/...>), it will be used to send notifications for queries that are running longer than slow_threshold seconds. There is no default value, so no slack notification will be sent if this parameter is not provided. Default: none.
Command-line arguments will always take precedence over config file options. That happens because the config file is read BEFORE the command-line arguments are applied. Order of operations, bestie 📋✨

Config File 🍓

Instead of always using bulky command-line parameters, you can also use a config file in your home directory (~/.pgtop). If present, pgtop will read it automatically. It is read before any of your command-line arguments are processed, so your command-line arguments will override directives in the config file 💅

Here is a sample config file ~/.pgtop which implements the defaults described above.

user=root
pass=
host=localhost
db=test
delay=5
port=3306
batchmode=0
header=1
color=1
idle=1

Using a config file will help to ensure that your database password isn't visible to users on the command-line. Just make sure that the permissions on ~/.pgtop are such that others cannot read it (unless you want them to, of course 👀).

You may have white space on either side of the = in lines of the config file. Live your truth 🫶

Shortcut Keys ⌨️💖

The following keys perform various actions while mytop is running. Those which have not been implemented are listed as such. They are included to give the user an idea of what is coming 🔮

  • ? — display help.
  • c — show "command counters" based on the Com_* values in SHOW STATUS. This is a new feature. Feedback welcome 💌
  • d — show only threads connected to a particular database.
  • f — given a thread id, display the entire query that thread was (and still may be) running.
  • F — disable all filtering (host, user, and db).
  • h — only show queries from a particular host.
  • H — toggle the header display. You can also specify either header=0 or header=1 in your config file to set the default behavior.
  • i — toggle the display of idle (sleeping) threads. If sleeping threads are filtered, the default sorting order is reversed so that the longest running queries appear at the top of the list.
  • k — kill a thread.
  • K — kill all threads/queries that have been running for longer than a given threshold in seconds.
  • m — toggle modes. Currently this switches from top mode to qps (Queries Per Second Mode). In this mode, mytop will write out one integer per second. The number written reflects the number of queries executed by the server in the previous one second interval. More modes may be added in the future.
  • o — reverse the default sort order.
  • p — pause display.
  • q — quit pgtop.
  • r — reset the server's status counters via a FLUSH STATUS command.
  • s — change the sleep time (number of seconds between display refreshes).
  • u — show only threads owned by a given user.

The s key has a command-line counterpart: -s.

The h key has two command-line counterparts: -header and -noheader.

💅 DISCLAIMER

Okay bestie, real talk before we go: this is certainly not a quality work. Like, not even gonna pretend. But here's the thing — before this beautiful crap existed, there was nothing but pg_stat_* tables. That's it. That was the whole vibe. Just like Jeremy, I needed something more immediate and usable, like top. ✨

And to tell you the real real truth? I didn't even know about pg_stat tables. :-) Not a clue. 😭

There is huge room for improvement! Like, the potential is giving main character energy, fr fr.

Copyright (C) 2005-2020, Cosimo Streppone.

Copyright (C) 2000-2001, Jeremy D. Zawodny.

🫶 MYTOP CREDITS

Fix a bug. Add a feature. See your name here! 👑

Many thanks go to these fine folks — no cap, absolute icons, every single one:

  • Sami Ahlroos (sami@avis-net.de) — suggested the idle/noidle stuff.
  • Jan Willamowius (jan@janhh.shnet.org) — minor bug report, plus documentation fixes.
  • Alex Osipov (alex@acky.net) — long command-line options and Unix socket support.
  • Stephane Enten (tuf@grolier.fr) — suggested batch mode.
  • Richard Ellerbrock (richarde@eskom.co.za) — bug reports and usability suggestions.
  • William R. Mattil (wrm@newton.irngtx.tel.gte.com) — bug report about empty passwords not working.
  • Benjamin Pflugmann (philemon@spin.de) — suggested the -P command-line flag, as well as other changes.
  • Justin Mecham <justin@aspect.net> — suggested setting $0 to mytop.
  • Thorsten Kunz <thorsten.kunz@de.tiscali.com> — provided a fix for cases when we try to remove the domain name from the display even if it is actually an IP address.
  • Sasha Pachev <sasha@mysql.com> — provided the idea of real-time queries per second in the main display.
  • Paul DuBois <paul@snake.net> — pointed out some option-handling bugs.
  • Mike Wexler <mwexler@tias.com> — suggested that we don't mangle (normalize) whitespace in query info by default.
  • Mark Zweifel <markez@yahoo-inc.com> — made the --idle command-line argument negatable.
  • Axel Schwenke <schwenke@jobpilot.de> — noticed the incorrect formula for query cache hit percentages in version 1.2.
  • Steven Roussey <sroussey@network54.com> — supplied a patch to help filter binary junk in queries so that terminals don't freak out.
  • jon r. luini <falcon@chime.com> — supplied a patch that formed the basis for -prompt support. Sean Leach <sleach@wiggum.com> submitted a similar patch.
  • Yogish Baliga <baliga@yahoo-inc.com> — supplied a patch that formed the basis for -resolve support.
  • Per Andreas Buer <perbu@linpro.no> — supplied an excellent patch to tidy up the top display. This includes showing most values in short form, such as 10k rather than 10000.

See the Changes file on the pgtop distribution page for more details on what has changed. 🦋

📜 LICENSE

pgtop is licensed under the GNU General Public License version 2. For the full license information, please visit http://www.gnu.org/copyleft/gpl.html

🫧 POD ERRORS

Hey! The above document had some coding errors, which are explained below. Not me, not my fault, just saying 🙈:

  • Around line 203: =cut found outside a pod block. Skipping to next block.
  • Around line 209: =cut found outside a pod block. Skipping to next block.
  • Around line 271: =cut found outside a pod block. Skipping to next block.
  • Around line 277: =cut found outside a pod block. Skipping to next block.
And that's the whole page, bestie! 🎀 This one comes from perl v5.42.3, dated 1980-01-01, and it's page PGTOP(1) — go forth and top your Postgres like the icon you are. 💖✨
📦 where this page comes from