Last active
November 13, 2015 19:05
-
-
Save kmacgugan/6f494716d0b5a35d2ff7 to your computer and use it in GitHub Desktop.
postgres size analysis
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--Questions for customer: | |
--- How many client nodes are you running? | |
--- Are they windows, linux or mixed? | |
-- Oldest data in activities | |
select min(recorded_at) from public.activities; | |
--how many rows over the past week per day | |
select count(*), date_part('day', recorded_at) from public.activities where recorded_at > now() - INTERVAL '7 days' group by 2; | |
--how many rows in a month | |
select count(*) from public.activities where recorded_at > now() - INTERVAL '1 month'; | |
--Size of all databases | |
select t1.datname AS db_name, | |
pg_size_pretty(pg_database_size(t1.datname)) as db_size | |
from pg_database t1 | |
order by pg_database_size(t1.datname) desc; | |
-- total size of biggest tables | |
SELECT nspname || '.' || relname AS "relation", | |
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" | |
FROM pg_class C | |
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) | |
WHERE nspname NOT IN ('pg_catalog', 'information_schema') | |
AND C.relkind <> 'i' | |
AND nspname !~ '^pg_toast' | |
ORDER BY pg_total_relation_size(C.oid) DESC | |
LIMIT 20; | |
--Column size of activities table | |
select pg_column_size('payload') as payload, pg_column_size('tags') as tags, pg_column_size('properties') as properties; | |
-- Details of activities table | |
WITH x AS ( | |
SELECT count(*) AS ct | |
,sum(length(t::text)) AS txt_len -- length in characters | |
,'public.activities'::regclass AS tbl | |
FROM public.activities t | |
) | |
, y AS ( | |
SELECT ARRAY [ | |
pg_relation_size(tbl) | |
,pg_relation_size(tbl, 'vm') | |
,pg_relation_size(tbl, 'fsm') | |
,pg_table_size(tbl) | |
,pg_indexes_size(tbl) | |
,pg_total_relation_size(tbl) | |
,txt_len | |
] AS val | |
,ARRAY [ | |
'core_relation_size' | |
,'visibility_map' | |
,'free_space_map' | |
,'table_size_incl_toast' | |
,'indexes_size' | |
,'total_size_incl_toast_and_indexes' | |
,'live_rows_in_text_representation' | |
] AS name | |
FROM x | |
) | |
SELECT unnest(name) AS what | |
,unnest(val) AS bytes | |
,pg_size_pretty(unnest(val)) AS bytes_pretty | |
,unnest(val) / ct AS per_row_bytes | |
FROM x,y | |
UNION ALL | |
SELECT '----------'::text, NULL::int8, '----'::text, NULL::int8 | |
UNION ALL | |
SELECT 'row_count'::text, ct | |
,NULL::text, NULL::bigint FROM x | |
UNION ALL | |
SELECT 'live_tuples'::text, pg_stat_get_live_tuples(tbl) | |
,NULL::text, NULL::bigint FROM x | |
UNION ALL | |
SELECT 'dead_tuples'::text, pg_stat_get_dead_tuples(tbl) | |
,NULL::text, NULL::bigint FROM x; | |
--Bloat (wasted bytes taken up by unavailable rows) | |
SELECT | |
current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/ | |
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat, | |
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes, | |
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 bs*(ipages-iotta) END AS wastedibytes | |
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 AND nn.nspname <> 'information_schema' | |
LEFT JOIN pg_index i ON indrelid = cc.oid | |
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid | |
) AS sml | |
ORDER BY wastedbytes DESC; |
[root@atl1cheflytics01 ~]# sudo /opt/opscode-analytics/embedded/bin/psql -U chef-pgsql -d actions
psql: FATAL: Peer authentication failed for user "chef-pgsql"
[root@atl1cheflytics01 ~]# sudo /opt/opscode-analytics/embedded/bin/psql -U opscode-pgsql -d actions
psql: FATAL: no pg_hba.conf entry for host "[local]", user "opscode-pgsql", database "actions", SSL off
root@atl1cheflytics01 ~]# su - chef-pgsql
-bash-4.1$ /opt/opscode-analytics/embedded/bin/psql -U chef-pgsql -d actions
psql (9.2.8)
Type "help" for help.
actions=# ^D\q
fwiw
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
or for older installs: