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

[Chuck Hagenbuch]

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