Modify

Opened 3 years ago

Closed 3 years ago

Last modified 14 months ago

#8869 closed defect (fixed)

Loading of Test Manager takes too long and sometimes time out

Reported by: nicualex@… Owned by: seccanj
Priority: normal Component: TestManagerForTracPlugin
Severity: major Keywords:
Cc: kelvin@… Trac Release: 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

Attachments (7)

testman_1st_page.png (24.3 KB) - added by anonymous 3 years ago.
mysql.log.110622 (186.3 KB) - added by anonymous 3 years ago.
mysql log of test manager database access
TopLevelCat.png (18.6 KB) - added by solarwind75 3 years ago.
top level category
ClickedOnLevel3G1.png (32.3 KB) - added by solarwind75 3 years ago.
ClickedOnHdmiFullHdCec.png (20.1 KB) - added by solarwind75 3 years ago.
ClickedOnHdmiCec.png (15.2 KB) - added by solarwind75 3 years ago.
macros.py (34.3 KB) - added by seccanj 3 years ago.
New approach using group by

Download all attachments as: .zip

Change History (35)

comment:1 Changed 3 years ago by seccanj

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

Changed 3 years ago by anonymous

comment:2 Changed 3 years ago by anonymous

  • Cc kelvin@… added

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

comment:3 Changed 3 years ago by solarwind

  • Summary changed from singapore to Loading of Test Manager takes too long and sometimes time out

comment:4 Changed 3 years ago by solarwind

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

comment:5 follow-ups: Changed 3 years ago by seccanj

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 3 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 3 years ago by anonymous

mysql log of test manager database access

comment:7 in reply to: ↑ 5 Changed 3 years ago by solarwind75

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

comment:8 Changed 3 years ago by seccanj

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 3 years ago by seccanj

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

comment:10 Changed 3 years ago by seccanj

  • Status changed from new to assigned

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

Ciao,
Roberto

Last edited 14 months ago by rjollos (previous) (diff)

comment:11 Changed 3 years ago by solarwind75

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: Changed 3 years ago by seccanj

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 3 years ago by solarwind75

top level category

Changed 3 years ago by solarwind75

Changed 3 years ago by solarwind75

Changed 3 years ago by solarwind75

comment:13 in reply to: ↑ 12 ; follow-up: Changed 3 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: Changed 3 years ago by solarwind75

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 3 years ago by solarwind75

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: Changed 3 years ago by seccanj

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 3 years ago by solarwind75

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 3 years ago by seccanj

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: Changed 3 years ago by seccanj

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: Changed 3 years ago by solarwind75

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 3 years ago by solarwind75

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: Changed 3 years ago by seccanj

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 3 years ago by seccanj

New approach using group by

comment:23 in reply to: ↑ 22 ; follow-up: Changed 3 years ago by solarwind75

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: Changed 3 years ago by solarwind75

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 3 years ago by anonymous

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

Cheers,
Kelvin

comment:26 Changed 3 years ago by seccanj

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 3 years ago by seccanj

  • Resolution set to fixed
  • Status changed from assigned to 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 3 years ago by solarwind75

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

Add Comment

Modify Ticket

Action
as closed .
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.