MantisBT - TestLink
View Issue Details
0008640TestLinkTest Spec. - Keywordspublic2019-04-18 13:182019-04-29 10:24
gnustavo 
fman 
normalminoralways
resolvedfixed 
Ubuntu Linux18.04
1.9.19 (2019 Q1) 
1.9.20 Raijing DEV 
MySQL
7.2.15-0ubuntu0.18.04.2
TBD
0008640: Huge SQL expression used to filter test cases without keywords
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. :-(
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
No tags attached.
child of 0008549assigned fman Availables hot-fixes for 1.9.19 & How To get full fixed package from GitHub 
gz testlink.txt.gz (98,316) 2019-04-18 13:18
http://mantis.testlink.org/file_download.php?file_id=5140&type=bug
png screenshot_11.png (155,467) 2019-04-21 01:05
http://mantis.testlink.org/file_download.php?file_id=5143&type=bug
png

png screenshot_14.png (97,758) 2019-04-21 22:57
http://mantis.testlink.org/file_download.php?file_id=5144&type=bug
png
Issue History
2019-04-18 13:18gnustavoNew Issue
2019-04-18 13:18gnustavoFile Added: testlink.txt.gz
2019-04-18 14:00fmanNote Added: 0028733
2019-04-20 21:47fmanNote Added: 0028743
2019-04-20 21:47fmanAssigned To => fman
2019-04-20 21:47fmanStatusnew => feedback
2019-04-21 01:05gnustavoNote Added: 0028744
2019-04-21 01:05gnustavoStatusfeedback => assigned
2019-04-21 01:05gnustavoFile Added: screenshot_11.png
2019-04-21 09:29fmanNote Added: 0028745
2019-04-21 22:57gnustavoNote Added: 0028746
2019-04-21 22:57gnustavoFile Added: screenshot_14.png
2019-04-22 09:40fmanNote Added: 0028748
2019-04-22 14:28gnustavoNote Added: 0028756
2019-04-22 16:50gnustavoNote Added: 0028757
2019-04-25 08:56fmanNote Added: 0028773
2019-04-25 09:44fmanNote Added: 0028776
2019-04-25 09:44fmanQA Team - Task Workflow Status => TBD
2019-04-25 09:44fmanFixed in Version => 1.9.20 Raijing DEV
2019-04-28 00:06gnustavoNote Added: 0028793
2019-04-29 10:24fmanNote Added: 0028795
2019-04-29 10:24fmanStatusassigned => resolved
2019-04-29 10:24fmanResolutionopen => fixed
2019-04-29 10:24fmanRelationship addedchild of 0008549

Notes
(0028733)
fman   
2019-04-18 14:00   
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
(0028743)
fman   
2019-04-20 21:47   
please get this code

https://github.com/TestLinkOpenSourceTRMS/testlink-code/commit/c87844ea76ced4f8a9cfa808e5dc5fe50ecece6c [^]

test and provide feedback

new a temporary table is used.
I've tested with 25000 test cases in postgres
(0028744)
gnustavo   
2019-04-21 01:05   
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.
(0028745)
fman   
2019-04-21 09:29   
@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

regards

Happy eastern
(0028746)
gnustavo   
2019-04-21 22:57   
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!
(0028748)
fman   
2019-04-22 09:40   
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:

https://github.com/TestLinkOpenSourceTRMS/testlink-tests/tree/master/sample-db [^]

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.

IMPORTANT POINT:
is this the only feature that is suffering this way?

best regards
(0028756)
gnustavo   
2019-04-22 14:28   
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.

Thanks!
(0028757)
gnustavo   
2019-04-22 16:50   
The project team tell me that so far this is the only feature in which they suffer big delays.
(0028773)
fman   
2019-04-25 08:56   
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 );

then retest


regards
(0028776)
fman   
2019-04-25 09:44   
https://github.com/TestLinkOpenSourceTRMS/testlink-code/commit/f3532b36f275633e5bfd11a156a405e281baab9f [^]
(0028793)
gnustavo   
2019-04-28 00:06   
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.
(0028795)
fman   
2019-04-29 10:24   
great!, anyway standard code will use the temporary table