Testlink and DB tuning

[gianmaria]

Hi,
recently I'm seeing low performance on TL in particular under test execution and test specification area.
Now I have a big database of tests like 16000 for many projects.
I'm not using a fast server:

RAM 1,5Gb
2 cpu 2,4 Ghz

With the IT we are trying to understand what we can do, in partucular:

- move TL on a faster server (more RAM)
- tune the actual DB/server

Can you suggest me something that we can check/change?

Thanks.

[fman]

Having just a big test projects is not a good choice, and I will try to explain why from TestLink performance point of view and for my personal point of view.

TestLink create Test Specification tree when no filter is used in lazy mode, i.e. information is requested to database only when user traverse the tree.
When filters are using, there is no other option (as far as I know) to provide to user the right tree structure from the TOP level test suites, that request the whole
info filtering it, this means lazy mode can not be used anymore.

When TestLink works with Test Plan tree, it need to create test tree structure walking from leaves to root, pruning not used branches.
If you have a Test project with 10 branches with 16K test cases, and you create a test plan with just 2K test cases, TestLink probably do lot of work to get
useless info that it is going to be discarded.

May be some of these bad effects on performance can be reduced with a different structure to map tree to DB, that is something that need lot of work and testing.

From my point of view working with a test project with 10K test cases can not be easy from a human being.
Probably the best approach (on TestLink context) is trying to create several small (2K test case max for test project) test projects.
May be best option is create test projects that resemble as much as possible what will be the test plan structure.
This way you know that all work that will be done by test link to create the test spec and test plan tree will be used for users.

OK, there is a drawback: TestLink do not provide reports that are cross test projects, but do not think this can be a great limitation.
Very interesting for me is that if there are problems with this amount of test cases on a tool (testlink or other) I suppose it will be impossible to use an spreadsheet
to manage this kind of test cases scenario.

Some users have posted contributions on mantis to try to improve performance, but I can not applied it because documentation unfortunatelly was not enough, and in additon
solution targeted only MySQL.

Any idea and real world contribution will be welcomed.

Francisco Mancardi
TestLink Team Leader

[TurboPT]

Is there any way to get some "explain query" outputs from those areas? This is done by simply adding the word "explain" before the select keyword.

The explain output does not include any user test data [as following the above link will show], but does give other DB clues/details about the query that might be helpful to improve the performance.

I don't have anything near that magnitude useful to check the "explains", but I did cross-check most of the queries, looking at the WHERE clauses, and most [~90-95% ?] seem to be covered well by id-related keys [primary, or other unique keys], but there may potentially be two-three other fields that could use a KEY definition to possibly help improve the performance.

[gianmaria]

Thanks a lot for your contribution.
I'll try to make small project instead big project.
In your opinion could be good have an opion like "test suite active"(flag)? The same as the project. It's a sort of pre-filter for the execution.
In the Test specification could be good see everythings but in the test execution/report it's enough to show only the test required (test suite active).

My 2 cents.
Gianmaria

[fman]

More options, more user confusion => do not think is a good solution.

[gianmaria]

More options, more user confusion => do not think is a good solution.

So, is it there the possibility to disable the tree?

[ansar]

From my point of view working with a test project with 10K test cases can not be easy from a human being.

We have over 50K test cases now in one project. Obviously not all test cases are executed per release cycle, but we use Priorities to manage this. The reporting shows us the percentage of each priority that has been executed.

If we switch to multiple projects per single release, then it becomes harder to manage. You have to create multiple projects per release, which requires assigning test cases to users for each project, and reassign to other testers if the original testers are unavailable, etc.

Currently, this is the only major problem I have with TestLink. I have uploaded a database with 40K testcases in http://mantis.testlink.org/view.php?id=5810.

Maybe with more people having this problem, it will get moved up on the fixit tree... pun intended.

[gianmaria]

From my point of view working with a test project with 10K test cases can not be easy from a human being.

We have over 50K test cases now in one project. Obviously not all test cases are executed per release cycle, but we use Priorities to manage this. The reporting shows us the percentage of each priority that has been executed.

If we switch to multiple projects per single release, then it becomes harder to manage. You have to create multiple projects per release, which requires assigning test cases to users for each project, and reassign to other testers if the original testers are unavailable, etc.

Currently, this is the only major problem I have with TestLink. I have uploaded a database with 40K testcases in http://mantis.testlink.org/view.php?id=5810.

Maybe with more people having this problem, it will get moved up on the fixit tree... pun intended.

To solve the problem we started to split the tests for releases. No other choice right now.

[ansar]

Yeah, the way we have our test cases laid out now is logical. As the administrator, I am the main one that sees the problem. Our testers don't currently see test cases not assigned to them, so they don't get the full brunt of the problem. I hate to make a change to the projects, which affects everyone, just to fix issues that are only seen by me.

It is getting to the point now though where something will have to give.