如何在RedShift / ParAccel中测量磁盘上的表空间
如何在RedShift / ParAccel中测量磁盘上的表空间
提问于 2013-10-22 13:53:41
我在RedShift有一张桌子。如何查看它使用了多少磁盘空间？
回答 4
Stack Overflow用户
回答已采纳
发布于 2013-10-22 22:22:34
使用此演示文稿中的查询：http://www.slideshare.net/AmazonWebServices/amazon-redshift-best-practices
分析群集的磁盘空间使用情况：
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; 分析节点之间的表分布：
select slice, col, num_values, minvalue, maxvalue
from svv_diskusage
where name = '__INSERT__TABLE__NAME__HERE__' and col = 0
order by slice, col;Stack Overflow用户
发布于 2015-07-30 17:50:20
我知道这个问题很老，已经有了一个公认的答案，但我必须指出答案是错误的。查询在那里输出的"mb“实际上是”块的数量“。只有当数据块大小为1MB (默认值)时，答案才是正确的。
如果块大小不同(在我的示例中为256K)，则必须将块的数量乘以其字节大小。我建议对您的查询进行以下更改，我将块的数量乘以块大小(以字节为单位) (262144字节)，然后除以(1024x1024)，以输出以in为单位的总和：
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; Stack Overflow用户
发布于 2014-01-30 18:15:52
正在向上述查询添加所有者和架构筛选器：
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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19509989
复制相关文章
相似问题