Hello,
I would like present a useful good tool DQLPerformanceTest v1.1 provided by DELL EMC and created by Krzysztof Jurkowski. The first part (PRESENTATION) of this post has been copied/pasted from EMC website (https://community.emc.com/docs/DOC-32505). In the second part (EXAMPLE OF USE), I would expose a full example of tool’s use and interpretation of generated results.
PART I : PRESENTATION
Description
DQLPerformanceTest is a java based command line tool that allows to perform a stress test of a DQL execution.
It provides following set of feature:
- Measures minimum, maximum, average and median time of a DQL execution
- Allows to pass login, password, workers count and single session switch. In this scenario DQLPerformanceTest will create a given number of workers which will use one session to execute given DQL (parameters: -l
, -p , -w , -s) - Allows to pass single login, password, workers count. In this scenario DQLPerformanceTest will create a given number of workers and each one will use separate session to execute given DQL (parameters: -l
, -p , -w ) - Allows to pass a CSV file with user credentials. In this scenario the number of workers will be equal to the number of valid credentials in the CSV file, i.e. if there are 50 valid rows in the CSV file there will be 50 workers. Each worker uses separate session, based on the corresponding credentials in CSV file (parameter: -c
) - Execution results are displayed on the console, but are saved to a log file as well. Each execution creates a separate log file test_
.log. Log file is generated using Log4j. Please note that all entries (even exceptions) in the test log are on INFO level. Changing log level to WARN, ERROR or FATAL in log4j.properties will result in no logs at all - Execute DQL in EXEC_QUERY for each worker (parameter: -e)
- Display (log) results of the executed DQL. It is required to pass a comma separated list of columns that should be displayed, for example -r r_object_id,object_name. Note not to put space bar after comma
- Workers can flush query cache before executing each DQL(parameter: -f)
Measurement methodology
DQLPerformanceTest tool measures only execution time of the DQL at the DFC level, i.e. there is a stopwatch on the query.execute:
setStart(System.currentTimeMillis()); col = query.execute(session, queryType); setStop(System.currentTimeMillis());
Therefore, execution time per worker and overall execution time (minimum, maximum average and median) doesn’t include acquiring session, releasing session, logging, cache flushing etc. If a worker fails to log in with given credentials – this measurement is not taken into account.
Setup
Before you can start using this tool you need to:
- Make sure that you’ve installed JRE on your system and that it is accessible in your command line/shell. This can be done by typing java –version. This tool requires JRE 1.6 and was tested using version 1.6.45.
- Copy dctm.jar and dfc.jar to the lib folder in the catalog where you extracted the DQLPerformanceTest archive
- Setup or copy dfc.keystore and dfc.properties to the catalog where you extracted the DQLPerformanceTest archive, i.e. in the same catalog where DQLPerformanceTest.jar is stored
- Read and agree with the License included in the DQLPerformanceTest archive
Usage
java -jar DQLPerformanceTest.jar -c,--csv Comma separated CSV file with user credentials. Cannot be used with -l, -p and -w -d,--docbase Docbase name -e Execute query in EXEC_QUERY mode -f Each worker will flush query cache before executing DQL -h,--help Display help -l,--login Worker user login. Cannot be used with -c -p,--password Worker user password. Cannot be used with -c -q,--query DQL query to be executed -r,--results Log query results. Argument has to be a comma separated column list -s,--session All workers will use single session. Cannot be used with -c -w,--workers How many times will the DQL be concurrently executed. Cannot be used with -c -x,--export Export execution time of each worker to CSV file. Optionally specify the file's path and name
This screen can be displayed from command line using -h, –help switch.
Example 1:
Execute a query 10 times using single session with cache flush
java -jar DQLPerformanceTest.jar -d mydocbase -l user1 -p topsecret -w 10 -q "select * from dm_user where user_name='dmadmin'" -f -s
Example 2:
Execute a query 25 times using separate session for each worker and log results
java -jar DQLPerformanceTest.jar -d mydocbase -l user1 -p topsecret -w 25 -q "select * from dm_user where user_name='dmadmin'" -r r_object_id,user_name,user_os_name
Example 3:
Execute a query using CSV file
java -jar DQLPerformanceTest.jar -d mydocbase -c "./credentials.csv" -q "select * from dm_document where object_name like 'A%'"
CSV partern is:
login1,password1
login2,password2
Sample output:
Average time: 5,5273 s
Median time: 5,5274 s
Min time: 5,5272 s
Max time: 5,5275 s
PART II : EXAMPLE OF USE
Stress test of our application
In this example, on average, we have 40 users connected simultaneously to our application ; So, for our tests we will have a scenario with 100 users in order to be sure having a coherent maximum number of users connected in same time.
DQL queries
First, it is necessary to identify the DQL queries which are very time-consuming according to the feel of the users.
The measures of SQL queries (Oracle execution plan) are welcome in order to identify the DQL queries to analyze in the performance testing.
- Query for document’s opening (Q1)
SELECT r_object_id FROM dm_document WHERE r_object_id = '<r_object_id_doc>' (ex: r_object_id_doc = 0901xxxxxxxxxb3)
- Query for documents selection (Q2)
SELECT r_object_id, object_name, r_modify_date FROM my_custom_type WHERE exists (SELECT 1 FROM dmi_queue_item, dmi_package WHERE dmi_queue_item.delete_flag=0 AND dmi_queue_item.router_id=dmi_package.r_workflow_id AND any dmi_package.r_component_chron_id=my_custom_type.i_chronicle_id) AND my_status IN (12,22,32,52) AND is_archived=FALSE AND (UPPER(object_name) like 'DOC2%') ORDER BY r_modify_date ASC
- Query for documents selection (Q3)
SELECT r_object_id, object_name, r_modify_date FROM my_custom_type WHERE exists (SELECT 1 FROM dmi_queue_item, dmi_package WHERE dmi_queue_item.delete_flag=0 AND dmi_queue_item.router_id=dmi_package.r_workflow_id AND any dmi_package.r_component_chron_id=my_custom_type.i_chronicle_id) AND my_status IN (13,23,33,53) AND is_archived=FALSE AND (UPPER(object_name) like 'DOC31%' OR UPPER(object_name) like 'DOC32%') ORDER BY r_modify_date ASC
- Query for documents selection (Q4)
SELECT r_object_id, object_name, r_modify_date FROM my_custom_type WHERE exists (SELECT 1 FROM dmi_queue_item, dmi_package WHERE dmi_queue_item.delete_flag=0 AND dmi_queue_item.router_id=dmi_package.r_workflow_id AND any dmi_package.r_component_chron_id = my_custom_type.i_chronicle_id) AND my_status IN (14,24,34,54) AND is_archived=FALSE AND (UPPER(object_name) like 'DOC41%' OR UPPER(object_name) like 'DOC42%') ORDER BY r_modify_date ASC
- Query for documents selection (Q5)
SELECT r_object_id, object_name, r_modify_date FROM my_custom_type WHERE exists (SELECT 1 FROM dmi_queue_item, dmi_package WHERE dmi_queue_item.delete_flag=0 AND dmi_queue_item.router_id=dmi_package.r_workflow_id AND any dmi_package.r_component_chron_id=my_custom_type.i_chronicle_id) AND my_status IN (25,45) AND is_archived=FALSE ORDER BY r_modify_date ASC
- Query for documents selection (Q6)
SELECT r_object_id, object_name, r_modify_date FROM my_custom_type (ALL) WHERE exists (SELECT 1 FROM my_custom_type ref, dmi_queue_item, dmi_package, dmi_workitem WHERE dmi_queue_item.delete_flag=0 AND dmi_queue_item.router_id=dmi_package.r_workflow_id AND any dmi_package.r_component_chron_id=ref.i_chronicle_id AND my_status IN (36,46) AND ref.is_archived=FALSE) ORDER BY r_modify_date ASC
- Query for archived documents selection (Q7)
SELECT r_object_id, object_name, r_modify_date FROM my_custom_type WHERE exists (SELECT 1 FROM dmi_queue_item, dmi_package WHERE dmi_queue_item.delete_flag=0 AND dmi_queue_item.router_id=dmi_package.r_workflow_id AND any dmi_package.r_component_chron_id=my_custom_type.i_chronicle_id) AND my_status<>67 AND is_archived=TRUE ORDER BY r_modify_date ASC
- Query for documents searching (Q8)
SELECT r_object_id, object_name, r_modify_date FROM my_custom_type (ALL) WHERE my_criteria_simple=18 AND ANY my_criteria_repeat1 IN ('123','456') AND my_criteria_date1>=date('2007-01-01 00:00:00','yyyy-mm-dd hh:mi:ss') AND my_criteria_date1<=date('2017-01-31 23:59:59','yyyy-mm-dd hh:mi:ss') AND ANY UPPER(my_criteria_repeat2) IN ('ABCDEFG','HIJKLMONP','QRSTUVWXYZ') AND my_criteria_date2>=date('2007-01-01 00:00:00','yyyy-mm-dd hh:mi:ss') AND my_criteria_date2<=date('2017-01-31 23:59:59','yyyy-mm-dd hh:mi:ss') UNION SELECT r_object_id, object_name, r_modify_date FROM my_custom_type WHERE a_is_hidden=FALSE AND my_criteria_simple=18 AND ANY my_criteria_repeat1 IN ('123','456') AND my_criteria_date1>=date('2007-01-01 00:00:00','yyyy-mm-dd hh:mi:ss') AND my_criteria_date1<=date('2017-01-31 23:59:59','yyyy-mm-dd hh:mi:ss') AND ANY UPPER(my_criteria_repeat2) IN ('ABCDEFG','HIJKLMONP','QRSTUVWXYZ') AND my_criteria_date2>=date('2007-01-01 00:00:00','yyyy-mm-dd hh:mi:ss') AND my_criteria_date2<=date('2017-01-31 23:59:59','yyyy-mm-dd hh:mi:ss')
Test Scenarios
The following scenarios will be tested:
- Scenario 1 : Execution of all queries, only once execution;
java -jar DQLPerformanceTest.jar -d mydocbase -l user1 -p topsecret -w 1 -q "select ...." -f -s
- Scenario 2 : Execution of all queries, by launching 40 simultaneous sessions;
java -jar DQLPerformanceTest.jar -d mydocbase -l user1 -p topsecret -w 40 -q "select ...." -f
- Scenario 3 : Execution of all queries, by launching 100 simultaneous sessions;
java -jar DQLPerformanceTest.jar -d mydocbase -l user1 -p topsecret -w 100 -q "select ...." -f
Comments
- The measured data will be: minimum time, maximum time, mean time and median time of execution DQL
- The tests and measures should be done in iso-PROD environment
- Session acquiring, session releasing, logging, cache flushing, etc (negligible times) are not taken into account in the measurements
- Our measures have been done with an unique functional user opening 100 différents sessions
- Indeed, it would take 100 différents users in order to obtain a distinct session by user.
Note : Content Server allows 1024 sessions concurrent. - Several concurrent sessions with the same user, will involve results not near of real situation because the Content Server accesses always to same data during the performance testing.
Results
The following measures are extracted from the log files generated by the tool:
- Scenario 1 :
Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Execution Time(s) 0.242 0.279 0.251 0.343 0.396 0.799 0.296 0.798 - Scenario 2 :
Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Average Time(s) 0.261 0.434 0.447 0.402 0.612 3.451 0.417 2.197 Median Time(s) 0.259 0.391 0.412 0.408 0.601 3.311 0.412 2.217 Min Time(s) 0.250 0.387 0.402 0.399 0.598 3.299 0.401 2.105 Max Time(s) 0.271 0.448 0.449 0.412 0.619 3.475 0.420 2.310 - Scenario 3 :
Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Average Time(s) 0.312 0.812 0.872 0.701 0.948 7.801 0.621 8.829 Median Time(s) 0.306 0.849 0.874 0.723 0.958 7.795 0.685 8.878 Min Time(s) 0.275 0.623 0.799 0.698 0.932 7.699 0.601 8.798 Max Time(s) 0.399 0.895 0.897 0.801 0.997 7.852 0.702 8.902
Interpretation
- The queries Q6 and Q8 are very time-consuming => It would be necessary to see if it is possible to optimize them
- The execution times of queries Q2 and Q3 are acceptable for 100 simultaneous users, but the results are very acceptable below this number => there is therefore no need to optimize these queries in the immediate future.
Tools : DQLPerformanceTest_1.1.zip. The download of tool needs the user to be authenticated on EMC DEVELOPER site (https://developer-content.emc.com/login/login.asp). Contents of this ZIP:
Resources : https://community.emc.com/docs/DOC-32505
Best regards,
Huseyin OZVEREN