Opened 7 years ago

Closed 6 years ago

# Loading of Test Manager takes too long and sometimes time out

Reported by: Owned by: nicualex@… Roberto Longobardi normal TestManagerForTracPlugin major solarwind 0.12

### Description

first of all , your plugin is very useful and much appreciated. we do have an issue though.

we've installed 1.4.4 and we've started to add existing test cases (from a previous system). we have about 3000 now. when trying to access the Test Manager tab , it is painfully slow to load ... most of the time, the browser is timing out. Once we're in , navigating the test catalogues and cases is alright.

I should say that the rest of our Trac is working perfectly.

I was wondering if you've seen this before and have any hints for us on how to fix it.

cheers, alex

### comment:1 Changed 7 years ago by Roberto Longobardi

Hi Alex, I haven't been reported performance issues so far, you are the first.

So the problem seems related to only the base catalogs list. Once browsing a single catalog, everything is ok, right?

What about showing only the top catalog names in the first page? Would this be acceptable?

By the way, in 1.4.5 I've added a test case importing feature you may want to look at.

Let me know. Ciao, Roberto

### comment:2 Changed 7 years ago by anonymous

i'm not entirely sure i understand your suggestion (about the top catalog names in the first page). i've attached a snapshot of our main page , for better understanding. I've also added my colleague Kelvin in the loop. He's in a much better position to comment on this topic. I believe he just installed the new version , i haven't had the chance to try it yet.

cheers, Alex

### comment:3 Changed 7 years ago by solarwind

Summary: singapore → Loading of Test Manager takes too long and sometimes time out

### comment:4 Changed 7 years ago by solarwind

Hi Roberto,

I believe we are already only displaying the top catalog names as Alex mentioned(as shown in the attachment) Some additional info - I am using apache server, with MySQL backend. I am using trac.fcgi. When I access test manager catalog, I see trac.fcgi taking up 100% of CPU usage on my machine.

However I have no problems accessing ticket reports during which trac.fcgi takes up less than 2% of CPU usage

Not sure what is causing the discrepancy in performance? Any ideas or solutions would be welcome.

Thanks, Kelvin

### comment:5 follow-ups:  6  7 Changed 7 years ago by Roberto Longobardi

Hi Kelvin, the suggestion about showing only the catalog names in the root page was actually kind of a proposal on how to change the plugin's code to run faster :D

Currently the catalogs are yes collapsed, but anyway their contents have already been loaded in the browser. There is no optimization in here.

I see two alternatives:

1. Less effort required: show only the catalog names in the root page, not expandable. You must click on the catalog itself to navigate to its page ans see its contents. This behavior may be activated by an option in the trac.ini file.
2. With more effort I may add the logic to load each top-level catalog's contents only when someone clicks on it.

I think I may do number 1 pretty quickly. Do you think it may be OK?

Ciao, Roberto

### comment:6 in reply to:  5 Changed 7 years ago by solarwind

Hi Robert,

Yes, that is good enough for me, I can check the new version to see if the performance is improved

Thanks! Kelvin

### Changed 7 years ago by anonymous

mysql log of test manager database access

### comment:7 in reply to:  5 Changed 7 years ago by solarwind

Some extra info for you:

A root catalog by itself can already consist of a few thousand test cases, and it contains subcatalogs which may contain hundreds of test cases. It would help if the sub-catalogs are taken care of as well (i.e. only load the subcatalog contents when clicked)

Also I found from mysql log that the database access is done one wikipage at a time, refer to mysql.log.110622, and in each second there are anywhere between 20 to 30 queries to mysql, so if I have a few thousand test cases, it would take a few minutes for the test catalog to load. And if there are a few concurrent users, you can imagine that many users will experience the webpage timing out.

It seems that you are using the trac API WikiPage to access the database to generate your html, which can only be done one test case at a time. Any chance you can somehow query the data you need at one go using trac.db API instead?

Am not sure if it would work though, just thinking aloud

Thanks again, Kelvin

### comment:8 Changed 7 years ago by Roberto Longobardi

Hi Kelvin, thanks for your problem analysis, I couldn't find the time to face this ticket yet (I focused on smaller/easier tickets instead :D).

You're right, I currently use th Wiki API to query catalogs and test cases, and I had no idea it was so poorly optimized!

I will try and use the db API directly, and see how it goes.

Ciao, Roberto

### comment:9 Changed 7 years ago by Roberto Longobardi

Hi Kelvin, I have found a very simple way of fixing this, using the db api as you suggested.

You can fiund in attachment the modified macros.py file, to be replaced into the "testman4trac/trunk/testmanager/" directory.

Build the whole plugin and replace the "TestManager-1.4.6-py*.egg" file into your Trac server's "plugin" directory.

Please, let me know if this speeds up things or not.

Ciao, Roberto

### comment:10 Changed 7 years ago by Roberto Longobardi

Status: new → assigned

Ciao, Roberto

Last edited 4 years ago by Ryan J Ollos (previous) (diff)

### comment:11 Changed 7 years ago by solarwind

Hi Robert,

That's fast! I have tried your changes, the webpage load speed has definitely improved. MySQL database access has been much reduced also. There is a bug though... The top level catalog names are wrong. However, when I click on a top level catalog name to go to its wiki page, the catalog name there is correct

The subcatalog names are fine though, just the top level ones are wrong

Thanks again and cheers, Kelvin

### comment:12 follow-up:  13 Changed 7 years ago by Roberto Longobardi

Hi Kelvin, I'm glad it works :D

Actually I can't reproduce your problem with the top catalog names... I've tried with Chrome, IE7 and Firefox 3.6, so I guess it's not a browser-related problem.

Could you attach a couple of screenshots, one of the top-level list and one of a top-level catalog once opened?

I suspect it may have something to do with the catalog names.

Ciao, Roberto

### Changed 7 years ago by solarwind

top level category

### comment:13 in reply to:  12 ; follow-up:  14 Changed 7 years ago by anonymous

Hi Roberto,

Thanks for the quick response. I have attached the Images

First when I access the top level TopLevelCat.png is shown. The category names are displayed wrongly. When I click on the category "Level 3 G1", ClickedOnLevel3G1.png is shown. Then when I click on "HDMI, FULL HD, CEC", ClickedOnHdmiFullHdCec.png is shown. Finally when I click on "HDMI CEC", I get this ClickedOnHdmiCec.png

Regards, Kelvin

### comment:14 in reply to:  13 ; follow-up:  15 Changed 7 years ago by solarwind

Hi Roberto,

Just a thought, my colleague commented the test cases seems to be from some time back and did not incorporate the latest changes, perhaps the test cases used to generate the tree etc are not using the latest test case revisions?

Cheers, Kelvin

### comment:15 in reply to:  14 Changed 7 years ago by solarwind

Hi Roberto,

In the macro.py, I notice that the line

sql = "SELECT name, text FROM wiki WHERE name LIKE '%s%%' AND version=1 ORDER BY name" % curpage

may be causing the problem by only selecting version 1 wikipages?

Cheers, Kelvin

### comment:16 follow-up:  17 Changed 7 years ago by Roberto Longobardi

Hi Kelvin, I can't teach you anything, you're too smart :D

You're right, I did that to only get one wiki page verison, completely forgetting that the version is there to do something :D

Please find in attachment the updated macro.py file, with a fixed query, and let me know how it goes.

Ciao, Roberto

### comment:17 in reply to:  16 Changed 7 years ago by solarwind

Hi Robert,

Unfortunately I got this error:

Oops…
Trac detected an internal error: NotSupportedError: (1235, "This version of
MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")
This is probably a local installation issue.


I was running mysql 5.1, which I have upgraded to 5.5 but still face the same issue

I then tried what I think would be similar to what you are trying to do (I think) by changing the following

    for subpage_name, text in _list_matching_subpages(env, curpage+'_'):
subpage_title = get_page_title(text)


to...

    prev_page_title_result = ''

for subpage_name, text in _list_matching_subpages(env, curpage+'_'):
subpage_title = get_page_title(text)
# only get latest revision of wiki page
if(prev_page_title_result==subpage_title):
continue;
prev_page_title_result = subpage_title


In this case. Only the top level catalog is ok, but the subcatalogs are still incorrect

Cheers, Kelvin

### comment:18 Changed 7 years ago by Roberto Longobardi

Wow! SQLite supports it and MySQL doesn't... O_o

Your code may work, but still requires the python code to go through all of the records...

Let me try and find a solution that delegates the work to the database.

I'll let you know.

Ciao, Roberto

### comment:19 follow-up:  20 Changed 7 years ago by Roberto Longobardi

Yet another fixed query.

I could only try it on SQLite, but according to here, this porkaround :D should work on MySQL.

Please, let me know if they're right.

Ciao, Roberto

### comment:20 in reply to:  19 ; follow-up:  21 Changed 7 years ago by solarwind

Hi Roberto,

I actually came across the post also and tried it on Friday but it didn't work. Maybe my query was wrong. I'll try with your macros.py on Monday and let you know.

Thanks again, Kelvin

### comment:21 in reply to:  20 Changed 7 years ago by solarwind

Hi Roberto,

I got this error with the new macros.py

Oops… Trac detected an internal error: OperationalError: (1054, "Unknown column 'w.name' in 'where clause'") This is probably a local installation issue.

Any ideas?

Cheers, Kelvin

### comment:22 follow-up:  23 Changed 7 years ago by Roberto Longobardi

Hi Kelvin, I'm now getting the same error... not sure what happened since I made it work...

Anyway, I changed the approach and found a better and simpler way of doing the query, which works fine in my env.

Please find in attachment the new macros.py and let me know :D

Ciao, Roberto

### Changed 7 years ago by Roberto Longobardi

New approach using group by

### comment:23 in reply to:  22 ; follow-up:  24 Changed 7 years ago by solarwind

Hi Roberto,

There is syntax error, but the generated tree is still incorrect. I tried with your most recent macros.py, as well as using the new query with your previous macros.py (I noticed there were many changes in your previous macros.py which was not reflected in this most recent one)

Both results are same as my comment 13, the wikipage versions used seem to be wrong

Cheers, Kelvin

### comment:24 in reply to:  23 ; follow-up:  25 Changed 7 years ago by solarwind

Sorry, I meant there is NO syntax error, but wikipage versions used for the tree are still incorrect

### comment:25 in reply to:  24 Changed 7 years ago by anonymous

The query results seems ok though, so probably it is somewhere else

Cheers, Kelvin

### comment:26 Changed 7 years ago by Roberto Longobardi

Kelvin, the fix works in my env...

Could you please double check the catalog titles?

The problem with the catalog titles was partly caused by the version and partly by the trailing clause "ORDER BY name", which MUST be there for the algorithm to work correctly.

Please le me know. Ciao, Roberto

### comment:27 Changed 6 years ago by Roberto Longobardi

Resolution: → fixed assigned → closed

Fixed in 1.4.7, with a better query that works on any supported database.

I encourage you to try it out and let me know how it works for you.

Ciao, Roberto

### comment:28 Changed 6 years ago by solarwind

Hi Roberto,

Sorry for the late reply, was busy with other stuff. I have tried your latest release 1.4.7 and it finally works now! Previous versioning problems are ok now. I'll check with previous test source code to see what was the difference

Thank you very much, you've been most helpful!

Cheers, Kelvin

### Modify Ticket

Change Properties
Action
as closed The owner will remain Roberto Longobardi.
The resolution will be deleted.