0eu6Xn2F

1-- PostgreSQL cheat sheet
2
3--postgres is set up to use local.6 which is syslog'd to sflog001
4--slow query log is /var/log/localmessages
5--config files are always in /data/friend/*.conf
6
7--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.
8
9--to log into psql:  psql -U postgres -d <DB> (usually friend)
10
11--table size:
12select pg_size_pretty(pg_relation_size('accounts'));
13
14--timing:
15\timing
16
17--MINUS:
18select * from accounts except select * from accounts_bak;
1910000000 | 100 |        0 |
20
21Time: 123541.187 ms
22
23
24
25-- What indexes are on my table?
26select * from pg_indexes where tablename = 'tablename';
27
28-- What triggers are on my table?
29select c.relname as "Table", t.tgname as "Trigger Name",
30t.tgconstrname as "Constraint Name", t.tgenabled as "Enabled",
31t.tgisconstraint as "Is Constraint", cc.relname as "Referenced Table",
32p.proname as "Function Name"
33from pg_trigger t, pg_class c, pg_class cc, pg_proc p
34where t.tgfoid = p.oid and t.tgrelid = c.oid
35and t.tgconstrrelid = cc.oid
36and c.relname = 'tablename';
37
38-- What constraints are on my table?
39select r.relname as "Table", c.conname as "Constraint Name",
40contype as "Constraint Type", conkey as "Key Columns",
41confkey as "Foreign Columns", consrc as "Source"
42from pg_class r, pg_constraint c
43where r.oid = c.conrelid
44and relname = 'tablename';
45
46testdb=# \i [script name]
47friend=# set work_mem=40000;
48friend=# show work_mem
49
50--cool, run psql with -E and it spits out the sql it uses for \d \t etc
51
52select procpid,substr(current_query,1,100),query_start::timestamp(0),waiting
53from pg_stat_activity
54where current_query !~ '.*<IDLE>*'
55order by query_start;
56
57--locks:
58select pg_class.relname,pg_locks.* from pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation;
59
60select pg_class.relname, substr(s.current_query,0,50), count(*) from pg_class, pg_locks, pg_stat_activity as s
61where pg_class.relfilenode=pg_locks.relation
62and pg_locks.pid = s.procpid
63group by pg_class.relname, substr(s.current_query,0,50)
64order by 3;
65
66
67
68--total transactions committed = "number of TX end statements.  so I I I commit = 1 tx
69--total rolled back in that database = inverse
70--total disk blocks read = physical I/O
71--total number of buffer hits = buffer hits
72--disk_blocks_read+buffer_hits = total logical I/O
73--no metric exists for executions
74--postgres has no cursor cache at all
75--at Hi5 we are not reusing cursor handles, simply we are not naming handles thus no re-use. each exec = parse.
76
77-- some space queries:
78select oid, relname, pg_size_pretty(pg_relation_size( oid ) ) from pg_class order by pg_relation_size( oid ) desc limit 30;
79select relname, pg_size_pretty(pg_relation_size(oid)) from pg_class where relname like '%bac%';
80
81SELECT
82schemaname, tablename, reltuples::bigint, relpages::bigint, otta,
83ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
84relpages::bigint - otta AS wastedpages,
85bs*(sml.relpages-otta)::bigint AS wastedbytes,
86pg_size_pretty((bs*(relpages-otta))::bigint) AS wastedsize,
87iname, ituples::bigint, ipages::bigint, iotta,
88ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
89CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
90CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
91CASE WHEN ipages < iotta THEN pg_size_pretty(0) ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize
92FROM (
93SELECT
94schemaname, tablename, cc.reltuples, cc.relpages, bs,
95CEIL((cc.reltuples*((datahdr+ma-
96(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
97COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
98COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
99FROM (
100SELECT
101ma,bs,schemaname,tablename,
102(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
103(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
104FROM (
105SELECT
106schemaname, tablename, hdr, ma, bs,
107SUM((1-null_frac)*avg_width) AS datawidth,
108MAX(null_frac) AS maxfracsum,
109hdr+(
110SELECT 1+count(*)/8
111FROM pg_stats s2
112WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
113) AS nullhdr
114FROM pg_stats s, (
115SELECT
116(SELECT current_setting('block_size')::numeric) AS bs,
117CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
118CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
119FROM (SELECT version() AS v) AS foo
120) AS constants
121GROUP BY 1,2,3,4,5
122) AS foo
123) AS rs
124JOIN pg_class cc ON cc.relname = rs.tablename
125JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname
126LEFT JOIN pg_index i ON indrelid = cc.oid
127LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
128) AS sml
129WHERE sml.relpages - otta > 2 OR ipages - iotta > 4
130ORDER BY wastedbytes DESC LIMIT 10;
131
132
133\pset pager off;
134Pager usage is off.
135
136select pg_cancel_backend(pid int)
137
138-- size
139-- 
140SELECT schemaname, tablename,
141pg_size_pretty(size) AS size_pretty,
142pg_size_pretty(total_size) AS total_size_pretty
143FROM (SELECT *,
144pg_relation_size(schemaname||'.'||tablename) AS size,
145pg_total_relation_size(schemaname||'.'||tablename) AS total_size
146FROM pg_tables WHERE schemaname = 'public') AS TABLES
147ORDER BY total_size DESC;
148
149
150
151SELECT relname, age(relfrozenxid), pg_relation_size(relname) FROM pg_class WHERE relkind = 'r' and relname like '%photo%' order by 2;
152
153select a.relname, age(a.relfrozenxid), pg_relation_size(a.relname) from pg_class a, pg_tables b where a.relname = b.tablename
154and b.schemaname = 'public' order by 2;
155
156
157$>slony w/ perl:
158$>cd /home/postgres/slony
159$>slonik_init_cluster -c friendsuggestions_1_2.conf | slonik   (if partially completes, you can always drop _replication (or rename) and restart)
160$>slonik_create_set -c friendsuggestions_1_2.conf 1 | slonik
161$>slon_start -c friendsuggestions_1_2.conf 1
162$>slon_start -c friendsuggestions_1_2.conf 2
163$>slonik_subscribe_set -c friendsuggestions_1_2.conf 1 2 | slonik
164$>ps -ef | grep slon
165select * from _replica1_2.sl_status;
166select * from _replica2_1.sl_status;
167
168
169-- killing loads of sessions:
170select 'select pg_cancel_backend('||procpid||');'
171from pg_stat_activity where waiting = 't' order by query_start;
172
173select 'select pg_cancel_backend('||procpid||');'
174from pg_stat_activity
175where current_query !~ '.*<IDLE>*'
176and query_start::timestamp(0) < current_date-(20/1440);
177
178select 'select pg_cancel_backend('||procpid||');'
179from pg_stat_activity where current_query = '<IDLE>' order by query_start;
180
181--size estimates:
182select tablename, attname, avg_width from pg_stats where tablename = 'app_invite';
183
184select a.schemaname, tablename, size_pretty, last_vacuum
185from
186(SELECT schemaname, tablename,
187pg_size_pretty(size) AS size_pretty,
188pg_size_pretty(total_size) AS total_size_pretty
189FROM (SELECT *,
190pg_relation_size(schemaname||'.'||tablename) AS size,
191pg_total_relation_size(schemaname||'.'||tablename) AS total_size
192FROM pg_tables) AS TABLES
193ORDER BY total_size DESC) a,
194(select relname, schemaname, last_vacuum
195from pg_stat_user_tables) b
196where a.tablename = b.relname
197and a.schemaname = b.schemaname
198order by 4,3 desc
199limit 10;
200
201select tablename from pg_tables where tablename not in (select r.relname
202from pg_class r, pg_constraint c
203where r.oid = c.conrelid
204and c.contype = 'p' and schemaname = 'public')
205and schemaname = 'public';
206
207--stuck:
208select 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';
209
210-- get toast tables
211select reltoastrelid::regclass from pg_class where relname ='mytable';
212
213
214-- remount filesystem with direct I/O options for vxfs
215mount -o remount,convosync=direct,mincache=direct /data
216
217-- cache hit ratio
218SELECT datname, blks_read, blks_hit,
219round(((blks_hit::float+1)/(blks_read+blks_hit+1)*100)::numeric, 2)
220AS cachehitratio
221FROM pg_stat_database
222WHERE datname !~ '^(template(0|1)|postgres)$'
223ORDER BY cachehitratio desc;
224
225-- disabling processors in linux:
226frutestdb002:~ # echo 0 >> /sys/devices/system/cpu/cpu7/online
227frutestdb002:~ # echo 0 >> /sys/devices/system/cpu/cpu6/online
228frutestdb002:~ # echo 0 >> /sys/devices/system/cpu/cpu5/online
229frutestdb002:~ # echo 0 >> /sys/devices/system/cpu/cpu4/online
2305:29 frutestdb002:~ # cat /proc/cpuinfo  | grep processor
231processor    : 0
232processor    : 1
233processor    : 2
234processor    : 3
235
236-- probing the pg_buffer_cache
237BEGIN;
238SET search_path = contrib;
239
240-- Register the function.
241CREATE OR REPLACE FUNCTION pg_buffercache_pages()
242RETURNS SETOF RECORD
243AS '$libdir/pg_buffercache', 'pg_buffercache_pages'
244LANGUAGE C;
245
246-- Create a view for convenient access.
247CREATE VIEW pg_buffercache AS
248	SELECT P.* FROM pg_buffercache_pages() AS P
249 	(bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid, 
250 	 relblocknumber int8, isdirty bool);
251 
252-- Don't want these to be available at public.
253REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
254REVOKE ALL ON pg_buffercache FROM PUBLIC;
255
256COMMIT;
257
258
259SELECT c.relname, count(*) AS buffers, count(*)*8192 as bytes
260FROM pg_buffercache b INNER JOIN pg_class c
261ON b.relfilenode = c.relfilenode AND
262b.reldatabase IN (0, (SELECT oid FROM pg_database
263WHERE datname = current_database()))
264GROUP BY c.relname
265ORDER BY 2 DESC LIMIT 10;
266
267-- controlling cache ration on HP cli
268hpacucli
269ctrl slot=3 modify cacheratio=?
270
271Available options are:
272   0% read / 100% write (current value)
273   25% read / 75% write (default value)
274   50% read / 50% write
275   75% read / 25% write
276   100% read / 0% write
277
278=> ctrl slot=3 modify cacheratio=50/50
279
280-- size and load report, good for figuring out what should move
281select a.schemaname, a.tablename, a.size_pretty, a.total_size_pretty, b.heap_blks_hit, heap_blks_read
282from (SELECT schemaname, tablename,
283pg_size_pretty(size) AS size_pretty,
284pg_size_pretty(total_size) AS total_size_pretty
285FROM (SELECT *,
286pg_relation_size(schemaname||'.'||tablename) AS size,
287pg_total_relation_size(schemaname||'.'||tablename) AS total_size
288FROM pg_tables WHERE schemaname = 'public') AS TABLES
289ORDER BY total_size DESC) a, (select * from pg_statio_user_tables) b
290where a.schemaname = b.schemaname
291and a.tablename = b.relname order by 4,6 desc;
292
293--slony status
294SELECT e.ev_origin AS st_origin, c.con_received AS st_received, e.ev_seqno AS
295st_last_event, e.ev_timestamp AS st_last_event_ts, c.con_seqno AS
296st_last_received, c.con_timestamp AS st_last_received_ts, ce.ev_timestamp AS
297st_last_received_event_ts, e.ev_seqno - c.con_seqno AS st_lag_num_events, now()
298- - ce.ev_timestamp::timestamp with time zone AS st_lag_time
299  FROM _replication.sl_event e, _replication.sl_confirm c, _replication.sl_event ce
300 WHERE e.ev_origin = c.con_origin AND ce.ev_origin = e.ev_origin AND
301ce.ev_seqno = c.con_seqno AND ((e.ev_seqnoc.con_origin, c.con_received,
302c.con_seqno) IN ( SELECT sl_confirm.con_origin, sl_confirm.con_received,
303max(sl_confirm.con_seqno) AS max
304          FROM _replication.sl_confirm
305         WHERE sl_confirm.con_origin = _replication.getlocalnodeid('_rep'::name)
306         GROUP BY sl_confirm.con_origin, sl_confirm.con_received));
307
308-- finding tables that have pour data clustering:
309select 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
310from 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;
311
312-- generating pg_reorg scripts for non-toast tables:
313select 'pg_reorg -t public.'||tablename||' -o friendid -e -v -U postgres photos_c1'
314FROM (SELECT *,
315pg_relation_size(schemaname||'.'||tablename) AS size,
316pg_total_relation_size(schemaname||'.'||tablename) AS total_size
317FROM pg_tables, pg_class  WHERE pg_class.relname = pg_tables.tablename 
318and schemaname = 'public' and tablename like '%tags_friend%' 
319and reltoastrelid = 0) AS TABLES
320ORDER BY total_size asc;
321
322SELECT
323   tablename,
324   pg_size_pretty(pg_total_relation_size(tablename)) AS total_usage,
325   pg_size_pretty((pg_total_relation_size(tablename)
326       - pg_relation_size(tablename))) AS external_table_usage
327   FROM pg_tables
328   WHERE schemaname != 'pg_catalog'
329       AND schemaname != 'information_schema'
330   ORDER BY pg_total_relation_size(tablename) DESC;