菜单
登录注册
欢 迎
登录
自动登录
忘记密码?
新朋友
注册
注册
老朋友
登录
笔记内容为空!
TA的笔记树 >>
RedShift常用使用
数据库
## 查看sql执行情况,扫描数据量等 ``` select * from svl_query_metrics where query = 41926328 select * from SVCS_S3QUERY_SUMMARY where query=1415 select query, datediff(seconds, starttime, endtime), starttime, endtime, trim(querytxt) as sqlquery from stl_query where starttime >= '2021-04-25 00:00' and endtime < '2021-04-26 00:00' and sqlquery like '%user_event_%95616%' order by sqlquery,date_diff desc; ``` ## 查看执行日志,如是否使用缓存 ``` select * from SVL_QLOG where query = 41926328 ``` ## 查看仓库表大小 ``` 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 1, 2, 4 desc; ``` ## 查看压缩建议 ``` analyze compression b_user_event_all_96328; 修改字段压缩类型: https://aws.amazon.com/cn/about-aws/whats-new/2020/10/amazon-redshift-supports-modifying-column-comprression-encodings-to-optimize-storage-utilization-query-performance/ ``` ## 查看表信息 ``` select * from svv_table_info where "table"='b_user_event_all_96328'; select * from pg_table_def where tablename='b_user_event_all_96328'; ``` ## 删除表数据 ### 1. 执行删除命令 ``` sql: delete from tablename where xx=xxx; ``` 此时不会释放磁盘 ### 2. 释放磁盘 ``` 参考:https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_VACUUM_command.html sql: -- 排序表 + 释放磁盘(见下方:unsorted,vacuum_sort_benefit) vacuum 表名 to 100 percent; -- 仅释放磁盘 vacuum delete only b_user_event_all_42509 to 100 percent; ``` ``` [to 100 percent] 若不加,缺省比例为:95% 如果:(tbl_rows - estimated_visible_rows) / tbl_rows < 5%,这个步骤就会跳过 ``` ``` 查询表信息: select database, schema, table_id, "table", sortkey_num, size, tbl_rows, estimated_visible_rows, unsorted,vacuum_sort_benefit from svv_table_info where "table" like '%41397%'; 参考文档: https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_SVV_TABLE_INFO.html https://docs.amazonaws.cn/redshift/latest/dg/t_Reclaiming_storage_space202.html ``` #### unsorted, vacuum_sort_benefit 表中排序影响 ``` table | unsorted | vacuum_sort_benefit -------+----------+--------------------- sales | 85.71 | 5.00 event | 45.24 | 67.00 对于表“sales”,即使该表的物理未排序项约为 86%,其对查询性能的影响也仅为 5%。这可能是因为查询只访问表的一小部分内容,也可能是因为访问表的查询几近于无。对于表“event”,该表的物理未排序项约为 45%。不过,67% 的查询性能影响表明查询访问了表的更大部分内容,或者访问表的查询的数量很多。表“event”可能会从运行 VACUUM SORT 中受益。 https://docs.amazonaws.cn/redshift/latest/dg/t_Reclaiming_storage_space202.html ```
vanki
再相见,会是怎样的世界?
浏览:
2736
创建:
2020-11-23 18:03:27
更新:
2021-07-14 15:07:16
TA的最新笔记
spring-boot配置redis多数据源
linux源修改(阿里)
python安装postgresql依赖
arthas使用
java基于spring的NamedParameterJdbcTemplate封装的获取sql工具类
Impala添加负载
S3常用使用
redis常用操作
hdfs相关命令
crontab使用
TA的最热笔记
java异步http请求工具类(org.asynchttpclient)
iTerm2主题配置与常用技巧
java基于spring.redisTemplate实现分布式锁工具类
Kotlin + SpringBoot + JPA(Hibernate) + Repository自定义方法
IDEA汉化
Sequel Pro连接mysql8打开数据库报错
centos-Hadoop2.7.3完全分布式搭建(HA)
SpringBoot上传文件报错(The temporary upload location [..] is not valid)
mac常用软件
kotlin对象属性值拷贝工具类