Mantis Bugtracker 

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0008640TestLinkTest Spec. - Keywordspublic2019-04-18 13:182019-04-29 10:24
Assigned Tofman 
PlatformOSUbuntu LinuxOS Version18.04
Product Version1.9.19 (2019 Q1) 
Fixed in Version1.9.20 
Summary0008640: Huge SQL expression used to filter test cases without keywords
DescriptionI 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 [^]

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 Reproduce1. 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
TagsNo tags attached.
Database (MySQL,Postgres,etc)MySQL
PHP Version7.2.15-0ubuntu0.18.04.2
QA Team - Task Workflow StatusTBD
Attached Filesgz file icon testlink.txt.gz [^] (98,316 bytes) 2019-04-18 13:18
png file icon screenshot_11.png [^] (155,467 bytes) 2019-04-21 01:05

png file icon screenshot_14.png [^] (97,758 bytes) 2019-04-21 22:57

- Relationships
child of 0008549closedfman Availables hot-fixes for 1.9.19 & How To get full fixed package from GitHub 

-  Notes
fman (administrator)
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
fman (administrator)
2019-04-20 21:47

please get this code [^]

test and provide feedback

new a temporary table is used.
I've tested with 25000 test cases in postgres
gnustavo (reporter)
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.
fman (administrator)
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


Happy eastern
gnustavo (reporter)
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.

fman (administrator)
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: [^]

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?

best regards
gnustavo (reporter)
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.

gnustavo (reporter)
2019-04-22 16:50

The project team tell me that so far this is the only feature in which they suffer big delays.
fman (administrator)
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

fman (administrator)
2019-04-25 09:44 [^]
gnustavo (reporter)
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, [^] mind you.

Thank you very much, @fman. I think this solves the problem.
fman (administrator)
2019-04-29 10:24

great!, anyway standard code will use the temporary table

- Issue History
Date Modified Username Field Change
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
Powered by Mantis Bugtracker