Automatic Tuning

Overview

AutoTune is an Ebean feature that can automatically tune ORM queries based on profiling the application. The profiling determines which part of the object graph are used by the application and provides an optimised query that can be used to tune the select() and fetch() part of the ORM query.

AutoTune

Automatic query tuning using profiling

Origin point

ORM queries have a origin point which have a hash key like wpbnw.BQejAt.Bzpaqe which is made up of 3 parts - the query bean type and query type, the immediate method executing the query and the call stack.

The origin hash includes the call stack so that a single query can be tuned for different use cases. For example, a find customer by Id query can be called by 3 different call stacks and tuned for each case so one might fetch only the customer name, the second use case might fetch some customer details and the third use case might fetch some customer details along with the customer shipping address and customer billing address.

In this way AutoTune can tune a single query for multiple use cases which is not easily done with manual explicit query tuning.

Profiling

When profiling is tuned on it will collect object graph usage (which paths/properties are used by the application) and relate the object graph usage back to the origin point. The merging of the paths/properties used provides the tuned query which is effectively the select and fetch clauses.

The profiling settings are configured by AutoTuneConfig but the default settings are expected to suite most cases and you can typically just turn profiling on with the default settings.

profilingBase = 5 : This means the first 5 queries for an origin point are profiled
profilingRate = 0.01 : After the first 5 queries for an origin point 1% of queries are profiled
garbageCollectionWait = 100 : On shutdown trigger GC and wait 100 milliseconds to collect profiling info
skipCollectionOnShutdown = false : Set this to true to skip the GC and wait on shutdown
profilingFile = "ebean-profiling" : This is the file name for the profiling output

Turn profiling on

ebean.autotune.profiling=true

ServerConfig serverConfig = ...

// turn on profiling
serverConfig.getAutoTuneConfig().setProfiling(true);

Profiling file: ebean-profiling.xml

If profiling has been turned on then on shutdown the collected profiling is output to the profiling file. The profiling file contains 3 sections which are:

profileNew : new profiling entries which no prior autotune tuning entry
profileDiff : an entry where the profiling query is different from the existing autotune tuning entry
profileEmpty : the origin key of an existing autotune tuning entry where no profiling was collected for

profileNew

Typically you can transfer profileNew entries from profiling into the autotune file. They represent new entries either due to new code/queries or because a origin point hash code has changed.

profileDiff

These entries indicate that when comparing the profiling query against an existing tuning query there is a difference. For example, a code change such that an additional path/property is now used.

For profileDiff entries the detail contains the query suggested by profiling and the original contains the existing tuned query.

Example

In the example below the diff entries shows us that for this origin point the application now additionally uses the order.shipDate and customer.name

<profileDiff>
  <origin key="h_zOh.wpbnw.DJ7cI3" beanType="org.example.domain.Order"
          detail="select (shipDate,orderDate)  fetch customer (id,name) "
          original="select (orderDate)  fetch customer (id) ">
    <callStack>org.example.domain.finder.OrderFinder.byStatus(OrderFinder.java:38)
      org.example.domain.finder.OrderFinderTest.testOther(OrderFinderTest.java:51)
      sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    </callStack>
  </origin>
</profileDiff>

profileEmpty

The profileEmpty entries are the tuning origin points that had no profiling collected. The profile empty entries will appear if the the associated part of the application was not run or if the associated hash is no longer valid as the code has been changed/deleted.

Example

<profileEmpty>
  <origin key="wpbnw.BQejAr.Bzpaqe"/>
  <origin key="wpbnw.BQejAr.CEn3iT"/>
</profileEmpty>

Tuning

When tuning is turned on then on Ebean startup the tuning file is read loading the tuning query and it's associated origin key.

ORM queries that are tunable take their origin key and lookup an associated tuning query. If there is a matching tuning query for the origin key then the query tuning is applied to the query.

select() / fetch()

This query tuning effectively applies the select/fetch clause from the tuning query to the query. If the query already has some fetch clauses that are not in the tuning query these currently are not removed.

JPA FetchGroups

If you are familiar with JPA/JDO FetchGroups then you can roughly equate this query tuning to applying a FetchGroup. However, it should be noted that Ebean will never generate a SQL cartesian product, always honour firstRows/maxRows and has no maximum depth limit (no maxDepth) - Ebean's select/fetch are bulletproof and there is no hidden limitations / traps / gotchas.

Turn tuning on

ebean.autotune.queryTuning=true

# optionally specify the location of ebean-autotune.xml
#ebean.autotune.queryTuningFile=ebean-autotune.xml

ServerConfig serverConfig = ...

AutoTuneConfig autoTuneConfig = serverConfig.getAutoTuneConfig();

// turn on query tuning
autoTuneConfig.setQueryTuning(true);

// Optional: specify full path to ebean-autotune.xml
// autoTuneConfig.setQueryTuningFile(" ...some file path... ");

Tuning file: ebean-autotune.xml

The ebean-autotune.xml tuning file contains a list of origins and their tuning query.

Example

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<autotune xmlns="http://ebean-orm.github.io/xml/ns/autotune">
  <origin key="wpbnw.BQejAr.Bzpaqe" beanType="org.example.domain.Order"
          detail="select (orderDate,shipDate,status)  fetch details (id,orderQty)"
          original="select ">
    <callStack>org.example.domain.finder.OrderFinder.byStatus(OrderFinder.java:38)
      org.example.domain.finder.OrderFinderTest.test(OrderFinderTest.java:20)
      sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    </callStack>
  </origin>
  <origin key="wpbnw.BQejAr.CEn3iT" beanType="org.example.domain.Order"
          detail="select (orderDate,shipDate)  fetch customer (id,name) "
          original="select ">
    <callStack>org.example.domain.finder.OrderFinder.byStatus(OrderFinder.java:38)
      org.example.domain.finder.OrderFinderTest.testOther(OrderFinderTest.java:51)
      sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    </callStack>
  </origin>
</autotune>

Populating ebean-autotune.xml

At the moment you populate ebean-autotune.xml manually via cut-n-paste from the profiling file but an option for merging the profiling into the tuning file on shutdown will be investigated.