Terminal Tools pg_top, pg_systat, pg_proctab PostgresOpen 2019 . - - PowerPoint PPT Presentation

▶
terminal tools
SMART_READER_LITE
LIVE PREVIEW

Terminal Tools pg_top, pg_systat, pg_proctab PostgresOpen 2019 . - - PowerPoint PPT Presentation

Feb 19, 2024 623 likes •1.01k views

Terminal Tools pg_top, pg_systat, pg_proctab PostgresOpen 2019 . . . . . . . . . . . . . . . . . . . . https://www. 2ndQuadrant.com . . . . . . . . . . . . . . . . . . . . PostgresOpen 2019 Orlando, FL |

slide-1
SLIDE 1

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Terminal Tools

pg_top, pg_systat, pg_proctab

PostgresOpen 2019

slide-2
SLIDE 2

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

Overview

  • Terminal based tools
  • Basic live views of the system
  • Intended to be simpler to use than psql
  • Compliment other tools
  • Present capabilities of these tools
slide-3
SLIDE 3

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

About me …

  • Employed by 2ndQuadrant
  • PostgreSQL Contributor since 2005
  • Director at United States

PostgreSQL Association since 2011

  • Portland PostgreSQL Users Group
slide-4
SLIDE 4

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

Open source projects

  • pg_top - Display PostgreSQL processes

https://pg_top.gitlab.io/

  • pg_systat - Display PostgreSQL statistics

https://pg_systat.gitlab.io/

  • pg_proctab - PostgreSQL extension to

query operating system process table https://pg_proctab.gitlab.io/

slide-5
SLIDE 5

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_top

Display top PostgreSQL processes by:

  • Memory
  • Processor utilization
  • Transaction or query run time
slide-6
SLIDE 6

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_top Default View

slide-7
SLIDE 7

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_top Backend States

slide-8
SLIDE 8

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_top State

slide-9
SLIDE 9

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_top Username

slide-10
SLIDE 10

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_top Transaction & Query Time

slide-11
SLIDE 11

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_top I/O Statistics

slide-12
SLIDE 12

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_top Replication

slide-13
SLIDE 13

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_top Display Query

slide-14
SLIDE 14

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_top Query Execution Plan

slide-15
SLIDE 15

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_top Display Locks Held

slide-16
SLIDE 16

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_top Additional Features

  • Batch-mode operation can be used

to capture output to file:

pg_top -b -d 1 -x 10 > pg_top.out

  • Can be used to display PostgreSQL

processes on remote systems if the remote database has the pg_proctab extension loaded

slide-17
SLIDE 17

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_systat

Display PostgreSQL statistics tables:

  • pg_stat_database
  • pg_stat_database_conflicts
  • pg_stat_all_tables
  • pg_stat_all_indexes
  • pg_statio_all_tables
  • pg_statio_all_indexes
  • pg_stat_process_vacuum
slide-18
SLIDE 18

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_systat database transactions

slide-19
SLIDE 19

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_systat database blocks

slide-20
SLIDE 20

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_systat database tuples

slide-21
SLIDE 21

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_systat database file system usage

slide-22
SLIDE 22

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_systat database confmicts (replicas only)

slide-23
SLIDE 23

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_systat table scans

slide-24
SLIDE 24

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_systat table tuples

slide-25
SLIDE 25

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_systat table vacuum

slide-26
SLIDE 26

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_systat table analyze

slide-27
SLIDE 27

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_systat index

slide-28
SLIDE 28

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_systat Additional Features

  • Batch-mode operation can be used

to capture output to file:

pg_systat -b > pg_systat.out

  • Almost all views can display PostgreSQL

statistics from remote systems

slide-29
SLIDE 29

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_proctab

A PostgreSQL extension that provides a SQL interface to the operating system’s process table through user-defined functions:

  • pg_cputime
  • pg_loadavg
  • pg_memusage
  • pg_proctab
slide-30
SLIDE 30

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_proctab Features

  • Generate reports on system utilization by

process; see scripts in contrib directory

  • Allows pg_top to get operating system

statistics from remote systems

  • Currently only for PostgreSQL systems

running on Linux

slide-31
SLIDE 31

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_proctab Information

  • Provides low level system

information

  • Example scripts provided for:
  • creating tables to store snapshots of

statistics data

  • collecting snapshots of statistics data
  • generating reports from the saved data
slide-32
SLIDE 32

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_system Additional Features

  • Batch-mode operation can be used

to capture output to file:

pg_top -b -d 1 -x 10 > pg_top.out

  • Can be used to display PostgreSQL

processes on remote systems if the remote database has the pg_proctab extension loaded

slide-33
SLIDE 33

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_proctab Example

# Create tables psql −f create−ps_procstat−tables . sql # Take a snapshot

  • f

data ; returns snapshot ID psql −f ps_procstat−snap . sql # Take another snapshot

  • f

data # returns snapshot ID psql −f ps_procstat−snap . sql

slide-34
SLIDE 34

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_proctab Processor and I/O Utilization Reports

% ps−processor−u t i l i z a t i o n . sh <pid > <snap1> <snap2> Processor U t i l i z a t i o n = 42.00 % % ps−io−u t i l i z a t i o n . sh <pid > <snap1> <snap2> Reads = 276981 Writes = 63803 Reads ( Bytes ) = 2164604928 Writes ( Bytes ) = 508166144 Cancelled ( Bytes ) = 36880384

slide-35
SLIDE 35

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_proctab Database Reports

% ps−report . pl <pid > <snapshot 1> <snapshot 2> Database : dbt5 Snapshot Start : 2010−03−26 15:24:51.516226−07 Snapshot End : 2010−03−26 15:25:51.57661−07 − − − − − − − − − − − − − − − − − − − Database S t a t i s t i c s − − − − − − − − − − − − − − − − − − − Commits : 421 Rollbacks : 2 Blocks Read : 13919368 Blocks Hit : 7876506

slide-36
SLIDE 36

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_proctab Table Stat Report

================ Table S t a t i s t i c s ================ − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − Schema . Relation Seq Scan Seq Tup Read Idx Scan Idx Tup Fetch N Tup Ins N Tup Up − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − public . broker 169 8067 259 . . .

slide-37
SLIDE 37

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

pg_proctab Index Stats Report

================ Index S t a t i s t i c s ================ − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − Schema . Relation . Index Idx Scan Idx Tup Read Idx Tup Fetch − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − − public . broker . pk_broker 259 259 . . .

slide-38
SLIDE 38

https://www.2ndQuadrant.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

PostgresOpen 2019

Orlando, FL | 11-13 September 2019

Thank you! Visit 2ndQuadrant in the Expo Hall!

Mark Wong Consultant, 2ndQuadrant Contributor, PostgreSQL mark@2ndQuadrant.com https://2ndquadrant.com/contact