Documentation / Query / Background / 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.
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 entryprofileDiff
: an entry where the profiling query is different from the existing autotune tuning entryprofileEmpty
: 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.