Loading of Test Manager takes too long and sometimes time out

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

Hi Roberto, thanks for your prompt reply.

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

Hi Roberto,

I have installed your latest version 1.4.5. The CSV importing works great!

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

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

Hi Robert,

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

Thanks! Kelvin

mysql log of test manager database access

Replying to seccanj: Hi Robert,

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

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

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.

Then restart your server.

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

Ciao, Roberto

Note: to download the macros.py file in the original format, use the following link:

http://trac-hacks.org/attachment/ticket/8869/macros.py?format=raw

Ciao, Roberto

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

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

top level category

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

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

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

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

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

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

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

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

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

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

New approach using group by

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

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

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

Cheers, Kelvin

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

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

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