首页 > 代码库 > 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 1Query 2 optimization ratio or status is 1Query 3 optimization ratio or status is 1Query 4 optimization ratio or status is 1Query 5 optimization ratio or status is 1Query 6 optimization ratio or status is 1Query 7 optimization ratio or status is 1Query 8 optimization ratio or status is 1Query 9 optimization ratio or status is 1Query 10 optimization ratio or status is 1Query 11 optimization ratio or status is 1Query 12 optimization ratio or status is 1Query 13 optimization ratio or status is 1Query 14 optimization ratio or status is 1Query 15 optimization ratio or status is 1Query 16 optimization ratio or status is 1Query 17 optimization ratio or status is 1Query 18 optimization ratio or status is 1Query 19 optimization ratio or status is 1Query 20 optimization ratio or status is 1Query 21 optimization ratio or status is 1Query 22 optimization ratio or status is 1Generating 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.sqlDeployment script is located in /home/dbadmin/designer1_deploy.sqlThe design will not be deployed.The new design was not automatically deployed.For manual deployment procedures consult Vertica Administrator‘s GuideDatabase Designer finished.Press <Enter> to return to the Administration Tools menu.Vertica Analytic Database 6.1.3-7 Administration Tools
Vertica DBD 分析优化设计