-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Both InnoDB and PostgreSQL - as well as many other databases - use a technique called multi-version concurrency control (MVCC) to provide transaction isolation: transactions should not see the work of other, uncommitted transactions. MVCC means that, when a row is updated, the database stores both the old and new versions of the row.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Tuesday, March 15, 2011

Monitor PostgreSQL with Queries

(Not All written by me.... )Some time just monitoring a DB servers from monitoring tools is just not enough, in that case we need some tool that let us know, what is DB is holding, processing, parameters etc..

So here are some PSQL Queries that will let us know some expected results:

Create Aggregate function
# psql

CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);

--------------------------------------------------------------------------------------------------------
The function pg_stat_get_backend_idset provides a convenient way to generate one row for each active server process. For example, to show the PIDs and current queries of all server processes:

# psql -c "SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;"
--------------------------------------------------------------------------------------------------------

Sample query to pull the number of rows, indexes, and some info about those indexes for each table. (Only works on 8.3; ditch the pg_size_pretty if you’re on an earlier version) 

postgres=# 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;

------------------------------------------------------------------------------------------------------

Query to check Database Size, Cache, Hit Success

# psql -c "select a.datname, pg_size_pretty(pg_database_size(a.datid)) as size, cast(blks_hit/(blks_read+blks_hit+0.000001)*100.0 as numeric(5,2)) as cache, cast(xact_commit/(xact_rollback+xact_commit+0.000001)*100.0 as numeric(5,2)) as success from pg_stat_database a order by a.datname"--

--------------------------------------------------------------------------------------------------------
Table & index sizes along which indexes are being scanned and how many tuples are fetched.

postgres=# SELECT
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(t.tablename)) AS table_size,
pg_size_pretty(pg_relation_size(indexrelname)) AS index_size,
CASE WHEN x.is_unique = 1  THEN 'Y'
ELSE 'N'
END AS UNIQUE,
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 indrelid,
max(CAST(indisunique AS integer)) AS is_unique
FROM pg_index
GROUP BY indrelid) x
ON c.oid = x.indrelid
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 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;
----------------------------------------------------------------------------------------------------------
Shared Locks

# psql -c "select pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted, pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30), pg_stat_activity.query_start, age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid)  where pg_locks.pid=pg_stat_activity.procpid order by query_start;"

----------------------------------------------------------------------------------------------------------

Finding unused indexes


#psql -c "SELECT *, pg_size_pretty(pg_relation_size(indexrelname))  FROM pg_stat_all_indexes   WHERE schemaname = 'public'  ORDER BY pg_relation_size(indexrelname) DESC, idx_scan ASC;
select indexrelid::regclass as index, relid::regclass as table from pg_stat_user_indexes JOIN pg_index USING (indexrelid) where idx_scan = 0 and indisunique is false;"

Finding duplicate indexes
# psql  -c "select indrelid::regclass, array_accum(indexrelid::regclass) from pg_index group by indrelid, indkey having count(*) > 1;"

Finding useless indexes
# psql -c "select starelid::regclass, indexrelid::regclass, array_accum(staattnum), relpages, reltuples, array_accum(stadistinct) from pg_index join pg_statistic on (starelid=indrelid and staattnum = ANY(indkey)) join pg_class on (indexrelid=oid) where case when stadistinct < 0 then stadistinct > -.8 else reltuples/stadistinct > .2  end and not (indisunique or indisprimary) and (relpages > 100 or reltuples > 1000) group by starelid, indexrelid, relpages, reltuples order by starelid ;"

-----------------------------------------------------------------------------------------------------------

Count number of current connections:
SELECT COUNT(*) FROM pg_stat_activity;

Number of commits and rollbacks for each database:
SELECT datname, xact_commit, xact_rollback FROM pg_stat_database;


Summaries of number of inserts/updates/deletes:
SELECT SUM(n_tup_ins), SUM(n_tup_upd), SUM(n_tup_del) FROM pg_stat_all_tables;

Summaries of query plans:
SELECT SUM(seq_scan), SUM(seq_tup_read), SUM(idx_scan), SUM(idx_tup_fetch) FROM pg_stat_all_tables;

Summaries of locks held:
SELECT mode, COUNT(mode) FROM pg_locks GROUP BY mode ORDER BY mode;

#############Summary of disk I/O in terms of blocks read:
SELECT SUM(heap_blks_read) FROM pg_statio_user_tables;
SELECT SUM(idx_blks_read) FROM pg_statio_user_tables;
SELECT SUM(toast_blks_read) FROM pg_statio_user_tables;
SELECT SUM(tidx_blks_read) FROM pg_statio_user_tables;
Swap `read' for `hit' to get the number of blocks read from memory.
Caution!
This does not take the operating system's disk cache into account!
pg_database_size(name)
pg_tablespace_size(name)

-----------------------------------------------------------------------------------------------
Check Master-Slave Sync Status in SR Mode

On Master
postgres=# select pg_current_xlog_location();
pg_current_xlog_location
--------------------------
7B/3CB4CBD0
(1 row)

On Slave
postgres=# select pg_last_xlog_replay_location();
pg_last_xlog_replay_location
------------------------------
 7B/3CB4CBD0
(1 row)

4 comments:

  1. Manish, do you have strong understanding of postgresql shared buffers? Just looking to understand it thoroughly.

    ReplyDelete
  2. @sidharth:
    That i know and starts thing about Shared Buffers, is this used for cache and write... mean, in terms of postgresql, it holds a block of memory, to hold data (from Disk to shared buffers) that has to read/write and then again sync back them to disk.

    ReplyDelete
  3. Yes, not sure why many documents say its so hard to set and configure. Do we need an increased level of understanding on how it works? Just looking for some help on how this is configured for enterprise level databases like the one i use.

    ReplyDelete
  4. But i am sure, normal tuning can be done according to your server RAM config and usability. Issue occur when, the DB Server are in high need of tuning. Like your server is having high numbers of reads/writes and experiencing load on server, disk business etc... in this case, SHARED BUFFERS can play a vital role in performance boost. Other tuning are also required with shared buffers, like,

    effective_cache_size
    enable_seqscan
    enable_indexscan
    work_mem
    maintenance_work_mem
    max_connections

    and many.. others depending environment need.

    ReplyDelete