amazon web services - que - Cómo medir el espacio de tabla en el disco en RedShift/ParAccel

redshift sql

Question

Tengo una mesa en RedShift. ¿Cómo puedo ver la cantidad de espacio en disco que utiliza?

Answer 1

Agregando propietario y un filtro de esquema a la consulta anterior:

select
 cast(use.usename as varchar(50)) as owner, 
 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
 left join pg_user use on (pgc.relowner = use.usesysid)
 join pg_namespace as pgn on pgn.oid = pgc.relnamespace 
   -- leave out system schemas
   and pgn.nspowner > 1
 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;

Answer 2

Sé que esta pregunta es antigua y ya se aceptó una respuesta, pero debo señalar que la respuesta es incorrecta. Lo que la consulta genera como "mb" es en realidad el "número de bloques". La respuesta sería correcta solo si el tamaño del bloque es de 1 MB (que es el valor predeterminado).

Si el tamaño del bloque es diferente (en mi caso, por ejemplo, 256K), tiene que multiplicar el número de bloques por su tamaño en bytes. Sugiero el siguiente cambio en su consulta donde multiplico la cantidad de bloques por el tamaño de bloque en bytes (262144 bytes) y luego divido por (1024 * 1024) para generar el total en megabytes:

select
    trim(pgdb.datname) as Database,
    trim(pgn.nspname) as Schema,
    trim(a.name) as Table,
    b.mbytes as previous_wrong_value,
    (b.mbytes * 262144)::bigint/(1024*1024) as "Total 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(blocknum) as mbytes
    from stv_blocklist
    group by tbl
) b on a.id = b.tbl
order by mbytes desc, a.db_id, a.name; 

Answer 3

Solo pensé que me expandiría en esto ya que me enfrento a un problema de distribución desigual. He agregado algunos enlaces y campos para permitir el análisis del espacio por nodo y sector. También se agregan los valores máximos / mínimos y el número de valores por segmento para la columna 0.

select
 cast(use.usename as varchar(50)) as owner, 
 trim(pgdb.datname) as Database,
 trim(pgn.nspname) as Schema,
 trim(a.name) as Table,
 a.node,
 a.slice,
 b.mbytes,
 a.rows,
 a.num_values,
 a.minvalue,
 a.maxvalue
from 
 (select 
   a.db_id,
   a.id, 
   s.node,
   s.slice,
   a.name,
   d.num_values,
   d.minvalue,
   d.maxvalue,
   sum(rows) as rows
  from stv_tbl_perm a
  inner join stv_slices s on a.slice = s.slice
  inner join (
    select tbl, slice, sum(num_values) as num_values, min(minvalue) as minvalue, max(maxvalue) as maxvalue
    from svv_diskusage
    where col = 0
    group by 1, 2) d on a.id = d.tbl and a.slice = d.slice
  group by 1, 2, 3, 4, 5, 6, 7, 8
 ) as a
 join pg_class as pgc on pgc.oid = a.id
 left join pg_user use on (pgc.relowner = use.usesysid)
 join pg_namespace as pgn on pgn.oid = pgc.relnamespace 
   -- leave out system schemas
   and pgn.nspowner > 1
 join pg_database as pgdb on pgdb.oid = a.db_id
 join 
  (select 
    tbl,
    slice,
    count(*) as mbytes
   from stv_blocklist
   group by tbl, slice
 ) b on a.id = b.tbl
    and a.slice = b.slice
order by mbytes desc, a.db_id, a.name, a.node;

Answer 4

Utilice las consultas de esta presentación: http://www.slideshare.net/AmazonWebServices/amazon-redshift-best-practices

Analizar el uso del espacio en disco para el clúster:

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; 

Analizar la distribución de tablas entre nodos:

select slice, col, num_values, minvalue, maxvalue
from svv_diskusage
where name = ''__INSERT__TABLE__NAME__HERE__'' and col = 0
order by slice, col;