significant performance decrease

we are using objected in embedded mode (on OS X and WINDOWS). The database increased now to around 400000 entities of one class (database file size around 1.5 GByte). The response time of standard queries increased with this database size to an unacceptable rate (several seconds compared to around 30% when the number of entities was just half of the current).

We tried to optimize the cache settings, current objectdb.conf is:

<!-- ObjectDB Configuration -->

<objectdb>

<general>
  <temp path="$temp" threshold="64mb" />
  <network inactivity-timeout="0" />
  <url-history size="50" user="true" password="true" />
  <log path="$objectdb/log/" max="8mb" stdout="false" stderr="false" />
  <log-archive path="$objectdb/log/archive/" retain="90" />
  <logger name="*" level="info" />
</general>

<database>
  <size initial="256kb" resize="256kb" page="2kb" />
  <recovery enabled="true" sync="false" path="." max="128mb" />
  <recording enabled="false" sync="false" path="." mode="write" />
  <locking version-check="true" />
  <processing cache="2048mb" max-threads="10"  synchronized="false"/>
  <query-cache results="1024mb" programs="500" />
  <extensions drop="temp,tmp" />
  <activation code="****-****-****-****-****" />
</database>

<entities>
  <enhancement agent="true" reflection="warning" />
  <cache ref="weak" level2="4096mb" />
  <persist serialization="false" />
  <cascade-persist always="auto" on-persist="false" on-commit="true" />
  <dirty-tracking arrays="false" />
</entities>

<schema>
</schema>

<server>
  <connection port="6136" max="100" />
  <data path="$objectdb/db" />
  <!--
  <replication url="objectdb://localhost/test.odb;user=admin;password=admin" />
  -->
</server>

<users>
  <user username="admin" password="admin">
   <dir path="/" permissions="access,modify,create,delete" />
  </user>
  <user username="$default" password="$$$###">
   <dir path="/$user/" permissions="access,modify,create,delete">
    <quota directories="5" files="20" disk-space="5mb" />
   </dir>
  </user>
  <user username="user1" password="user1" />
</users>

<ssl enabled="false">
  <server-keystore path="$objectdb/ssl/server-kstore" password="pwd" />
  <client-truststore path="$objectdb/ssl/client-tstore" password="pwd" />
</ssl>

</objectdb>

Now the question is, are there settings to change the caching/performance, what can we do ?

More information is needed.

Are you using indexes? Queries become slower with larger databases if indexes are not defined, or not defined properly.

Please provide more details about the query, relevant entity classes, indexes, database size, and query execution time.

Object class is defined like this (indexes are defined):

@Entity
public class ObjectNode implements Serializable {
    private static final long serialVersionUID = 1L; 

    @Id
    public String uUid;

    public Integer associatedFileCurrentVersion;
    public Date associatedFileLastModification;

    @Index public String classIdentifier;
    public Date created;
    // HS-778
    public Map<String, String> currentJournalData = new HashMap<String,String>();
    public String customIcon;

    @Index public String fileURL;
    public String foreignUuid;

    @Index public String identCode;
    public String internalReference;
    // HS-2024
    //@Index
    public Date modified;

    @Index public String nodePath;
    public Integer nodeToProcess;

    @Index public String objectName;

    @OneToOne public ObjectType objectType;

    @Index public Integer state;
    public Map<String, String> systemDictionary = new HashMap<String,String>();

    @Index public Integer type;
    public Double usedCustomObjectTypeVersion;
    public Double usedStandardObjectTypeVersion;
    public Double usedSolutionObjectTypeVersion;

    @OneToMany(fetch=FetchType.LAZY)
    public List<Action> actions = new ArrayList<Action>();

    @OneToMany(fetch=FetchType.LAZY)
    public List<ObjectNode> childNodes = new ArrayList<ObjectNode>();
 
    @OneToMany(fetch=FetchType.LAZY)
    public List<ObjectNode> container = new ArrayList<ObjectNode>();

    @OneToOne(fetch=FetchType.LAZY)
    public ObjectNode currentLocation;

    @OneToOne(fetch=FetchType.LAZY)
    public ObjectNode currentOperation;

    @OneToMany(fetch=FetchType.LAZY)
    public List<ObjectNode> isCurrentLocation = new ArrayList<ObjectNode>();

    // HS-685
    @OneToMany(fetch=FetchType.LAZY)
    public List<ObjectNode> isCurrentOperation = new ArrayList<ObjectNode>();
    @OneToMany(fetch=FetchType.LAZY)
    public List<ObjectNode> isNextOperations = new ArrayList<ObjectNode>();

    @OneToOne(fetch=FetchType.LAZY)
    public ObjectNode linkedType;

    @OneToMany(fetch=FetchType.LAZY)
    public List<ObjectNode> nextOperations = new ArrayList<ObjectNode>();

    @OneToOne(fetch=FetchType.LAZY)
    public ObjectNode parentNode;

    @OneToMany(fetch=FetchType.LAZY)
    public List<ObjectNode> physicalIdentities = new ArrayList<ObjectNode>();

    @OneToOne(fetch=FetchType.LAZY)
    public ObjectNode pointingTo;

    @OneToMany(fetch=FetchType.LAZY)
    public List<ObjectNode> precessors = new ArrayList<ObjectNode>();

    @OneToMany(fetch=FetchType.LAZY,cascade=CascadeType.REMOVE)
    public List<ObjectProperty> properties = new ArrayList<ObjectProperty>();

    @OneToMany(fetch=FetchType.LAZY)
    public List<ObjectNode> successors = new ArrayList<ObjectNode>();

    @OneToMany(fetch=FetchType.LAZY,cascade=CascadeType.REMOVE)
    public List<ObjectProperty> supportingData = new ArrayList<ObjectProperty>();

    @OneToMany(fetch=FetchType.LAZY)
    public List<ObjectNode> workflow = new ArrayList<ObjectNode>();

Example query is:

select o from ObjectNode o join o.childNodes c
where o.classIdentifier = '(CU)' and
      (c.classIdentifier = '(RG)' and c.objectName = 'CAM-MILLING') and
      o.type = 0

Time for query like this (400000 ObjectNode instances, database size around 1.5 GByte): around 1.5 seconds

An additional index on childNodes should help.

If it is still slow, try also composite indexes:

1. on classIdentifier + type
2. on classIdentifier + objectName

great, that helps, just to be sure for the cache settings, as normal, the bigger, the better (or are there additional parameters to tune) ?!

...

<processing cache="2048mb" max-threads="10"  synchronized="false"/>
<query-cache results="1024mb" programs="500" />

...

<cache ref="weak" level2="4096mb" />

Larger cache can help, but not always. Unfortunately getting optimal performance requires tuning the configuration to the specific application (e.g. by benchmarking different options).

Regarding the query program cache, it will be useless with queries that embed parameters as strings:

SELECT o FROM ObjectNode o JOIN o.childNodes c
WHERE o.classIdentifier = '(CU)' AND
      (c.classIdentifier = '(RG)' AND c.objectName = 'CAM-MILLING') AND
      o.type = 0

It is better to use parameters:

SELECT o FROM ObjectNode o JOIN o.childNodes c
WHERE o.classIdentifier = :parent AND
      (c.classIdentifier = :child AND c.objectName = :name) AND
      o.type = :type

Query programs can be cached only when a query string is static and repeating (and only the parameter values change).

just to double-check, if the query string is the same, the query program cache uses the cached query

If the query string is found in the query program cache - no query compilation is needed and a cached query program is used. However the query still has to be executed, unless the query result cache can be used.

The query result cache can be used (to return results without executing a query at all) if:

• There is match of the query string + all the parameter values against the query result cache.
• The database has not been changed since caching these results in a way that can affect them.