This is part 3 of a series on Auto-Analyze.

BigInsights Big SQL Auto-Analyze can use customized schedules for running Analyze. This allows balancing of the Auto-Analyze schedule with other Big SQL workloads.

It is best to run Auto-Analyze on a schedule, with concurrency settings that will not impact the resources needed to run other Big SQL workloads. If you have a window of time when more Big SQL resources can be devoted to running Analyze, then you can set a schedule and higher concurrency value to run more Auto-Analyze tasks during that period of time.

The default schedule checks the Auto-Analyze queue every 10 minutes for tables that need to be Analyzed. There is a default setting of 1 concurrent Auto-Analyze task. These settings together result in only one Auto-Analyze task being run in 10 minute intervals.

The query below will show the defaults set during install. The task to check the Auto-Analyze queue is named BIGSQL_CHECK_ANALYZE and is run with a cron schedule of ‘0,10,20,30,40,50 * * * *’.

SELECT NAME, SCHEDULE, PROCEDURE_INPUT FROM SYSTOOLS.ADMIN_TASK_LIST WHERE PROCEDURE_SCHEMA='SYSHADOOP' AND PROCEDURE_NAME='BIGSQL_CHECK_ANALYZE';

+----------------------+--------------------------+-----------------+
| NAME                 | SCHEDULE                 | PROCEDURE_INPUT |
+----------------------+--------------------------+-----------------+
| BIGSQL_CHECK_ANALYZE | 0,10,20,30,40,50 * * * * | VALUES(0)       |
+----------------------+--------------------------+-----------------+

The task’s input value of 0 means to use the value of property biginsights.stats.auto.analyze.concurrent.max as the number of concurrent Auto-Analyze tasks. An input value > 0 will override the biginsights.stats.auto.analyze.concurrent.max property value.

The default task was created using this statement below. Note the parameter values for NAME , SCHEDULE , and PROCEDURE_INPUT. The SCHEDULE is in UNIX cron format.

CALL SYSPROC.ADMIN_TASK_ADD(
  'BIGSQL_CHECK_ANALYZE',                      -- NAME
  NULL, NULL, NULL, 
  '0,10,20,30,40,50 * * * *',                  -- SCHEDULE
  'SYSHADOOP','BIGSQL_CHECK_ANALYZE',          -- PROCEDURE_SCHEMA,PROCEDURE_NAME
  'VALUES(0)',                                 -- PROCEDURE_INPUT
  NULL,NULL);

The default settings may not be enough to satisfy all the Auto-Analyze that is needed for your Big SQL tables. In this case you will want to identify time periods when more Auto-Analyze can be run and adjust the Auto-Analyze schedule and concurrent Analyze settings. Multiple tasks can be created with different schedules to accomplish this.

For example:

1.Remove the default Auto-Analyze check task

CALL SYSPROC.ADMIN_TASK_REMOVE('BIGSQL_CHECK_ANALYZE',NULL);

2. Add these 2 Auto-Analyze check tasks without overlapping schedules. The task names must each be unique.

High Analyze time period: Every day Midnight to 5 am execute every minute. Run up to 5 concurrent Auto-Analyze tasks.

CALL SYSPROC.ADMIN_TASK_ADD(
  'BIGSQL_CHECK_ANALYZE High Time',        -- NAME
  NULL, NULL, NULL, 
  '* 0-4 * * *',                           -- SCHEDULE
  'SYSHADOOP','BIGSQL_CHECK_ANALYZE',      -- PROCEDURE_SCHEMA,PROCEDURE_NAME
  'VALUES(5)',                             -- PROCEDURE_INPUT
  NULL,NULL);

Low Analyze time period: Every day 5 am to Midnight execute every 10 minutes. Run 1 Auto-Analyze task at a time.

CALL SYSPROC.ADMIN_TASK_ADD(
  'BIGSQL_CHECK_ANALYZE Low Time',         -- NAME
  NULL, NULL, NULL, 
  '0,10,20,30,40,50 5-23 * * *',           -- SCHEDULE
  'SYSHADOOP','BIGSQL_CHECK_ANALYZE',      -- PROCEDURE_SCHEMA,PROCEDURE_NAME
  'VALUES(1)',                             -- PROCEDURE_INPUT
  NULL,NULL);

Big SQL service does not need to be restarted for the new schedules to take effect.

This is now the new schedule that will be used for running Auto-Analyze.

SELECT NAME, SCHEDULE, PROCEDURE_INPUT FROM SYSTOOLS.ADMIN_TASK_LIST WHERE PROCEDURE_SCHEMA='SYSHADOOP' AND PROCEDURE_NAME='BIGSQL_CHECK_ANALYZE';

+--------------------------------+-----------------------------+-----------------+
| NAME                           | SCHEDULE                    | PROCEDURE_INPUT |
+--------------------------------+-----------------------------+-----------------+
| BIGSQL_CHECK_ANALYZE High Time | * 0-4 * * *                 | VALUES(5)       |
| BIGSQL_CHECK_ANALYZE Low Time  | 0,10,20,30,40,50 5-23 * * * | VALUES(1)       |
+--------------------------------+-----------------------------+-----------------+

More information about adding and modifying ATS tasks.

Previous Big SQL Auto-Analyze blogs:

Faster BigInsights Big SQL queries with Auto-Analyze
Big SQL Auto-Analyze Customization

1 comment on"Big SQL Auto-Analyze Scheduling"

  1. […] Faster BigInsights Big SQL queries with Auto-AnalyzeBig SQL Auto-Analyze CustomizationBig SQL Auto-Analyze Scheduling […]

Join The Discussion

Your email address will not be published. Required fields are marked *