postgresql_cheats.sql

-- PostgreSQL cheat sheet

--postgres is set up to use local.6 which is syslog'd to sflog001
--slow query log is /var/log/localmessages
--config files are always in /data/friend/*.conf

--vacuums are set via cfengine, we use both manual and auto.  vacuums/analyze help with frozen id's being recouped, and thus TX'id's not going over 2b thus causing massing shutdown/reset.  Fix it to exp/imp high TX tables.

--to log into psql:  psql -U postgres -d <DB> (usually friend)

--table size:
select pg_size_pretty(pg_relation_size('accounts'));

--timing:
\timing

--MINUS:
select * from accounts except select * from accounts_bak;
10000000 | 100 |        0 |

Time: 123541.187 ms



-- What indexes are on my table?
select * from pg_indexes where tablename = 'tablename';

-- What triggers are on my table?
select c.relname as "Table", t.tgname as "Trigger Name",
t.tgconstrname as "Constraint Name", t.tgenabled as "Enabled",
t.tgisconstraint as "Is Constraint", cc.relname as "Referenced Table",
p.proname as "Function Name"
from pg_trigger t, pg_class c, pg_class cc, pg_proc p
where t.tgfoid = p.oid and t.tgrelid = c.oid
and t.tgconstrrelid = cc.oid
and c.relname = 'tablename';

-- What constraints are on my table?
select r.relname as "Table", c.conname as "Constraint Name",
contype as "Constraint Type", conkey as "Key Columns",
confkey as "Foreign Columns", consrc as "Source"
from pg_class r, pg_constraint c
where r.oid = c.conrelid
and relname = 'tablename';

testdb=# \i [script name]
friend=# set work_mem=40000;
friend=# show work_mem

--cool, run psql with -E and it spits out the sql it uses for \d \t etc

select procpid,substr(current_query,1,100),query_start::timestamp(0),waiting
from pg_stat_activity
where current_query !~ '.*<IDLE>*'
order by query_start;

--locks:
select pg_class.relname,pg_locks.* from pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation;

select pg_class.relname, substr(s.current_query,0,50), count(*) from pg_class, pg_locks, pg_stat_activity as s
where pg_class.relfilenode=pg_locks.relation
and pg_locks.pid = s.procpid
group by pg_class.relname, substr(s.current_query,0,50)
order by 3;



--total transactions committed = "number of TX end statements.  so I I I commit = 1 tx
--total rolled back in that database = inverse
--total disk blocks read = physical I/O
--total number of buffer hits = buffer hits
--disk_blocks_read+buffer_hits = total logical I/O
--no metric exists for executions
--postgres has no cursor cache at all
--at Hi5 we are not reusing cursor handles, simply we are not naming handles thus no re-use. each exec = parse.

-- some space queries:
select oid, relname, pg_size_pretty(pg_relation_size( oid ) ) from pg_class order by pg_relation_size( oid ) desc limit 30;
select relname, pg_size_pretty(pg_relation_size(oid)) from pg_class where relname like '%bac%';

SELECT
schemaname, tablename, reltuples::bigint, relpages::bigint, otta,
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
relpages::bigint - otta AS wastedpages,
bs*(sml.relpages-otta)::bigint AS wastedbytes,
pg_size_pretty((bs*(relpages-otta))::bigint) AS wastedsize,
iname, ituples::bigint, ipages::bigint, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN pg_size_pretty(0) ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize
FROM (
SELECT
schemaname, tablename, cc.reltuples, 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,
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 (
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, (
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
WHERE sml.relpages - otta > 2 OR ipages - iotta > 4
ORDER BY wastedbytes DESC LIMIT 10;


\pset pager off;
Pager usage is off.

select pg_cancel_backend(pid int)

-- size
-- 
SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS size,
pg_total_relation_size(schemaname||'.'||tablename) AS total_size
FROM pg_tables WHERE schemaname = 'public') AS TABLES
ORDER BY total_size DESC;



SELECT relname, age(relfrozenxid), pg_relation_size(relname) FROM pg_class WHERE relkind = 'r' and relname like '%photo%' order by 2;

select a.relname, age(a.relfrozenxid), pg_relation_size(a.relname) from pg_class a, pg_tables b where a.relname = b.tablename
and b.schemaname = 'public' order by 2;


$>slony w/ perl:
$>cd /home/postgres/slony
$>slonik_init_cluster -c friendsuggestions_1_2.conf | slonik   (if partially completes, you can always drop _replication (or rename) and restart)
$>slonik_create_set -c friendsuggestions_1_2.conf 1 | slonik
$>slon_start -c friendsuggestions_1_2.conf 1
$>slon_start -c friendsuggestions_1_2.conf 2
$>slonik_subscribe_set -c friendsuggestions_1_2.conf 1 2 | slonik
$>ps -ef | grep slon
select * from _replica1_2.sl_status;
select * from _replica2_1.sl_status;


-- killing loads of sessions:
select 'select pg_cancel_backend('||procpid||');'
from pg_stat_activity where waiting = 't' order by query_start;

select 'select pg_cancel_backend('||procpid||');'
from pg_stat_activity
where current_query !~ '.*<IDLE>*'
and query_start::timestamp(0) < current_date-(20/1440);

select 'select pg_cancel_backend('||procpid||');'
from pg_stat_activity where current_query = '<IDLE>' order by query_start;

--size estimates:
select tablename, attname, avg_width from pg_stats where tablename = 'app_invite';

select a.schemaname, tablename, size_pretty, last_vacuum
from
(SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS size,
pg_total_relation_size(schemaname||'.'||tablename) AS total_size
FROM pg_tables) AS TABLES
ORDER BY total_size DESC) a,
(select relname, schemaname, last_vacuum
from pg_stat_user_tables) b
where a.tablename = b.relname
and a.schemaname = b.schemaname
order by 4,3 desc
limit 10;

select tablename from pg_tables where tablename not in (select r.relname
from pg_class r, pg_constraint c
where r.oid = c.conrelid
and c.contype = 'p' and schemaname = 'public')
and schemaname = 'public';

--stuck:
select count(*) from pg_stat_activity where (current_query ilike '%SELECT%' or current_query ilike '%UPDATE%' or current_query ilike '%INSERT%') and query_start < now() - interval '10 minutes';

-- get toast tables
select reltoastrelid::regclass from pg_class where relname ='mytable';


-- remount filesystem with direct I/O options for vxfs
mount -o remount,convosync=direct,mincache=direct /data

-- cache hit ratio
SELECT datname, blks_read, blks_hit,
round(((blks_hit::float+1)/(blks_read+blks_hit+1)*100)::numeric, 2)
AS cachehitratio
FROM pg_stat_database
WHERE datname !~ '^(template(0|1)|postgres)$'
ORDER BY cachehitratio desc;

-- disabling processors in linux:
frutestdb002:~ # echo 0 >> /sys/devices/system/cpu/cpu7/online
frutestdb002:~ # echo 0 >> /sys/devices/system/cpu/cpu6/online
frutestdb002:~ # echo 0 >> /sys/devices/system/cpu/cpu5/online
frutestdb002:~ # echo 0 >> /sys/devices/system/cpu/cpu4/online
5:29 frutestdb002:~ # cat /proc/cpuinfo  | grep processor
processor    : 0
processor    : 1
processor    : 2
processor    : 3

-- probing the pg_buffer_cache
BEGIN;
SET search_path = contrib;

-- Register the function.
CREATE OR REPLACE FUNCTION pg_buffercache_pages()
RETURNS SETOF RECORD
AS '$libdir/pg_buffercache', 'pg_buffercache_pages'
LANGUAGE C;

-- Create a view for convenient access.
CREATE VIEW pg_buffercache AS
	SELECT P.* FROM pg_buffercache_pages() AS P
 	(bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid, 
 	 relblocknumber int8, isdirty bool);
 
-- Don't want these to be available at public.
REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
REVOKE ALL ON pg_buffercache FROM PUBLIC;

COMMIT;


SELECT c.relname, count(*) AS buffers, count(*)*8192 as bytes
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = c.relfilenode AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC LIMIT 10;

-- controlling cache ration on HP cli
hpacucli
ctrl slot=3 modify cacheratio=?

Available options are:
   0% read / 100% write (current value)
   25% read / 75% write (default value)
   50% read / 50% write
   75% read / 25% write
   100% read / 0% write

=> ctrl slot=3 modify cacheratio=50/50

-- size and load report, good for figuring out what should move
select a.schemaname, a.tablename, a.size_pretty, a.total_size_pretty, b.heap_blks_hit, heap_blks_read
from (SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS size,
pg_total_relation_size(schemaname||'.'||tablename) AS total_size
FROM pg_tables WHERE schemaname = 'public') AS TABLES
ORDER BY total_size DESC) a, (select * from pg_statio_user_tables) b
where a.schemaname = b.schemaname
and a.tablename = b.relname order by 4,6 desc;

--slony status
SELECT e.ev_origin AS st_origin, c.con_received AS st_received, e.ev_seqno AS
st_last_event, e.ev_timestamp AS st_last_event_ts, c.con_seqno AS
st_last_received, c.con_timestamp AS st_last_received_ts, ce.ev_timestamp AS
st_last_received_event_ts, e.ev_seqno - c.con_seqno AS st_lag_num_events, now()
- - ce.ev_timestamp::timestamp with time zone AS st_lag_time
  FROM _replication.sl_event e, _replication.sl_confirm c, _replication.sl_event ce
 WHERE e.ev_origin = c.con_origin AND ce.ev_origin = e.ev_origin AND
ce.ev_seqno = c.con_seqno AND ((e.ev_seqnoc.con_origin, c.con_received,
c.con_seqno) IN ( SELECT sl_confirm.con_origin, sl_confirm.con_received,
max(sl_confirm.con_seqno) AS max
          FROM _replication.sl_confirm
         WHERE sl_confirm.con_origin = _replication.getlocalnodeid('_rep'::name)
         GROUP BY sl_confirm.con_origin, sl_confirm.con_received));

-- finding tables that have pour data clustering:
select a.relname, a.idx_scan as fetches_by_index, b.heap_blks_read+b.heap_blks_hit as blocks_read, (b.heap_blks_read+b.heap_blks_hit+1)/(a.idx_scan+1) as ratio, a.seq_scan as seq_scans
from pg_stat_user_tables a, pg_statio_user_tables b where a.relname = b.relname and a.relname not like '%tmp%' order by 4 desc;

-- generating pg_reorg scripts for non-toast tables:
select 'pg_reorg -t public.'||tablename||' -o friendid -e -v -U postgres photos_c1'
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS size,
pg_total_relation_size(schemaname||'.'||tablename) AS total_size
FROM pg_tables, pg_class  WHERE pg_class.relname = pg_tables.tablename 
and schemaname = 'public' and tablename like '%tags_friend%' 
and reltoastrelid = 0) AS TABLES
ORDER BY total_size asc;

SELECT
   tablename,
   pg_size_pretty(pg_total_relation_size(tablename)) AS total_usage,
   pg_size_pretty((pg_total_relation_size(tablename)
       - pg_relation_size(tablename))) AS external_table_usage
   FROM pg_tables
   WHERE schemaname != 'pg_catalog'
       AND schemaname != 'information_schema'
   ORDER BY pg_total_relation_size(tablename) DESC;