SHOW_POOL_BACKEND_STATS(1)

💖 SHOW_POOL_BACKEND_STATS 💖

okay bestie, real talk: this is a page from the pgpool-II 4.7.2 Documentation crew, and it's about the SHOW_POOL_BACKEND_STATS command. Lowkey obsessed with how much it tells you. Let's get into it 🫶

---

✨ NAME ✨

SHOW_POOL_BACKEND_STATS — bestie, it shows you the backend SQL command statistics. that's the whole vibe 💅

---

🌸 SYNOPSIS 🌸

SHOW POOL_BACKEND_STATS

periodt. that's it. no args, no flags, just send it ✨

---

🦋 DESCRIPTION 🦋

so SHOW POOL_BACKEND_STATS spills the tea on every backend: the node id, the hostname, the port, the status, the role, and the query counts for SELECT/INSERT/UPDATE/DELETE/DDL/other, broken down per backend. it also counts up the error messages that came back from the backend and shows them, sorted by severity. ngl that's a lot of intel. 🕵️‍♀️

the node id, the hostname, the port, the status and the role are the exact same ones you'd see from SHOW POOL_NODES — check SHOW_POOL_NODES(1) if you need the lore 😌

now the counts, fr fr:

• select_cnt, insert_cnt, update_cnt, delete_cnt, ddl_cnt, other_cnt are how many SQL commands of each kind — SELECT, INSERT, UPDATE, DELETE, DDL and others — got issued since Pgpool-II started. ⏰
• commands that failed (like, say, a SELECT from a table that literally does not exist 🙈) are counted. no skipping, no mercy.
• commands that got rolled back are also counted. 💀
• and here's the spicy bit: currently, every command that isn't SELECT/WITH/INSERT/UPDATE/DELETE/CHECKPOINT/DEALLOCATE/DISCARD/EXECUTE/EXPLAIN/LISTEN/LOAD/LOCK/NOTIFY/PREPARE/SET/SHOW/Transaction commands/UNLISTEN gets classified as DDL. yes way. that's just how it is 😭

gateway drug to monitoring your cluster btw. once you see these numbers you'll never look away 👑

here's a lil example session, so you can see the aesthetic in action 💖:

test=# show pool_backend_stats;
 node_id | hostname | port  | status |  role   | select_cnt | insert_cnt | update_cnt | delete_cnt | ddl_cnt | other_cnt | panic_cnt | fatal_cnt | error_cnt
---------+----------+-------+--------+---------+------------+------------+------------+------------+---------+-----------+-----------+-----------+-----------
 0       | /tmp     | 11002 | up     | primary | 12         | 10         | 30         | 0          | 2       | 30        | 0         | 0         | 1
 1       | /tmp     | 11003 | up     | standby | 12         | 0          | 0          | 0          | 0       | 23        | 0         | 0         | 1
(2 rows)

look at those two rows just sitting there, thriving, up and unbothered ✨ two backends, one primary, one standby, and yes the error_cnt is 1 on both — not me zooming in on that 👀

---

and that's the whole page, bestie!! mwah 🎀

this was brought to you by pgpool-II 4.7.2 — documentation from the year 2023, and the page is SHOW_POOL_BACKEND_STATS(1) (yes the footer truncates it to SHOW POO...END_STATS(1) because even manpages run out of room 💀). anyway, go run it on your cluster and slay. 💜