• Vertica DBD 分析优化设计


    DBD = Database Designer,是Vertica数据库优化中最主要的原生工具。

    首先运行admintools工具,按下面步骤依次执行:

    1.选择"6 Configuration Menu"
    2.选择"2 Run Database Designer"
    3."Select a database for design" 选择需要分析的数据库
    4."Enter directory for Database Designer output:" 输入dbd的输出目录
    5."Designer name:" 输入dbd的名字,随意起
    6."Design Type[Comprehensive|Query-specific]" 选择dbd的设计类型,有两个单选项,根据实际需求选择
    7."Select schema(s) for design" 选择需要设计的shema(s)
    8."Design options[Optimize with queries|Update statistics|Deploy design]" 这里有三个复选项,推荐先去掉Deploy design,之后可以手工应用dbd的建议
    9."Enter the full path for queries file" 输入业务常用到的sql查询语句,使得dbd的分析更有针对性
    10."Proposed K-safety value:1" 这里默认即可
    11."Select storage footprint preference[Balanced query/load performance|Query performance(larger footprint)|Load performance(smaller footprint)]" 这里有三个单选项,根据实际需求选择,一般选择第一项,均衡兼顾查询和加载性能
    12."Proceed" 执行dbd
    13."Database Designer started." dbd已经开始,会有类似下面的输出,直到完成dbd

    Database Designer started.
    
    For large databases a design session could take a long time; allow it to complete uninterrupted.
    Use Ctrl+C if you must cancel the session.
    
    Setting up design session...
    
    Examining table data...
    
    Loading queries from '/home/dbadmin/sqltune.sql'.
    Processed 22 SQL statement(s), all accepted and considered in the design.
    No existing projections found.
    
    
    Creating design...
    [100%] Design in progress... Completed of .
    
    
    [100%] Analyzing data statistics... Completed of .
    
    [100%] Design in progress... Completed of .
    
    [100%] Optimizing query performance... Completed of .
    
    [100%] Design in progress... Completed of .
    
    [100%] Optimizing storage footprint... Completed of .
    
    [100%] Internal warning during design... Completed of .
    
    Query optimization results...
    
    Query 1 optimization ratio or status is 1
    Query 2 optimization ratio or status is 1
    Query 3 optimization ratio or status is 1
    Query 4 optimization ratio or status is 1
    Query 5 optimization ratio or status is 1
    Query 6 optimization ratio or status is 1
    Query 7 optimization ratio or status is 1
    Query 8 optimization ratio or status is 1
    Query 9 optimization ratio or status is 1
    Query 10 optimization ratio or status is 1
    Query 11 optimization ratio or status is 1
    Query 12 optimization ratio or status is 1
    Query 13 optimization ratio or status is 1
    Query 14 optimization ratio or status is 1
    Query 15 optimization ratio or status is 1
    Query 16 optimization ratio or status is 1
    Query 17 optimization ratio or status is 1
    Query 18 optimization ratio or status is 1
    Query 19 optimization ratio or status is 1
    Query 20 optimization ratio or status is 1
    Query 21 optimization ratio or status is 1
    Query 22 optimization ratio or status is 1
    
    
    Generating deployment script...
    
    Could not generate default deployment script.
    
    
    The deployment error is Deployment did not complete successfully. ERROR - Insufficient resources to execute plan on pool dbd [Timedout waiting for resource request: waiting for reservation spot]
    
    Design script is located in /home/dbadmin/designer1_design.sql
    Deployment script is located in /home/dbadmin/designer1_deploy.sql
    The design will not be deployed.
    
    The new design was not automatically deployed.
    For manual deployment procedures consult Vertica Administrator's Guide
    
    Database Designer finished.
    Press <Enter> to return to the Administration Tools menu.
    Vertica Analytic Database 6.1.3-7 Administration Tools
  • 相关阅读:
    【转】团队管理
    Oracle 11g中关于数据定义的思考
    【转】InfoQ的Java安全认证机制
    Oracle 11g windows简体中文版安装指南
    【转】InfoQ的集成Java内容仓库和Spring
    Oracle数据库常用操作命令(一)
    常用DQL
    如何处理Oracle客户端查询乱码问题
    Documentum中的TCS与对应权限设置
    【转】Windows系统下的Apache性能优化mpm
  • 原文地址:https://www.cnblogs.com/jyzhao/p/3809118.html
Copyright © 2020-2023  润新知