|Anonymous | Login | Signup for a new account||2020-07-10 19:15 UTC|
|Main | My View | View Issues | Change Log | My Account|
|View Issue Details|
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0008640||TestLink||Test Spec. - Keywords||public||2019-04-18 13:18||2019-04-29 10:24|
|Platform||OS||Ubuntu Linux||OS Version||18.04|
|Product Version||1.9.19 (2019 Q1)|
|Fixed in Version||1.9.20|
|Summary||0008640: Huge SQL expression used to filter test cases without keywords|
|Description||I used tcpdump to capture the TCP flow between TestLink and its MySQL database and extracted from it the SQL expression generated when I click the Apply button mentined in the Steps To Reproduce field. The SQL expression is in the attached file testlink.txt.gz.|
I'm working in a TestLink project containing 38948 test cases. Note that the SQL expression contains 281017 characters and an IN clause containing the list of 38944 test case IDs!
When TestLink sends this query to MySQL it takes a long time to respond and the webserver in front of it aborts after a timeout which I already increased to five minutes.
I executed the query in the command line to see how much time MySQL would take to answer and it took 13 minutes!
This IN clause is generated at https://github.com/TestLinkOpenSourceTRMS/testlink-code/blob/testlink_1_9/lib/functions/testproject.class.php#L3389 [^]
It seems that the logic in this getTCLatestVersionFilteredByKeywords function simply does not scale well for projects with large numbers of test cases. Unfortunately, I think there is no simple solution to this problem, besides a complete rethink of the SQL expression. :-(
|Steps To Reproduce||1. Select a project with a huge number of test cases|
2. Go to Test Specification
3. Select "[without keywords]" in the Keyword field
4. Click the Apply button
|Tags||No tags attached.|
|QA Team - Task Workflow Status||TBD|
|Attached Files|| testlink.txt.gz [^] (98,316 bytes) 2019-04-18 13:18|
screenshot_11.png [^] (155,467 bytes) 2019-04-21 01:05
screenshot_14.png [^] (97,758 bytes) 2019-04-21 22:57
it's clear that solution is not the best or good, and need to be refactored.
Probably using test project with such amount of test cases is not the best organization to use, but this can not be an excuse by my side
please get this code
test and provide feedback
new a temporary table is used.
I've tested with 25000 test cases in postgres
Thank you. I replaced the testproject.class.php file by your new code and tried it. Unfortunately, in my case I'm still getting timed out after five minutes.
I increased the nginx timeout configuration to see how much time it would take to complete the operation and it took 14.8min, as you can see in the screenshot I'm attaching.
@gnustavo, thanks for your feedback
I'm going to try to do same test with MySQL. I'm testing in my laptop.
would you mind to modify the code provided in order to use not a temp table and do not drop it, this way you will be able to test the query from your mysql client, and we will get info about the query time.
May be the issue is related to the tree construction to be displayed.
As already stated, IMHO you need to rethink your project organization
Thank you, @fman.
I captured the network packets between my TestLink server and my MySQL server (they are in different machines) and analysed the MySQL flow. I can see that thousands of SQL commands are sent and processed in the first 14 seconds. Then the SELECT query is sent at 14.82 seconds and only at 803 seconds (13.38 minutes) we see the DROP TABLE comand.
I'm attaching a screenshot of wireshark where you can see this.
You're probably right that we shouldn't be using TestLink like this. I'm not involved in the project, I'm just supporting TestLink for them. But I'll try to understand why they have so many test cases to try to come up with a new strategy that don't create so many or that spreads them among several other projects.
Thanks for your help.
What is happening?
The tree structure in DB is a simple one, then the only way to get all test cases (really test case versions) is to walk the tree doing recursion, to get only the items that belong to the test project.
This can not be avoided with current design.
I've got all this items ID and instead of creating a MONSTER IN clause, I've inserted them in a temp table, and then use it in a join .
This change it's supposed to improve performance, but according to your measurements it seems there is no improvement.
I've created to samples db (one mysql and one postgres) trying to simulate your use case.
these db are here:
it will be great if you can do a test using this sample DB.
Or if you can provide me your DB in order to do a test on my laptop (probably your answer will be NO due to nature of data).
If you are kindly enough to dig a little bit on the MySQL , may be analizing some execution plan it will be of great help.
is this the only feature that is suffering this way?
I can provide you with our database in private. If it's ok with you, please, send me an email at "gustavo AT gnustavo DOT com".
I asked the project team if there are other features causing problems. I'll let you know when I get news from them.
|The project team tell me that so far this is the only feature in which they suffer big delays.|
Please add folowing indexes:
CREATE INDEX `testcase_keywords_tcversion_id` USING BTREE ON `testcase_keywords`( tcversion_id );
CREATE INDEX `nodes_hierarchy_node_type_id` USING BTREE ON `nodes_hierarchy`( node_type_id );
Hi. I'm sorry for the delay... It worked!
I just created the two indexes and the filter operation took only 19 seconds!
I'm using the original Testlink 1.9.19 testproject.class.php file and not the one you commented about on http://mantis.testlink.org/view.php?id=8640#c28743, [^] mind you.
Thank you very much, @fman. I think this solves the problem.
|great!, anyway standard code will use the temporary table|
|2019-04-18 13:18||gnustavo||New Issue|
|2019-04-18 13:18||gnustavo||File Added: testlink.txt.gz|
|2019-04-18 14:00||fman||Note Added: 0028733|
|2019-04-20 21:47||fman||Note Added: 0028743|
|2019-04-20 21:47||fman||Assigned To||=> fman|
|2019-04-20 21:47||fman||Status||new => feedback|
|2019-04-21 01:05||gnustavo||Note Added: 0028744|
|2019-04-21 01:05||gnustavo||Status||feedback => assigned|
|2019-04-21 01:05||gnustavo||File Added: screenshot_11.png|
|2019-04-21 09:29||fman||Note Added: 0028745|
|2019-04-21 22:57||gnustavo||Note Added: 0028746|
|2019-04-21 22:57||gnustavo||File Added: screenshot_14.png|
|2019-04-22 09:40||fman||Note Added: 0028748|
|2019-04-22 14:28||gnustavo||Note Added: 0028756|
|2019-04-22 16:50||gnustavo||Note Added: 0028757|
|2019-04-25 08:56||fman||Note Added: 0028773|
|2019-04-25 09:44||fman||Note Added: 0028776|
|2019-04-25 09:44||fman||QA Team - Task Workflow Status||=> TBD|
|2019-04-25 09:44||fman||Fixed in Version||=> 1.9.20|
|2019-04-28 00:06||gnustavo||Note Added: 0028793|
|2019-04-29 10:24||fman||Note Added: 0028795|
|2019-04-29 10:24||fman||Status||assigned => resolved|
|2019-04-29 10:24||fman||Resolution||open => fixed|
|2019-04-29 10:24||fman||Relationship added||child of 0008549|
|Copyright © 2000 - 2020 MantisBT Team|