首页 > 代码库 > Vertica DBD 分析优化设计
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
Vertica DBD 分析优化设计