7. Monitoring and routine checks

By the end of this chapter: You decide what to watch, how often to check it, and at which values to act.

• GritDB 1.1 has no metrics collection endpoint, so check with the commands and SQL in this chapter. The diagnostic queries are in A.6.

7.1 Monitoring metrics

Check the GritDB engine memory.

grep 'process_rss=' ~/gritdb1/log/grit_unify_main.log | tail -1
free -h

• Engine memory % is the process_rss value divided by total on the Mem: line of free -h. The judgment criteria are in 7.2. The warning level is 80%.
• GB in process_rss is based on 1024, so it is the same unit as Gi in free -h.
• A statistics line is written every 5 seconds, so read only the current file (grit_unify_main.log). If you include the rotated files (.1 to .20), you can get an old value.

GritDB always uses workers and internal connections inside PostgreSQL. Do not terminate them even when they appear in pg_stat_activity (4.2).

SELECT CASE WHEN application_name LIKE 'gritdb\_catalog\_%' THEN 'internal'
            ELSE backend_type END AS kind,
       count(*)
FROM pg_stat_activity
WHERE backend_type IN ('grit_admin_worker', 'gritstore_heartbeat', 'gritsurfer_recovery',
                       'GritSurferSlotManagerWorker')
   OR application_name LIKE 'gritdb\_catalog\_%'
GROUP BY 1 ORDER BY 1;

• It is normal if each of the four worker lines shows 1 and the one internal line shows 6 (default).
• The watermark worker (GritSurfer Watermark Worker) also always runs. It does not connect to a database, so it is not in pg_stat_activity. To check, run pgrep -u gritdb -c -f 'GritSurfer Watermark Worker'. It returns 1.
• Only the 6 internal connections use max_connections. Workers do not use max_connections (10.2).

7.2 Thresholds

• Some metrics have an empty cell or no number. They are unify checkpoint age, WAL retained by the recovery replication slot (7.3), and daily growth of log/ (7.4). For these, collect the 7.4 records for a few weeks after you start production. Then set the values for that server.

7.3 Routine checklist

Number of open files and /dev/shm (service account):

for p in $(cut -d: -f2 ~/gritdb1/run/grit_admin.pid); do echo "$p $(ls /proc/$p/fd | wc -l) $(awk '/^Max open files/ {print $4, $5}' /proc/$p/limits)"; done
df -h /dev/shm

• The output columns are PID, open count, and the limit of that process (soft and hard). Compare with this value, not with the ulimit of your shell. If GritDB starts as a service at boot, the limit is LimitNOFILE in 4.6, not limits.conf.

• If the limit is below 65536, check how GritDB was started. For a start at boot, see 4.6. For a manual start, see ① in 1.4.

• Importance (High, Medium, or Low) is the size of the impact that can occur if you skip the check.

• The severity level for a support request follows the severity levels of the subscription contract (1 Urgent to 3 Normal). It differs from the importance in this table.

7.4 Check record and trends

Record the results in the same table every day. Add columns to the table in QuickStart 5.3.

Collect the values as the service account. For the slot retained WAL, see A.6, and for engine memory %, see 7.1. If you installed with --pgdata, write both volumes in the Disk % column.

df -h ~/gritdb1/datafiles/ ~/gritdb1/datafiles/pgdata/
du -sh ~/gritdb1/datafiles/* ~/gritdb1/log
du -sh ~/gritdb1/datafiles/pgdata/pg_wal
find ~/gritdb1/datafiles/cold_file -mindepth 1 -maxdepth 1 -type d | wc -l
grep 'process_rss=' ~/gritdb1/log/grit_unify_main.log | tail -1
free -h

Judge by the trend.

• Daily growth is today’s value minus the value 7 days ago, divided by 7. Days left is the free space divided by the daily growth.
• If the days left are fewer than the time it takes to add disk space, expand the volume. Keep the path (1.5).

• Record the log/ size (du -sh ~/gritdb1/log) every day. The 2 shared logs almost never rotate by size (9.3).
• Under workloads with many updates and deletes, cold files can grow much larger than the source data. So plan the disk with the cold_file size trend (1.3).

7.5 Monitoring the analytics setup

For an analytics setup (6.4), monitor both sides together: the operational database and GritDB. On the operational database side, check the WAL held by the subscription slot and the slot status. On the GritDB side, check whether the subscription is receiving, and check the error counts.

On the operational database (subscription slot and held WAL):

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
       pg_size_pretty(safe_wal_size) AS safe_wal,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS behind
FROM pg_replication_slots
WHERE slot_name = '<sub>' OR slot_name LIKE 'pg\_<oid>\_sync\_%';

• <oid> is the subscription oid that you wrote down in 6.4.1 step 8. Do not mix in slots of standby servers or other CDC tools. This is the same condition as 6.4.4 step 2.

On GritDB (subscription status and error counts):

SELECT subname, pid, latest_end_lsn, latest_end_time FROM pg_stat_subscription;
SELECT subname, apply_error_count, sync_error_count FROM pg_stat_subscription_stats;

• It is normal if all of the following are true. On the operational database, the slot’s active is t, its wal_status is reserved, and its retained_wal does not keep growing. On GritDB, pid has a value, latest_end_time keeps changing, and the error counts do not increase.
• If wal_status is extended, the slot holds WAL beyond max_wal_size of the operational database. In that case, check the stall signs below. If it is unreserved, WAL that the slot needs is removed at the next checkpoint. In that case, fix the cause on the GritDB side right away. If it goes further, it becomes lost (8.6).
• safe_wal is the amount of WAL that can still build up before the slot becomes lost. It appears only when max_slot_wal_keep_size is set on the operational database, and it is empty when that is -1. The time you can keep GritDB stopped is safe_wal divided by the WAL generation rate (4.7).
• behind is the amount of WAL that GritDB has not yet reported as received. Check the GritDB engine apply separately with 6.6.1.
• To check the subscription configuration, run SELECT subname, substream FROM pg_subscription;. The substream column shows f.
• Views: pg_stat_subscription, pg_stat_subscription_stats

If the analytics setup stops (GritDB stop or apply error):

• The operational database slot holds WAL, and the operational database disk fills up (6.4.3).
• The limit is max_slot_wal_keep_size of the operational database. For details, see 6.4.1 step 1 and 6.4.3. Past the limit, the operational database slot becomes invalid, and wal_status becomes lost. In that case, take the analytics setup again from the start (8.6).
• ⚠ Do not set max_slot_wal_keep_size on the GritDB server (10.5).

Signs that the subscription stopped:

grep -n -E 'ERROR: .*logical replication|logical replication (apply|tablesync) worker.* exited' ~/gritdb1/log/postgresql.log | tail -5

• Find the cause from the error text.
  • has neither REPLICA IDENTITY index nor PRIMARY KEY means that the GritDB table has no primary key. In that case, see 6.4.1 step 6.
  • is missing replicated column or does not exist means that a column or a name was changed on the operational database. In that case, see 6.4.2.
• If GritDB stops and then stops again during the analytics setup apply after each restart, do not keep restarting it. A large transaction on the operational database, or a large table’s initial copy, may have exceeded the single-transaction limit (6.8) (6.4.3). In that case, collect diagnostic data with QuickStart 7.4 and contact support (chapter 0).

7.6 Routine cleanup tasks

GritDB does not delete these files by itself. Set a schedule and delete them.

Find temporary files left after an abnormal termination. Run the commands as the service account while GritDB is stopped.

ls -la ~/gritdb1/datafiles/buffer_spill/
ls -la "${TMPDIR:-/tmp}"/__tkidx_spill_*.tmp

• It is normal if both are empty after a normal stop. No such file or directory also counts as empty.
• GritDB deletes the cold file directory of an unregistered or dropped (DROP) table after it deletes those cold files. An empty directory left by an abnormal termination is cleaned up at the next start. So do not delete it by hand. The directory of a newly registered table appears when the first row is applied (6.3).
• Long analytics session queries delay the reclaim of cold file disk space. The space is reclaimed when the query ends (7.8).

7.7 Pre-production checks

Make the baseline collection once when you start production. Before and after planned work (a 3.4 upgrade or a 4.7 planned shutdown), make the same collection again and compare.

B=~/gritdb_baseline_$(date +%Y%m%d_%H%M%S)
mkdir -p "$B"
{ date; uname -a; free -g; df -h; ulimit -a; } > "$B/system.txt" 2>&1
for p in $(cut -d: -f2 ~/gritdb1/run/grit_admin.pid); do echo "== $p"; cat /proc/$p/limits; done >> "$B/system.txt" 2>&1
cp ~/gritdb1/share/gritdb/manifest.json "$B/"
cp ~/gritdb1/datafiles/pgdata/postgresql.conf ~/gritdb1/datafiles/pgdata/postgresql.auto.conf ~/gritdb1/datafiles/pgdata/pg_hba.conf "$B/"
cp -r ~/gritdb1/config "$B/"
~/gritdb1/bin/psql -p 5432 -d postgres -c "SELECT name, setting, source FROM pg_settings WHERE source NOT IN ('default', 'override') ORDER BY 1" > "$B/settings.txt"
~/gritdb1/bin/psql -p 5432 -d <db> -c "SELECT schema_name, table_name, register_ts FROM get_gritsurfer_tables() ORDER BY 1, 2" > "$B/tables_<db>.txt"
tail -n 200 ~/gritdb1/log/grit_admin_main.log > "$B/grit_admin_main_tail.txt"
ls -l "$B"

• For <db>, repeat with each database that has registered tables.
• To check, run ls -l "$B". It shows all files, and no file has size 0.
• To compare, collect with the same commands after the work, then run diff -r <collection before work> <collection after work>. Check that the only changed lines are the planned changes (version number and configuration).
• The collection contains server configuration, so keep it with the same permissions as the installation directory (11.7).

7.8 Maintenance (VACUUM, statistics, transaction IDs)

• Do not turn off autovacuum. To check, run SHOW autovacuum;. It returns on.

Transaction ID age (for each database):

SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY 2 DESC;

• It is normal if the age is well below autovacuum_freeze_max_age (PostgreSQL default 200 million). For details, see Preventing transaction ID wraparound. If it gets close, look for the causes in “What blocks VACUUM” below.

Dead rows (bloat, for each database that has registered tables):

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

• It is normal if n_dead_tup is small compared with n_live_tup and last_autovacuum is recent.

What blocks VACUUM:

• GritDB performs cold file compaction and cleanup by itself. There is no setting to change. Compaction is the GritDB engine’s work of merging small cold files into large cold files. Its log is grit_unify_cold_store_compactor_gc_processor.log (9.1).
  • They are delayed by long analytic queries or a unify checkpoint stall. The files that a query reads are deleted after the query ends.
  • To check, watch the size trend of INACTIVE and STAGED in get_gritsurfer_coldfiles() (A.6, 7.4).

7.9 Connect to a monitoring tool

Because there is no metrics collection endpoint, a check script outputs values and an exit code. Your in-house monitoring tool runs the script periodically.

Below is an example that you run as the service account. Exit code 0 means normal, and 2 means abnormal.

#!/bin/bash
G=~/gritdb1
rc=0
if ! "$G/bin/pg_isready" -q -p 5432 -d postgres; then
  echo "CRITICAL connect"; rc=2
fi
n=$(ps -o comm= -p "$(cut -d: -f2 "$G/run/grit_admin.pid" 2>/dev/null | paste -sd,)" 2>/dev/null | wc -l)
if [ "$n" -ne 4 ]; then
  echo "CRITICAL processes=$n"; rc=2
fi
if [ -s "$G/log/GRIT_SURFER_ASSERT.log" ]; then
  echo "CRITICAL assert_log"; rc=2
fi
echo "slot_retained_bytes=$("$G/bin/psql" -p 5432 -d postgres -Atc "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) FROM pg_replication_slots WHERE slot_name = 'gritsurfer_slot'")"
echo "postmaster_start=$("$G/bin/psql" -p 5432 -d postgres -Atc "SELECT pg_postmaster_start_time()")"
exit "$rc"

• For other values, add A.6 diagnostic queries in the same form (psql -Atc).
• Change the meaning of the exit codes to match the conventions of your monitoring tool.
• To check, run the script while everything is normal, then run echo $?. It returns 0.
• If CRITICAL assert_log continues, check whether you already reported that record to support. If you did, move the file as in 9.5 and run the script again. The line then disappears.

Log monitoring:

7.10 Consistency check

At a set interval, confirm that regular sessions and analytics sessions return the same results (once a week, 7.3).

1. Choose sample tables. Pick tables that you query often and tables with many updates and deletes. For the numeric column, pick an integer or numeric(p, s) column. Sums of real or double precision can differ in the last digits (10.3).
2. Stop writes to those tables, or pick a time with no writes.
3. For an analytics setup, confirm that GritDB has caught up with the operational database (6.4.1 step 9, sub-steps 1 to 2).
4. Confirm that changes are applied (6.6.1).
5. Measure with the same query.

Regular session (for an analytics setup, also on the operational database):

SELECT count(*), sum(<numeric column>) FROM <s>.<t>;

Analytics session (6.6, force the GritDB engine, 6.6.1):

/*+ engine=burst, fallback=false */ SELECT count(*), sum(<numeric column>) FROM <s>.<t>;

6. Judge the result.
   • If they are the same, write it in the 7.4 record.
   • If they differ, check whether that table had an operation from 6.7 (TRUNCATE, column changes, and so on). For a single-database setup, follow “Rebuild the table” in 6.7. For an analytics setup, take that table again as in 6.4.2. If several tables differ, see 8.6.
   • If you do not know the cause, collect diagnostic data with QuickStart 7.4 and contact support (chapter 0).

• Check that all values from step 5 are the same (regular session, analytics session, and, for an analytics setup, the operational database).

If a problem occurs

• See the TroubleShooting entries in the “If abnormal” column of the checklist. If you did not receive TroubleShooting, contact support (chapter 0).

• If GritDB does not respond or processes are missing, record the current state before you restart. Run the commands below during QuickStart 7.4 step 4, before the tar line, in the same shell (cd ~, $S).

  ps -u gritdb -o pid,stat,wchan:32,etime,args > "$S/process_state.txt" 2>&1
  timeout 15 ~/gritdb1/bin/psql -p 5432 -d postgres -X -c "SET statement_timeout = '10s'" -c "SELECT now(), pid, backend_type, usename, datname, state, wait_event_type, wait_event, xact_start, query_start, pg_blocking_pids(pid) FROM pg_stat_activity ORDER BY backend_start" > "$S/activity.txt" 2>&1
  timeout 15 ~/gritdb1/bin/psql -p 5432 -d postgres -X -c "SET statement_timeout = '10s'" -c "SELECT * FROM pg_replication_slots" -c "SELECT gid, prepared, owner, database FROM pg_prepared_xacts" > "$S/slots.txt" 2>&1
  timeout 15 ~/gritdb1/bin/psql -p 5432 -d postgres -X -c "SET statement_timeout = '10s'" -c "SELECT max(ckpt_lsn), now() - max(created_at) FROM get_gritsurfer_unify_checkpoints()" > "$S/unify_checkpoint.txt" 2>&1
  timeout 15 ~/gritdb1/bin/psql -p 5432 -d <db> -X -c "SET statement_timeout = '10s'" -c "SELECT seq, pid, query_start_time, query_hash, status, session_start_time FROM get_gritsurfer_active_queries()" > "$S/active_<db>.txt" 2>&1
  

  • Run the last line for each database that has registered tables. Do not save the query text columns query and query_text (9.7).
  • Each line finishes within 15 seconds (timeout). If a line cannot connect or does not finish, its file contains an error or is empty. In that case, send it as it is, and note this in the record from QuickStart 7.4 step 3.
  • For the meaning of the queries, see A.6. The function is described in pg_blocking_pids. For the bundle to send, see 9.7.

Learn more

• For diagnostic queries, see A.6. For logs, see chapter 9.
• PostgreSQL 17 documentation: Statistics views, pg_replication_slots, Routine vacuuming