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, 0 insertions, 402 deletions
diff --git a/docs/queries/index.rst b/docs/queries/index.rst
deleted file mode 100644
index 76218e4..0000000
--- a/docs/queries/index.rst
+++ /dev/null
@@ -1,12 +0,0 @@
-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
deleted file mode 100644
index f8d8984..0000000
--- a/docs/queries/kubernetes.rst
+++ /dev/null
@@ -1,29 +0,0 @@
-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
deleted file mode 100644
index 2ee57a3..0000000
--- a/docs/queries/loki.rst
+++ /dev/null
@@ -1,25 +0,0 @@
-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
deleted file mode 100644
index 5120145..0000000
--- a/docs/queries/postgres.rst
+++ /dev/null
@@ -1,336 +0,0 @@
-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-19 18:33:13 +0000