Redshift Maintenance scripts

redshift-maintenance

-- Table Sizes & Rows
select
    trim(pgdb.datname) as Database,
    trim(pgn.nspname) as Schema,
    trim(a.name) as Table,
    b.mbytes,
    a.rows
from (
    select db_id, id, name, sum(rows) as rows
    from stv_tbl_perm a
    group by db_id, id, name
) as a
join pg_class as pgc on pgc.oid = a.id
join pg_namespace as pgn on pgn.oid = pgc.relnamespace
join pg_database as pgdb on pgdb.oid = a.db_id
join (
    select tbl, count(*) as mbytes
    from stv_blocklist
    group by tbl
) b on a.id = b.tbl
order by mbytes desc, a.db_id, a.name;

-- Node distribution for a table
select slice, col, num_values, minvalue, maxvalue
from svv_diskusage
where name = '[table_name]' and col = 0
order by slice, col;

-- Switch to high priority super user queue
-- Good when CPU utilization is maxed out
set query_group to 'superuser';
-- eg: cancel 1234;
reset query_group;

-- See the running queries
select pid, trim(user_name), starttime, substring(query,1,20) 
from stv_recents
where status='Running';

-- cancel a running query
cancel [PID];

-- See Load errors
SELECT *
FROM stl_load_errors
ORDER BY starttime DESC LIMIT 10;

-- Vaccumming.. why?
-- When data is inserted into redshift it goes in unsorted at the end of a table. as this data grows query time increases.
-- When data is deleted (or updated) it is marked in a special column. As this data grows query time increases.
-- Vaccumming sorts the unsorted data and cleans the deleted rows.
-- http://docs.aws.amazon.com/redshift/latest/dg/r_VACUUM_command.html

-- increase concurrency/memory devoted to action
set wlm_query_slot_count=4;
vacuum delete only;
set wlm_query_slot_count to 1;

-- see progress of a vacuum
select * from svv_vacuum_progress;

-- see effectiveness of vacuum
-- http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_VACUUM_SUMMARY.html
select * from svv_vacuum_summary order by xid;