aboutsummaryrefslogtreecommitdiffstats
path: root/docs/_queries
diff options
context:
space:
mode:
Diffstat (limited to 'docs/_queries')
-rw-r--r--docs/_queries/index.md7
-rw-r--r--docs/_queries/kubernetes.md28
-rw-r--r--docs/_queries/loki.md26
-rw-r--r--docs/_queries/postgres.md301
4 files changed, 362 insertions, 0 deletions
diff --git a/docs/_queries/index.md b/docs/_queries/index.md
new file mode 100644
index 0000000..991f86d
--- /dev/null
+++ b/docs/_queries/index.md
@@ -0,0 +1,7 @@
+---
+layout: default
+title: Queries
+has_children: true
+nav_exclude: true
+search_exclude: true
+---
diff --git a/docs/_queries/kubernetes.md b/docs/_queries/kubernetes.md
new file mode 100644
index 0000000..032ad70
--- /dev/null
+++ b/docs/_queries/kubernetes.md
@@ -0,0 +1,28 @@
+---
+layout: page
+title: Kubernetes
+---
+
+# Kubernetes tips
+
+## Find top pods by CPU/memory
+
+```bash
+$ kubectl top pods --all-namespaces --sort-by='memory'
+$ top pods --all-namespaces --sort-by='cpu'
+```
+
+## Find top nodes by CPU/memory
+
+```bash
+$ kubectl top nodes --sort-by='cpu'
+$ kubectl top nodes --sort-by='memory'
+```
+
+## Kubernetes cheat sheet
+
+[Open Kubernetes cheat sheet](https://kubernetes.io/docs/reference/kubectl/cheatsheet/){: .btn .btn-purple }{:target="_blank"}
+
+## Lens IDE
+
+[Open Lens IDE](https://k8slens.dev){: .btn .btn-purple }{:target="_blank"}
diff --git a/docs/_queries/loki.md b/docs/_queries/loki.md
new file mode 100644
index 0000000..5dee3c3
--- /dev/null
+++ b/docs/_queries/loki.md
@@ -0,0 +1,26 @@
+---
+layout: default
+title: Loki
+---
+
+# Loki queries
+
+## Find any logs containing "ERROR"
+
+```sql
+{job=~"default/.+"} |= "ERROR"
+```
+
+## Find all logs from bot service
+
+```sql
+{job="default/bot"}
+```
+
+The format is `namespace/object`
+
+## Rate of logs from a service
+
+```sql
+rate(({job="default/bot"} |= "error" != "timeout")[10s])
+```
diff --git a/docs/_queries/postgres.md b/docs/_queries/postgres.md
new file mode 100644
index 0000000..13728f6
--- /dev/null
+++ b/docs/_queries/postgres.md
@@ -0,0 +1,301 @@
+---
+layout: default
+title: PostgreSQL
+---
+
+# PostgreSQL queries
+
+## Disk usage
+
+Most of these queries vary based on the database you are connected to.
+
+### General Table Size Information Grouped For Partitioned Tables
+
+```sql
+WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS
+ (select inhrelid, inhparent
+ FROM pg_inherits
+ UNION
+ SELECT child.inhrelid, parent.inhparent
+ FROM pg_inherit child, pg_inherits parent
+ WHERE child.inhparent = parent.inhrelid),
+pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit))
+SELECT table_schema
+ , TABLE_NAME
+ , row_estimate
+ , pg_size_pretty(total_bytes) AS total
+ , pg_size_pretty(index_bytes) AS INDEX
+ , pg_size_pretty(toast_bytes) AS toast
+ , pg_size_pretty(table_bytes) AS TABLE
+ FROM (
+ SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
+ FROM (
+ SELECT c.oid
+ , nspname AS table_schema
+ , relname AS TABLE_NAME
+ , SUM(c.reltuples) OVER (partition BY parent) AS row_estimate
+ , SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes
+ , SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes
+ , SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes
+ , parent
+ FROM (
+ SELECT pg_class.oid
+ , reltuples
+ , relname
+ , relnamespace
+ , pg_class.reltoastrelid
+ , COALESCE(inhparent, pg_class.oid) parent
+ FROM pg_class
+ LEFT JOIN pg_inherit_short ON inhrelid = oid
+ WHERE relkind IN ('r', 'p')
+ ) c
+ LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
+ ) a
+ WHERE oid = parent
+) a
+ORDER BY total_bytes DESC;
+```
+
+### General Table Size Information
+
+```sql
+SELECT *, pg_size_pretty(total_bytes) AS total
+ , pg_size_pretty(index_bytes) AS index
+ , pg_size_pretty(toast_bytes) AS toast
+ , pg_size_pretty(table_bytes) AS table
+ FROM (
+ SELECT *, total_bytes-index_bytes-coalesce(toast_bytes,0) AS table_bytes FROM (
+ SELECT c.oid,nspname AS table_schema, relname AS table_name
+ , c.reltuples AS row_estimate
+ , pg_total_relation_size(c.oid) AS total_bytes
+ , pg_indexes_size(c.oid) AS index_bytes
+ , pg_total_relation_size(reltoastrelid) AS toast_bytes
+ FROM pg_class c
+ LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
+ WHERE relkind = 'r'
+ ) a
+) a;
+```
+
+### Finding the largest databases in your cluster
+
+```sql
+SELECT d.datname as Name, pg_catalog.pg_get_userbyid(d.datdba) as Owner,
+ CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
+ THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
+ ELSE 'No Access'
+ END as Size
+FROM pg_catalog.pg_database d
+ order by
+ CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
+ THEN pg_catalog.pg_database_size(d.datname)
+ ELSE NULL
+ END desc -- nulls first
+ LIMIT 20;
+```
+
+### Finding the size of your biggest relations
+
+Relations are objects in the database such as tables and indexes, and this query shows the size of all the individual parts.
+
+```sql
+SELECT nspname || '.' || relname AS "relation",
+ pg_size_pretty(pg_relation_size(C.oid)) AS "size"
+ FROM pg_class C
+ LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
+ WHERE nspname NOT IN ('pg_catalog', 'information_schema')
+ ORDER BY pg_relation_size(C.oid) DESC
+ LIMIT 20;
+```
+
+### Finding the total size of your biggest tables
+
+```sql
+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 20;
+```
+
+## Indexes
+
+### Index summary
+
+```sql
+SELECT
+ pg_class.relname,
+ pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes,
+ pg_class.reltuples AS num_rows,
+ count(indexname) AS number_of_indexes,
+ CASE WHEN x.is_unique = 1 THEN 'Y'
+ ELSE 'N'
+ END AS UNIQUE,
+ SUM(case WHEN number_of_columns = 1 THEN 1
+ ELSE 0
+ END) AS single_column,
+ SUM(case WHEN number_of_columns IS NULL THEN 0
+ WHEN number_of_columns = 1 THEN 0
+ ELSE 1
+ END) AS multi_column
+FROM pg_namespace
+LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
+LEFT OUTER JOIN
+ (SELECT indrelid,
+ max(CAST(indisunique AS integer)) AS is_unique
+ FROM pg_index
+ GROUP BY indrelid) x
+ ON pg_class.oid = x.indrelid
+LEFT OUTER JOIN
+ ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
+ JOIN pg_class c ON c.oid = x.indrelid
+ JOIN pg_class ipg ON ipg.oid = x.indexrelid )
+ AS foo
+ ON pg_class.relname = foo.ctablename
+WHERE
+ pg_namespace.nspname='public'
+AND pg_class.relkind = 'r'
+GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
+ORDER BY 2;
+```
+
+### Index size/usage statistics
+
+```sql
+SELECT
+ t.schemaname,
+ t.tablename,
+ indexname,
+ c.reltuples AS num_rows,
+ pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(t.tablename)::text)) AS table_size,
+ pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(indexrelname)::text)) AS index_size,
+ CASE WHEN indisunique THEN 'Y'
+ ELSE 'N'
+ END AS UNIQUE,
+ number_of_scans,
+ tuples_read,
+ tuples_fetched
+FROM pg_tables t
+LEFT OUTER JOIN pg_class c ON t.tablename = c.relname
+LEFT OUTER JOIN (
+ SELECT
+ c.relname AS ctablename,
+ ipg.relname AS indexname,
+ x.indnatts AS number_of_columns,
+ idx_scan AS number_of_scans,
+ idx_tup_read AS tuples_read,
+ idx_tup_fetch AS tuples_fetched,
+ indexrelname,
+ indisunique,
+ schemaname
+ FROM pg_index x
+ JOIN pg_class c ON c.oid = x.indrelid
+ JOIN pg_class ipg ON ipg.oid = x.indexrelid
+ JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid
+) AS foo ON t.tablename = foo.ctablename AND t.schemaname = foo.schemaname
+WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema')
+ORDER BY 1,2;
+```
+
+### Duplicate indexes
+
+```sql
+SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
+ (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2,
+ (array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4
+FROM (
+ SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
+ coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key
+ FROM pg_index) sub
+GROUP BY key HAVING count(*)>1
+ORDER BY sum(pg_relation_size(idx)) DESC;
+```
+
+## Maintenance
+
+[PostgreSQL wiki](https://wiki.postgresql.org/wiki/Main_Page)
+
+### CLUSTER-ing
+
+[CLUSTER](https://www.postgresql.org/docs/current/sql-cluster.html)
+
+```sql
+CLUSTER [VERBOSE] table_name [ USING index_name ]
+CLUSTER [VERBOSE]
+```
+
+`CLUSTER` instructs PostgreSQL to cluster the table specified by `table_name` based on the index specified by `index_name`. The index must already have been defined on `table_name`.
+
+When a table is clustered, it is physically reordered based on the index information.
+
+### VACUUM-ing
+
+Proper vacuuming, particularly autovacuum configuration, is crucial to a fast and reliable database.
+
+[Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT](https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT)
+
+It is not advised to run `VACUUM FULL`, instead look at clustering. VACUUM FULL is a much more intensive task and acquires an ACCESS EXCLUSIVE lock on the table, blocking reads and writes. Whilst `CLUSTER` also does acquire this lock it's a less intensive and faster process.
+
+#### Finding number of dead rows
+
+```sql
+SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE n_dead_tup <> 0 ORDER BY 2 DESC;
+```
+
+#### Finding last vacuum/auto-vacuum date
+
+```sql
+SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
+```
+
+#### Checking auto-vacuum is enabled
+
+```sql
+SELECT name, setting FROM pg_settings WHERE name='autovacuum';
+```
+
+#### View all auto-vacuum setting
+
+```sql
+SELECT * from pg_settings where category like 'Autovacuum';
+```
+
+## Locks
+
+### Looking at granted locks
+
+```sql
+SELECT relation::regclass, * FROM pg_locks WHERE NOT granted;
+```
+
+### Сombination of blocked and blocking activity
+
+```sql
+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;
+```
generated by cgit v1.2.3 (git 2.39.1) at 2025-11-04 14:37:45 +0000