aboutsummaryrefslogtreecommitdiffstats
path: root/docs/queries
diff options
context:
space:
mode:
Diffstat (limited to 'docs/queries')
-rw-r--r--docs/queries/index.rst12
-rw-r--r--docs/queries/kubernetes.rst29
-rw-r--r--docs/queries/loki.rst25
-rw-r--r--docs/queries/postgres.rst336
4 files changed, 402 insertions, 0 deletions
diff --git a/docs/queries/index.rst b/docs/queries/index.rst
new file mode 100644
index 0000000..76218e4
--- /dev/null
+++ b/docs/queries/index.rst
@@ -0,0 +1,12 @@
+Queries
+=======
+
+Get the data you desire with these assorted handcrafted queries.
+
+.. toctree::
+ :maxdepth: 2
+ :caption: Contents:
+
+ kubernetes
+ loki
+ postgres
diff --git a/docs/queries/kubernetes.rst b/docs/queries/kubernetes.rst
new file mode 100644
index 0000000..f8d8984
--- /dev/null
+++ b/docs/queries/kubernetes.rst
@@ -0,0 +1,29 @@
+Kubernetes tips
+===============
+
+Find top pods by CPU/memory
+---------------------------
+
+.. code:: bash
+
+ $ kubectl top pods --all-namespaces --sort-by='memory'
+ $ top pods --all-namespaces --sort-by='cpu'
+
+Find top nodes by CPU/memory
+----------------------------
+
+.. code:: 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/>`__
+
+Lens IDE
+--------
+
+`OpenLens <https://github.com/MuhammedKalkan/OpenLens>`__
diff --git a/docs/queries/loki.rst b/docs/queries/loki.rst
new file mode 100644
index 0000000..2ee57a3
--- /dev/null
+++ b/docs/queries/loki.rst
@@ -0,0 +1,25 @@
+Loki queries
+============
+
+Find any logs containing “ERROR”
+--------------------------------
+
+.. code:: shell
+
+ {job=~"default/.+"} |= "ERROR"
+
+Find all logs from bot service
+------------------------------
+
+.. code:: shell
+
+ {job="default/bot"}
+
+The format is ``namespace/object``
+
+Rate of logs from a service
+---------------------------
+
+.. code:: shell
+
+ rate(({job="default/bot"} |= "error" != "timeout")[10s])
diff --git a/docs/queries/postgres.rst b/docs/queries/postgres.rst
new file mode 100644
index 0000000..5120145
--- /dev/null
+++ b/docs/queries/postgres.rst
@@ -0,0 +1,336 @@
+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
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+.. code:: 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
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+.. code:: 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
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+.. code:: 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.
+
+.. code:: 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
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+.. code:: 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
+~~~~~~~~~~~~~
+
+.. code:: 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
+~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+.. code:: 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
+~~~~~~~~~~~~~~~~~
+
+.. code:: 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>`__
+
+.. code:: 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.
+
+The
+`clusterdb <https://www.postgresql.org/docs/current/app-clusterdb.html>`__
+CLI tool is recommended, and can also be used to cluster all tables at
+the same time.
+
+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.
+
+The
+`vacuumdb <https://www.postgresql.org/docs/current/app-vacuumdb.html>`__
+CLI tool is recommended for manual runs.
+
+Finding number of dead rows
+^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+.. code:: 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
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+.. code:: sql
+
+ SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
+
+Checking auto-vacuum is enabled
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+.. code:: sql
+
+ SELECT name, setting FROM pg_settings WHERE name='autovacuum';
+
+View all auto-vacuum setting
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+.. code:: sql
+
+ SELECT * from pg_settings where category like 'Autovacuum';
+
+Locks
+-----
+
+Looking at granted locks
+~~~~~~~~~~~~~~~~~~~~~~~~
+
+.. code:: sql
+
+ SELECT relation::regclass, * FROM pg_locks WHERE NOT granted;
+
+Сombination of blocked and blocking activity
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+.. code:: 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-10-31 21:06:00 +0000