Roundup Tracker - Issues

Issue 2550702

classification
Title: The default search should not crawl on users
Type: resource usage Severity: normal
Components: Database, Web interface Versions: 1.4
process
Status: new Resolution:
Dependencies: Superseder:
Assigned To: Nosy List: ber, lu_zero, rouilj
Priority: Keywords:

Created on 2011-04-30 21:32 by lu_zero, last changed 2013-10-12 05:40 by rouilj.

Messages
msg4303 Author: [hidden] (lu_zero) Date: 2011-04-30 21:32
The default search looks up usernames twice using the search_select macro,
due our horrible orm/database mapping that involves using string compare
in python over all the users and some not so simple joins.
Would be nice provide a better default while the database issue isn't fixed.
msg4611 Author: [hidden] (ber) Date: 2012-08-21 08:04
Hi Luca,
just discovered your report.
Can you tell me a little bit more about what you have already
found out about it?

Which searches in particular show the unwanted behaviour?
Searching for some user names or all searches?
Did you try different database backends or just one? Which ones?

Thanks,
Bernhard
msg4613 Author: [hidden] (lu_zero) Date: 2012-08-21 08:32
On 8/21/12 10:04 AM, Bernhard Reiter wrote:
> Which searches in particular show the unwanted behaviour?
> Searching for some user names or all searches?
> Did you try different database backends or just one? Which ones?

Given the database layout is the cause any search matching username has 
the problem on any backend.

I used to use roundup for for a large project and this, among other 
issues made us move to something else.
msg4616 Author: [hidden] (ber) Date: 2012-08-21 11:13
A good to know!
I'd be interested in your experiences, e.g. what is "big"
and "other things". Ideally on the -devel list. :)
Especially: What would we need to improve to make roundup fit your needs?
Which version did you try and which database?

(You probably know that PosgresQL or MySQL is recommended for people
with many issues.)
msg4618 Author: [hidden] (lu_zero) Date: 2012-08-21 11:32
On 8/21/12 1:13 PM, Bernhard Reiter wrote:
>
> Bernhard Reiter added the comment:
>
> A good to know!
> I'd be interested in your experiences, e.g. what is "big"
> and "other things". Ideally on the -devel list. :)

Big -> anything bigger than 20-30 would slow to a crawl, we are talking 
about bicubic complexity for nothing. (nested lookup+ string comparison)

> Especially: What would we need to improve to make roundup fit your needs?

1- have a stock setup not requiring to edit TAL to be on par with the 
stock bugzilla
2- have a stock setup not so slow that it is unusable on the same system 
bugzilla (or even trac) can run decently
3- have the wsgi deploy in a canned recipe easy to be followed
4- have a way to import/export to bugzilla xml.

Not something that could be done overnight I guess ^^;

> Which version did you try and which database?

I used the mysql backend but it is completely unrelated to the problem 
at hand.

The database abstraction is/was completely bogus. Something like 
sqlalchemy declarative base probably could replace that faulty component 
with minor disruption. (then I could point that the whole "store the 
messages in the filesystem" requiring a little unsettling)

Chameleon probably would make the web ui work faster even if tal is sort 
of hard to follow compared to genshi or jinja

lu
msg4620 Author: [hidden] (ber) Date: 2012-08-21 13:40
Thanks again for the feedback.
What is intersting is that we are using roundup with many more issues
and users, which makes me believe there may be something different
to your setup.

As towards database connector and template engine:
* Roundup predates a number of "modern" pythonic database layers
and it was already discussed if we wanted to give up this flexibility
which we have now, as it seems to be basically working (of course not
counting your crawling experience). 
* Since roundup 1.4.20 there is _experimental_ support for Chameleon.
msg4621 Author: [hidden] (lu_zero) Date: 2012-08-21 15:39
On 8/21/12 3:40 PM, Bernhard Reiter wrote:
>
> Bernhard Reiter added the comment:
>
> Thanks again for the feedback.
> What is intersting is that we are using roundup with many more issues
> and users, which makes me believe there may be something different
> to your setup.

I hope so, it was an old 1.4 updated till 1.4.12

> As towards database connector and template engine:
> * Roundup predates a number of "modern" pythonic database layers
> and it was already discussed if we wanted to give up this flexibility
> which we have now, as it seems to be basically working (of course not
> counting your crawling experience).

I can iterate as many time that the root problem is the schema being 
unfit for large number of users (1646) and large number of users in 
specific roles (the whole security check involves a number of ill 
conceived python mediated "join" between tables).

That means for a quasi-stock setup in order to populate a normal issue 
you end up going over that table a number of time and doing that on 
python while computing the template (e.g. to have a list of developers)

> * Since roundup 1.4.20 there is _experimental_ support for Chameleon.

wonderful.
msg4690 Author: [hidden] (rouilj) Date: 2012-12-01 03:35
The standard way to handle this is to add a caching mechanism to
the templating engine so we only have to grab the data from the
database via the ORM once per page creation.

 
http://pko.ch/2008/08/22/memoization-in-python-easier-than-what-it-should-be/

  http://wiki.python.org/moin/PythonDecoratorLibrary#Memoize

  https://bitbucket.org/zzzeek/dogpile.cache or

for Memoize with a timeout:  http://pypi.python.org/pypi/gocept.cache

The problem with this is we would have to delete the items
in the cache when they get changed in the database.
However I think every access to roundup opens the database
and should provide a consistent value for every access to
the database.

So if it can be added to the database get functions, the
cache gets destroyed when the database object is destroyed
which is at the end of every page view.

Replacing the ORM may be possible but one of the nice things about
the current ORM is that you can change the database simply by
changing the schema in the application without having to issue DDL
statements directly to the database (in SQL etc).
msg4773 Author: [hidden] (ber) Date: 2013-01-24 08:10
John, possibly there should be an easier solution than changing the core
architecture by replacing the ORM. :) At least a workaround.
msg4806 Author: [hidden] (rouilj) Date: 2013-02-24 05:39
Might the mechanism discussed in:

http://www.roundup-tracker.org/cgi-bin/moin.cgi/UserMenu

be the workaround?

Basically it uses a detector to generate the html
statically on user changes and defines a metal macro
that can just be substituted when the page is rendered.

So it takes the generation of the list out of the flow
of displaying the page.
msg4938 Author: [hidden] (rouilj) Date: 2013-10-12 05:40
There is some discussion on how to improve this situation occurring on
the roundup-devel list.

See:

  https://sourceforge.net/p/roundup/mailman/message/31511901/

titled: warm_cache call initial results and failures

for a prototype of a mechanism to replace many single
item loads with a batch item load that provides a speedup.

Also see:

  https://sourceforge.net/p/roundup/mailman/message/31511633/

titled: Re: load spikes due to user lookup

for tuning to make sure that the rdbms cache is correctly tuned for
your workload along with background discussion on the thread
that lead to the warm_cache implementation.
History
Date User Action Args
2013-10-12 05:40:03rouiljsetmessages: + msg4938
2013-02-24 05:39:52rouiljsetmessages: + msg4806
2013-01-24 08:10:52bersetmessages: + msg4773
2012-12-01 03:36:00rouiljsetnosy: + rouilj
messages: + msg4690
2012-08-23 02:40:41rouiljsettype: resource usage
components: + Database
versions: + 1.4
2012-08-21 15:39:24lu_zerosetmessages: + msg4621
2012-08-21 13:40:36bersetmessages: + msg4620
2012-08-21 11:32:26lu_zerosetmessages: + msg4618
2012-08-21 11:13:44bersetmessages: + msg4616
2012-08-21 08:32:39lu_zerosetmessages: + msg4613
2012-08-21 08:04:12bersetnosy: + ber
messages: + msg4611
2011-04-30 21:32:01lu_zerocreate