The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Tuning plan
Hi Experts,
What is the tuning plan you follow in Vertica for fine tuning queries or database?
0
Comments
-if yes use the admintools DBD with the incremental sql query feed.
- once you identify the tables with missing encoding run the
- carefull with this running it on big tables ! might take time.
- see the output and work around it based on the tunning_cost HIGH to LOW
- vertica comes with native load balancer (Round Robin) and also you need to setup you clients(obdc/jdbc) for that
use this query
Note:
- there is a catck here, running analyze on the table even if it was never used the table will be marked as used , which invalidades the actuall look for unused projecitons
.
- there are many more ideas of where to start ! but i guess tihs is the basic
hope this was helpful !
EXCELLENT , Thanks for your reply :-) :-)
The catch you mentioned: if the unused projections are identified prior analyzing statistics will help us to find the actual unused projections. What do u think >?
Hi ,
Below some highlights on how to find bottlenecks in querys :
The idea is to correlate the output you get in the explain plan with the output of query profiling .
As you can see in the attach explain plan output we have 4 different steps , each step do some execution.
Now in order to know the execution time of each step , you need to run profile to same query and correlate its output with the step numbers you have on the explain plan .
The data that was generated by the profile command include useful execution statistics , one of them is execution time ('clock time (us)) .
Using this approch you can know for sure which step in the plan take most of the time to execute .
See some simple example :
Eg: Explain plan
explain select * from tables;
Access Path:
+-STORAGE ACCESS for t [Cost: 18K, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
| Projection: v_internal.vs_tables_view_p
| Materialize: t.table_schema_id, t.table_schema, t.table_id, t.table_name, t.owner_id, t.owner_name, t.is_temp_table, t.has_aggregate_projection, t.is_system_table, t.force_outer, t.flextable_format, t.system_table_creator, t.partition_expression, t.create_time, t.table_definition, t.recover_priority
| Execute on: Query Initiator
------------------------------
-----------------------------------------------
PLAN: BASE QUERY PLAN (GraphViz Format)
-----------------------------------------------
digraph G {
graph [rankdir=BT, label = "BASE QUERY PLAN\nQuery: explain select * from tables;\n\nAll Nodes Vector: \n\n node[0]=v_opsadb_node0002 (initiator) Up\n node[1]=v_opsadb_node0001 (executor) Up\n node[2]=v_opsadb_node0003 (executor) Up\n", labelloc=t, labeljust=l ordering=out]
0[label = "Root \nOutBlk=[UncTuple(16)]", color = "green", shape = "house"];
1[label = "NewEENode \nOutBlk=[UncTuple(16)]", color = "green", shape = "box"];
2[label = "StorageUnionStep: vs_tables_view_p\nUnc: Integer(8)\nUnc: Varchar(128)\nUnc: Integer(8)\nUnc: Varchar(128)\nUnc: Integer(8)\nUnc: Varchar(128)\nUnc: Boolean(1)\nUnc: Boolean(1)\nUnc: Integer(8)\nUnc: Boolean(1)\nUnc: Boolean(1)\nUnc: Varchar(128)\nUnc: Varchar(8192)\nUnc: TimestampTz(8)\nUnc: Varchar(8192)\nUnc: Integer(8)", color = "purple", shape = "box"];
3[label = "ExprEval: \n t.table_schema_id\n t.table_schema\n t.table_id\n t.table_name\n t.owner_id\n t.owner_name\n t.is_temp_table\n t.is_system_table\n t.force_outer\n (t.flextable_format \<\> \'\')\n t.has_aggregate_projection\n t.system_table_creator\n t.partition_expression\n t.create_time\n t.table_definition\n t.recover_priority\nUnc: Integer(8)\nUnc: Varchar(128)\nUnc: Integer(8)\nUnc: Varchar(128)\nUnc: Integer(8)\nUnc: Varchar(128)\nUnc: Boolean(1)\nUnc: Boolean(1)\nUnc: Integer(8)\nUnc: Boolean(1)\nUnc: Boolean(1)\nUnc: Varchar(128)\nUnc: Varchar(8192)\nUnc: TimestampTz(8)\nUnc: Varchar(8192)\nUnc: Integer(8)", color = "brown", shape = "box"];
4[label = "ScanStep: vs_tables_view_p\ntable_schema_id\ntable_schema\ntable_id\ntable_name\nowner_id\nowner_name\nis_temp_table\nhas_aggregate_projection\nis_system_table\nforce_outer\nflextable_format\nsystem_table_creator\npartition_expression\ncreate_time\ntable_definition\nrecover_priority\nUnc: Integer(8)\nUnc: Varchar(128)\nUnc: Integer(8)\nUnc: Varchar(128)\nUnc: Integer(8)\nUnc: Varchar(128)\nUnc: Boolean(1)\nUnc: Boolean(1)\nUnc: Boolean(1)\nUnc: Integer(8)\nUnc: Varchar(128)\nUnc: Varchar(128)\nUnc: Varchar(8192)\nUnc: TimestampTz(8)\nUnc: Varchar(8192)\nUnc: Integer(8)", color = "brown", shape = "box"];
1->0 [label = "V[0] C=16",color = "black",style="bold", arrowtail="inv"];
2->1 [label = "0",color = "blue"];
3->2 [label = "0",color = "blue"];
4->3 [label = "0",color = "blue"];
}
(29 rows)
Eg: query profiling
dbadmin=> \o /tmp/a.a
dbadmin=> profile select * from tables;
NOTICE 4788: Statement is being profiled
HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=49539596043655460 and statement_id=6;
NOTICE 3557: Initiator memory for query: [on pool sysquery: 5396 KB, minimum: 5396 KB]
NOTICE 5077: Total memory required by query: [5396 KB]
The column which you need to use for correlation is -> operator_id.
Select node_name,operator_name,operator_id ,counter_value from v_monitor.execution_engine_profiles where transaction_id=49539596043655460 and statement_id=6 and counter_name='clock time (us)';
I hope you will find it useful
Thanks