alfrescoarchive / activiti-cloud-query-service Goto Github PK

During performance testing of Activiti Cloud Query GraphQL implementation, I had identified slow query performance of any process instance or task record with associated variable values. The basic test case was performed by running a single GraphQL query to fetch 300 process instances each containing 1 task and 3 process variables.

Given this query, it takes ~3 seconds to send the request, convert it to SQL, execute a JDBC query to retrieve 300 process instances with 3 variable values, map the result to GraphQL format and send it back to the client.

In comparison, the same query without variables, takes only 100ms, which is 30x faster.

The slow query performance is caused by @Lob annotation on VariableValue attribute in VariableEntity class (https://github.com/Activiti/activiti-cloud-query-service/blob/ab5758566930dac343c068ae29bcb64346a614d7/activiti-cloud-services-query/activiti-cloud-services-query-model/src/main/java/org/activiti/cloud/services/query/model/VariableEntity.java#L74) which Hibernate maps into binary blob datatype in PostgreSQL database. The binary blob uses OID column which references a file kept separately from record data. When we do a join query to retrieve process variables, the Postgres JDBC driver is making additional N+1 queries to the blob store to get the JSON data for each variable value. This very inefficient way of mapping and storing variable data for query performance. The @Lob annotation also requires @Transactional attribute on GraphQL REST Controller in order make additional queries into PostgreSQL blob store.

The alternative to using binary blob storage is to try using implicit character lob datatype mapping to store and retrieve variable values as part of the variable record. The proposed solution is to simply remove @Lob annotation to create implicit text datatype mapping. The catch is that it will require more memory to execute queries with variable json by the database engine.

@Entity
public class VariableEntity implements Serializable {
   @Convert(converter = VariableValueJsonConverter.class)
   @Column
   // @Lob <-- remove to create implicit character lob that uses text SQL datatype
   private VariableValue<?> value;

This is the GraphQL query used for test case:

query {
  ProcessInstances(where: {status: {EQ: COMPLETED}}  {
    select {
      id
      name
      processDefinitionId
      processDefinitionKey
      name
      businessKey
      tasks {
        id
        name
        assignee
        status
        variables {
          id
          name
          value
        }
      }
      variables {
        id
        name
        value
      }
    }
  }
}

This is the SQL query generated from GraphQL query:

Hibernate:
    select
        distinct processins0_.id as id1_0_0_,
        variables1_.id as id1_4_1_,
        tasks2_.id as id1_1_2_,
        processins0_.app_name as app_name2_0_0_,
        processins0_.app_version as app_vers3_0_0_,
        processins0_.service_full_name as service_4_0_0_,
        processins0_.service_name as service_5_0_0_,
        processins0_.service_type as service_6_0_0_,
        processins0_.service_version as service_7_0_0_,
        processins0_.business_key as business8_0_0_,
        processins0_.description as descript9_0_0_,
        processins0_.initiator as initiat10_0_0_,
        processins0_.last_modified as last_mo11_0_0_,
        processins0_.last_modified_from as last_mo12_0_0_,
        processins0_.last_modified_to as last_mo13_0_0_,
        processins0_.name as name14_0_0_,
        processins0_.process_definition_id as process15_0_0_,
        processins0_.process_definition_key as process16_0_0_,
        processins0_.start_date as start_d17_0_0_,
        processins0_.status as status18_0_0_,
        variables1_.app_name as app_name2_4_1_,
        variables1_.app_version as app_vers3_4_1_,
        variables1_.service_full_name as service_4_4_1_,
        variables1_.service_name as service_5_4_1_,
        variables1_.service_type as service_6_4_1_,
        variables1_.service_version as service_7_4_1_,
        variables1_.create_time as create_t8_4_1_,
        variables1_.execution_id as executio9_4_1_,
        variables1_.last_updated_time as last_up10_4_1_,
        variables1_.marked_as_deleted as marked_11_4_1_,
        variables1_.name as name12_4_1_,
        variables1_.process_instance_id as process13_4_1_,
        variables1_.task_id as task_id14_4_1_,
        variables1_.type as type15_4_1_,
        variables1_.value as value16_4_1_,
        variables1_.process_instance_id as process13_4_0__,
        variables1_.id as id1_4_0__,
        tasks2_.app_name as app_name2_1_2_,
        tasks2_.app_version as app_vers3_1_2_,
        tasks2_.service_full_name as service_4_1_2_,
        tasks2_.service_name as service_5_1_2_,
        tasks2_.service_type as service_6_1_2_,
        tasks2_.service_version as service_7_1_2_,
        tasks2_.assignee as assignee8_1_2_,
        tasks2_.category as category9_1_2_,
        tasks2_.claimed_date as claimed10_1_2_,
        tasks2_.created_date as created11_1_2_,
        tasks2_.description as descrip12_1_2_,
        tasks2_.due_date as due_dat13_1_2_,
        tasks2_.last_modified as last_mo14_1_2_,
        tasks2_.last_modified_from as last_mo15_1_2_,
        tasks2_.last_modified_to as last_mo16_1_2_,
        tasks2_.name as name17_1_2_,
        tasks2_.owner as owner18_1_2_,
        tasks2_.parent_task_id as parent_19_1_2_,
        tasks2_.priority as priorit20_1_2_,
        tasks2_.process_definition_id as process21_1_2_,
        tasks2_.process_instance_id as process22_1_2_,
        tasks2_.status as status23_1_2_,
        tasks2_.process_instance_id as process22_1_1__,
        tasks2_.id as id1_1_1__
    from
        process_instance processins0_
    left outer join
        variable variables1_
            on processins0_.id=variables1_.process_instance_id
    left outer join
        task tasks2_
            on processins0_.id=tasks2_.process_instance_id
    where
        processins0_.status=?
    order by
        processins0_.id asc

Hibernate query execution statistics:

2018-10-30 03:47:31.202  INFO 1 --- [nio-8080-exec-4] i.StatisticalLoggingSessionEventListener : Session Metrics {
    729628 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    904254 nanoseconds spent preparing 1 JDBC statements;
    16233981 nanoseconds spent executing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    9963 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)

[INFO] ------------------------------------------------------------------------
[INFO] Reactor Summary for Activiti Cloud Query :: Parent 7.1.0-SNAPSHOT:
[INFO]
[INFO] Activiti Cloud Query :: Parent ..................... SUCCESS [ 1.269 s]
[INFO] Activiti Cloud :: Query Dependencies BOM (Bill Of Materials) Tests SUCCESS [ 0.518 s]
[INFO] Activiti Cloud :: Query Dependencies BOM (Bill Of Materials) SUCCESS [ 0.031 s]
[INFO] Activiti Cloud Query :: Services :: Parent ......... SUCCESS [ 0.029 s]
[INFO] Activiti Cloud Query :: Services :: Query Model .... FAILURE [ 2.140 s]
[INFO] Activiti Cloud Query :: Services :: Query Repo ..... SKIPPED
[INFO] Activiti Cloud Query :: Services :: Query REST ..... SKIPPED
[INFO] Activiti Cloud Query :: Starter :: Query ........... SKIPPED
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 4.924 s
[INFO] Finished at: 2020-02-07T18:44:45+07:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:3.8.1:compile (default-compile) on project activiti-cloud-services-query-mo
del: Fatal error compiling: invalid flag: --release -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException
[ERROR]
[ERROR] After correcting the problems, you can resume the build with the command
[ERROR] mvn -rf :activiti-cloud-services-query-modelb

When including activiti-cloud-starter-query as maven dependency then activiti-cloud-services-query-rest is also included and it contains application.properties which is being picked instead of my application's application.yml. I believe, that activiti-cloud-services-query/activiti-cloud-services-query-rest/src/main/resources/application.properties shouldn't be placed in jar file.

GraphQl schema configuration should be able to handle future changes to the data model at runtime, i.e. to add a new field to the Task entity or similar.

The schema file is loaded by configuration from classpath. We can’t easily change configuration without a restart (or maybe just a context refresh?) as the autoconfiguation builds the executor with the schema at the autoconfiguration stage and the builder likewise is instantiated by an autoconfiguration and it contains a final type registry.

By default it is reading the file using getClass().getClassLoader().getResourceAsStream so it needs to come from the classpath. We need to make schema loading changeable at runtime by doing a k8s rollingUpdate to the gateway and query components.

There is a spelling mistake in the first line of this file:
activiti-cloud-query-service/activiti-cloud-services-query/activiti-cloud-services-query-rest/src/test/java/org/activit/cloud/services/security/SecurityPoliciesApplicationServiceTest.java

where 'activit' should be 'activiti'

In org.activiti.cloud.query:activiti-cloud-services-query-rest:7.1.0.M6 during process instance variables querying in ProcessInstanceVariableController does not apply ProcessInstanceRestrictionService and not authorized user can see process instance variables.

in process instance entity which causes the exception in subsequent handling variable events in VariableUpdateEventHandler.

We need to review the case when process and task variable have the same and how to handle name collisions with @erdemedeiros and @ryandawsonuk

Related to Activiti/Activiti#2164 and Activiti/Activiti#2163