Difference between revisions of "PostgreSQL"
(→Terminate Idle Transactions for Postgres version 12 and previous) |
(→Terminate Idle Transactions for Postgres version 12 and previous) |
||
Line 267: | Line 267: | ||
= How to = | = How to = | ||
== Terminate Idle Transactions for Postgres version 12 and previous == | == Terminate Idle Transactions for Postgres version 12 and previous == | ||
+ | |||
+ | '''Background''': | ||
+ | since configuration '''idle_in_transaction_session_timeout''' on available at version 13 and above, here is the workaround solutions | ||
In PostgreSQL version 10, you can use a SQL script to identify and terminate idle transactions based on your requirements. | In PostgreSQL version 10, you can use a SQL script to identify and terminate idle transactions based on your requirements. |
Revision as of 13:55, 26 October 2023
Contents
- 1 Introduction
- 2 Database Maintenance and Monitoring script
- 2.1 Monitor Index status dan size for optimization
- 2.2 Monitor Temporary Table
- 2.3 Monitor Dead Tupple
- 2.4 Monitor Cache Hit Ratio
- 2.5 Monitoring Database Query Performance
- 2.6 Locks held by Open Transactions (pg_locks)
- 2.7 Check Trigger Status
- 2.8 Monitor LIVE and DEAD Tupples
- 2.9 Monitor LIVE and DEAD Tupples with AUTO VACUUM
- 3 How to
Introduction
Database Maintenance and Monitoring script
Monitor Index status dan size for optimization
Over optimizing is a thing We always our database to be performant, so in order to do that we keep things in memory/cache (see earlier) and we index things so we don’t have to scan everything on disk. But there is a trade-off when it comes to indexing your database. Each index the system has to maintain will slow down your write throughput on the database. This is fine when you do need to speed up queries, as long as they’re being utilized. If you added an index years ago, but something within your application changed and you no longer need it best to remove it. Postgres makes it simply to query for unused indexes so you can easily give yourself back some performance by removing them:
/* * Over optimizing is a thing We always our database to be performant, so in order to do that we keep things in memory/cache (see earlier) and we index things so we don’t have to scan everything on disk. But there is a trade-off when it comes to indexing your database. Each index the system has to maintain will slow down your write throughput on the database. This is fine when you do need to speed up queries, as long as they’re being utilized. If you added an index years ago, but something within your application changed and you no longer need it best to remove it. Postgres makes it simply to query for unused indexes so you can easily give yourself back some performance by removing them: */ SELECT schemaname || '.' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid --WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 --where relname = 'ret_gen_tsm_targetomzetline' where relname in ('ret_gen_tsm_targetomzetgrow','ret_gen_tsm_targetomzetline','ret_tsmrekap_category','ret_gen_tsmtarget_category') ORDER BY relname asc, pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, --idx_scan asc, pg_relation_size(i.indexrelid) desc ; --drop index ret_gen_tsm_targetomzetline_tag_status_idx;
Monitor Temporary Table
/* * We all aware of the temp tables and many times developers are creating temp table for their ad-hoc testing purpose. * But unfortunately sometimes, they forget to drop unused temp tables, and temp tables keep active in those idle connections. * It also requires unnecessary utilization of disk space. * * DBAs are responsible for identifying unused temp tables and then inform to developers so that they can drop their unused temp tables. */ SELECT n.nspname as SchemaName ,c.relname as RelationName ,CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as RelationType ,pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner ,pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','s') AND (n.nspname !~ '^pg_toast' and nspname like 'pg_temp%') ORDER BY pg_relation_size(n.nspname ||'.'|| c.relname) DESC
Monitor Dead Tupple
/* * Under the covers Postgres is essentially a giant append only log. * When you write data it appends to the log, when you update data it marks the old record as invalid and writes a new one, * when you delete data it just marks it invalid. Later Postgres comes through and vacuums those dead records (also known as tuples). * All those unvacuumed dead tuples are what is known as bloat. Bloat can slow down other writes and create other issues. * Paying attention to your bloat and when it is getting out of hand can be key for tuning vacuum on your database. */ WITH constants AS ( SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 4 AS ma ), bloat_info AS ( SELECT ma,bs,schemaname,tablename, (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM ( SELECT schemaname, tablename, hdr, ma, bs, SUM((1-null_frac)*avg_width) AS datawidth, MAX(null_frac) AS maxfracsum, hdr+( SELECT 1+count(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename ) AS nullhdr FROM pg_stats s, constants GROUP BY 1,2,3,4,5 ) AS foo ), table_bloat AS ( SELECT schemaname, tablename, cc.relpages, bs, CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta FROM bloat_info JOIN pg_class cc ON cc.relname = bloat_info.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema' ), index_bloat AS ( SELECT schemaname, tablename, bs, COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols FROM bloat_info JOIN pg_class cc ON cc.relname = bloat_info.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema' JOIN pg_index i ON indrelid = cc.oid JOIN pg_class c2 ON c2.oid = i.indexrelid ) SELECT type, schemaname, object_name, bloat, pg_size_pretty(raw_waste) as waste FROM (SELECT 'table' as type, schemaname, tablename as object_name, ROUND(CASE WHEN otta=0 THEN 0.0 ELSE table_bloat.relpages/otta::numeric END,1) AS bloat, CASE WHEN relpages < otta THEN '0' ELSE (bs*(table_bloat.relpages-otta)::bigint)::bigint END AS raw_waste FROM table_bloat UNION SELECT 'index' as type, schemaname, tablename || '::' || iname as object_name, ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat, CASE WHEN ipages < iotta THEN '0' ELSE (bs*(ipages-iotta))::bigint END AS raw_waste FROM index_bloat) bloat_summary ORDER BY raw_waste DESC, bloat DESC
Monitor Cache Hit Ratio
/* * monitor your cache hit ratio * URL : https://www.citusdata.com/blog/2019/03/29/health-checks-for-your-postgres-database/ */ SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables;
Monitoring Database Query Performance
/* * monitoring your database query performance. * It records a lot of valuable stats about which queries are run, how fast they return, how many times their run, etc. * Checking in on this set of queries regularly can tell you where is best to add indexes * or optimize your application so your query calls may not be so excessive. * URL : https://www.citusdata.com/blog/2019/03/29/health-checks-for-your-postgres-database/ * * Jalankan : create extension pg_stat_statements; jika belum pernah dilakukan */ SELECT query, calls, ceil(total_time) as total_time, ceil(total_time / calls) as time_per_call, ceil(stddev_time) as stddev_time, rows, rows / calls as rows_per_call, ceil(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0)) AS hit_percent FROM pg_stat_statements WHERE query not similar to '%pg_%' --WHERE query similar to '%omzetline%' --and calls > 500 --ORDER BY calls desc --ORDER BY total_time desc order by time_per_call desc --ORDER BY rows_per LIMIT 100;
Locks held by Open Transactions (pg_locks)
/* * Script to find information about the Locks held by Open Transactions (pg_locks) * * PostgreSQL view pg_locks contains the main information about the all types of lock of Database Server. * It contains one row per active lockable object. * This script is very helpful to all PostgreSQL Database Administrators. */ SELECT pl.pid AS ProcessID ,psa.datname AS DatabaseName ,psa.usename AS UserName ,psa.application_name AS ApplicationName ,ps.relname AS ObjectName ,psa.query_start AS QueryStartTime ,psa.state AS QueryState ,psa.query AS SQLQuery ,pl.locktype ,pl.tuple AS TupleNumber ,pl.mode AS LockMode ,pl.granted -- True if lock is held, false if lock is awaited FROM pg_locks AS pl LEFT JOIN pg_stat_activity AS psa ON pl.pid = psa.pid LEFT JOIN pg_class AS ps ON pl.relation = ps.oid
Check Trigger Status
SELECT * FROM pg_trigger WHERE tgisinternal=false and tgenabled = 'D' --tgrelid 'public.c_order'::regclass and -- enable trigger -- ALTER TABLE m_product ENABLE TRIGGER m_product_trg; -- Disable trigger -- ALTER TABLE m_product DISABLE TRIGGER m_product_trg;
Monitor LIVE and DEAD Tupples
SELECT relname AS ObjectName ,pg_stat_get_live_tuples(c.oid) AS LiveTuples ,pg_stat_get_dead_tuples(c.oid) AS DeadTuples FROM pg_class c --where relname like 'c_%' order by pg_stat_get_dead_tuples(c.oid) desc; --order by pg_stat_get_live_tuples(c.oid) desc;
Monitor LIVE and DEAD Tupples with AUTO VACUUM
SELECT schemaname ,relname ,n_live_tup ,n_dead_tup ,last_autovacuum FROM pg_stat_all_tables ORDER BY n_dead_tup /(n_live_tup * current_setting('autovacuum_vacuum_scale_factor')::float8 + current_setting('autovacuum_vacuum_threshold')::float8) DESC
How to
Terminate Idle Transactions for Postgres version 12 and previous
Background: since configuration idle_in_transaction_session_timeout on available at version 13 and above, here is the workaround solutions
In PostgreSQL version 10, you can use a SQL script to identify and terminate idle transactions based on your requirements.
Create below function:
CREATE OR REPLACE FUNCTION oez_terminate_idle_sessions(idle_timeout_minutes integer) RETURNS void AS $$ BEGIN -- Terminate idle sessions that exceed the specified timeout in minutes PERFORM pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - pg_stat_activity.query_start > interval '1 minute' * idle_timeout_minutes; END; $$ LANGUAGE plpgsql;
To use this function, you can call it with your desired idle timeout value. For example, to terminate sessions idle for more than 5 minutes, you can call the function like this:
SELECT oez_terminate_idle_sessions(5); -- Terminate idle sessions idle for more than 5 minutes
Convert Integer or numeric to row number
select generate_series(1,tot_wo) as row from oez_gen_simple_wo sw