Configure Optimizer Service

The Optimizer service manages optimizations of job executions that involve queries to relational sources. Based on the enabled optimizations, the service can tune the SQL to optimize queries for better performance and less data transfer.

Prerequisites

The Optimizer service database must be installed. This database is installed as part of normal install and upgrade operations. For more information, see Install Databases.

Configure Service

You can apply this change through the Admin Settings Page (recommended) or trifacta-conf.json. For more information, see Platform Configuration Methods.

Locate the following configuration:

"optimizer-service.enabled":true,
"optimizer-service.host":"localhost",
"optimizer-service.port":41913,
"optimizer-service.autoRestart":true,

Verify that the enabled parameter is set to true.
Additional configuration settings are described below.

Parameter	Description
enabled	Set this value to `true` to enable the Optimizer service.
host	Host of the Optimizer service. Leave this value as `localhost`.
port	Port number that Optimizer service uses to communicate. Default value is `41922`. This port must be opened on the Trifacta node.
autoRestart	When set to `true`, the Optimizer service automatically restarts and attempts to return to its pre-restart state. This value should be set to `false` for debugging purposes only.

Additional configuration

The following parameters can be modified as needed.

Parameter	Description
optimizer-service.maximumOptimizationTimeInSeconds	Maximum time in seconds that the service is permitted to optimize a job. If this time is exceeded, the service errors out, and the job proceeds without optimization. Default value is `45`. This value may be increased if optimization is desired for any of the following: Very complex recipes Extremely wide datasets Jobs reading from several relational sources and pushing down filters to them
optimizer-service.maximumNumberOfWalksPerBatch	Maximum number of passes the service is permitted to make on a recipe to optimize a job. If this number of passes is exceeded, the job is partially optimized. Default value is `300`. This value may be increased if the full suite of optimizations is desired for complex recipes with more than 100 steps.

Parameter

Description

optimizer-service.maximumOptimizationTimeInSeconds

Maximum time in seconds that the service is permitted to optimize a job. If this time is exceeded, the service errors out, and the job proceeds without optimization. Default value is 45.

This value may be increased if optimization is desired for any of the following:

Very complex recipes
Extremely wide datasets
Jobs reading from several relational sources and pushing down filters to them

optimizer-service.maximumNumberOfWalksPerBatch

Maximum number of passes the service is permitted to make on a recipe to optimize a job. If this number of passes is exceeded, the job is partially optimized. Default value is 300.

This value may be increased if the full suite of optimizations is desired for complex recipes with more than 100 steps.

Use

Enable for workspace users

A workspace administrator must enable optimizations for workspace users.

Steps:

You apply this change through the Workspace Settings Page. For more information, see Platform Configuration Methods.
Locate the following setting and set it to Enabled:
```
Logical and physical optimizations of jobs
```
Users can now optimize jobs at the flow level. See below.

Enable optimizations for individual flows

For an individual flow, you can specify the set of optimizations to apply to the job. In Flow View, select Optimization settings from the flow context menu. For more information, see Flow Optimization Settings Dialog.

Logging

You can configure how logging is managed for the Optimizer service. For more information on configuring logging for the Optimizer service, see Configure Logging for Services.

In this section: