Skip to content

Instantly share code, notes, and snippets.

@jegj
Last active August 1, 2022 12:42
Show Gist options
  • Save jegj/c0291352defda050be8f27b0cea2de00 to your computer and use it in GitHub Desktop.
Save jegj/c0291352defda050be8f27b0cea2de00 to your computer and use it in GitHub Desktop.
Basic Postgres Administration
# Tunings
## Tuned
```
apt install tuned
```
/etc/tuned/mypg/tuned.conf
```ini
[main]
summary=Tuned profile for PostgreSQL CPUs
[cpu]
governor=performance
energy_perf_bias=performance
min_perf_pct=100
force_latency=1
```
## Setra
/usr/local/bin/setra.sh – Set the block device readahead to 4k, may need adjustment.
```sh
#!/bin/bash
for disk in $(lsblk -n -a -d -e 252,7,11 -o NAME); do
/sbin/blockdev --setra 4096 /dev/${disk}
done
```
systemctl unit file setra.service /etc/systemd/system/
```
[Unit]
Description=Set Readahead
After=local-fs.target
Requires=local-fs.target
[Service]
Type=oneshot
ExecStart=/usr/local/sbin/setra.sh
RemainAfterExit=yes
[Install]
WantedBy=multi-user.target
systemctl enable tune_interfaces.service
```
## Tune network interfaces (as needed)
```
# Tune interfaces for 10Gb throughput
# And 1GB throughput. Modify per system.
#!/bin/bash
# Settings per interface via ethtool -g <interface>
# txqueuelen 8333 for gigabit
# txqueuelen 13888 for 10 gigabit.
/sbin/ethtool -G ens192 rx 4096 tx 4096
/sbin/ip link set ens192 txqueuelen 8333
#/sbin/ip link set ens192 txqueuelen 13888
```
/etc/systemd/system/tune_interfaces.service
```
[Unit]
Description=Set buffers and queue length
After=network.target
Requires=network.target
[Service]
Type=oneshot
ExecStart=/usr/local/sbin/tune_interfaces.sh
RemainAfterExit=yes
[Install]
WantedBy=multi-user.target
```
```
systemctl enable tune_interfaces.service
```
## GRUB
/etc/default/grub
```sh
GRUB_CMDLINE_LINUX_DEFAULT="transparent_hugepage=never elevator=deadline"
update-grub
```
#! /bin/bash
set -E
display() {
echo -e "\n----->" $*
}
abort() {
display $* ; exit 77
}
# ensure root context
if [[ $EUID -ne 0 ]]; then
echo "You must be a root user"
exit 64 # NOTROOT
fi
# Define default values
dbname="vcenter"
pgversion="9.6"
pgport=5433
options="-v -Fc -C "
daily_backup_path="/backups/daily"
retention_path="/backups/retention"
enckey="merrymary"
backupsize=10485760 #Size is always in KB ( By defaul 10 GB)
# Print final values
echo "pgport: ${pgport}"
echo "dbname: ${dbname}"
echo "pgversion: ${pgversion}"
echo "options: ${options}"
echo "daily_backup_path: ${daily_backup_path}"
echo "enckey: ${enckey}"
echo "retention_path: ${retention_path}"
echo "backup_size: ${backupsize}"
echo "=========================================="
# Generate backupname
datestamp=$(date --iso-8601=seconds)
backupname="daily_${dbname}_${datestamp}.backup.enc"
# Set default log
log="/backups/log/backup_${datestamp}.log"
echo '' > $log
# Get space available in the retention path
space_available=$(df -k $retention_path | tail -1 | awk '{print $4}')
###### Main block #######
(
# Move old daily backup into retention folder
daily=$(ls -t $daily_backup_path | tail -1)
if [ -f "${daily_backup_path}/${daily}" ]
then
mv "${daily_backup_path}/${daily}" "${retention_path}/"
fi
# Making space for the new backup
while [ $backupsize -gt $space_available ]
do
oldestenc=$(ls -t $retention_path | tail -1)
display "Deleting old backups..${oldestenc}"
rm "${retention_path}/${oldestenc}"
space_available=$(df -k $retention_path | tail -1 | awk '{print $4}')
done
# Run the backup.
display "Backing up the PostgreSQL database ${dbname}";
su - postgres -c "/usr/lib/postgresql/$pgversion/bin/pg_dump -p ${pgport} -d ${dbname} $options | openssl enc -e -aes-256-cbc -out ${daily_backup_path}/${backupname} -pass pass:${enckey}; echo \${PIPESTATUS[*]} > /tmp/dumpexit.code"
# su - postgres -c "/usr/lib/postgresql/9.6/bin/pg_dump -p 5433 -d vcenter -v -Fc -C | openssl enc -e -aes-256-cbc -out /backups/daily/first.backup.enc -pass pass:merrymary; echo \${PIPESTATUS[*]} > /tmp/dumpexit.code"
# Check return codes
while IFS=" " read -r dumpexit encexit remainder
do
if [[ $dumpexit -ne 0 ]]
then
display "Dump failed"
if [ -f "${daily_backup_path}/${backupname}" ]
then
display "Deleting failed backup ${daily_backup_path}/${backupname}"
rm ${daily_backup_path}/${backupname}
fi
else
if [[ $encexit -ne 0 ]]
then
display "Enc failed"
if [ -f "${daily_backup_path}/${backupname}" ]
then
display "Deleting failed backup ${daily_backup_path}/${backupname}"
rm ${daily_backup_path}/${backupname}
fi
else
display "Backup success!!"
fi
fi
echo "dumpexit: ${dumpexit}"
echo "encexit: ${encexit}"
done < "/tmp/dumpexit.code"
) 2>&1 | tee -a $log
# Common Admin tasks
https://pgtune.leopard.in.ua/#/
https://postgresqlco.nf/
## Multiple clusters
https://www.percona.com/blog/2019/06/24/managing-multiple-postgresql-instances-on-ubuntu-debian/
## Dump role only
pg_dumpall -p 5433 -v --roles-only -f /tmp/roles.sql
## Get all connections
SELECT *
FROM pg_stat_activity
WHERE datname = 'lyons_001_clone';
## Kill all the connections on one db
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname='$PG_CLON_DATABASE';
## Specific table size
select pg_size_pretty(pg_total_relation_size('public.signabledocuments'));
## Column Information
SELECT *
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'addtime' and column_name ='pub_psd_id'
## Kill connection
SELECT pg_terminate_backend (24628)
FROM pg_stat_activity
WHERE datname = 'lyons_001_clone';
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'db_xxxx' AND pid <> pg_backend_pid();
## Slow Running Queries on DB from Last 5 Min
SELECT now()-query_start as Running_Since,pid, datname, usename, application_name, client_addr, left(query,60) from pg_stat_activity where state in ('active','idle in transaction') and (now() - pg_stat_activity.query_start) > interval '5 minutes';
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
## Get database size
SELECT pg_size_pretty( pg_database_size('dbname') );
## Get table size
SELECT pg_size_pretty( pg_total_relation_size('tablename') );
## Get list of table sizes
select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"'), pg_size_pretty(pg_relation_size('"'||table_schema||'"."'||table_name||'"'))
from information_schema.tables
order by 3 DESC
## Block all connections
update pg_database set datallowconn = false where datname = 'lyons_001_clone';
## Vacuum on tables
SELECT schemaname,
relname,
now() - last_autovacuum AS "noautovac",
now() - last_vacuum AS "novac",
n_tup_upd,
n_tup_del,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)),
autovacuum_count,
last_autovacuum,
vacuum_count,
last_vacuum
FROM pg_stat_user_tables
WHERE (now() - last_autovacuum > '7 days'::interval
OR now() - last_vacuum >'7 days'::interval )
OR (last_autovacuum IS NULL AND last_vacuum IS NULL )
ORDER BY novac DESC;
## Unused index
SELECT relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
## Duplicated index
SELECT indrelid::regclass AS table, indkey AS column_numbers, array_agg(indexrelid::regclass) AS indexes, pg_catalog.pg_get_expr(indpred, indrelid, true) AS expression
FROM pg_index
GROUP BY indrelid, indkey, pg_catalog.pg_get_expr(indpred, indrelid, true)
HAVING count(*) > 1;
## Top 10 WRITE Tables
select schemaname as "Schema Name", relname as "Table Name",
n_tup_ins+n_tup_upd+n_tup_del as "no.of writes" from
pg_stat_all_tables where schemaname not in ('snapshots','pg_catalog')
order by n_tup_ins+n_tup_upd+n_tup_del desc limit 10;
## Top 10 READ Tables
SELECT schemaname as "Schema Name", relname as "Table
Name",seq_tup_read+idx_tup_fetch as "no. of reads" FROM
pg_stat_all_tables WHERE (seq_tup_read + idx_tup_fetch) > 0 and
schemaname NOT IN ('snapshots','pg_catalog') ORDER BY
seq_tup_read+idx_tup_fetch desc limit 10;
## Largest Tables in DB
SELECT QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name) as
table_name,pg_relation_size(QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name)) as size,
pg_total_relation_size(QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name)) as total_size,
pg_size_pretty(pg_relation_size(QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name))) as pretty_relation_size,pg_size_pretty(pg_total_relation_size(QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name))) as pretty_total_relation_size FROM information_schema.tables WHERE QUOTE_IDENT(TABLE_SCHEMA) NOT IN ('snapshots') ORDER BY size DESC LIMIT 10;
## DB Size
SELECT datname, pg_database_size(datname),
pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY 2 DESC;
## Table Size
SELECT schemaname, relname, pg_total_relation_size(schemaname
|| '.' || relname ) ,
pg_size_pretty(pg_total_relation_size(schemaname || '.' ||
relname ))
FROM pg_stat_user_tables
ORDER BY 3 DESC;
## Index Size
SELECT schemaname, relname, indexrelname,
pg_total_relation_size(schemaname || '.' || indexrelname ) ,
pg_size_pretty(pg_total_relation_size(schemaname || '.' ||
indexrelname ))
FROM pg_stat_user_indexes
ORDER BY 1,2,3,4 DESC;
## Index Utilization
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_fetch,
idx_tup_read
FROM pg_stat_user_indexes
ORDER BY 4 DESC,1,2,3;
## Tables That Are Being Updated the Most and Looking for VACUUM
select relname, /* pg_size_pretty( pg_relation_size( relid ) ) as table_size,
pg_size_pretty( pg_total_relation_size( relid ) ) as table_total_size, */
n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum::date, last_autovacuum::date, last_analyze::date, last_autoanalyze::date
from pg_stat_all_tables
where relid in (select oid from pg_class
where relnamespace not in (select oid from pg_namespace
where nspname in ('information_schema', 'pg_catalog','pg_toast', 'edbhc' ) ) )
order by n_tup_upd desc, schemaname, relname;
SELECT schemaname,
relname,
now() - last_autovacuum AS "noautovac",
now() - last_vacuum AS "novac",
n_tup_upd,
n_tup_del,
autovacuum_count,
last_autovacuum,
vacuum_count,
last_vacuum
FROM pg_stat_user_tables
WHERE (now() - last_autovacuum > '7 days'::interval
AND now() - last_vacuum >'7 days'::interval)
OR (last_autovacuum IS NULL AND last_vacuum IS NULL ) AND n_dead_tup > 0
ORDER BY novac DESC;
SELECT relname, n_live_tup, n_dead_tup, trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio%",
to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as autovacuum_date,
to_char(last_autoanalyze, 'YYYY-MM-DD HH24:MI:SS') as autoanalyze_date
FROM pg_stat_all_tables where schemaname not in ('pg_toast','pg_catalog','information_schema')
ORDER BY last_autovacuum ;
## Triggers under table
SELECT event_object_table
,trigger_name
,event_manipulation
,action_statement
,action_timing
FROM information_schema.triggers
WHERE event_object_table = 'tableName'
ORDER BY event_object_table
,event_manipulation
## Bloated Index to Run Reindexing (Locking Operation)\pgrepack (Online Rebuilding)
SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size,
bs*(relpages-est_pages)::bigint AS extra_size,
100 * (relpages-est_pages)::float / relpages AS extra_ratio,
fillfactor, bs*(relpages-est_pages_ff) AS bloat_size,
100 * (relpages-est_pages_ff)::float / relpages AS bloat_ratio,
is_na
-- , 100-(sub.pst).avg_leaf_density, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, sub.reltuples, sub.relpages -- (DEBUG INFO)
FROM (
SELECT coalesce(1 +
ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
) AS est_pages,
coalesce(1 +
ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0
) AS est_pages_ff,
bs, nspname, table_oid, tblname, idxname, relpages, fillfactor, is_na
-- , stattuple.pgstatindex(quote_ident(nspname)||'.'||quote_ident(idxname)) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)
FROM (
SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, relam, table_oid, fillfactor,
( index_tuple_hdr_bm +
maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign
ELSE index_tuple_hdr_bm%maxalign
END
+ nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
WHEN nulldatawidth = 0 THEN 0
WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
ELSE nulldatawidth::integer%maxalign
END
)::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na
-- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
FROM (
SELECT
i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.relam, a.attrelid AS table_oid,
current_setting('block_size')::numeric AS bs, fillfactor,
CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8
ELSE 4
END AS maxalign,
/* per page header, fixed size: 20 for 7.X, 24 for others */
24 AS pagehdr,
/* per page btree opaque data */
16 AS pageopqdata,
/* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
CASE WHEN max(coalesce(s.null_frac,0)) = 0
THEN 2 -- IndexTupleData size
ELSE 2 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
END AS index_tuple_hdr_bm,
/* data len: we remove null values save space using it fractionnal part from stats */
sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth,
max( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
FROM pg_attribute AS a
JOIN (
SELECT nspname, tbl.relname AS tblname, idx.relname AS idxname, idx.reltuples, idx.relpages, idx.relam,
indrelid, indexrelid, indkey::smallint[] AS attnum,
coalesce(substring(
array_to_string(idx.reloptions, ' ')
from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor
FROM pg_index
JOIN pg_class idx ON idx.oid=pg_index.indexrelid
JOIN pg_class tbl ON tbl.oid=pg_index.indrelid
JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace
WHERE pg_index.indisvalid AND tbl.relkind = 'r' AND idx.relpages > 0
) AS i ON a.attrelid = i.indexrelid
JOIN pg_stats AS s ON s.schemaname = i.nspname
AND ((s.tablename = i.tblname AND s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE)) -- stats from tbl
OR (s.tablename = i.idxname AND s.attname = a.attname))-- stats from functionnal cols
JOIN pg_type AS t ON a.atttypid = t.oid
WHERE a.attnum > 0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
) AS s1
) AS s2
JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree'
) AS sub
-- WHERE NOT is_na
ORDER BY 2,3,4;
## Bloated Tables to Do Vacuumfull (Locking Operation)\pgrepack (Online Rebuilding)
SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
(tblpages-est_tblpages)*bs AS extra_size,
CASE WHEN tblpages - est_tblpages > 0
THEN 100 * (tblpages - est_tblpages)/tblpages::float
ELSE 0
END AS extra_ratio, fillfactor, (tblpages-est_tblpages_ff)*bs AS bloat_size,
CASE WHEN tblpages - est_tblpages_ff > 0
THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float
ELSE 0
END AS bloat_ratio, is_na
-- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag
FROM (
SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,
ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
-- , stattuple.pgstattuple(tblid) AS pst
FROM (
SELECT
( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
- CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
- CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na
FROM (
SELECT
tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
coalesce(toast.reltuples, 0) AS toasttuples,
coalesce(substring(
array_to_string(tbl.reloptions, ' ')
FROM '%fillfactor=#"__#"%' FOR '#')::smallint, 100) AS fillfactor,
current_setting('block_size')::numeric AS bs,
CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
24 AS page_hdr,
23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END
+ CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024) ) AS tpl_data_size,
bool_or(att.atttypid = 'pg_catalog.name'::regtype) AS is_na
FROM pg_attribute AS att
JOIN pg_class AS tbl ON att.attrelid = tbl.oid
JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
JOIN pg_stats AS s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
WHERE att.attnum > 0 AND NOT att.attisdropped
AND tbl.relkind = 'r'
GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids
ORDER BY 2,3
) AS s
) AS s2
) AS s3;
## Never-Used Indexes
WITH table_scans as (
SELECT relid,
tables.idx_scan + tables.seq_scan as all_scans,
( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
pg_relation_size(relid) as table_size
FROM pg_stat_user_tables as tables
),
all_writes as (
SELECT sum(writes) as total_writes
FROM table_scans
),
indexes as (
SELECT idx_stat.relid, idx_stat.indexrelid,
idx_stat.schemaname, idx_stat.relname as tablename,
idx_stat.indexrelname as indexname,
idx_stat.idx_scan,
pg_relation_size(idx_stat.indexrelid) as index_bytes,
indexdef ~* 'USING btree' AS idx_is_btree
FROM pg_stat_user_indexes as idx_stat
JOIN pg_index
USING (indexrelid)
JOIN pg_indexes as indexes
ON idx_stat.schemaname = indexes.schemaname
AND idx_stat.relname = indexes.tablename
AND idx_stat.indexrelname = indexes.indexname
WHERE pg_index.indisunique = FALSE
),
index_ratios AS (
SELECT schemaname, tablename, indexname,
idx_scan, all_scans,
round(( CASE WHEN all_scans = 0 THEN 0.0::NUMERIC
ELSE idx_scan::NUMERIC/all_scans * 100 END),2) as index_scan_pct,
writes,
round((CASE WHEN writes = 0 THEN idx_scan::NUMERIC ELSE idx_scan::NUMERIC/writes END),2)
as scans_per_write,
pg_size_pretty(index_bytes) as index_size,
pg_size_pretty(table_size) as table_size,
idx_is_btree, index_bytes
FROM indexes
JOIN table_scans
USING (relid)
),
index_groups AS (
SELECT 'Never Used Indexes' as reason, *, 1 as grp
FROM index_ratios
WHERE
idx_scan = 0
and idx_is_btree
UNION ALL
SELECT 'Low Scans, High Writes' as reason, *, 2 as grp
FROM index_ratios
WHERE
scans_per_write <= 1
and index_scan_pct < 10
and idx_scan > 0
and writes > 100
and idx_is_btree
UNION ALL
SELECT 'Seldom Used Large Indexes' as reason, *, 3 as grp
FROM index_ratios
WHERE
index_scan_pct < 5
and scans_per_write > 1
and idx_scan > 0
and idx_is_btree
and index_bytes > 100000000
UNION ALL
SELECT 'High-Write Large Non-Btree' as reason, index_ratios.*, 4 as grp
FROM index_ratios, all_writes
WHERE
( writes::NUMERIC / ( total_writes + 1 ) ) > 0.02
AND NOT idx_is_btree
AND index_bytes > 100000000
ORDER BY grp, index_bytes DESC )
SELECT reason, schemaname, tablename, indexname,
index_scan_pct, scans_per_write, index_size, table_size
FROM index_groups;
## Duplicated Indexes
SELECT
indrelid::regclass AS TableName
,array_agg(indexrelid::regclass) AS Indexes
FROM pg_index
GROUP BY
indrelid
,indkey
HAVING COUNT(*) > 1;
## Blocked Queries
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
## Locking session :
SELECT bl.pid AS blocked_pid,
a.query AS blocking_statement,
now ( ) - ka.query_start AS blocking_duration,
kl.pid AS blocking_pid,
a.query AS blocked_statement,
now ( ) - a.query_start AS blocked_duration
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid
JOIN pg_catalog.pg_locks kl
JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.pid
ON bl.transactionid = kl.transactionid
AND bl.pid != kl.pid
WHERE NOT bl.granted;
## Blocking query
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED;
## table size corrected
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 5;
# Wakeups to write back in hundredths of a second
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 250
# Byte percentages to sync without causing I/O stalls.
# Generally about the size of a RAID cache
vm.dirty_background_ratio=0
vm.dirty_ratio=0
vm.dirty_bytes=67108864
vm.dirty_background_bytes=268435456
# Don't overcommit memory, allocate normally to 100%
# available memory, keep the OOMKiller from randomly
# firing.
vm.overcommit_memory=2
vm.overcommit_ratio=100
# Keep filesystem stuff cached unless the memory is
# under pressure
vm.swappiness=10 # could also be 3
# NUMA memory reclamation from zones, can cause
# performance issues
# This is really only useful to set if you know your
# workload fits into NUMA partitions.
# Most of the time, caching is more important than data locality
vm.zone_reclaim_mode=0
# No NUMA
kernel.numa_balancing = 0
# Keeps threads from bouncing across cores willy nilly.
#
kernel.sched_migration_cost_ns=5000000
kernel.sched_autogroup_enabled=0
# 10ms and 15ms
kernel.sched_wakeup_granularity_ns=15000000
kernel.sched_min_granularity_ns=10000000
# Core dumps with pids
kernel.core_uses_pid=1
kernel.msgmax=65536
kernel.msgmnb=65536
kernel.shmall=4194304
kernel.shmmax=4294967296
# Reboot after 10 seconds on panic
kernel.panic=10
kernel.printk=4 4 1 7
# HTCP and fair queueing
net.ipv4.tcp_congestion_control=htcp
net.core.default_qdisc=fq
# expand the local port range as far as possible
net.ipv4.ip_local_port_range=1024 65535
# Expand the memory available to various kernel
# structures for the network stack
net.core.netdev_max_backlog=262144
net.ipv4.tcp_max_orphans=1048576
net.ipv4.tcp_max_syn_backlog=16384
net.ipv4.route.max_size=8048576
net.ipv4.tcp_max_tw_buckets=1440000
net.core.optmem_max=25165824
# 256 MB
net.core.rmem_default=268435456
net.core.rmem_max=268435456
net.core.wmem_default=268435456
net.core.wmem_max=268435456
# 128 MB
net.ipv4.tcp_rmem=4096 87380 134217728
net.ipv4.tcp_wmem=4096 65536 134217728
net.ipv4.tcp_mem=65536 131072 262144
net.ipv4.udp_mem=65536 131072 262144
net.ipv4.udp_rmem_min=16384
net.ipv4.udp_wmem_min=16384
net.core.somaxconn=65535
net.core.netdev_budget=600
# Disallow forwarding and other
# router functionality.
net.ipv4.conf.all.accept_redirects=0
net.ipv4.conf.all.accept_source_route=0
net.ipv4.conf.all.rp_filter=1
net.ipv4.conf.all.send_redirects=0
net.ipv4.icmp_echo_ignore_broadcasts=1
net.ipv4.icmp_ignore_bogus_error_responses=1
net.ipv4.ip_forward=0
net.ipv4.ip_no_pmtu_disc=1
# Adjust TCP stack
net.ipv4.tcp_syncookies=1
net.ipv4.tcp_timestamps=1
net.ipv4.tcp_window_scaling=1
net.ipv4.tcp_mtu_probing=1
net.ipv4.tcp_no_metrics_save=1
net.ipv4.tcp_rfc1337=1
# This is only necessary for very high
# latency and low throughput networks
net.ipv4.tcp_slow_start_after_idle=0
# Fail quickly on lingering connections
# And recycle resources quickly as well
net.ipv4.tcp_syn_retries=2
net.ipv4.tcp_synack_retries=2
net.ipv4.tcp_sack=1
net.ipv4.tcp_dsack=1
net.ipv4.tcp_ecn=2
net.ipv4.tcp_fack=1
net.ipv4.tcp_fastopen=3
net.ipv4.tcp_fin_timeout=5
net.ipv4.tcp_keepalive_intvl=15
net.ipv4.tcp_keepalive_intvl=60
net.ipv4.tcp_keepalive_probes=10
net.ipv4.tcp_keepalive_probes=5
net.ipv4.tcp_keepalive_time=60
net.ipv4.neigh.default.gc_interval=5
net.ipv4.neigh.default.gc_stale_time=120
net.ipv4.neigh.default.gc_thresh1=4096
net.ipv4.neigh.default.gc_thresh2=8192
net.ipv4.neigh.default.gc_thresh3=16384
# Reuse sockets in TIME_WAIT
net.ipv4.tcp_tw_reuse=1
# Run after temp tables creation
CREATE OR REPLACE FUNCTION test (name text) RETURNS void AS $$
BEGIN
EXECUTE 'ANALYZE VERBOSE ' || name;
END;
$$ LANGUAGE plpgsql;
# Expand work mem and temp buffers for heavy functions
CREATE OR REPLACE FUNCTION test(i integer) RETURNS integer AS $$
DECLARE
guc_value text;
BEGIN
SET work_mem='1024MB';
SET temp_buffers='512MB';
SELECT setting||unit INTO guc_value FROM pg_settings WHERE name = 'work_mem';
RAISE NOTICE 'Current setting is: %',guc_value;
SELECT setting||unit INTO guc_value FROM pg_settings WHERE name = 'temp_buffers';
RAISE NOTICE 'Current setting is: %',guc_value;
RESET work_mem;
RESET temp_buffers;
SELECT setting||unit INTO guc_value FROM pg_settings WHERE name = 'work_mem';
RAISE NOTICE 'Default setting is: %',guc_value;
SELECT setting||unit INTO guc_value FROM pg_settings WHERE name = 'temp_buffers';
RAISE NOTICE 'Default setting is: %',guc_value;
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;
# PGBadger Usage
## Concatane logs
```sh
cd /var/log/postgresql/11/main/
ls -lthr
cat *.log >aggregated.log
```
## Generate report
```sh
pgbadger --prefix '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' /var/log/postgresql/11/main/aggregated.log -o $(hostname)_$(date +%F).html
```
shared_preload_libraries = 'pg_stat_statements,auto_explain'
# May need to increase kernel.shmmax or kernel.shmall
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.save = true
# Set this to -1 when it's not needed to effectively disable it.
auto_explain.log_min_duration = '5s'
auto_explain.log_analyze = true
auto_explain.log_nested_statements = true
log_destination = 'stderr'
log_directory = '/var/log/postgresql/11/main' # It may need to have a mkdir and chown postgres:postgres done if this directory does not exist.
logging_collector = on
log_filename = 'postgresql-%a.log' # postgresql-Thu.log
log_rotation_age = 1d # Rotate every day
log_rotation_size = 0 # Don't rotate based on size
log_truncate_on_rotation = on # Truncate last week's log before starting.
log_checkpoints = on # How often are checkpoints happening and statistics about them
log_connections = on # When does something connect
log_disconnections = off # When does something disconnect
log_lock_waits = on # Logs backends and the duration of lock waits
log_temp_files = 0 # If there are tmp files that spill to disk, of any size, let us know.
log_autovacuum_min_duration = 0 # Gives a lot of statistics for what autovacuum is doing in the logs.
log_min_duration_statement = '3s' # If there's a statement taking 2 seconds or longer, log it.
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' # Recommended log line prefix for pgbadger and other tools. Very informative.
/ root 50GB RAID 1 – Needs enough to have home directories and system logs not continually run out of space.
/data/postgresql/main RAID 10 – Sized according to the disk space
/data/postgresql/pg_wal RAID 1 – Sized according to bursts of WAL.
/data/postgresql/pg_tmp Raid not needed – Size according to the largest temp files generated
/data/postgresql/pg_logs RAID 1 – Sized to keep an appropriate amount of logging for postgresql for debugging.
/data/postgresql/pg_tablespaces RAID 10 – Sized per table that needs to be moved to separate storage
/data/postgresql/pgarchive RAID 1 – For the amount of database WAL files you’ll anticipate keeping
/data/postgresql/pgbackup RAID 1 – For the size and number of backups you recommend keeping locally.
All filesystems should be mounted with 'noatime' or 'noatime, nodiratime'
Swap 8GB
RAM: 25% of the size of the db on disk
Disk Space (main) : Multiply the current db size on disk by 8.
Disk Space (logs) : Multiply the current db size on disk by 8.
Disk Space(WAL): Multiply the size of pg_wal/pg_xlog on disk by 4.
Disk Space(tablespaces): Multiply according to the size of the tables that need to be moved.
Disk Space(pgbackup): Multiply according to the retaining requirements for database backups available locally.
Disk Space(pgarchive): Multiply according to the retaining requirements for WAL in cases where you need to do Point in Time Recovery, e.g. someone runs DELETE FROM <critical_table>;
Disk Space(pg_tmp): Multiply according to the size of pg_temp or the maximum number of temp files by 4.
CPUs: Number of connected sessions / 50 or minimum of 8.
Recommended filesystems for the database are xfs or zfs.
For security best practices the following partitions should be separated and used with the following mount options:
/var/log nodev,noexec,nosuid,relatime
/var/log/audit nodev,noexec,nosuid,relatime
/var
/usr
/tmp nodev,noexec,nosuid,relatime
/var/tmp nodev,noexec,nosuid,relatime
/home nodev
# sysstat/sar commands
## All stats
```sh
sar -A
```
## devices
```sh
sar -d 1
sar -d -f /var/log/sysstat/sa<day_of_month>
```
## network interfaces
```sh
sar -n DEV
```
## network errors
```sh
sar -n EDEV
```
## memory used
```sh
sar -r
```
## pages swapping in and out from disk
```sh
sar -W
```
## tasks created and context switches
```sh
sar -w
```
## CPU utilization
```
sar -u
```
## queue length and load average
```sh
sar -q
```
## higher level swapspace utilization.
```sh
sar -S
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment