2.1.4 Performance tune (Windows)

Abbreviations, used in the manual:

QG - Quality Gates

EF - Execution Flow

DV - Data Validation

JS - JavaScript

RDA - Reference Data Admin

DD - Drop Down

The purpose of this manual is to explain the main reasons for performance issues and, according to that, to request the relevant information in order to find the root cause and resolve the problem.

General Information:

EFs/Tests in QG could be stuck/killed by timeout because of several reasons:

- Not enough memory is allocated to the quilliup service.
- Too many entities are running in parallel/starting roughly at the same time.
- Heavy entities are running in parallel.
- Wrong/unsuitable parameter values.

- DV Tests of type JS exist (usually consume a lot of memory)

- Heavy Compare Tests (for example, of "Raw Data" type) run roughly at the same time.

- DDs cache should be switched off if not used (is related to RDA module but could influence performance of the whole product).

The following information should be sent to the quilliup support team in the Zendesk Ticket:

1. Run the following query in quilliup DB repository for the relevant environment:

MySQL:

Select * from executions 
where DATE(createdDate)='2023/06/15';

MSSQL:

SELECT * FROM executions 
WHERE CONVERT(date, createddate) = '2023/06/15';

Oracle:

select * from executions 
WHERE to_char(createddate,'YYYY/MM/DD')>='2023/06/15'
GROUP BY to_char(createddate,'YYYY/MM/DD')
order by to_char(createddate,'YYYY/MM/DD');

Pay attention: fill in the relevant date value and send us the SQL query results in an Excel file.

2. The parameter: pool-size.

Open the file "...\quilliup\webapps\ROOT\WEB-INF\classes\spring\app-config.xml" and search for the parameter "pool-size":

Send the parameter's value that appears in relevant environment.

3. The parameters: tasks.max, aquire.thread.sleep, thread.queue.sleep.

Open the file "...\quilliup\webapps\ROOT\WEB-INF\classes\config.properties" and search for the parameters "tasks.max", "aquire.thread.sleep" and "thread.queue.sleep":

4. Share the total memory size of the server where quilliup is installed.

5. Share the memory that is allocated for quilliup:

Open the file: "...\quilliup\bin\service.bat" and search for the folllowing parameter's values:

--JvmMs
--JvmMx 

For example:

6. Share the Apache Tomcat version. For Windows, it could be checked in the Services:

*For Linux, it could be checked by running the script in your tomcat/bin directory:

sh tomcat/bin/version.sh

7. In quilliup UI, in Administration module -> General Settings -> System Settings -> System Properties -> "Maximum Number Of Threads": 

8.  For problematic quilliup Project(s) (those that contain problematic/stuck entities) from the left menu go to Settings -> Projects -> Edit Project -> "Concurrent Executions Limit":

General Recommendations:
If the main cause for the system's slowdown is due to increased number of tests and EF's that run on a daily basis, we recommend having two (or more) quilliup servers and quilliup environments.
For example: Prod & Pre-Prod, or Prod 1 & Prod 2.
In general, the Production environment must contain only checked, tested, and used items.
All other items should be handled in other environments such as Dev, Pre Prod ,etc.
This will improve the slowdown that occurs due to SQL queries that are being sent to the repository.

Actions that can be applied immediately (increase/tune quilliup performance):

1. Refresh and clean the environment from inactive items:
● Delete all unneeded Data Sources.
● Delete all unneeded Projects.
● Delete all unneeded EFs under each Project.
● Delete all unneeded Tests under each Project.
● Delete all unneeded Scripts under each Project.

2. You might want to increase the memory of quilliup's server.

3. If you frequently use Raw Data tests:

● We advise not to run "Raw Data" comparison Test type without high necessity. 
For example, a Raw Data test could be implemented as part of an Execution Flow (EF), and to run after another test with a certain condition. This way, the Raw Data tests would run only when necessary according to specific conditions.

● Each compare test should have appropriate Chunk Size value that can be defined in "Toolbox -> Settings -> Chunk Size". For example, if a Compare Test runs over 500.000 rows => chunk size can be increased to 100,000. Choosing suitable chunk size will increase Test’s performance.
It is preferable to choose a value in "Toolbox -> Settings -> Column To Index".

4. If possible - rework EFs so that the items will be connected sequentially.

5. Switch off ehcache/DDs cache in order to avoid any memory allocation (note, that this step
requires quilliup service restart and reinstallation ).

Please follow the steps described in the following manual: RDA-module-Drop-Down-s-Cache

6. For each scheduled EF or Test (scheduled by run_qp or as a part of an EF) set the relevant timeout
value in quilliup UI to prevent processes from taking longer than the allotted time.

7. Check if there are scheduled EFs or Test (scheduled by run_qp or as part of an EF) for which it’s
possible to keep execution history for less than 7 days (as currently defined in global History
Cleansing Settings). For such items please set "Keep result history of the last:” to a value less
than 7.

8. Check validity of Data Sources, making sure there aren't many 'stuck' connections.

9. Make sure to bring to a minimum the number of tests/executions that are scheduled to run at the same time.