Mantis Bugtracker          
testlink.org

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0007427TestLinkReportspublic2016-01-28 09:402016-01-28 13:17
Reportergapiro 
Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
StatusnewResolutionopen 
Platform IIS mysqlOSWindows 2007 serverOS Version
Product Version1.9.14 (2015 Q3) 
Fixed in Version 
Summary0007427: report query takes too long and times out
DescriptionBasically the same as 0004235
I am unable to load the general test plan metrics for our latest project - I assume due to the amount of data being too large.

the SQL workbench shows the following query taking 600s before timing out (as defined in my php, timeout of 10 minutes)

/* Class:tlTestPlanMetrics - Method: getExecCountersByBuildExecStatus UNION WITH ALL CLAUSE */ SELECT build_id,status, count(0) AS exec_qty FROM (/* Class:tlTestPlanMetrics - Method: getExecCountersByBuildExecStatus sqlUnionAB - executions */ SELECT DISTINCT UA.build_id, TPTCV.tcversion_id, TPTCV.platform_id, COALESCE(E.status,'n') AS status FROM testplan_tcversions TPTCV JOIN user_assignments UA ON UA.feature_id = TPTCV.id AND UA.build_id IN (491,505,552,604,529,548,670,677,683,686,689,693,694,699,700,706,709,713,715,718,723,726,731,737,744,747,753,760,765,772,776,780,782,790,795,797,802) AND UA.type = 1 /* GO FOR Absolute LATEST exec ID ON BUILD and PLATFORM */ JOIN ( SELECT EE.tcversion_id,EE.testplan_id,EE.platform_id,EE.build_id, MAX(EE.id) AS id FROM executions EE WHERE EE.testplan_id=202400 AND EE.build_id IN (491,505,552,604,529,548,670,677,683,686,689,693,694,699,700,706,709,713,715,718,723,726,731,737,744,747,753,760,765,772,776,780,782,790,795,797,802) GROUP BY EE.tcversion_id,EE.testplan_id,EE.platform_id,EE.build_id ) AS LEBBP ON LEBBP.testplan_id = TPTCV.testplan_id AND LEBBP.platform_id = TPTCV.platform_id AND LEBBP.tcversion_id = TPTCV.tcversion_id AND LEBBP.testplan_id = 202400 /* Get execution status WRITTEN on DB */ JOIN executions E ON E.id = LEBBP.id AND E.build_id = UA.build_id WHERE TPTCV.testplan_id=202400 AND UA.build_id IN (491,505,552,604,529,548,670,677,683,686,689,693,694,699,700,706,709,713,715,718,723,726,731,737,744,747,753,760,765,772,776,780,782,790,795,797,802) UNION ALL /* Class:tlTestPlanMetrics - Method: getExecCountersByBuildExecStatus sqlUnionBB - NOT RUN */ SELECT DISTINCT UA.build_id, TPTCV.tcversion_id, TPTCV.platform_id, COALESCE(E.status,'n') AS status FROM testplan_tcversions TPTCV JOIN user_assignments UA ON UA.feature_id = TPTCV.id AND UA.build_id IN (491,505,552,604,529,548,670,677,683,686,689,693,694,699,700,706,709,713,715,718,723,726,731,737,744,747,753,760,765,772,776,780,782,790,795,797,802) AND UA.type = 1 /* Get REALLY NOT RUN => BOTH LE.id AND E.id ON LEFT OUTER see WHERE */ LEFT OUTER JOIN ( SELECT EE.tcversion_id,EE.testplan_id,EE.platform_id,EE.build_id, MAX(EE.id) AS id FROM executions EE WHERE EE.testplan_id=202400 AND EE.build_id IN (491,505,552,604,529,548,670,677,683,686,689,693,694,699,700,706,709,713,715,718,723,726,731,737,744,747,753,760,765,772,776,780,782,790,795,797,802) GROUP BY EE.tcversion_id,EE.testplan_id,EE.platform_id,EE.build_id ) AS LEBBP ON LEBBP.testplan_id = TPTCV.testplan_id AND LEBBP.platform_id = TPTCV.platform_id AND LEBBP.tcversion_id = TPTCV.tcversion_id AND LEBBP.build_id = UA.build_id AND LEBBP.testplan_id = 202400 LEFT OUTER JOIN executions E ON E.tcversion_id = TPTCV.tcversion_id AND E.testplan_id = TPTCV.testplan_id AND E.platform_id = TPTCV.platform_id AND E.build_id = LEBBP.build_id /* FILTER BUILDS in set on target test plan (not alway can be applied) */ WHERE TPTCV.testplan_id=202400 AND UA.build_id IN (491,505,552,604,529,548,670,677,683,686,689,693,694,699,700,706,709,713,715,718,723,726,731,737,744,747,753,760,765,772,776,780,782,790,795,797,802) /* Get REALLY NOT RUN => BOTH LE.id AND E.id NULL */ AND E.id IS NULL AND LEBBP.id IS NULL ) AS SQBU GROUP BY build_id,status
Steps To Reproduce Go to reports
Click general testplan metrics
Additional InformationTestplan with around 6000 tests, 4 platforms and approx 35 builds

I have my php timeout set to 600s
query_cache_size=1000M
tmp_table_size=600M

Test project this is in has ~ 36000 tests active tests.
TagsNo tags attached.
Database (MySQL,Postgres,etc)mysql 5.5.14
Browserchrome
PHP Version
TestCaseID
QA Team - Task Workflow Status
Attached Files

- Relationships

-  Notes
(0024462)
fman (administrator)
2016-01-28 10:26

1) as usual without your db dump no action can be taken
2) have you run the query on MySQL workbench and get some hints/indications?
(0024463)
gapiro (reporter)
2016-01-28 10:40

1)As usual , due to secure nature of our work, I cannot provide it.
2)Not sure what hints/ etc I should expect. The cpu usage runs at 9% and the mem usage 19%. Key efficienty 99.96, traffic is around 50 kb/s.
(0024464)
fman (administrator)
2016-01-28 13:12

unfortunately as you can expect the work to setup an environment likes you is not easy, then I can do nothing without your help.

hints are: what is execution time of the offending query ?
(0024465)
gapiro (reporter)
2016-01-28 13:14

Tried running the query manually, and hit 600s again before erroring with the error
 Lost connection to MySQL server during query 600.604 sec
(0024467)
fman (administrator)
2016-01-28 13:17

Now is clear that query needs a rework.
I've no ETA for this

- Issue History
Date Modified Username Field Change
2016-01-28 09:40 gapiro New Issue
2016-01-28 10:26 fman Note Added: 0024462
2016-01-28 10:40 gapiro Note Added: 0024463
2016-01-28 13:12 fman Note Added: 0024464
2016-01-28 13:14 gapiro Note Added: 0024465
2016-01-28 13:17 fman Note Added: 0024467



Copyright © 2000 - 2019 MantisBT Team
Powered by Mantis Bugtracker