DB-Tune

PostgreSQL-Diagnostic-Queries – May 2019

Advertisements

psql queries to quickly Identify & resolve database performance problems

 

 

As a seasoned data store engineer, I often find myself in situations where a production application is down due to some sort of performance issue and I am asked “What’s wrong with the database?”. In almost all these situations, the database (and DBA) is automatically considered guilty until proven innocent. As a DBA, I need the tools and knowledge to help quickly determine the actual problem, if there is one, because maybe there’s nothing wrong with the database or the database server. My favorite approach to start with performance analysis is via PostgreSQL systems catalog

In below post, I am sharing bunch of PostgreSQL system catalog queries that can be used to troubleshoot database engine performance

Postgres system catalogs are a place where database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information. PostgreSQL’s system catalogs are regular tables.

Instance Details

 

1. Get server IP address, Postgres Version and Port number

SELECT inet_server_addr() AS "Server IP"
, version() AS "Postgres Version"
, setting AS "Port Number"
, current_timestamp::timestamp
FROM pg_settings WHERE name = 'port';

--Postgres query for the specific server version, in the standard major.minor.patch format

2. Get server version

SHOW server_version;

-- Version First Release Final Release
-- 11 October 18, 2018 November 9, 2023
-- 10 October 5, 2017 November 10, 2022
-- 9.6 September 29, 2016 November 11, 2021
-- 9.5 January 7, 2016 February 11, 2021
-- 9.4 December 18, 2014 February 13, 2020
-- 9.3 September 9, 2013 November 8, 2018
-- 9.2 September 10, 2012 November 9, 2017
-- 9.1 September 12, 2011 October 27, 2016
-- 9.0 September 20, 2010 October 8, 2015

3. Get system information

SELECT inet_server_addr() AS Server_IP --server IP address
, inet_server_port() AS Server_Port --server port
, current_database() AS Current_Database --Current database
, current_user AS Current_User --Current user
, pg_backend_pid() AS ProcessID --Current user pid
, pg_postmaster_start_time() AS Server_Start_Time --Last start time
, current_timestamp::timestamp - pg_postmaster_start_time()::timestamp AS Running_Since;

--Server up time

4. Get details of server parameter configuration from view pg_settings

SELECT * FROM pg_settings;							-- all parameters

SELECT name, unit, setting FROM pg_settings WHERE name ='port'                  
UNION ALL
SELECT name, unit, setting FROM pg_settings WHERE name ='shared_buffers'        -- shared_buffers determines how much memory is dedicated for caching data
UNION ALL
SELECT name, unit, setting FROM pg_settings WHERE name ='work_mem'              -- work memory required for each incoming connection
UNION ALL
SELECT name, unit, setting FROM pg_settings WHERE name ='maintenance_work_mem'  -- work memory of maintenace type queries "VACUUM, CREATE INDEX etc."
UNION ALL
SELECT name, unit, setting FROM pg_settings WHERE name ='wal_buffers'           -- Sets the number of disk-page buffers in shared memory for WAL
UNION ALL          
SELECT name, unit, setting FROM pg_settings WHERE name ='effective_cache_size'  -- used by postgres query planner
UNION ALL
SELECT name, unit, setting FROM pg_settings WHERE name ='TimeZone'              -- server time zone

-- This gives you a lot of useful information about your postgres instance

5. Get OS information

SELECT version();

-- Gives you major OS version
OS version Wiki References
RedHat-https://en.wikipedia.org/wiki/Red_Hat_Enterprise_Linux
Windows-https://en.wikipedia.org/wiki/List_of_Microsoft_Windows_versions
Mac OS-https://en.wikipedia.org/wiki/MacOS
Ubuntu-https://en.wikipedia.org/wiki/Ubuntu_version_history

6. Get location of data directory (this is where postgres stores the database files)

SELECT name, setting FROM pg_settings WHERE name = 'data_directory';
--OR
SHOW data_directory;

CONNECTION DETAILS

7. Get max_connections configuration

SELECT name, setting, short_desc from pg_settingsWHERE name = 'max_connections';

8. Get total count of current user connections

SELECT COUNT(*) FROM pg_stat_activity;

9. Get total Active Vs Inactive connections

SELECT state, count(pid) 
FROM pg_stat_activity
GROUP BY state, datname
HAVING datname = '<your_database_name>'
ORDER BY count(pid) DESC;

-- One row per server process, showing database OID, database name, process ID, user OID, user name, current query, query's waiting status, time at which the current query began execution
-- Time at which the process was started, and client's address and port number. The columns that report data on the current query are available unless the parameter stats_command_string has been turned off.
-- Furthermore, these columns are only visible if the user examining the view is a superuser or the same as the user owning the process being reported on

Database Details

**** Switch to a user database that you are interested in *****

10. Get database current size (pretty size)

SELECT current_database(), pg_size_pretty(pg_database_size(current_database()));

11. Get details of TOP 20 objects in database

SELECT nspname AS schemaname
, cl.relname AS objectname
, CASE relkind WHEN 'r' THEN 'table'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
ELSE 'other'
END AS type
, s.n_live_tup AS total_rows
, pg_size_pretty(pg_total_relation_size(cl.oid)) AS size
FROM pg_class cl
LEFT JOIN pg_namespace n ON (n.oid = cl.relnamespace)
LEFT JOIN pg_stat_user_tables s ON (s.relid =cl.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND cl.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(cl.oid) DESC
LIMIT 20;

12. Get size of all tables

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;

13. Get table metadata

SELECT relname, relpages, reltuples, relallvisible
, relkind, relnatts, relhassubclass, reloptions
, pg_table_size(oid)
FROM pg_class WHERE relname='<table_name_here>';

14. Get table structure / DESCRIBE a table

SELECT column_name, data_type
, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS where table_name = '<table_name_here>';

-- Does the table have anything unusual about it?
-- a. contains large objects
-- b. has a large proportion of NULLs in several columns
-- c. receives a large number of UPDATEs or DELETEs regularly
-- d. is growing rapidly
-- e. has many indexes on it
-- f. uses triggers that may be executing database functions, or is calling functions directly

CONNECTIONS

15. Get Lock connection count

SELECT COUNT(DISTINCT pid) AS count 
FROM pg_locks WHERE NOT GRANTED;

16. Get locks_relation_count

SELECT relation::regclass AS relname
, COUNT(DISTINCT pid) AS count
FROM pg_locks WHERE NOT GRANTED GROUP BY 1;

17. Get locks_statement_duration

SELECT a.query AS blocking_statement
, EXTRACT('epoch' FROM NOW() - a.query_start) AS blocking_duration
FROM pg_locks bl JOIN pg_stat_activity a
ON a.pid = bl.pid WHERE NOT bl.GRANTED;

INDEXING

18. Get missing indexes

SELECT relname
,seq_scan - idx_scan AS too_much_seq,
CASE
WHEN seq_scan - coalesce(idx_scan, 0) > 0
THEN 'Missing Index?'
ELSE 'OK'
end
,pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan
FROM pg_stat_all_tables
WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000
ORDER BY too_much_seq DESC;

19. Get Unused Indexes

SELECT indexrelid::regclass as index
, relid::regclass as table
, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0 AND indisunique is false;

20. Get index usage stats

SELECT
t.tablename AS "relation",
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS 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, idx_tup_read, idx_tup_fetch, indexrelname, indisunique 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
WHERE t.schemaname='public'
ORDER BY 1,2;

QUERY PERFORMANCE

21. Get TOP 10 costly queries

SELECT  r.rolname,
round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu ,
round(total_time::numeric, 2) AS total_time,
calls,
round(mean_time::numeric, 2) AS mean,
substring(query, 1, 800) AS short_query
FROM pg_stat_statements
JOIN pg_roles r ON r.oid = userid
ORDER BY total_time DESC
LIMIT 5;

CACHING

22. Get TOP cached tables

SELECT relname AS "relation"
, heap_blks_read AS heap_read
, heap_blks_hit AS heap_hit
, ((heap_blks_hit*100) / NULLIF((heap_blks_hit + heap_blks_read), 0)) AS ratio
FROM pg_statio_user_tables;

AUTOVACUUM

23. Last Autovaccum

SELECT relname as "relation"
, EXTRACT (EPOCH FROM current_timestamp-last_autovacuum) as since_last_av
, autovacuum_count as av_count
, n_tup_ins
, n_tup_upd
, n_tup_del
, n_live_tup
, n_dead_tup
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY relname;

PARTITIONING

24. List all table partitions (as Parent/Child relationship)

SELECT
nmsp_parent.nspname AS parent_schema
, parent.relname AS parent
, child.relname AS child
, CASE child.relkind WHEN 'r' THEN 'table'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
ELSE 'other'
END AS type
, s.n_live_tup AS total_rows
FROM pg_inherits
JOIN pg_class parent
ON pg_inherits.inhparent = parent.oid
JOIN pg_class child
ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent
ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child
ON nmsp_child.oid = child.relnamespace
JOIN pg_stat_user_tables s
ON s.relid = child.oid
WHERE child.relkind = 'r'
ORDER BY parent,child;

 

 

 

Advertisements

Advertisements