Revamp the header

[blogh]

Currently, we have those infos in the header part : (the 3 last lines are removed in non local mode)

PostgreSQL 13.2 - benoit-dalibo - postgres@/var/run/postgresql:5433/postgres - Ref.: 2s
 Size:       845.73M - 0B/s           | TPS:                     1         | Active connections:          1 | Duration mode: query
 Mem.:      35.6% - 2.12G/7.46G       | IO Max:                  0/s
 Swap:      4.9% - 575.23M/11.39G     | Read:           0B/s - 0/s
 Load:         0.06 0.10 0.09         | Write:          0B/s - 0/s

Detail :

• Size : Size of the database you are connected to - Growth in B/s
• Mem. : Pct used - memory used / memory available (in local mode only)
• Swap : Pct swap used - swap used / swap available (in local mode only)
• Load : CPU load (in local mode only)
• TPS : Transaction per second (sum of commit & rollback for all databases / time elapsed since last snap)
• IO Max : Displays the iops (in local mode only)
• Read : Current Read thruput (aggregated data from the psutil library) (in local mode only)
• Write : Current Write thruput (aggregated fata from the psutil library) (in local mode only)
• Active connections : count of active connections
• Duration mode : query/transaction/backend (seems out of place)

It would be nice to have more:

• Active connections : count of active connections / max_connections (superuser_reserved_connections)
• Idle : count of idle connection / max_connections (superuser_reserved_connections)
• Idle in transaction : count of idle in transaction connections / max_connections (superuser_reserved_connections)
• Idle in transaction aborted : count of idle in transaction aborted connections / max_connections (superuser_reserved_connections)
• worker process : count of worker processes / max_worker_processes
• parallel workers : count of parallel workers used for query processing / max_parallel_workers
• maintenance workers : count of parallel workers used for maintenance / max_parallel_maintenance_workers
• logical workers : count of workers used for logical replication / max_logical_replication_workers
• autovacuum workers : count of autovacuum workers / autovacuum_max_workers

We also could list the number of wal senders / wal receiver. But I am not convinced that it's usefull in the header. I am also not fond of having logical workers but they are taken from the worker process pool (like parallel workers and maintenance workers) so not having them here could make the info incomplete / misleading.

Note: the availability of these infos depend on the version of PostgreSQL

Example :

PostgreSQL 13.2 - benoit-dalibo - postgres@/var/run/postgresql:5433/postgres - Ref.: 2s, Duration mode: query
 Size:       845.73M - 0B/s           | TPS:                     1         | Active connections:           9/100(3) | worker process:      8/8
 Mem.:      35.6% - 2.12G/7.46G       | IO Max:                  0/s       | Idle:                        80/100(3) | parallel workers:    6/8
 Swap:      4.9% - 575.23M/11.39G     | Read:           0B/s - 0/s         | Idle in transaction:         10/100(3) | maintenance workers: 1/2
 Load:         0.06 0.10 0.09         | Write:          0B/s - 0/s         | Idle in transaction aborted:  1/100(3) | logical workers:     1/4
                                      |                                    |                                        | autovacuum workers:  1/3

EDIT: data from ps = > data from the psutil library

[Krysztophe]

+1

[frbn]

+1

[gleu]

That's quite a lot of informations. My main wonder is how you would handle smaller screens. This header is way beyond 80 character-wide (looks like 143 to me), it's going to be an issue. If you put that much informations, you need to think about how you will handle smaller screens.

On the details themselves, that looks interesting, and I'm mostly +1 on them.

[dlax]

About content width and smaller screens, that's true. But we're already quite bad as the process table has a fixed (and large) width. That should be improved overall, but I'm not sure how at the moment.

[gleu]

OK. I had no idea about the look and width of the process table, but it actually makes sense. Then go for it. Sounds like interesting details to add up in the header.

[ioguix]

Hi,

First, in the same fashion than top, you could add some interactive commands to enable/disable some infos and save some space. These keys can also turn to command argument so users can alias the command the way they like it.

About existing content, it would be useful to add some infos about the caches. In Mem, having the cache size (eg. Used/Cache/Total) would be less confusing. You could add the shared_buffers hit/miss ratio around there as well.

In regard with the backends, it's a shame we have to compute the total number of connections by summing four lines.

Lag with standbys, archiver status and WAL MB/s might be interesting, but I feel they might be hidden by default and enabled interactively.

[blogh]

Thanks for the inputs !

It's possible to disable some info from the command line and we plan to make it more flexible thru a config file.
Adding Keybinds to do the same should be feasible.

[blogh]

I finally started working on this again.

Here is the new header.

PostgreSQL 14.0 - benoit-dalibo - postgres@/var/run/postgresql:5435/postgres - Ref.: 2s - Duration mode: query
 Global: 22 hours and 39 minutes uptime ⋅ 124.36M dbs size - 131.52K/s growth ⋅ 99.86% cache hit ratio
 Sessions: 51/100 total (3 reserved) ⋅ 51 active ⋅ 0 idle ⋅ 0 idle in txn ⋅ 0 idle in txn abrt
 Activity: 2126 tps ⋅ 2122 insert/s ⋅ 6368 update/s ⋅ 0 delete/s ⋅ 17863 tuples returned/s
 Worker processes: 0/8 total ⋅ 0/4 logical workers ⋅ 0/8 parallel workers
 Other processes & infos: 0/3 autovacuum workers ⋅ 0/10 wal senders ⋅ 0 wal receivers ⋅ 0/10 repl. slots
 Mem.: 7.45G total ⋅ 3.31G (44.46%) free ⋅ 2.82G (37.81%) used ⋅ 1.32G (17.73%) buff+cached
 Swap: 11.39G total ⋅ 9.91G (87.03%) free ⋅ 1.48G (12.97%) used
 IO: 2419/s max iops ⋅ 0B/s - 0/s read ⋅ 9.45M/s - 2419/s write
 Load average: 0.75 0.44 0.52

This is the new info. It's a lot but everything doesn't have to be displayed all the time :

• Global, Session, Activity can be toggled on/off with the "i" key
• Worker processes, Other processes & infos can be toggle on/off with the "w" key
• Mem, Swap, IO, Load average can be toggle on/off with the "s" key

We recently added a filter option. When appropriate the counters will show information according to the filters (currently we can filter only on a database name).

I didn't include the replication lag because it's dependant on the number of standbies.
I think a screen dedicated to replication would be better.

@ioguix @gleu @Krysztophe: do you think some info is missing or unnecessary.

[Krysztophe]

Nice !
Hard to say how it would look in real life, but that's worth a try.

[Krysztophe]

A thought : any way to print the size/nbr of temp files? (in big fat red)

[blogh]

That's a good one. I'll try to squeeze it in there.

[gleu]

A real screenshot would help to better see what you've done.

[blogh]

@gleu : screen shot :)

[gleu]

Real nice. The bigger header is quite crowed though.

[blogh]

Yes, but you can choose to display any "group" you want. I think the instance info will be displayed by defaut and not the rest.

Here is a new screenshot with the tempfiles and waiting query, plus a cheap attempt at making the groups more visible.

For now, adding color when a threshold is reached on a metric is not implemented. We lack the facility to do it and it could be something we add when we move to Rich (#261).

[blogh]

@Krysztophe : The changes have been merged in the master branch feel free to test it and give us feedback.

[blogh]

It's in.