Let's talk about the Workload Analyzer!

  ( observation_type,  tuning_type,  [param1=value, …] ) as <SQL statement>;

Follow the SELECT order in the example in the blog. It more or less matches to what is in tuning_recommendation_details.

The observation_type and tuning_type are arbitrary names you assign. They are intended to be categories. You can run workload analyzer against specific categories if you want to. But unless you had hundreds of rules, there'd be little reason to do that.

Parameters are optional, and they only support integers. So, something like db_name='VMartDB' as a a parameter choice would not be supported.  Percentages have to be stored as whole integers as well. Just convert them in the SQL query. 

Additional commands include the ability to disable rules, and alter parameters. The set param commands only work if the rule has parameters. Not all rules do.

ALTER TUNING RULE r1 SET param1 = new_value, …;

ALTER TUNING RULE r1 DISABLE/ENABLE;

DROP TUNING RULE r1;

There's no way to see the actual queries used for the rules in Vertica, but you can list the rules by querying vs_tuning_rules.  All the available parameters can be found in vs_tuning_rule_parameters.

It's kind of a chicken and egg problem.  Not all rules will have parameters, but if they do, you're probably referencing the value of the parameter in the syntax to check to see that your percentage (or whatever) is greater than the parameter definition. The syntax of the create tuning rule is a SELECT statement. It's simply a query that runs some kind of rule looking for a problem. If it returns a record, you've found a problem - otherwise WLA assumes everything is OK.

To test it, I run just the query itself - without the create tuning rule syntax around it. Once I have the rule working as I want it, then I add the create tuning rule syntax. If it includes a parameter, the entry in vs_tuning_rule_parameters will be created automatically. Once that happens, then I could modify the query in the tuning rule to reference the entry in that table to get the value of my parameter with an additional join, or (more likely) a subquery referencing that table.  Obviously, that subquery won't work if there's nothing in the vs_Tuning_rule_parameters table, which is why referencing it is often the last step. 

You can tweak the query by dropping and recreating the rule until you get it just right. I recommend saving it in a file that you can easily reload just for this purpose. 

Hope that helps!

Hi Curtis ,

I have created some Tuning Rules. Now I am interested in knowing when does this rule internally run in Vertica.

I know its triggered by SELECT ANALYSZE_WORKLOAD('') which regularly once a day. But what is the time when this statement runs.

Few questions based on this:

1. When does SELECT ANALYZE_WORKLOAD run internally

2. How to check the above

3. How to change the schedule of ANALYZE_WORLOAD to run multiple times (not using cron)

4. if tuning rule is set , the last observation_time in tuning_recommendation is when the ANALYZE_WORKLOAD ran last and triggered this ?

Hi Navin, the WLA runs once per day.  The actual time it runs isn't documented anywhere, and unfortunately it's not configurable. It's also not configurable to run multiple times per day (but there shouldn't be any great need to do that, fortunately).

Yes, I believe last_observation_time is a reflection of when the rule was triggered.  You could create a very simple rule just to get the current time if you wanted to figure out when it ran in your system. It might vary by system.