[horde] Database performance problem with Horde3/IMP4 with a large user base

[Luc Germain]

Hi,

This message is in reference to an old thread from last september, where I
reported a big performance problem with the permissions database queries for
a large user base. We are still in search of a solution that would allow us
to upgrade our installation to horde3...

Did any one tried to implement caching of the sharing permissions in the
session? Or, has any work been done to try to optimize the database
structure or requests so the permissions would be faster to read when there
are tens of thousands of users and shared objects?

If not, is it possible to completely deactivate the sharing of calendars,
tasks etc, in horde3 so we can avoid making those slow database queries?

We really need to upgrade from horde2/imp3 this summer... Would a bounty
help to find a solution to this performance problem? 

Thanks!!

Luc.
--
Luc Germain, analyste
Service des technologies de l'information
Universite de Sherbrooke, Sherbrooke (Quebec) Canada  J1K 2R1
email: Luc.Germain at USherbrooke.ca
 

> -----Original Message-----
> From: horde-bounces at lists.horde.org 
> [mailto:horde-bounces at lists.horde.org] On Behalf Of Chuck Hagenbuch
> Sent: Thursday, September 08, 2005 2:49 PM
> To: horde at lists.horde.org
> Subject: Re: [horde] Database performance problem with 
> Horde3/IMP4with large user base
> 
> Quoting Luc Germain <Luc.Germain at USherbrooke.ca>:
> 
> > Concerning the database performance problem reported last 
> week for horde
> > v3 with a large user base, I did some simulations on our 
> database, and
> > I can confirm that the requests that generate a heavy load on the
> > database server are those that evaluate permissions on the datatree
> > table, like this one:
> >
> > SELECT c.datatree_id, c.datatree_name FROM horde_datatree c 
> LEFT JOIN
> [etc]
> 
> > Apart from redesigning the database, the only way I see to 
> support lots
> > of concurrent users would be to cache the results of these 
> permissions
> > evaluation requests in the user session for some 
> configurable time. Is
> > it a solution that would be acceptable to horde developers? 
> Would it be
> > easy to implement?
> 
> First, as Kevin mentioned, History has been moved out of the DataTree 
> tables (see http://bugs.horde.org/ticket/?id=2298), which speeds up 
> history lookups a lot, and also empties a _lot_ of data out of the 
> datatree tables. That's a start. It might be possible to do the same 
> thing for permissions and/or shares. It'd be tricky but 
> perhaps at this 
> point worth it.
> 
> I would love to work on this but I'm extremely busy right now. If 
> someone wanted to do code for it, just look at the History 
> changes and 
> send it in.
> 
> As for caching, caching in the session would be pretty easy; if we're 
> careful it shouldn't even affect session size too much. The flip side 
> is that permissions changes would take a logout/login cycle 
> to show up 
> - would that be acceptable to people? (yes, if you initiate 
> the change 
> yourself, it can be flushed from _your_ session, but flagging other 
> sessions would be tricky and might end up losing us the performance 
> gains anyway).
> 
> -chuck
> 
> -- 
> "But she goes not abroad in search of monsters to destroy." - John 
> Quincy Adams
> -- 
> Horde mailing list - Join the hunt: http://horde.org/bounties/#horde
> Frequently Asked Questions: http://horde.org/faq/
> To unsubscribe, mail: horde-unsubscribe at lists.horde.org
> 
>